BEGIN TRANSACTION – Marks the Start point of implicit or explicit transaction.

COMMIT TRANSACTION – Marks the end of a successful implicit or explicit transaction.

ROLLBACK TRANSACTION – rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction.

SAVE TRANSACTION – sets a savepoint within a transaction.

MS SQL Statement Execution Order / Logical Query Processing Phase

MS SQL Server always executes statement logically in a predefined manner which is called as Logical Query Processing Phase (like in mathematics a statement with (a+b*c) executes the ‘b*c’ first & add then add ‘a’ with its result). The order of execution in SQL Server is mentioned below

Like this:

Microsoft on 31 March 2016, started distributing the full working Developer version of SQL Server 2014 & onward versions for free for learning & practice. Anyone can now register himself and download the full working version of SQL Server for learning & practice not for commercial purpose.

Earlier Microsoft SQL Server Express Edition is available for free with lots of limitation.To learn, practice & run all the SQL examples provided in this blog will now be easier for all. Just click on the above link and follow the steps to get the free SQL Developer version.

A single constraint can be attached with multiple tables and columns in the table.

The datatype of the RULE & the column where it is going to be attach must be same or compatible. In case the datatype are different it won’t give any error until any insert / update statement is issued.

It is similar to CHECK constraint.

Only 1 Rule can be applied at a time in a column.

Binding any RULE with a column will override any existing RULE attached with the column. It’ll never give any warning or error but simple override the existing RULE and applied the newer one.

It cannot be altered after defining. It need to be drop and re-create with the new definition.

To apply the constraint, it need to be bind with the column.

To drop the constraint, it need to unbind from all the column of the table.

As per Microsoft, it is not a recommended practice to use it, as it’ll be removed from the future version of MS SQL Server.Click here for MSDN detail.

It always includes a conditional expression or function that returns a Boolean value.

It can be applied on column to restrict the entry to specific type of value or in-between the specified boundaries that are accepted by one or more columns.

It can be defined on table or temp table (both local & global) or table variable.

It can be defined with Primary key,Unique key,Foreign key (though defining with Foreign key doesn’t make any difference as it’ll always check against the referencing Primary key)

Single CHECK constraints can be applied to a single column.

Multiple CHECK constraints can be applied to a single column.

Single CHECK constraint can be applied to multiple columns.

When applied with function, the function must return a single value.

To modify the CHECK constraint, we first need to delete the existing CHECK constraint and create a new CHECK constraint with new value.

Column with CHECK constraint cannot be deleted or modified. The CHECK constraint must be deleted first to make the changes in the column.

CHECK constraint can applied for 3 properties in a table

Check for existing data when it is created on existing column in a table with data.So that all the existing rows of column must satisfy the CHECK constraint Boolean condition.If it not, then SQL throws an error and it cannot be applied.

Check for Insert / Update. So all the new inserts & updates will follow the its rule.

Check for replication. Replication is a process in SQL where a copy of the table / database is created for backup. So any update made by replication engine must also follow its rule.

Limitation of the CHECK constraint

It does not check against NULL value.Boolean value is always produce 3 types of values True or False or Unknown ( when comparing NULL values). In case of Unknown value the Check constraint will be overridden and insert / update will be successfully done.

Disable the existing CHECK constraint : After disable the check validation is removed from the column until it is enabled again.

ALTER TABLE [dbo].[CheckTbl] NOCHECK CONSTRAINT [CK_ID]

Enable the existing disabled CHECK constraint without validating the existing value in the column against the check validation.

ALTER TABLE [dbo].[CheckTbl] CHECK CONSTRAINT [CK_ID]

Enable the existing disabled CHECK constraint and also validate the existing value in the column against the check validation. If any rows fails to validate then SQL throws an error and the CHECK constraint will not be enabled.

ALTER TABLE [dbo].[CheckTbl] WITH CHECK CHECK CONSTRAINT [CK_ID]

Remove the CHECK constraint

ALTER TABLE [dbo].[CheckTbl] DROP CONSTRAINT [CK_ID]

To get all information about all the CHECK constraints defined in all the tables in the current database. (Note : CK= CHECK Constraint)

NOT NULL constraint once added with a column will not allow the column to store the NULL value.

NOT NULL constraint can be added with column added to existing table with / without data / during table definition creation.

NOT NULL constraint can be remove from the column at anytime.

Column set to PRIMARY key is always NOT NULL.It never accepts NULL value.Primary key automatically makes the column NOT NULL even it was not declared while creation.

Column set to UNIQUE key allows a single row with NULL value by default. Until we specify the NOT NULL constraint with the UNIQUE constraint.

Column set to FOREIGN key never allows a NULL value by default.It accepts NULL value only if WITH SET NULL cascade property is set with FOREIGN key definition.

Though SQL Server treated NOT NULL as a Domain constraint, but it never allocates any name for its definition. We cannot find either by procedure ‘sp_helpconstraint’ or under the Constraints group inside the table.

Two NULL values cannot be compared by using Equal operator (=) in between them.(Default property)

Comparing 2 NULL values with Equal operator (=) can be done by setting the property SET ANSI_NULLS OFF. Note : Microsoft SQL Server is going to remove in future version. For more details pleaseclick here for MSDN.

NULL values can be check with either IS NULL, IS NOT NULL,ISNULL(),COALESC().

Any new column will be automatically set to NULL value by default either created with the table or adding a new column to existing table.

Default constraint can have NULL value.

Examples

create table City(Id int primary key,name varchar(50))

–Example1: Get all the records where City where name is NULLSelect * from City where ISNULL(name,”)=”–ORSelect * from City where COALESCE(name,”)=”–ORSelect * from City where name IS NULL

–Example2: Get all the records where City is NOT NULLSelect * from City where ISNULL(name,”)<>”–ORSelect * from City where COALESCE(name,”)<>”–ORSelect * from City where name IS NOT NULL

–Example3: Use SET ANSI_NULLS OFF to get all the city where name is NULL. It is not recomended practice to use it by MS BOL.SET ANSI_NULLS OFFSelect * from City where name=NULL

–Both the below statement is equivalent and insert default NULL value in the tableinsert into test DEFAULT values–ORinsert into test values(NULL)

NULL values can be remove by 2 ways

By updating the NULL valued column with Update statement.

By updating the column property, apply the NOT NULL constraint and provide new value to all the rows where the column is having the NULL value. Note : If the database is very large and consists of many NULL values in the column, need to check the fragmentation & log file size of the table before & after replacement of the NULL values.

Example

–To Get the log file size of all the DatabaseDBCC SQLPERF(logspace)

–To get the size of the dbSELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name as DB_Path, (size*8)/1024 SizeMB,((size*8)/1024)/1024 SizeGBFROM sys.master_files