Types of Statement in SQL

In this blog, we’ll discuss about Primary division of all the statements in Microsoft SQL Server.

All the statements in MS SQL Server is primarily divided into below 4 parts

S.No. Type SQL Statement
1 DDL

(Data Definition Language)

CREATE – to create objects in the database
ALTER – alters the structure of the database
DROP – delete objects from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
Enable DDL Trigger – to enable / activate an existing disable DDL trigger
RENAME – rename an object
UPDATE STATISTICS – used to update statistics on a table or indexed view.
 
2 DML

(Data Manipulation Language)

SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – deletes all records from a table, the space for the records remain
MERGE – Perform operation :insert or update or delete
BULK INSERT – imports a data file into a database Table or View in a user-specified format.
READTEXT – reads text, ntext, or image values from a text, ntext, or image column
UPDATETEXT – updates an existing text, ntext, or image field.
WRITETEXT – permits minimally logged, interactive updating of an existing text, ntext, or image column.
 
3 DCL

(Data Control Language)

GRANT – Provide specific access rights (like view/alter/define) to users
REVOKE – withdraw specific access rights (like view/alter/define) from users
EXECUTE AS, statement – sets the execution context of a session.
EXECUTE AS, clause – With UDF(except inline table-valued functions), UD_Procedures, and UD_triggers.
 
4 TCL

(Transaction Control Language)

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

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE / ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

Advertisements

Microsoft SQL Server Version History

In this blog, we’ll discuss about version history of the MS SQL Server.

Version Year Release name Code name
1.0 (OS/2) 1989 SQL Server 1.0 (16-bit) Ashton-Tate / Microsoft SQL Server
1.1 (OS/2) 1991 SQL Server 1.1 (16-bit)
4.2A (OS/2) 1992 SQL Server 4.2A (16-bit)
4.2B (OS/2) 1993 SQL Server 4.2B (16-bit)
4.21a (WinNT) 1993 SQL Server 4.21a SQLNT
6 1995 SQL Server 6.0 SQL95
6.5 1996 SQL Server 6.5 Hydra
7 1998 SQL Server 7.0 Sphinx
1999 SQL Server 7.0 OLAP Tools Palato mania
8 2000 SQL Server 2000 Shiloh
8 2003 SQL Server 2000 64-bit Edition Liberty
9 2005 SQL Server 2005 Yukon
10 2008 SQL Server 2008 Katmai
10.25 2010 Azure SQL database Cloud database or CloudDB
10.5 2010 SQL Server 2008 R2 Kilimanjaro (aka KJ)
11 2012 SQL Server 2012 Denali
12 2014 SQL Server 2014 SQL14
13 2016 SQL Server 2016
14 2017 SQL Server 2017 Helsinki

The above list details are taken from Wiki link. Click here for the original Wiki link.

Free MS SQL Server Developer Version

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.

Click here for more details and download link

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.

It’ll include complete package of SQL Server , SSAS, SSRS & SSIS.

Rule Constraint

In this blog, we’ll discuss about RULE constraint.

  1. It is defined in the database level.
  2. It storage location is Database=>Programmability=>Rule.
  3. It provides a way of code re-usability.
  4. A single constraint can be attached with multiple tables and columns in the table.
  5. 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. 
  6. It is similar to CHECK constraint.
  7. Only 1 Rule can be applied at a time in a column.
  8. 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.
  9. It cannot be altered after defining. It need to be drop and re-create with the new definition.
  10. To apply the constraint, it need to be bind with the column.
  11. To drop the constraint, it need to unbind from all the column of the table.
  12. 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.

Example

–Define the RULE constraint
CREATE RULE Rule_Int_Positive AS @A > 0

create table test
(
Id int,
Gender varchar(10)
)

–Bind the RULE constraint
EXEC sp_bindrule Rule_Int_Positive, ‘test.Id’

–Error on issuing the below insert statement
insert into test values(0,’Male’)

–Successfully inserted
insert into test values(1,’Male’)

Select * from test

/* Output
Id Gender
1 Male
*/

–UnBind the Rule or remove the constraint from the column
EXEC sp_unbindrule ‘test.Id’

–Delete the RULE constraint
Drop RULE Rule_Int_Positive

–Complex RULE to store the PinCode in the format : 123 456-78 or UNKNOWN
CREATE RULE Rule_PinCode
AS
(@a=’UNKNOWN’) OR
(
LEN(@a)=10
AND ISNUMERIC(LEFT(@a,3))=1
AND SUBSTRING(@a,4,1)=’ ‘
AND ISNUMERIC(SUBSTRING(@a,5,3))=1
AND SUBSTRING(@a,8,1)=’-‘
AND ISNUMERIC(RIGHT(@a,2))=1
)

To view all the RULES defined in the current database

SELECT
OBJECT_NAME(sc.rule_object_id) AS RuleName
,OBJECT_NAME(sc.object_id) AS TableName
,sc.name AS ColumnName
FROM
sys.objects so
INNER JOIN
sys.columns sc
ON
so.object_id = sc.rule_object_id
WHERE
so.type_desc = ‘rule’

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

CHECK Constraint

In this blog, we’ll discuss about CHECK constraint.

  1. A CHECK constraint applies a rule to a column or table.
  2. It always includes a conditional expression or function that returns a Boolean value.
  3. 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.
  4. It can be defined on table or temp table (both local & global) or table variable.
  5. 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)
  6. Single CHECK constraints can be applied to a single column.
  7. Multiple CHECK constraints can be applied to a single column.
  8. Single CHECK constraint can be applied to multiple columns.
  9. When applied with function, the function must return a single value.
  10. To modify the CHECK constraint, we first need to delete the existing CHECK constraint and create a new CHECK constraint with new value.
  11. 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

  1. 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.
  2. Check for Insert / Update. So all the new inserts & updates will follow the its rule.
  3. 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

  1. 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. 
  2. It can’t contain sub queries, sequence or ROWNUM.
  3. It never provide any support for Delete statement.
  4. It can’t be defined on View.

Examples

–Example1 : CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication with table definition
create table test
(
Id int,
Age int check ([Age]>5 AND [Age]<=10)
)

— OR with constraint name CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication
create table test
(
Id int,
Age int constraint CK_ID check ([Age]>5 AND [Age]<=10)
)

–Example2 :CHECK constraint outside table definition
create table test
(
Id int,
Age int,
Name varchar(50)
)

–Example3 :CHECK constraint Yes for existing data + Yes for Insert/Update + Yes for replication
ALTER TABLE [dbo].[CheckTbl] add constraint CK_ID check(([Age]>(5) AND [Age]<=(10)))

–OR

ALTER TABLE [dbo].[CheckTbl] WITH CHECK ADD CONSTRAINT [CK_CheckTbl] CHECK (([Age]>(5) AND [Age]<=(10)))
–Example4 : CHECK constraint No for existing data + No for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example5 : CHECK constraint Yes for existing data + No for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example6 : CHECK constraint No for existing data + Yes for Insert/Update + No for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK NOT FOR REPLICATION (([Age]>(5) AND [Age]<=(10)))

–Example7 : CHECK constraint No for existing data + Yes for Insert/Update + Yes for replication
ALTER TABLE [dbo].[CheckTbl] WITH NOCHECK ADD CONSTRAINT [CK_CheckTbl] CHECK (([Age]>(5) AND [Age]<=(10)))

–Example8 : Single CHECK constraint on Multiple column
ALTER TABLE [dbo].[CheckTbl]
ADD CONSTRAINT CK_ID_Age
CHECK ([Id] IS NOT NULL OR [Age] IS NOT NULL )

–Example9 : Multiple CHECK constraint on Single column
ALTER TABLE [dbo].[CheckTbl1]
ADD CONSTRAINT CK_ID1
CHECK ([Id] IS NOT NULL )

GO
ALTER TABLE [dbo].[CheckTbl2]
ADD CONSTRAINT CK_ID2
CHECK ([Id]>5 AND [Id]<=10)

–Example10 : Name should be 5 chars long and must consists of and capital alphabets only
ALTER TABLE [dbo].[CheckTbl2]
ADD CONSTRAINT CK_Name
CHECK ([Name] like ‘[A-Z][A-Z][A-Z][A-Z][A-Z]’)

–Example11 : CHECK constraint with function
CREATE FUNCTION dbo.checkdata(@id int)
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT * FROM tab1 WHERE id = @id)
RETURN 1

IF EXISTS(SELECT * FROM tab2 WHERE id = @id)
RETURN 1

RETURN 0
END

ALTER TABLE [dbo].[CheckTbl]
ADD CONSTRAINT CK_ID
CHECK (dbo.fn_check_p1p2(Id) = 1)

–Example12 : CHECK constraint with Primary key
create table t1
(
id int primary key check(id>5),
name varchar(50)
)

–Example13 : CHECK constraint with Unique key
create table t2
(
id int unique check(id>5),
name varchar(50)
)

–Example14 : CHECK constraint with Foreign key
create table t3
(
id int foreign key references t1(id) check (id>4),
name varchar(50)
)

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)

SELECT
@@Servername AS ServerName,
DB_NAME() AS DBName,
OBJECT_SCHEMA_NAME(ck.parent_object_id) AS SchemaName,
OBJECT_NAME(ck.parent_object_id) AS TableName,
c1.name as ColName,
t.name as ColDataType,
c1.max_length as ColMaxLength,
c1.precision as ColPrecision,
c1.scale as ColScale,
ck.Name AS CK_ConstraintName,
(CASE ck.is_disabled when 0 Then ‘YES’ Else ‘NO’ END) as CK_EnableStatus,
OBJECT_DEFINITION(ck.object_id) AS CK_Definition,
(Case c1.is_nullable When 0 Then ‘NO’ Else ‘Yes’ End) as CK_Col_IsNULL,
(CASE ck.is_not_for_replication when 0 Then ‘YES’ Else ‘NO’ END) as CK_EnforceForReplicattion,
(CASE ck.is_not_trusted when 0 Then ‘YES’ Else ‘NO’ END) as CK_ValidateExisitingData,
ck.create_date AS CK_CreatDate,
ck.modify_date AS CK_ModifyDate
FROM
sys.Check_constraints ck
INNER JOIN sys.columns c1                                — find the foreign key column name
ON ck.parent_object_id = c1.object_id
AND ck.parent_column_id = c1.column_id
INNER JOIN sys.types t                                       — Get the column datatypes
ON c1.user_type_id = t.user_type_id
ORDER BY 4,5

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

NOT NULL Constraint

In this blog, we’ll discuss about NOT NULL constraint.

In MS SQL NULL means UNKNOWN / MISSING / UNDISCOVERED value.

  1. NOT NULL constraint once added with a column will not allow the column to store the NULL value.
  2. NOT NULL constraint can be added with column added to existing table with / without data / during table definition creation.
  3. NOT NULL constraint can be remove from the column at anytime.
  4. 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.
  5. 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.
  6. 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.
  7. 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.

Examples

–Example1: NOT NULL constraint with table definition & NOT NULL constraint with DEFAULT constraint
create table test
(
Id int primary key,
name varchar(50) not null,
city varchar(50) not null DEFAULT ‘New Delhi’
)

–It’ll throw an error as name cannot be NULL
Insert into test values (1,NULL,DEFAULT)

–It’ll successfully insert a record with ‘New Delhi’ as city
Insert into test values (1,’XXX’,DEFAULT)

–Example2: Adding NOT NULL new column in the existing table
Alter table test Add Gender varchar(1) NOT NULL

–Example3: Alter existing column & adding NOT NULL constraint
Alter table test Add Country varchar(50)
Alter table test Alter column Country varchar(50) NOT NULL

–Example4: Removing NOT NULL constraint
Alter table test alter column Gender varchar(1)

To get all the detail information about all the column having NULL or not in all the tables in the current database

select
@@Servername AS ServerName,
DB_NAME() AS DBName,
t1.name as TableName,
c1.name as ColName,
c1.collation_name as SQLCollation,
t.name as ColDataType,
c1.max_length as ColMaxLength,
c1.precision as ColPrecision,
c1.scale as Scale,
(Case c1.is_nullable When 0 Then ‘No’ Else ‘Yes’ End) as Col_IsNULL
from sys.tables t1
inner join sys.columns c1
on t1.object_id=c1.object_id
INNER JOIN sys.types t                            — Get the column datatypes
ON c1.user_type_id = t.user_type_id
order by 3,8

NULL is also keyword in MS SQL Server. To get more details about NULL please click here.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.

NULL keyword

In this blog, we’ll discuss about NULL keyword in the table.

In MS SQL Server NULL means UNKNOWN / MISSING / UNDISCOVERED value.

  1. Two NULL values cannot be compared by using Equal operator (=) in between them.(Default property)
  2. 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 please click here for MSDN.
  3. NULL values can be check with either IS NULL, IS NOT NULL,ISNULL(),COALESC().
  4. 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.
  5. 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 NULL
Select * from City where ISNULL(name,”)=”
–OR
Select * from City where COALESCE(name,”)=”
–OR
Select * from City where name IS NULL

–Example2: Get all the records where City is NOT NULL
Select * from City where ISNULL(name,”)<>”
–OR
Select * from City where COALESCE(name,”)<>”
–OR
Select * 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 OFF
Select * from City where name=NULL

–Example4: Use NULL as the default value
Create table test
(
name varchar(50) DEFAULT(NULL)
)

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

NULL values can be remove by 2 ways

  1. By updating the NULL valued column with Update statement.
  2. 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 Database
DBCC SQLPERF(logspace)

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

To know the details about NOT NULL Constraint, please click here.

Few examples might not work properly in MS SQL version less than 2008 R2. Please share your valuable comments.