A rules engine is a schedule based data validity application that typically runs as a meta-layer on top of an OLTP application. It fires a set of queries (defined as rules) which determine whether the underlying data elements comply with a specific rule’s definition. The compliance to the rule is then recorded indicating a pass or fail. Determining the object to report on The first step is to understand the main object of data you are monitoring. This could range from monitoring customers, loans, employees.. Basically any object that has data surrounding it can be monitored. This object will then be the focus of the rules engine report. Sometimes the data being monitored may be composed of more than one object. You may want to monitor the data surrounding customers at certain store locations. In which case, the granularity of your application will be customer-location combinations. In our example we will monitor customers for our retail store AlphaBeta. The rule data we want to collect falls within 3 categories: Failure to Pay Significant Purchases Returns These 3 categories are parent categories for a series of “Rules”. These rules are actually stored procedures that check for specific conditions surrounding our customers. Here are the rules in each category: Failure to pay: Rule 1 – Has failed credit card authorizations within 30 days Rule 2 – Insufficient funds outstanding Significant purchases: Rule 1 – Order amount over $500 within 30 days Rule 2 – Alcohol purchase over $200 […]
Continue reading ...
This seems to be the easiest way to kill all connections (sessions) on a SQL Server database: [cc lang=”sql”] ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE [/cc] After executing the above, it will put the database in single user mode. So you then need to remove single user mode using: [cc lang=”sql”] EXEC sp_dboption ‘database_name’, ‘single user’, ‘FALSE’ [/cc]
Continue reading ...
A very powerful yet obscure feature in SQL is the ability to have parts of the where clause conditionally execute. Most often developers will make duplicate copies of an entire query to facilitate two different variable choices. Below is an example stored procedure to return employees that has an optional parameter to return employees based on GenderType. If no values is passed into the parameter, all employees will be returned. If the parameter is filled it will return the employees of that gendertype. Many developers will write the query this way: [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN IF @GenderType IS NOT NULL BEGIN SELECT * FROM dbo.Employees e WHERE e.GenderType = @GenderType END ELSE BEGIN SELECT * FROM dbo.Employees e END END [/cc] As you can see, the same query is repeated twice. This accounts for twice the maintenance and two different execution plans being generated (depending on which version of SQL Server you are using). Now we’ll write the same query with a conditional where clause [cc lang=”sql”] CREATE PROCEDURE spGetEmployees ( @GenderType varchar(20) = NULL ) AS BEGIN SELECT * FROM dbo.Employees e WHERE (@GenderType IS NULL OR e.GenderType = @GenderType) END [/cc] What we did here is utilize the power of the OR statement in the Where clause. In an or statement if the left side of the OR statement evaluates to true, then the second side is not evaluated. So when no value is entered for @GenderType, then the […]
Continue reading ...
To add a constraint to an existing table use the alter table statement with the add constraint command. There are four different types of constraints: Primary Key Constraints – Enforces unique values for specified column, can be referenced. Foreign Key Constraints – Enforces a reference to a primary key Unique Constraints – Ensures unique values within a column Check Constraints – Limits values acceptable for a column based on an evaluation Add unique constraint [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique UNIQUE (President) [/cc] Add constraint to test value [cc lang=”sql”] ALTER TABLE dbo.Presidents ADD CONSTRAINT President_unique CHECK (YearsInOffice >= 0 AND YearsInOffice < 13) [/cc]
Continue reading ...
The maximum value for an Bigint in SQL Server is: -9223372036854775808 through 9223372036854775807 And the byte size is 8 bytes. Here is the proof (Thanks to BJ) [cc lang=”sql”] DECLARE @max bigint, @min bigint SELECT @max = 127, @min = 1 WHILE @min = 1 BEGIN BEGIN TRY SELECT @max = @max * 2 + 1 END TRY BEGIN CATCH BEGIN TRY SET @min = -1 – @max END TRY BEGIN CATCH SET @min = 0 END CATCH END CATCH END SELECT @min , @max [/cc] other maximum values: Int: -2147483648 through 2147483647 (4 bytes) SmallInt: -32768 through 32767 (2 bytes) TinyInt: 0 through 255 (1 byte)
Continue reading ...
