Saturday, February 25, 2017

SQL Server Default and Rule objects - Should not I use them now?

In order to make sure that the database contains high quality data, we ensure data integrity with our data that refers to the consistency and accuracy of data stored. There are different types of data integrity that can be enforced at different levels of solutions. Among these types, we have three types called Domain, Entity and Referential Integrity that are specific to database level for enforcing data integrity.

For enforcing Domain Integrity, SQL Server has given two types of objects called Default and Rule. We have been using these objects for handling Domain Integrity but now it is not recommended to use these for enforcing Domain Integrity.

Let's try to understand what these objects first and see the usage. Default object can be used for creating an object that holds a default value and it can be bound to a column of the table. Rule is same as Default and it creates an object for maintaining rules for columns. See below code as an example.

USE tempdb;

-- creating default object
CREATE DEFAULT CreditLimitDefault AS 10000;

-- creating a sample table
CREATE TABLE dbo.Customer
 CustomerId int PRIMARY KEY
 , LastName varchar(50) NOT NULL
 , CreditLimit decimal(16,4) NOT NULL

-- Binding the default to a column
-- The object can be bound to many tables
EXEC sp_bindefault 'CreditLimitDefault', 'dbo.Customer.CreditLimit';

-- creating rule object
CREATE RULE CreditLimitRule AS @CreditLimit > 9000;

-- Binding the rule to a column
-- The object can be bound to many tables
EXEC sp_bindrule 'CreditLimitRule', 'dbo.Customer.CreditLimit';

As you see, above code creates two objects, CreditLimitDefault and CreditLimitRule that are Default and Rule objects. These objects can be assigned to any column in any table.

As I mentioned above, it is not recommended to use them now as they are deprecated. It is recommended to use Default and Check constraints instead.

Read more on CREATE DEFAULT at:

No comments: