T-SQL Concepts PDF

T-SQL Concepts
Table of Contents
RDBMS Concepts..................................................................................................................................................................... 5
 Constraints ............................................................................................................................................................. 5
 Difference between BLANK and NULL ................................................................................................................... 9
 Normalization and De-Normalization .................................................................................................................... 9
SQL Commands Types ........................................................................................................................................................... 13
 DDL - Data Definition Language ........................................................................................................................... 13
 DML - Data Manipulation Language .................................................................................................................... 13
 DCL - Data Control Language ............................................................................................................................... 13
 DQL - Data Query Language ................................................................................................................................. 13
 Difference between Truncate and Delete ........................................................................................................... 14
Services Running in SQL Server ............................................................................................................................................. 15
SSMS Tool Overview ............................................................................................................................................................. 16
 System databases ................................................................................................................................................ 16
 Some important system objects .......................................................................................................................... 17
 Linked Server........................................................................................................................................................ 18
 Mdf, Ndf and Ldf files........................................................................................................................................... 20
SELECT statement sequence ................................................................................................................................................. 23
Joins....................................................................................................................................................................................... 24
 Inner Join:............................................................................................................................................................. 25
 Left Join: ............................................................................................................................................................... 25
 Right Join: ............................................................................................................................................................. 26
 Cross Join: ............................................................................................................................................................ 26
 Self -Join: .............................................................................................................................................................. 27
 Duplicate values join example ............................................................................................................................. 27
DISTINCT, Group BY, Having clause, Difference between WHERE and HAVING .................................................................. 28
 Group BY .............................................................................................................................................................. 28
 Multiple Column Grouping: ................................................................................................................................. 31
 Other Examples: ................................................................................................................................................... 31
 HAVING and WHERE ............................................................................................................................................ 33
 Distinct/Count ...................................................................................................................................................... 35
Sreehari Katageri, http://Skatageri.blogspot.com | 2

T-SQL Concepts
System Functions .................................................................................................................................................................. 39

 String Functions ................................................................................................................................................... 39
 Data type conversion Functions .......................................................................................................................... 46
 Math Functions .................................................................................................................................................... 47
 Aggregate Functions ............................................................................................................................................ 49
 Ranking/Analytical Functions ............................................................................................................................... 51
 Date Functions ..................................................................................................................................................... 54
 Other Functions ................................................................................................................................................... 57
Data Types............................................................................................................................................................................. 59
Identity Property ................................................................................................................................................................... 64
Set operators......................................................................................................................................................................... 66
Logical operators................................................................................................................................................................... 68
Sub Query.............................................................................................................................................................................. 76
Clustered and Non Clustered Index ...................................................................................................................................... 78
View purpose and usage ....................................................................................................................................................... 84
 What is the purpose of view? .............................................................................................................................. 84
 Standard view ...................................................................................................................................................... 84
 Index view ............................................................................................................................................................ 84
 Some important point’s w.r.t index view: ........................................................................................................... 84
 Working with normal view ................................................................................................................................... 86
 Working with Indexed view ................................................................................................................................. 88
Stored procedure purpose and usage .................................................................................................................................. 92
 Input and Output parameter ............................................................................................................................... 92
 Nested stored proc .............................................................................................................................................. 93
User defined function (UDF) ................................................................................................................................................. 94
 Scalar functions .................................................................................................................................................... 94
 Inline table valued function ................................................................................................................................. 94
 Multi statement table valued function ................................................................................................................ 95
PIVOT and UNPIVOT.............................................................................................................................................................. 96
 PIVOT.................................................................................................................................................................... 96
 UNPIVOT .............................................................................................................................................................. 98

T-SQL Concepts
Temp table, Table variable, CTE ............................................................................................................................................ 99

 Temporary table .................................................................................................................................................. 99
 Table variable ..................................................................................................................................................... 100
 CTE (Common Table Expression) ....................................................................................................................... 101
Cross Apply and Outer Apply .............................................................................................................................................. 108
Dynamic SQL Query ............................................................................................................................................................ 111
 Example 1: .......................................................................................................................................................... 111
 Example 2: .......................................................................................................................................................... 113
 Example 3: .......................................................................................................................................................... 114
Table Valued Parameter (TVP) ............................................................................................................................................ 115
Merge .................................................................................................................................................................................. 117
Cursor and While loop ........................................................................................................................................................ 120
 Cursor ................................................................................................................................................................. 120
 While loop .......................................................................................................................................................... 126
XML PATH............................................................................................................................................................................ 127
 How we can use FOR XML in our queries .......................................................................................................... 135
TRY CATCH FOR ERROR HANDLING..................................................................................................................................... 136

T-SQL Concepts
RDBMS Concepts
Constraints
 Primary key: It is the column value which uniquely identifies a record or row in a database table. It is not
NULL column. It means no NULL values are allowed. A table can have only one primary key. But the
primary key can also be the combination of columns.
In the below table Emp_Id is the PK as each row is uniquely identified.

Emp_Id Emp_Name Dept_Id
E001 John 1
E002 Ken 2
E003 John 1
E004 marry 3
Example:
/* Method 1*/
IF EXISTS(SELECT * FROM sys.tables WHERE name ='Employee')
DROP TABLE Employee
CREATE TABLE Employee

(
Emp_Id INT PRIMARY KEY,
Emp_Name VARCHAR(100),
Dept_Id INT
)
/* Method 2*/
DROP TABLE Employee

(
Emp_Id INT,
Dept_Id INT,
CONSTRAINT PK_Emp PRIMARY KEY (Emp_Id)
)
/* Throw error since Primary key is created on the nullable column (Emp_Id)*/
DROP TABLE Employee

(
Emp_Id INT,
Dept_Id INT
)
Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 5

T-SQL Concepts
ALTER TABLE Employee

ADD CONSTRAINT PK_Employee PRIMARY KEY(Emp_Id)
/*Method 3*/
DROP TABLE Employee

(
Emp_Id INT NOT NULL,
Dept_Id INT
)

/* Drop the Primary Key*/

DROP CONSTRAINT PK_Employee
INSERT INTO Employee VALUES(1,'AAA',1)

INSERT INTO Employee VALUES(1,'AAA',1)
INSERT INTO Employee VALUES(NULL,'AAA',1)
 Unique key: It is same as Primary key which uniquely identifies a record but Unique key allows one NULL
value. This unique key is also called as Alternate key. A table can have multiple Unique key columns.
/* Method 1*/
DROP TABLE Employee

(
Emp_No INT UNIQUE,
Dept_Id INT
)
/* Method 2*/
DROP TABLE Employee

(
Emp_Id INT,
Emp_No INT,
Dept_Id INT,
CONSTRAINT PK_Emp PRIMARY KEY (Emp_Id),
CONSTRAINT UK_Emp UNIQUE (Emp_No)
)

T-SQL Concepts
/* Method 3*/
DROP TABLE Employee

(
Emp_Id INT NOT NULL,
Emp_No INT,
Emp_serial_No INT,
Dept_Id INT
)


ADD CONSTRAINT UK_Employee UNIQUE (Emp_No)

ADD CONSTRAINT UK_Employee_1 UNIQUE (Emp_serial_No)
/* Drop the Unique Key*/

DROP CONSTRAINT UK_Employee
INSERT INTO Employee VALUES(1,1,1,'AAA',1)

INSERT INTO Employee VALUES(2,1,1,'AAA',1)
INSERT INTO Employee VALUES(3,NULL,NULL,'AAA',1)
 Foreign key: Foreign key is used to link two tables. A column in one table will be the primary key in
another table. Then such column is called Foreign key.
Let’s take an example, suppose we have an Employee table and Dept table. Employee table has Emp_Id as
primary key and it also has Emp_Name, Dept_Id columns. Now in Dept table Dept_Id is the primary key and
it also has Dept_Name column. Now Dept_Id in Employee table is called Foreign key as this is primary key
in Dept table.
Emp_Id Emp_Name Dept_Id

E001 John 1
E002 Ken 2
E003 John 1
E004 marry 3
Dept_Id Dept_Name
1 HR
2 Sales
3 Finance

T-SQL Concepts
IF EXISTS(SELECT * FROM sys.tables WHERE name ='Dept')

DROP TABLE Dept
CREATE TABLE Dept

(
Dept_Id INT PRIMARY KEY,
Dept_Name VARCHAR(100)
)

DROP TABLE Employee

(
Emp_No INT UNIQUE,
Dept_Id INT FOREIGN KEY REFERENCES Dept(Dept_Id)
)
/*Throws error since there is no entry in the dept table*/

INSERT INTO Employee VALUES(1,1,'AAA',1)
/*First Make an entry in the dept table and then enter the value in Employee table*/
INSERT INTO Dept VALUES(1,'D1')
INSERT INTO Employee VALUES(1, 1,'AAA', 1)
/*Throws error since the dept value exists in the employee table*/
TRUNCATE TABLE Dept
DELETE FROM Dept
/*First truncate or delete the value in employee table and then delete the dept table(You cannot
truncate the dept table bcoz its DDL command). On truncating Dept table it throws error*/
TRUNCATE TABLE Employee
TRUNCATE TABLE Dept
DELETE FROM Dept
/*Drop Foreign Key constraint*/

DROP CONSTRAINT FK__Employee__Dept_I__05D8E0BE
 Composite key: It is the combination of columns values which uniquely identify a record. That is if more
than one column form a Primary key then it is also a Composite key.
In the below table Cust_Id and Prod_Id both together uniquely identifies a record. Since PK includes more
than one column as PK this is called Composite PK.
Cust_Id Prod_Id Amt

C001 P001 4354
C001 P002 534
C002 P002 345
C003 P004 3456

T-SQL Concepts
 Candidate key: Suppose a table has 5 columns and among them 3 columns are unique, then any 1 column
among 3 can be primary key. Since 3 columns participate to be a primary key, all 3 columns are called
Candidate key.
 Default Constraint: The DEFAULT constraint is used to insert a default value into a column.The default
value will be added to all new records, if no other value is specified.

DROP TABLE Employee

(
Loaddate DATE DEFAULT GETDATE()
)
INSERT INTO Employee VALUES(1,'AAA',NULL)

INSERT INTO Employee VALUES(2,'AAA') --- Throws error since it is expecting third column value.
INSERT INTO Employee(Emp_Id,Emp_Name) VALUES(2,'AAA')
SELECT * FROM Employee
Difference between BLANK and NULL

Blank is a string of zero characters. NULL is nothing. In the below query output you can see that length of blank
gives 0 and length of NULL as NULL which is nothing.
;WITH CTE
AS
(
SELECT '' AS BLANK,NULL AS [NULL]
)
SELECT LEN(BLANK),LEN([NULL]) FROM CTE
Normalization and De-Normalization
What is Normalization?
Normalization is the process of organizing the fields and tables of relational database to minimize redundancy and
dependency. Normalization usually involves dividing large tables into smaller and less redundant tables and
defining relationships between them.
Types of Normalization:
 INF: No Repeating groups.

 2NF: Every non-key attribute should be fully dependent on the whole key (Composite key) but not on the
part of the key.
 3NF: If any non-key attribute is dependent on other non-key attribute, and in turn that non-key attribute is
dependent on any key attribute, then keep both non-key attribute in other table.

T-SQL Concepts
Apart from this we also have:

 BCNF (Boyce Codd NF)
 4NF
 5NF
But its normal practice that we follow till 3NF. Here only three normal forms are explained. Let’s consider the
below example which in not in normalized form, where for each employee there are multiple values in some fields.
1st NF :-->
• The table cells must be of single value.
• Eliminate repeating groups in individual tables.
• Create a separate table for each set of related data.
• Identify each set of related data with a primary key.
EmpID EmpName ProjectNum Time ProjectTitle ProjectMgr MgrPhn

Mohan
P001, 1.25, AAA, Kumar, 9449558722,
P002, 3, BBB, Guru, 9998735676,
E001 Rakesh Kumar P003 2.54 CCC Ramesh 9966722975
Mohan
E002 Sampath Sharma P001 0.45 AAA Kumar 9449558722
P002, 2.21, BBB, Guru, 9998735676,
E003 Vikram Rathod P004 4 DDD Guru 9998735676
P002, 1.5, BBB, Guru, 9998735676,
E004 Dinesh Naidu P003 0.29 CCC Ramesh 9966722975
Mohan
E005 Pavan Vasudev P005 3.2 EEE Kumar 9449558722
Fig 1
In the below fig 2 you can see that each cell has one and only one value, but we need to remove the redundancy by
placing each set of related data in different table and identify Primary key
in each table as in fig 3A and 3B.
EmpID EmpFName EmpLName ProjectNum Time ProjectTitle ProjectMgr MgrPhn

E001 Rakesh Kumar P001 1.25 AAA Mohan Kumar 9449558722
E001 Rakesh Kumar P002 3 BBB Guru 9998735676
E001 Rakesh Kumar P003 2.54 CCC Ramesh 9966722975
E002 Sampath Sharma P001 0.45 AAA Mohan Kumar 9449558722
E003 Vikram Rathod P002 2.21 BBB Guru 9998735676
E003 Vikram Rathod P004 4 DDD Guru 9998735676
E004 Dinesh Naidu P002 1.5 BBB Guru 9998735676
E004 Dinesh Naidu P003 0.29 CCC Ramesh 9966722975
E005 Pavan Vasudev P005 3.2 EEE Mohan Kumar 9449558722
Fig 2

T-SQL Concepts
EmpID EmpFName EmpLName EmpID ProjectNum Time ProjectTitle ProjectMgr MgrPhn

Mohan
E001 Rakesh Kumar E001 P001 1.25 AAA Kumar 9449558722
E002 Sampath Sharma E001 P002 3 BBB Guru 9998735676
E003 Vikram Rathod E001 P003 2.54 CCC Ramesh 9966722975
Mohan
E004 Dinesh Naidu E002 P001 0.45 AAA Kumar 9449558722
E005 Pavan Vasudev E003 P002 2.21 BBB Guru 9998735676
Fig 3A E003 P004 4 DDD Guru 9998735676
E004 P002 1.5 BBB Guru 9998735676
E004 P003 0.29 CCC Ramesh 9966722975
Mohan
E005 P005 3.2 EEE Kumar 9449558722
Fig 3B
2nd NF :-->
Every Non key attribute should be fully dependent on the whole key(Composite key) but not on the part of the key.
In the fig 3B we can see that only Time is dependent on both EmpId and ProjectNum, where as ProjectTitle,
ProjectMgr, MgrPhn are only dependent on ProjectNum but not on EmpID. So keep EmpID, ProjectNum and Time
in one table . ProjectNum, ProjectTitle, ProjectMgr ,MgrPhn in other table as shown in fig 4C.
EmpID EmpFName EmpLName EmpID ProjectNum Time

E001 Rakesh Kumar E001 P001 1.25
E002 Sampath Sharma E001 P002 3
E003 Vikram Rathod E001 P003 2.54
E004 Dinesh Naidu E002 P001 0.45
E005 Pavan Vasudev E003 P002 2.21
Fig 4A E003 P004 4
E004 P002 1.5
E004 P003 0.29
E005 P005 3.2
Fig 4B
ProjectNum ProjectTitle ProjectMgr MgrPhn

P001 AAA Mohan Kumar 9449558722
P002 BBB Guru 9998735676
P003 CCC Ramesh 9966722975
P004 DDD Guru 9998735676
P005 EEE Mohan Kumar 9449558722
Fig 4C

T-SQL Concepts
3rd NF :-->
If any non key attribute is dependent on other non key attribute , and inturn that non key attribute is dependent on
any key attribute ,then keep both non key attributes in other table.
In fig 4C you can see that MgrPhn is dependent on ProjectMgr but not dependent on ProjectNum and ProjectMgr is
dependent on ProjectNum. So place MgrPhn and ProjectMgr in other table as shown in fig 5D.
EmpID EmpFName EmpLName EmpID ProjectNum Time

E001 Rakesh Kumar E001 P001 1.25
E002 Sampath Sharma E001 P002 3
E003 Vikram Rathod E001 P003 2.54
E004 Dinesh Naidu E002 P001 0.45
E005 Pavan Vasudev E003 P002 2.21
Fig 5A E003 P004 4
E004 P002 1.5
E004 P003 0.29
E005 P005 3.2
Fig 5B
ProjectNum ProjectTitle ProjectMgr ProjectMgr MgrPhn

P001 AAA Mohan Kumar Mohan Kumar 9449558722
P002 BBB Guru Guru 9998735676
P003 CCC Ramesh Ramesh 9966722975
P004 DDD Guru Fig 5D
P005 EEE Mohan Kumar
Fig 5C
What is De-normalization?
De-normalization is the process of attempting to optimize the read performance of a database by adding redundant
data or by grouping data. Adding same number of redundancy increases the read performance.
Normalized databases fair very well under conditions where the applications are write-intensive and the write-
load is more than the read-load. Like, the updates are very fast because the data to be updated is located at a single
place and there are no duplicates. Normalization is followed in the OLTP systems (Application database)
De-normalized databases fair well under heavy read-load and when the application is read intensive. The data is
present in the same table so there is no need for any joins; hence the selects are very fast. But because the data is
duplicated, the updates and inserts become complex and costly. De-normalization is followed in the OLAP systems
(Data Warehouse)

T-SQL Concepts
SQL Commands Types

DDL - Data Definition Language
As the name implies, Definition means the structure of the object. Create an object structure, alter the object
structure and drop the object structure.
Command Description
CREATE Creates a new table, a view of a table, or other object in database
ALTER Modifies an existing database object, such as a table.
DROP Deletes an entire table, a view of a table or other object in the database.
TRUNCATE Deletes the data and reset the table structure.
DML - Data Manipulation Language

As the name implies, Manipulation means making changes in the table. i.e., insert a new record in the table, update
the existing record, delete the table record.
Command Description
INSERT Creates a record
UPDATE Modifies records
DELETE Deletes records
DCL - Data Control Language

As the name implies, it will control the access to object. Whether user is allowed to access the object or not.
Command Description
GRANT Gives a privilege to user
REVOKE Takes back privileges granted from user
DQL - Data Query Language

As the name implies, it is used to retrieve the data from the table.
Command Description
SELECT Retrieves certain records from one or more tables
TCL - Transaction Control Language

As the name implies, it controls the transactions on the table data. Suppose if you have updated to any new value,
and if you want the old value back then you can use this TCL commands.
Command Description
ROLLBACK Rollback any DML operation performed.
COMMIT Commit any DML operation.
BEGIN TRANSACTION Begins any transaction.
END TRANSACTION Ends any transaction.
Sreehari Katageri, http://Skatageri.blogspot.com | SQL Commands Types 13

T-SQL Concepts
Difference between Truncate and Delete
 Truncate is a DDL command, whereas Delete is a DML command. This is because, suppose a table has
identity column and if we Truncate table and insert a record its identity property is reset i.e. count starts
from beginning. It means the table structure is reset. Where as in case of delete if we delete and insert a
record the identity column value will be one more than the value last deleted.
 Truncate and Delete both is logged. Where Delete logs the whole record deleted but Truncate logs pointer
to the data page. Because of this Truncate is faster and Delete is slower.
 Truncate is bulk operation whereas Delete is Row by Row operation.
 In Truncate we cannot specify where condition, where as in Delete we can specify where condition.
IF EXISTS(SELECT * FROM sys.tables WHERE name ='tranTest')

DROP TABLE tranTest
CREATE TABLE tranTest

(
Id int Identity(1,1),
Name Varchar(100)
)
INSERT INTO tranTest(Name) VALUES('prashant')

INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')
BEGIN TRAN
DELETE FROM tranTest
SELECT * FROM tranTest
ROLLBACK
DELETE FROM tranTest
INSERT INTO tranTest(Name) VALUES('prashant')

INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')
BEGIN TRAN
TRUNCATE TABLE tranTest
ROLLBACK
Sreehari Katageri, http://Skatageri.blogspot.com | SQL Commands Types 14

T-SQL Concepts
Services Running in SQL Server

SQL Server 2008 and
SQL Server 2008 R2
Common Name Service Display Name Service Name Executable Name
Distributed Distributed Transaction MSDTC msdtc.exe
Transaction Coordinator
Coordinator (DTC)
SQL Server SQL Server MSSQLSERVER sqlservr.exe
(MSSQLSERVER) MSSQL$CONTOSO1
SQL Server (CONTOSO1)
SQL Server Active SQL Active Directory MSSQLServerADHelper100 SQLADHLP.EXE
Directory Helper Helper Service
SQL Server Agent SQL Server Agent SQLSERVERAGENT SQLAGENT.EXE
(MSSQLSERVER) SQLAgent$CONTOSO1
SQL Server Agent
(CONTOSO1)
SQL Server SQL Server Analysis MSSQLServerOLAPService msmdsrv.exe
Analysis Services Services MSOLAP$CONTOSO1
(MSSQLSERVER)
SQL Server Analysis
Services (CONTOSO1)
SQL Server SQL Server Browser SQLBrowser sqlbrowser.exe
Browser
SQL Server Full SQL Full-text Filter MSSQLFDLauncher fdlauncher.exe
Text Search Daemon Launcher MSSQLFDLauncher$CONTOSO1
(MSSQLSERVER)
SQL Full-text Filter
Daemon Launcher
(CONTOSO1)
SQL Server SQL Server Integration MsDtsServer100 MsDtsSrvr.exe
Integration Services 10.0
Services
SQL Server SQL Server Reporting ReportServer ReportingServicesService.exe
Reporting Services Services ReportServer$CONTOSO1
(MSSQLSERVER)
SQL Server Reporting
Services (CONTOSO1)
SQL Server VSS SQL Server VSS Writer SQLWriter sqlwriter.exe
Writer
Sreehari Katageri, http://Skatageri.blogspot.com | Services Running in SQL Server 15

T-SQL Concepts
SSMS Tool Overview

System databases
Master database: The master database contains the information about all the databases installed on the current
instance of SQL Server. It also contains configuration and status about the current SQL Server instance. This data is
stored in system tables and can be accessed by database administrators using system views and functions. When a
new database is created, entries corresponding to that database are also created in that master database to track
the file structure and logical database structure of the new database. You should avoid modifying the master
database since modification in the master database may corrupt the entire server.
Model database: The model database provides you a template for creating new databases. All the objects in the
model databases are automatically copied to the new user database. Any modification in the model database is also
applied to all the user databases created on the server.
USE [model]
CREATE TABLE BluePrintTable

(
ID Int
)
CREATE DATABASE Temp
MSDB database: The msdb database contains configuration information about various support services, such as
SQL Server Agent, Database Mail and Service Broker. The SQL Server Agent service uses the msdb database to store
scheduling information about jobs and alerts. Modification of the data of the msdb database should be avoided;
however, if modifications are necessary, you should use the stored procedures and views of the msdb database to
modify the database.
TempDB database: The tempdb database is used to store temporary tables generated automatically by SQL
Server and the temporary tables created explicitly by the users. SQL Server uses the tempdb database to store the
intermediate results of the queries when doing complex database operations. Whenever SQL Server restarts, the
tempdb database is dropped and then recreated.
CREATE TABLE #Sreehari(id int)
SELECT * FROM tempdb.[INFORMATION_SCHEMA].[TABLES]
DROP TABLE #Sreehari
ReportServer database: The report server database is a SQL Server database that stores the following content:
 Items managed by a report server (reports and linked reports, shared data sources, report models, folders,
resources) and all of the properties and security settings that are associated with those items.
 Subscription and schedule definitions.
 Report snapshots (which include query results) and report history.
 System properties and system-level security settings.
 Report execution log data.
 Symmetric keys and encrypted connection and credentials for report data sources.
Sreehari Katageri, http://Skatageri.blogspot.com | SSMS Tool Overview 16

T-SQL Concepts
ReportServerTempDB database: Each report server database uses a related temporary database to store session
and execution data, cached reports, and work tables that are generated by the report server. Background server
processes will periodically remove older and unused items from the tables in the temporary database. Reporting
Services does not re-create the temporary database if it is missing, nor does it repair missing or modified tables.
Although the temporary database does not contain persistent data, you should back up a copy of the database
anyway so that you can avoid having to re-create it as part of a failure recovery operation. If you back up the
temporary database and subsequently restore it, you should delete the contents. Generally, it is safe to delete the
contents of the temporary database at any time. However, you must restart the Report Server Windows service
after you delete the contents.
Database Snapshot: Database snapshots are an Enterprise only feature which made its debut in SQL Server 2005
A database snapshot is a view of what the source database looked like at the time at which the snapshot was
created. This means that all the objects will be the same as what it was when the snapshot was taken and all of the
data will be exactly as it was then. To use database snapshots to recover from an unwanted DML statement, you
need to have a suitable snapshot in place. Snapshots can only be created by using a T-SQL statement. Here is an
example of how to create a database snapshot
CREATE DATABASE TestSnapShot1 ON (NAME = Test,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\TestSnapShot.ss')
AS SNAPSHOT OF Test
CREATE TABLE test1(id int)
CREATE DATABASE TestSnapShot1 ON (NAME = Test,

FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\TestSnapShot1.ss')
AS SNAPSHOT OF Test
Suppose something went wrong with the actual database, like objects got dropped or data got deleted, then using
the snapshot you can restore it back. And we can use the snapshot for read operation only like SELECT. We cannot
do any modifications like INSERT, UPDATE, DELETE, DROP, CREATE on the snapshot database. Please refer below
example
DROP TABLE Test.dbo.[Employee2]
USE [master]
RESTORE DATABASE Test

FROM DATABASE_SNAPSHOT = 'TestSnapShot1'
Some important system objects

--- Syntax: SELECT * FROM [DB Name].[System table/view]
SELECT * FROM sys.databases -- system view

SELECT * FROM sysdatabases -- system view
SELECT OBJECT_NAME(object_Id),* FROM sys.Objects
SELECT * FROM sys.Tables -- system view
SELECT * FROM sys.columns -- system view

SELECT * FROM syscolumns -- system view
SELECT OBJECT_NAME(object_Id),* FROM sys.Objects

T-SQL Concepts
SELECT * FROM [INFORMATION_SCHEMA].[VIEWS]

SELECT * FROM [INFORMATION_SCHEMA].[TABLES]
SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS]
sp_helptext '[sys].[sp_addapprole]' --- syntax: sp_helptext '[Stored procedure/ View]'
sp_help 'Employee' --- syntax: sp_help '[Table Name/view name]'
sp_helpindex 'Employee' --- syntax: sp_helpindex '[Table Name/view name]'
Linked Server
It is used to link between two servers. From one server you can access the objects of another server. It is not
recommended to use linked server unless and until it is necessary.
Distributed Query:
Syntax: SELECT * FROM [Linked Server Name].[Database].[Schema].[Table/view]
/** Create below table in Server1, which in my case is amsmsaapp02-prd.hq.netapp.com server**/

USE [imports]
IF EXISTS (SELECT * FROM sys.tables WHERE name='Temp_SK_Training')

DROP TABLE imports.dbo.Temp_SK_Training
CREATE TABLE imports.dbo.Temp_SK_Training

(
Id INT IDENTITY (1,1),
Name VARCHAR (100),
Location VARCHAR (100)
)
/** Execute below query in Server2 which is been linked. In my case it’s my local system Sql server**/
SELECT TOP 10 * FROM [AMSMSAAPP02-PRD.HQ.NETAPP.COM].imports.dbo.Temp_SK_Training
SELECT * FROM sys.servers
Open Query:
Executes the specified pass-through query on the specified linked server.

Syntax: OPENQUERY (linked_server, ’query’)
SELECT * FROM OPENQUERY ([AMSMSAAPP02-PRD.HQ.NETAPP.COM], 'SELECT * FROM imports.dbo.Temp_SK_Training')
Executing an UPDATE pass-through query:

UPDATE OPENQUERY ([AMSMSAAPP02-PRD.HQ.NETAPP.COM], 'SELECT Location FROM imports.dbo.Temp_SK_Training
WHERE id = 1')
SET Location = 'Mumbai';
Executing an INSERT pass-through query

INSERT OPENQUERY ([AMSMSAAPP02-PRD.HQ.NETAPP.COM], 'SELECT Name, Location FROM
imports.dbo.Temp_SK_Training ')
VALUES ('AAA','Bangalore');

T-SQL Concepts
INSERT OPENQUERY ([AMSMSAAPP02-PRD.HQ.NETAPP.COM], 'SELECT name, Location FROM

imports.dbo.Temp_SK_Training ')
VALUES ('BBB','Delhi');
Executing a DELETE pass-through query

DELETE OPENQUERY ([AMSMSAAPP02-PRD.HQ.NETAPP.COM], 'SELECT * FROM imports.dbo.Temp_SK_Training WHERE
Location = ''Mumbai''');
There are implications when trying to join the results of OPENQUERY with a local table. Typically joining remote
tables with local tables across the network is not the most efficient method of querying. In some cases it may be
better to subscribe to a remote table by importing it locally, then joining it locally.Open queries are useful when
you are not referencing multiple servers in one query. It’s generally fast as SQL does not break it into multiple
operations.
So to access the tables in Server1 (AMSMSAAPP02-PRD.HQ.NETAPP.COM) we have to create linked server in Server2
(which is my local system SQL Server in my case). Below is the steps to create a linked server. Linked Server can be
found under Server objects.

T-SQL Concepts
In case of distributed query the query optimizer will try to parse the remote table locally and even though the data
filter applied in the query, first all the data from the remote table is bought to local and then the data filter is
applied. This takes more time.
In case of open query the entire query is passed to the remote server and there the remote table is parsed, by
applying filter and then the result obtained is joined with the local table. This is much faster when compared to the
distributed query. But in the open query you can refer only one linked server.
Mdf, Ndf and Ldf files
Mdf stands for “Master Data File/ Primary Data File”.

Ndf stands for “Secondary Data File”.
Ldf stands for “Log Data File” (Transaction Log).
Primary Data File (.mdf): It's a SQL Data file where all the data in the database objects are stored in. (like tables,
stored procedures, views, triggers etc. are stored in the mdf file of sql server). There can be only one primary file
for a database.
Secondary Data File (.ndf): This is same as the primary data file, where there can be “n” number of secondary files.
Secondary database files are used to store all data that does not fit in the primary database file. A database should
not include a secondary database file if the primary database file can hold all of the data. Some databases might
need one or more secondary data files because of their size or use secondary files on separate disk drives to spread
data across multiple disks.

T-SQL Concepts
Transaction Log File (.ldf): Every database by default has one transaction log which is created along with the mdf
file, but it can also have multiple transaction logs in case if the 1st log is full and running out of space. A transaction
log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK
IMPORT or SELECT INTO. The transaction log is the most important component of a SQL Server database when it
comes to the disaster recovery – however, it must be uncorrupted.
http://www.sqlshack.com/beginners-guide-sql-server-transaction-logs/
http://www.mssqltips.com/sqlservertip/3076/how-to-read-the-sql-server-database-transaction-log/
Query to check the space available with the log file.

DBCC SQLPERF (LOGSPACE);
GO
We will see now how the transactions are written to log file and how to read the log. This can be understood by a
function called "fn_dblog ()".
--Create DB.
USE [master];
GO
CREATE DATABASE ReadingDBLog;
GO
-- Create tables.
USE ReadingDBLog;
GO
CREATE TABLE [Location] (
[Sr.No] INT IDENTITY,
[Date] DATETIME DEFAULT GETDATE (),
[City] CHAR (25) DEFAULT 'Bangalore');
Below query will give the information about the transaction log.
SELECT [Current LSN],

[Operation],
[Transaction Name],
[Transaction ID],
[Transaction SID],
[SPID],
[Begin Time]
FROM fn_dblog(null,null)
Insert/update few records in to location table.
USE ReadingDBLog
go
INSERT INTO Location DEFAULT VALUES ;
GO 100
GO
UPDATE Location
SET City='New Delhi'
WHERE [Sr.No]<5
GO
DELETE Location
WHERE [Sr.No]>90
Go

T-SQL Concepts
Now read the transaction log again. It tells what all transactions happened.
USE ReadingDBLog
go
SELECT
[Current LSN],
[Transaction ID],
[Operation],
[Transaction Name],
[CONTEXT],
[AllocUnitName],
[Page ID],
[Slot ID],
[Begin Time],
[End Time],
[Number of Locks],
[Lock Information]
FROM sys.fn_dblog(NULL,NULL)
WHERE Operation IN
('LOP_INSERT_ROWS','LOP_MODIFY_ROW',
'LOP_DELETE_ROWS','LOP_BEGIN_XACT','LOP_COMMIT_XACT')
Now shrink the database log and check the transaction log. All the transactions will be cleaned off from the log.
DBCC SHRINKFILE (ReadingDBLog, 1)

T-SQL Concepts
SELECT statement sequence

The commonly used sequence of select statement is as follows:
SELECT [col1, col2… coln]

FROM [table/view]
JOIN [table/view] ON [Join column condition]
WHERE [condition]
GROUP BY [col1, col2…coln]
HAVING [condition]
ORDER BY [col1, col2… coln]
FOR XML
Following are the examples of valid select statements
IF EXISTS(SELECT * FROM sys.tables WHERE name='tblStudents')

DROP TABLE tblStudents
CREATE TABLE tblStudents (

StudentName VARCHAR(100),
SubjectName VARCHAR(100),
Marks INT
)
GO
INSERT INTO tblStudents VALUES('Chris', 'Maths',90 )

INSERT INTO tblStudents VALUES('Chris', 'English',85 )
INSERT INTO tblStudents VALUES('Chris', 'Chemistry',56)
INSERT INTO tblStudents VALUES('James', 'Maths',72)
INSERT INTO tblStudents VALUES('James', 'English',68)
INSERT INTO tblStudents VALUES('James', 'Chemistry',49)
INSERT INTO tblStudents VALUES('Dom', 'Maths',96)
INSERT INTO tblStudents VALUES('Dom', 'English',39)
INSERT INTO tblStudents VALUES('Dom', 'Chemistry',80)
INSERT INTO tblStudents VALUES('Jacobs', 'Maths',84 )
INSERT INTO tblStudents VALUES('Jacobs', 'English',67)
GO
SELECT StudentName,SubjectName,SUM(Marks) Marks

FROM tblStudents
WHERE SubjectName='Maths'
GROUP BY StudentName,SubjectName
HAVING SUM(Marks)>50
FOR XML RAW

FROM tblStudents
WITH ROLLUP
HAVING SUM(Marks)>100

FROM tblStudents
WITH CUBE
Sreehari Katageri, http://Skatageri.blogspot.com | SELECT statement sequence 23

T-SQL Concepts
Joins
Joins are used to retrieve the data from two or more tables, based on a relationship between certain columns in
these tables.
Types of joins (note that these are the logical joins):

 Inner Join
 Left Join
 Right Join
 Cross Join
 Self-Join
 Cross Apply
Let’s take sample data set to demonstrate joins
IF EXISTS(SELECT * FROM sys.tables WHERE name='tblEmployee')

DROP TABLE tblEmployee
CREATE TABLE tblEmployee

(
Emp_ID VARCHAR(6),
Emp_Dept VARCHAR(6),
Emp_Manager VARCHAR(6)
)
INSERT INTO tblEmployee VALUES('E001','AAA','D002',NULL)

INSERT INTO tblEmployee VALUES('E002','BBB','D001',NULL)
INSERT INTO tblEmployee VALUES('E003','CCC','D002','E001')
INSERT INTO tblEmployee VALUES('E004','DDD','D001','E002')
INSERT INTO tblEmployee VALUES('E005','EEE','D002','E003')
INSERT INTO tblEmployee VALUES('E006','FFF','D001','E004')
INSERT INTO tblEmployee VALUES('E007','GGG',NULL,NULL)
IF EXISTS(SELECT * FROM sys.tables WHERE name='tblDept')

DROP TABLE tblDept
CREATE TABLE tblDept

(
Dept_Id VARCHAR(6),
)
INSERT INTO tblDept VALUES ('D001','HR')

INSERT INTO tblDept VALUES ('D002','FINANCE')
INSERT INTO tblDept VALUES ('D003','IT')
SELECT * FROM tblEmployee

SELECT * FROM tblDept
Sreehari Katageri, http://Skatageri.blogspot.com | Joins 24

T-SQL Concepts
Inner Join:
Returns only matching records from two or more tables which are being joined.
Left Join:
Returns all the records from the left table and only matching records from the right table.

T-SQL Concepts
Right Join:
Returns all the records from the right table and only matching records from the left table.
Cross Join:
Each and every record from left table is joined with each and every record in the right table.

T-SQL Concepts
Self -Join:
Here we join the same table again with itself.
NOTE: while using self-join be care full about the order of column used in join condition. The condition
e.Emp_manager=m.Emp_id is different from e.Emp_Id=m.Emp_Manager
Duplicate values join example

T-SQL Concepts
DISTINCT, Group BY, Having clause, Difference

between WHERE and HAVING
Group BY
Group BY is used to summarize the table data. As the name implies it will group the set of similar data in a column.
Grouping may be based on only one column or multiple columns. Below examples illustrate this:
CREATE TABLE dbo.Employee

(
EmpID VARCHAR(5),
EmpName VARCHAR(40),
EmpDept VARCHAR(10),
EmpLoc VARCHAR(40),
EmpSal MONEY
)
INSERT INTO dbo.Employee VALUES('E001','AAA','HR','Delhi',20000)

INSERT INTO dbo.Employee VALUES('E002','BBB','IT','Bangalore',30000)
INSERT INTO dbo.Employee VALUES('E003','CCC','IT','Delhi',15000)
INSERT INTO dbo.Employee VALUES('E004','AAA','HR','Bangalore',10000)
INSERT INTO dbo.Employee VALUES('E005','GGG','FIN','Hydrabad',25000)
INSERT INTO dbo.Employee VALUES('E006','YYY','FIN','Hydrabad',26000)
INSERT INTO dbo.Employee VALUES('E007','SSS','IT','Goa',40000)
INSERT INTO dbo.Employee VALUES('E008','WWW','IT','HP',26000)
INSERT INTO dbo.Employee VALUES('E009','WWW','FIN','HP',80000)
INSERT INTO dbo.Employee VALUES('E010','QQQ','FIN','Gujrath',13000)
SELECT * FROM dbo.Employee
Sreehari Katageri, http://Skatageri.blogspot.com | DISTINCT, Group BY, Having clause, Difference between 28
WHERE and HAVING
T-SQL Concepts
SELECT * FROM dbo.Employee ORDER BY EmpDept
In the above fig we can see that EmpDept has a set of similar values as highlighted in read box: FIN, HR , IT.
Single Column Grouping:
Below query returns Number of Employees in each department.
SELECT EmpDept,COUNT(*) AS NoOfEmp

FROM dbo.Employee
GROUP BY EmpDept
ORDER BY EmpDept
Below query returns Number of Employees in each department with the following condition:
 A dept should have more than 2 employees, only those records should be displayed.
 So HR dept will not be returned in the output as it has 2 employees.

FROM dbo.Employee
GROUP BY EmpDept
HAVING COUNT(*)>2
ORDER BY EmpDept
WHERE and HAVING
T-SQL Concepts
Working:
Below query returns the Number of Employees in each dept with the following condition:
 A dept should have more than 2 employees, only those records should be displayed.
 Except FIN dept all other dept should be displayed.

FROM dbo.Employee
WHERE EmpDept<>'FIN'
GROUP BY EmpDept
HAVING COUNT(*)>2
ORDER BY EmpDept
Working:
WHERE and HAVING
T-SQL Concepts
Multiple Column Grouping:
Below query is the example for multiple column grouping which will return, in a dept how many employees are
from same places. In below example we can see, from FIN dept from Hydrabad there are 2 employees and rest all
have 1 employee.
SELECT EmpDept,EmpLoc,COUNT(*) AS NoOfEmp

FROM dbo.Employee
GROUP BY EmpDept,EmpLoc
ORDER BY EmpDept
Working:
Other Examples:
Similarly instead of count we can use aggregate functions like MIN(), MAX(), AVG(), SUM() etc.
Below queries returns Minimum and Maximum salary in each department
SELECT EmpDept,MIN(EmpSal) AS MinSal

FROM dbo.Employee
GROUP BY EmpDept
WHERE and HAVING
T-SQL Concepts
SELECT EmpDept,MAX(EmpSal) AS MaxSal

FROM dbo.Employee
GROUP BY EmpDept
Below query returns average salary in each department.
SELECT EmpDept,AVG(EmpSal) AS AvgSal

FROM dbo.Employee
GROUP BY EmpDept
WHERE and HAVING
T-SQL Concepts
Below query returns total salary paid in each department.
SELECT EmpDept,SUM(EmpSal) AS SumSal

FROM dbo.Employee
GROUP BY EmpDept
HAVING and WHERE
People say that HAVING cannot exists without GROUP BY, but this is wrong. HAVING can exists without GROUP BY.
When HAVING alone is used its compulsory to use aggregate function. In the absence of GROUP BY HAVING will
group entire table as one and applies aggregate function.
WHERE is also a filter condition and HAVING is also a filter condition. WHERE applies filter on single record,
HAVING applies filter on grouped data. In where clause we cannot use aggregate functions but in having clause we
can use aggregate function.
Having works like Where clause without Group By Clause.
WHERE and HAVING
T-SQL Concepts
Below query will return the table count if HAVING condition is satisfied or else no output is displayed. Here since
HAVING exists without GROUP BY entire table is grouped as one . Since Avg of EmpSal in the entire table is 28500
((20000.00 + 30000.00 + 15000.00 + 10000.00 + 25000.00 + 26000.00 +40000.00 + 26000.00 + 80000.00 + 13000
.00)/10) and in the Having clause it is mentioned that if this avg is greater than 28000 ,output 10 is displayed
which is the count of table records. It is something like, if HAVING condition is true it will return the select
statement output. Similarly you can find the one for average also.
SELECT COUNT(*)
FROM dbo.Employee
HAVING AVG(EmpSal)>28000
SELECT AVG(EmpSal)
FROM dbo.Employee
HAVING AVG(EmpSal)>28000
WHERE and HAVING
T-SQL Concepts
Distinct/Count
Distinct will remove the duplicate records in the output as shown in below example:
SELECT DISTINCT EmpDept FROM dbo.Employee
Below query first it will take the count of EmpDept values and then applies distinct on the result obtained. First it
will return 10 and DISTINCT 10 is always 10.
SELECT DISTINCT COUNT(EmpDept) FROM dbo.Employee
Below query first it will take the distinct values and then applies count on the result obtained. First it will return
the distinct EmpDept which is 3 records IT,FIN,HR then COUNT of obtained result is 3.
SELECT COUNT(DISTINCT EmpDept) FROM dbo.Employee
Lets see DISTINCT and COUNT with NULL's and BLANK's in a column
UPDATE dbo.Employee
SET EmpLoc=''
WHERE EmpID='E006'
UPDATE dbo.Employee
SET EmpLoc=NULL
WHERE EmpID='E007'
WHERE and HAVING
T-SQL Concepts
SELECT DISTINCT EmpLoc FROM dbo.Employee
SELECT COUNT(EmpLoc) FROM dbo.Employee
SELECT COUNT(*) FROM dbo.Employee
SELECT COUNT(EmpLoc) AS EmpLocCnt,COUNT(EmpSal) EmpSalCnt FROM dbo.Employee
 DISTINCT will consider both NULL as well as BLANK values. NULL is nothing whereas BLANK is a string of
zero characters.
 COUNT will not consider NULL values but it considers BLANK values.
 “*” represents all the columns in the table. So when COUNT(*) is specified the count is taken on group of
columns. So irrespective of one column EmpLoc it will consider all columns and displays count as 12.
CREATE TABLE dbo.TestCnt
(
Col1 varchar(max),
Col2 varchar(max),
Col3 varchar(max),
Col4 varchar(max)
)
insert into dbo.TestCnt values(NULL,NULL,NULL,NULL)

WHERE and HAVING
T-SQL Concepts

select * from dbo.TestCnt
select COUNT(*) from dbo.TestCnt -----> 7

select COUNT(1) from dbo.TestCnt -----> 7
select COUNT(Col1) from dbo.TestCnt -----> 0
select COUNT(Col2) from dbo.TestCnt -----> 0
select COUNT(Col1,Col2) from dbo.TestCnt -----> Error
select COUNT(Col1),COUNT(Col2) from dbo.TestCnt -----> 0,0
select COUNT(1),COUNT(Col2) from dbo.TestCnt -----> 7,0
select COUNT(1),COUNT(*) from dbo.TestCnt -----> 7,7
select COUNT(*),COUNT(*) from dbo.TestCnt -----> 7,7
CREATE TABLE dbo.TestCnt1

(
Col1 varchar(max),
Col2 varchar(max),
Col3 varchar(max),
Col4 varchar(max)
)
insert into dbo.TestCnt1 values('',NULL,NULL,NULL)

insert into dbo.TestCnt1 values(NULL,'',NULL,NULL)
insert into dbo.TestCnt1 values(NULL,NULL,NULL,NULL)
insert into dbo.TestCnt1 values(NULL,'',NULL,NULL)
select * from dbo.TestCnt1
WHERE and HAVING
T-SQL Concepts
select COUNT(*) from dbo.TestCnt1 -----> 7

select COUNT(1) from dbo.TestCnt1 -----> 7
select COUNT(Col1) from dbo.TestCnt1 -----> 1
select COUNT(Col2) from dbo.TestCnt1 -----> 2
select COUNT(Col1,Col2) from dbo.TestCnt1 -----> Error
select COUNT(Col1),COUNT(Col2) from dbo.TestCnt1 -----> 1,2
select COUNT(1),COUNT(Col2) from dbo.TestCnt1 -----> 7,2
select COUNT(1),COUNT(*) from dbo.TestCnt1 -----> 7,7
select COUNT(*),COUNT(*) from dbo.TestCnt1 -----> 7,7
WHERE and HAVING
T-SQL Concepts
System Functions
String Functions
LOWER (): This will convert all the characters of a string to lower case.
Syntax: LOWER ([String])
UPPER (): This will convert all the characters of a string to upper case.
Syntax: UPPER ([String])
LTRIM (): This will return a character expression after it removes leading blanks. Meaning if there is any blank
space before the character/word this will remove it.
Syntax: LTRIM ([String])
Sreehari Katageri, http://Skatageri.blogspot.com | System Functions 39

T-SQL Concepts
RTRIM (): This will return a character expression after it removes all trailing space. Meaning if there is any blank
space after the character/word this will remove it.
Syntax: RTRIM ([String])
REPLACE (): This will replace all the occurrence of the specified string/character in a string/character with the
given string/character.
Syntax: REPLACE ([String], [String or character to replace], [the new replacement string or character])

T-SQL Concepts
SUBSTRING (): This will return the specified length of characters from the given string. You need to specify start
position and number of characters from the start position.
Syntax: SUBSTRING ([String], [Start position], [number of characters from start position])
LEFT (): This will return the left part of a character string with the specified number of characters.
Syntax: LEFT ([String], [number of characters])
RIGHT (): This will return the right part of a character string with the specified number of characters.
Syntax: RIGHT ([String], [number of characters])

T-SQL Concepts
LEN (): This will return the number of characters of the specified string expression, excluding trailing blanks and
including leading blanks.
Syntax: LEN ([String])
DATALENGTH (): This will return the number of bytes used to represent any expression. It will consider both
trailing as well as leading space.
Syntax: DATALENGTH ([String])
CHARINDEX (): This will return the starting position of the specified string/character in a given string. You can
also define an option to say from which position within a given string to start searching the specified
character/string.
Syntax: CHARINDEX ([String or character to search], [Actual String])

CHARINDEX ([String or character to search], [Actual String], [start position to search])

T-SQL Concepts
PATINDEX (): This will return the starting position of the first occurrence of a pattern in a specified expression, or
zero’s if the pattern is not found, on all valid text and character data types.
Syntax: PATINDEX ([‘%string or character%’], [Actual String])

PATINDEX ([‘%string or character%’], [Actual String], [start position to search])
REPLICATE (): This will return a string value a specified number of times.
Syntax: REPLICATE ([String], [number of times to replicate the string])
REVERSE (): This will return reverse of the specified string.
Syntax: REVERSE ([String])
STUFF (): This will insert a string into another string. It deletes a specified length of characters in the first string at
the start position and then inserts the second string into the first string at the start position.
Syntax: STUFF ([String], [start position], [length], [replacement string or character])

T-SQL Concepts
STR (): This will return character data converted from numeric data. It takes the numeric/float/decimal value and
if the length specified is less than the number of digits before decimal then it will return ‘*’. Note that in the second
example the decimal points are rounded off for few outputs. In the length decimal point is also considered.
Syntax: STR ([Float/Decimal/Numeric], [length of digits], [number of digits after decimal point])
SPACE (): This will return a string of repeated spaces.
Syntax: SPACE ([Integer value])

T-SQL Concepts
SOUNDEX (): This will convert a character string to a four-digit code for use in a comparison. Vowels are ignored
in the comparison. Based on the sound produced by the string it returns a value. It ignore the numeric values as
well.
Syntax: SOUNDEX ([String1])
DIFFERENCE (): This will return an integer value that indicates the difference between the SOUNDEX values of two
character expressions. The return value ranges from 0 to 4. 4 is the best match and 0 is the worst match. This value
is derived from the number of characters in the SOUNDEX of each phrase that are the same.
Syntax: DIFFERENCE ([String1], [String2])
This can be used when you want to match the similarities between the string, like for address, names etc.

T-SQL Concepts
Data type conversion Functions
CAST (): This will convert one data type to another. This is an ASCII standard. We don’t have any formatting option
here while converting date time.
Syntax: CAST ([value] AS Datatype)
CONVERT (): This will convert one data type to another. This can be used for date time formatting.
Syntax: CONVERT ([Datatype], [value or expression], [style]

CONVERT ([Datatype] [value or expression)]

T-SQL Concepts
Math Functions
ROUND (): This will round a numeric field to the number of decimals specified. The optional value can be either 0
or any other numeric value. When operation is 0 (or this parameter is omitted), the ROUND function will round the
result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the
result to the number of decimal_places.
Syntax: ROUND ([float number], [number of decimal places to round off], [Optional value to truncate the decimal
part])
ABS (): This will return absolute value of the given number. Meaning if the number is negative it will make it
positive.
Syntax: ABS ([number])
ISNUMERIC (): Determines whether an expression is a valid numeric type. It returns 0 if the expression non
numeric, 1 if expression is a numeric value.
Syntax: ISNUMERIC ([expression])

T-SQL Concepts
CEILING (): The CEILING function returns the smallest integer greater than or equal to the specified numeric
expression.
Syntax: CEILING ([numeric value])
FLOOR (): The FLOOR function returns the largest integer less than or equal to the specified numeric expression
Syntax: FLOOR ([numeric value])

T-SQL Concepts
Aggregate Functions
MIN (): This will return minimum value of the expression. It gives minimum on both numeric as well as character.
Syntax: MIN (Column or Expression)
MAX (): This will return maximum value of the expression. It gives maximum on both numeric as well as character.
Syntax: MAX (Column or Expression)
AVG (): This will return average value of the expression. AVG will not consider NULL.
Syntax: AVG (Column or Expression)

T-SQL Concepts
COUNT (): This will return COUNT value of the expression. COUNT will not consider NULL but it will consider
BLANK value as shown in below example.
Syntax: COUNT (Column)
SUM (): This will return SUM value of the expression.
Syntax: SUM (Column or Expression)

T-SQL Concepts
Ranking/Analytical Functions
PARTITION BY (): This is used to divide the rows based on the specified columns.
Syntax: OVER (PARTITION BY Column ORDER BY COLUMN)
ROW_NUMBER (): This will give the sequential number based on the partitioned column or ordered column. For
this ORDER BY is mandatory. But it can exist without PARTITION BY.
Syntax: ROW_NUMBER () OVER (PARTITION BY Column ORDER BY COLUMN)

Or ROW_NUMBER () OVER (ORDER BY COLUMN)
RANK (): This will give rank based on the partitioned column or ordered column. For this ORDER BY is mandatory.
But it can exist without PARTITION BY. If there are two records with same value then both get the same rank, but
for next record the next rank will be skipped and assigned that next rank. Suppose if two records are assigned rank
as 1 each, 3rd record will get rank as 3 but not 2.
Syntax: RANK () OVER (PARTITION BY Column ORDER BY COLUMN)

Or RANK () OVER (ORDER BY COLUMN)
DENSE_RANK (): This is same as RANK () which give rank based on the partitioned column or ordered column. For
this ORDER BY is mandatory. But it can exist without PARTITION BY. If there are two records with same value then
both get the same rank, but for next record the next rank will not be skipped and assigned that next rank. Suppose
if two records are assigned rank as 1 each, 3rd record will get rank as 2 but not 3.
Syntax: DENSE_RANK () OVER (PARTITION BY Column ORDER BY COLUMN)

Or DENSE_RANK () OVER (ORDER BY COLUMN)

T-SQL Concepts
GROUPING SETS (): Suppose if you need the aggregations on different column combination (permutation -
combination) of the columns specified in the group by, it cannot be achieved using normal group by. For this we
need to use GROUPING SETS.
Syntax: GROUP BY GROUPING SETS (different permutation combination of the grouped columns)
GROUPING (): Indicates whether a specified column expression in a GROUP BY list is aggregated or not.
GROUPING returns 1 for aggregated or 0 for not aggregated in the result set. GROUPING can be used only in the
SELECT <select> list, HAVING, and ORDER BY clauses when GROUP BY is specified.
Syntax: GROUPING (column or expression)
ROLLUP: This is used to get the subtotals in the aggregations. ROULLUP is used with the GROUP BY.
Syntax: WITH ROLLUP
CUBE: This is similar to the GROUPING SETS, which gives all possible aggregations of the grouped columns. CUBE
is used with the GROUP BY.
Syntax: WITH CUBE

T-SQL Concepts

T-SQL Concepts
NTILE (): Distributes the rows in an ordered partition into a specified number of groups. The groups are
numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax: NTILE (integer expression) OVER (ORDER BY Column)

Or NTILE (integer expression) OVER (PARTITION BY Column ORDER BY Column)
Date Functions
DATEADD (): This is used add number of days, weeks, seconds, nanoseconds, month etc. to the specified date.
Syntax: DATEADD (OPTION, Value, Date)

T-SQL Concepts
DATEDIFF (): This will return the difference between two days, based on the specified option.
Syntax: DATEDIFF (OPTION, Date1, Date2)
YEAR (): This will return year part of the specified date.
Syntax: YEAR (Date)
MONTH (): This will return month part of the specified date.
Syntax: MONTH (Date)
DAY (): This will return day part of the specified date.
Syntax: DAY (Date)

T-SQL Concepts
DATENAME (): This will return the result based on the specified datepart. In few case it will give the name instead
of the number.
Syntax: DATENAME (Datepart, Date)
DATEPART (): This will return the result based on the specified datepart.
Syntax: DATEPART (Datepart, Date)

T-SQL Concepts
ISDATE (): This will validate if the specified value is date or not. If it is date it will return 1 else 0.
Syntax: ISDATE (Expression)
Other Functions
COALESCE (): This will return first non-NULL value among the multiple columns specified.
Syntax: COALESCE (Col1, Col2, Col3…)

T-SQL Concepts
ISNULL (): This will replace the NULL with the specified value.
Syntax: ISNULL (Col1, Value)

T-SQL Concepts
Data Types
INT: This will take the values in between -2147483648 to 2147483647. Its size is 4 bytes and it’s constant for any
value in the specified range.
BIGINT: This will take the values in between -9223372036854775808 to 9223372036854775807. Its size is 8
bytes and it’s constant for any value in the specified range.
SMALLINT: This will take the values in between -32768 to 32767. Its size is 2 bytes and it’s constant for any value
in the specified range.
TINYINT: This will take the values in between 0 to 255. Its size is 1 bytes and it’s constant for any value in the
specified range.
Sreehari Katageri, http://Skatageri.blogspot.com | Data Types 59

T-SQL Concepts
CHAR: This is of fixed length in memory. Even though if u define length as 10 bytes and use only 5 characters, in
the memory 10 bytes are allotted (1 character = 1 byte). This data type is used when the values in the columns are
of consistent size/wont vary. Maximum characters this can accommodate is 8000.
Syntax: CHAR (n)
NCHAR: This is of fixed length in memory. Even though if u define length as 10 bytes and use only 2 characters, in
the memory 10 bytes are allotted (1 character = 2 byte). This data type is used when the values in the columns are
of consistent size/wont vary. This is used for special languages like Chinese, Arabic, and German etc. Maximum
characters this can accommodate is 4000.
Syntax: NCHAR (n)
VARCHAR: This is of variable length in memory. Even though if u define length as 10 bytes and use only 2
characters, in the memory 2 bytes are allotted (1 character = 2 byte). This data type is used when the values in the
columns are of not consistent size. Maximum characters this can accommodate is 8000. VARCHAR (max) will take
only 8000 characters.
Syntax: VARCHAR (n) OR VARCHAR (MAX)
NVARCHAR: This is of variable length in memory. Even though if u define length as 10 bytes and use only 2
characters, in the memory 4 bytes are allotted (1 character = 2 byte). This data type is used when the values in the
columns are of not consistent size. Maximum characters this can accommodate is 4000. NVARCHAR (max) will
take only 4000 characters.
Syntax: VARCHAR (n) OR VARCHAR (MAX)

T-SQL Concepts
BIT: This is an integer type which can take either 1 or 0 or NULL.
NUMERIC/ DECIMAL: Numeric and Decimal data types are same. They give the same result. It takes the precision,
which is the length of the digit and the scale, which tells how many digits after the decimal point. Maximum
precision you can specify is 38.
Syntax: NUMERIC (precision, scale)

DECIMAL (precision, scale)
FLOAT: This is similar to Numeric and Decimal. In case of Numeric and Decimal if the length of value is less than
the precision, then the end of digits will be occupied by 0’s. For example my value is 12.34, but the precision is 5
and scale is 3, result will be 12.340. But in case of Float it will not append with 0’s.
Precision with 1 to 24 will occupy 7 digits which takes 4 bytes.
Precision with 25 to 53 will occupy 15 digits which takes 8 bytes.
So basically for float it depends on how many bytes your column needs to be.
Syntax: FLOAT (p)

FLOAT

T-SQL Concepts
DATETIME: Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour
clock. It takes both date and the time
DATE: This takes only date part.
MONEY: Data types that represent monetary or currency values.
USER DEFINED DATA TYPE: In some case we may need to define our own data type. Like a column with phone
number, email address etc. These columns should take only those values defined with some rule.
Example 1: Just create user defined data type saying the column should take varchar value of length less than or
equal to 20
Step 1: Create the Data type

CREATE TYPE dbo.Name FROM VARCHAR(20)
Step 2: Create a table with data type name

DECLARE @Table TABLE
(
CustName Name
)
Step 3: Insert a record with string more than 20 characters, it will throw error.
INSERT INTO @Table VALUES ('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
Step 4: Insert a record with less than or equal to 20 character. The record gets inserted.
INSERT INTO @Table VALUES ('AAAAAAAAAAAAAAAAAAA')

T-SQL Concepts
Example 2: Just create user defined data type saying the column should take varchar value of length less than or
equal to 14 and column with phone number, which should take either “Unknown Number” or number with
following format : +91-9999999999.
Step 1: Create the data type

CREATE TYPE dbo.Phno FROM VARCHAR(14) NOT NULL--- +91-9667556432
Step 2: Create a rule

CREATE RULE dbo.Phno_Rule
AS
(
@Phone='Unknown Number'
)
OR
(LEN(@Phone)=14
AND SUBSTRING(@Phone,1,1)='+'
AND SUBSTRING(@Phone,4,1)='-')
GO
Step 3: Bind the rule with data type

EXEC sp_bindrule 'Phno_Rule', 'Phno'
Step 4: Create the table with user defined data type

IF EXISTS (SELECT * FROM sys.tables WHERE name='UDD_Example')
DROP TABLE Test.dbo.UDD_Example
CREATE TABLE Test.dbo.UDD_Example

(
CustName Name,
PhoneNumber Phno
)
Step 4: Insert records

INSERT INTO Test.dbo.UDD_Example(CustName,PhoneNumber)VALUES ('AAAAAA','+9919664534236')
INSERT INTO Test.dbo.UDD_Example(CustName,PhoneNumber)VALUES ('AAAAAAAAA','+91-9876234567')
INSERT INTO Test.dbo.UDD_Example(CustName,PhoneNumber) VALUES ('AAAAAA','Unknown Number')
INSERT INTO Test.dbo.UDD_Example(CustName) VALUES ('AAAAAA')

T-SQL Concepts
Identity Property
IDENTITY: Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE
Transact-SQL statements. It is an integer incremental number as specified by the seed. Seed is the value that is
used for the very first row loaded into the table. Increment is the incremental value that is added to the identity
value of the previous row that was loaded.
Syntax: IDENTITY (Seed, increment)
IDENTITY INSERT ON/OFF: Allows explicit values to be inserted into the identity column of a table. Identity
column must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to
be nonnullable.
SET IDENTITY_INSERT Test.dbo.ID_Example ON; -- Allows value to insert in identity column
SET IDENTITY_INSERT Test.dbo.ID_Example OFF; -- Not Allows value to insert in identity column
Sreehari Katageri, http://Skatageri.blogspot.com | Identity Property 64

T-SQL Concepts
RESEED IDENTITY: This will reset the identity value as specified.
IF EXISTS (SELECT * FROM sys.tables WHERE name='ID_Example')

DROP TABLE Test.dbo.ID_Example
CREATE TABLE Test.dbo.ID_Example

(
ID_1 INT IDENTITY(1,1),
Name VARCHAR(10)
)
INSERT INTO Test.dbo.ID_Example VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G')

SELECT * FROM Test.dbo.ID_Example
DELETE FROM Test.dbo.ID_Example

DBCC CHECKIDENT ('ID_Example', RESEED, 0)



DBCC CHECKIDENT ('ID_Example', RESEED, 1)

Sreehari Katageri, http://Skatageri.blogspot.com | Identity Property 65

T-SQL Concepts
Set operators
EXCEPT: This will return distinct records from the left query output which are not present in the right query
output. If both the query output matches it will not return anything. This is helpful in doing QA.
Syntax: [Left query] EXCEPT [Right Query]
INTERSECT: This will return distinct records which are common between the left query output and the right query
output. This is helpful in doing QA.
Syntax: [Left query] INTERSECT [Right Query]
UNION: This will combine two distinct data sets. It will remove the duplicate records.
Syntax: [Left query] UNION [Right Query]
Sreehari Katageri, http://Skatageri.blogspot.com | Set operators 66

T-SQL Concepts
UNION ALL: This will combine two data sets. It will include the duplicate records.
Syntax: [Left query] UNION ALL [Right Query]
Sreehari Katageri, http://Skatageri.blogspot.com | Set operators 67

T-SQL Concepts
Logical operators
Below is the sample data set
AND: If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.
OR: If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.
BETWEEN: If you want to select records based on the range of values then use BETWEEN. It will consider the
extreme values as well, meaning the extreme values of the range specified, in our example 20 and 27. This can be
used for date range as well.
Sreehari Katageri, http://Skatageri.blogspot.com | Logical operators 68

T-SQL Concepts
EXISTS/ NOT EXISTS: This is used to search for the presence of a row in a specified table that meets certain
criteria. Returns true if a subquery contains any rows.
IF EXISTS (SELECT * FROM sys.tables WHERE name='people')

DROP TABLE Test.dbo.people
CREATE TABLE Test.dbo.people

(
ID int,
firstname varchar (20),
lastname varchar (20),
statecode varchar (2),
alive bit,
height int
)
IF EXISTS (SELECT * FROM sys.tables WHERE name='addressbook')

DROP TABLE Test.dbo.addressbook
CREATE TABLE Test.dbo.addressbook

(
ID int,
email varchar (50)
)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (1,'Paul',
'Revere', 'AL', 0, 72)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (2,'Elgibeth',
'Revere', 'AL', 0, 72)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (3,'Peter',
'John', 'AL', 0, 34)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (4,'John',
'Lennon', 'NY', 0, 69)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (5,'Steve',
'Martin', 'NY', 1, 75)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (6,'George',
'Washington', 'VA', 0, 75)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (6,'Patrick',
'Hamilton', 'VA', 0, 75)
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (1,'Paul', 'Revere',
'paul@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (2,'Wade', 'Harvey',
'wade@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (3,'Jack', 'Powers',
'jack@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (4,'George', 'Washington',
'george@email.com')

T-SQL Concepts
IN: Determines whether a given value matches any value in a subquery or a list.

T-SQL Concepts
NOT IN: Returns the records apart from the values in the sub query result or list.
LIKE: This is used when you don’t know exactly what word/ string you are looking for. Like operator compares
given character string with specified pattern.
Wildcard
character Description Example
WHERE ProductName LIKE '%chai%' displays
Compares 0 or more characters in a all products where productname includes
% string. word 'chai'.
Compares any single character in a WHERE ProductName LIKE '_hai' finds all
_ (underscore) string. four-letter first names that end with hai.
Compares any single character within WHERE ProductName LIKE '[a-c]hai' displays
the specified range or set of characters product name ending with hai and first
[] like range [a-c] or set [abc]. character between a and c.
Compares any single character not WHERE ProductName LIKE '[â-c]%' displays
within the specified range [â-c] or set all product name not starting with character
[^] [âbc]. range a,b and c.

T-SQL Concepts
ALL: This compares a single value against a set of data from a query. With ALL you can use only <= or >=. If you use
<= then the outer query will return only those records where the column value specified in the where clause is less
than the min value in the subquery. If you use >= then the outer query will return only those records where the
column value specified in the where clause is greater than the max value in the subquery.


(
ID int,
alive bit,
height int
)
IF EXISTS (SELECT * FROM sys.tables WHERE name='addressbook')

DROP TABLE Test.dbo.addressbook
CREATE TABLE Test.dbo.addressbook

(
ID int,
email varchar (50)
)

T-SQL Concepts
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (1,'Paul',
'Revere', 'AL', 0, 72)
'Revere', 'AL', 0, 72)
'John', 'AL', 0, 34)
'Lennon', 'NY', 0, 69)
'Martin', 'NY', 1, 75)
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (1,'Paul', 'Revere',
'paul@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (2,'Wade', 'Harvey',
'wade@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (3,'Jack', 'Powers',
'jack@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (4,'George', 'Washington',
'george@email.com')
INSERT INTO Test.dbo.addressbook (ID, firstname, lastname, email) VALUES (5,'George', 'dsfds',
'george@email.com')

T-SQL Concepts
ANY / SOME: This compares a single value against a set of data from a query. For ANY or SOME if anyone value is
present it will return the records. In the below example, both paul and George is present in both the table, so it
returned the value. Like = you can use >= or <= or > or <.
IS NULL / IS NOT NULL: If you want the records with a particular column value as NULL then use IS NULL. If you
want to see on a particular column not null records then use IS NOT NULL.


(
ID int,
alive bit,
height int
)
INSERT INTO Test.dbo.people (ID, firstname, lastname, statecode, alive, height) VALUES (1,'Paul', NULL,
'AL', 0, 72)
'Revere', 'AL', 0, 72)
'John', 'AL', 0, 34)
'Lennon', 'NY', 0, 69)
NULL, 'NY', 1, 75)

T-SQL Concepts
TOP: This is most commonly used with SELECT STATEMENT and also can be used with the UPDATE STATEMENT.
This will select the top specified number of records. In the below query top 50 percent means, it will return 50% of
the entire records. Suppose there are 10 records it will return 5 records.
SELECT TOP 1 * FROM Test.dbo.people
SELECT TOP 50 PERCENT * FROM Test.dbo.people

T-SQL Concepts
Sub Query
Subqueries are of 2 types:
1. Standard or Non Correlated Subqueries
2. Correlated Subqueries
Generally a subquery is a query that is nested inside a SELECT, UPDATE or DELETE statements, or inside another
subquery. A subquery can be used anywhere an expression is allowed. We can have upto 32 levels of subqueries.
Subqueries are slower than joins. A subquery can return a scalar value or a series of value.
Non Correlated subqueries

A non correlated subquery is one which is independent of the outer query and it can be executed on its own
without depending on the outer query.
IF EXISTS (SELECT * FROM sys.tables WHERE name='Employee')

DROP TABLE Test.dbo.Employee
CREATE TABLE Test.dbo.Employee

(
EmpID INT,
EmpSal INT
)
INSERT INTO Test.dbo.Employee VALUES (1,'AAA','HR',10000)
INSERT INTO Test.dbo.Employee VALUES (2,'BBB','HR',20000)
INSERT INTO Test.dbo.Employee VALUES (3,'CCC','HR',40000)
INSERT INTO Test.dbo.Employee VALUES (4,'DDD','IT',23000)
INSERT INTO Test.dbo.Employee VALUES (5,'EEE','IT',42789)
INSERT INTO Test.dbo.Employee VALUES (6,'FFF','IT',12568)
INSERT INTO Test.dbo.Employee VALUES (7,'GGG','IT',28000)
INSERT INTO Test.dbo.Employee VALUES (8,'HHH','HR',80000)
INSERT INTO Test.dbo.Employee VALUES (9,'III','FIN',25670)
INSERT INTO Test.dbo.Employee VALUES (10,'JJJ','FIN',34000)
INSERT INTO Test.dbo.Employee VALUES (11,'KKK','FIN',60000)
INSERT INTO Test.dbo.Employee VALUES (12,'LLL','FIN',10000)
INSERT INTO Test.dbo.Employee VALUES (13,'MMM','FIN',10000)
Sreehari Katageri, http://Skatageri.blogspot.com | Sub Query 76

T-SQL Concepts
Correlated subqueries
A correlated subquery is one where inner query and the outer query are related with each other. This means that
the subquery is executed repeatedly, once for each row that might be selected by the outer query. Inner query and
outer query are not independent to execute.
Sreehari Katageri, http://Skatageri.blogspot.com | Sub Query 77

T-SQL Concepts
Clustered and Non Clustered Index

Index over the table is similar to the index what we see in the books. Index is used to access the content quickly or
to make the key word search faster.
Clustered Index:
This is similar to the telephone directory, where the name is arranged in a sorted order and we directly located the
name and then the phone number in front of it. When a primary key is created on a table, by default clustered index
is created. There can be only one clustered index per table. The column on which index is created will be physically
sorted.
Non - Clustered Index:

This is similar to the index behind the text book, where we look for a key word in the index and find the page. And
then go to that page and again search manually for the key word. We cannot locate directly like the phone number
we got in case of clustered index. When a unique key is created on the table, by default a non-clustered index is
created. There can be multiple non-clustered indexes created on a table. The column on which index is created will
be logically sorted.
Sreehari Katageri, http://Skatageri.blogspot.com | Clustered and Non Clustered Index 78

T-SQL Concepts
Index is made up of a set of pages called index nodes that are organized in a B-tree structure (Binary tree). This
structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom,
as shown in below figure.
When you see the execution plan, you see following few words:
 Table Scan
 Index Scan
 Index Seek
 RID Lookup
 Key Lookup/ Book Mark lookup
Now lets look into these.
 Clustered Index Scan and Table Scan is almost same and expensive, because in case of Clustered index all
data resides in the leaf level of clustered index page and in case of Table scan all the data resides in data
pages. Suppose you have 100 columns and you are interested in 10 columns and you use only 10 columns
in your select query, in case of Clustered index scan even though you are interested in 10 columns the I/O it
takes is for all the columns because the leaf level has all 100 columns. In case of Table scan all data resides
in the data pages and will take time to I/O 100 columns even though you are interested in 10 columns.
 Non Clustered Index scan is better than the clustered index scan. In case of non clustered index scan, the
index page will have only those columns which are defined while creating the index. So even though your
table has 100 columns the I/O it takes is for only those columns which are defined in the index.

T-SQL Concepts
 Clustered Index Scan and Table Scan both are same. Suppose in our example below, we have only clustered
index on ID column and if we execute the below query, we get the Clustered index scan.Because here we
have index on ID but not on City. In the same query instead of city if we use the ID column to filter, you get
the clustered index seek.
In the above figure you can see that the clustered index is created on the ID column. The index page is structured in
to B-Tree. So the search value 3 falls between 1 to 25 at the leaf node. The search is shown with the arrow in the
figure. In case of clustered index the leaf node contains the data.

T-SQL Concepts
CASE 1 (RID Lookup): RID Lookup happens when your table has a NONCLUSTERED index without INCLUDING
any columns from the SELECT and has no CLUSTERED index. If we take our example below, for RID Lookup don’t
have Clustered index to point to the clustered index page.In this case SQL engine will have Row ID for each record
in the data page and same is mapped in the index page too. So using Row ID, table is being scanned to get the
required columns/data from the data page. This is more expensive than the key Lookup. InCase key lookup is
almost nearly equal to RID lookup.
In the above figure you can see that the Non Clustered index is created on the ID column. At the leaf index page you
can see the RID lookup. Using that lookup it will search the data page for the required columns like FirstName and
LastName. Here you can notice that the leaf index page do not include the FirstName and the LastName. So it is an
extra over head to look for those columns in the data page.
CASE 2(Key Lookup/Bookmark Lookup): Key Lookup happens when your table has a CLUSTERED INDEX and
NONCLUSTERED INDEX without including all the columns in the select. If we take our example we have clustered
index on ID column and Non Clustered index on City. FirstName is not included in index page and so when a city is
found at the Non clustered index page it finds for FirstName but its is not there in the non clustered index page so
using Key Lookup ID(which is a primary key value) it points to the Clustered index page and gets the remaining
column it needs.

T-SQL Concepts
CASE 3(Remove Lookup’s): To remove either of lookup INCLUDE all the required columns in the
NONCLUSTERED index. So that all the required columns reside in the index page itself and the data is retrieved at
the faster rate. No need to again look into the data page.
Please refer the below you tube link as well.

https://www.youtube.com/watch?v=puTvvDB2XuQ
NOTE: Index works better on a column when the redundancy of data is less i.e. less repeating values.

T-SQL Concepts
Sample script for working on index
IF EXISTS(SELECT * FROM sys.objects WHERE name='OneIndex')

DROP TABLE OneIndex
-- Create Table OneIndex with few columns

CREATE TABLE OneIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO OneIndex (ID,FirstName,LastName,City)
SELECT TOP 100000000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
UPDATE OneIndex
SET City='California'
WHERE City='Las Vegas'
GO
UPDATE OneIndex
SET City='Las Vegas'
WHERE ID =52
GO

T-SQL Concepts
View purpose and usage

A view is a virtual table i.e, it looks like a table but its actually not a table. A view is built on top of the table. The
data retrived from the view will be from a table or set of tables joined.
What is the purpose of view?

 Using view you can implement data level security.
 To reduce complexity of query when there are multiple join query which is used very often. So you can also
say it as reusability.
Assume that you have a huge table which contains data for multiple departments and multiple regions. Now you
should make sure that each of the department has access to only those fields which are relevant to them. So what
you do is you create a view with only those columns which are required for that department and give user that
view instead of that table. Same way you can implement row level security.
There are two types of views,

 Standard view/ Normal view
 Indexed view/ materialized view
Standard view
In case of standard view data is not stored on the disk. The data from the view is obtained only when the view is
executed i.e., just the definition of the view is stored in the database but not the data. A view is executed normally
like you select a table.
Index view
In case of indexed view data is stored on the disk. When you create an index on the view it becomes index view.
For indexed view there should be a unique clustered index. Without Creating UNIQUE CLUSTERED INDEX we
cannot create NON CLUSTERED INDEX on a view
Some important point’s w.r.t index view:

 Suppose consider a normal view has some aggregation/computation/joins then, every time the view is
executed aggregation/computation/joins need to happen. This degrades the performance. So if index is
created on a view aggregation/computation/joins gets stored physically. When any base table gets updated
the view also gets updated.
 View with index has pre-computed data.
 Indexed views work best when the underlying data is not frequently updated.
 Another cost of indexed views is that the data is actually stored. By applying the clustered index we are
creating a copy of the data. So if we have several indexed views on a single table, we will have several
copies of the data, along with the statistics of the data and the indexes and all of the overhead that goes
with it.
 Index can only be created on the schema bound view. On normal view we can’t create the view.
 In the normal view once view is created we can drop the base table, but in case of indexed view once view
is created if we drop the base table it throws error.
Sreehari Katageri, http://Skatageri.blogspot.com | View purpose and usage 84

T-SQL Concepts
 In schema binding view if we create a view with the SCHEMABINDING option it will lock the tables being
referred by the view and restricts any kind of changes that may change the table schema (No Alter
command). While creating schema binding view, we can’t mention “SELECT * FROM TABLENAME” with the
query. We have to mention the entire column name for reference.
Now let’s see few practical examples.

IF EXISTS (SELECT * FROM sys.tables WHERE name='TSQL_View1_Emp')
DROP TABLE dbo.TSQL_View1_Emp
GO
CREATE TABLE dbo.TSQL_View1_Emp

(
EmpID INT IDENTITY(1,1) PRIMARY KEY,
Gender VARCHAR(1),
Location VARCHAR(50)
)
GO
IF EXISTS (SELECT * FROM sys.tables WHERE name='TSQL_View1_Sal')

DROP TABLE dbo.TSQL_View1_Sal
GO
CREATE TABLE dbo.TSQL_View1_Sal

(
SalID INT IDENTITY(1,1),
EmpID INT,
[Basic] MONEY,
Salary MONEY
)
GO
ALTER TABLE dbo.TSQL_View1_Sal

ADD CONSTRAINT FKTSQL_View1_Sal_EmpID FOREIGN KEY (EmpID) REFERENCES dbo.TSQL_View1_Emp (EmpID)
GO
INSERT INTO dbo.TSQL_View1_Emp VALUES ('AAA','M','Bangalore')

INSERT INTO dbo.TSQL_View1_Emp VALUES ('BBB','M','Delhi')
INSERT INTO dbo.TSQL_View1_Emp VALUES ('CCC','M','Bangalore')
INSERT INTO dbo.TSQL_View1_Emp VALUES ('DDD','F','Mumbai')
INSERT INTO dbo.TSQL_View1_Emp VALUES ('EEE','F','Ahamdabad')
INSERT INTO dbo.TSQL_View1_Emp VALUES ('FFF','F','Goa')
INSERT INTO dbo.TSQL_View1_Emp VALUES ('GGG','M','Mangalore')
GO
INSERT INTO dbo.TSQL_View1_Sal VALUES (1,6000,20000)

GO

T-SQL Concepts
Working with normal view
Creating normal view

T-SQL Concepts

T-SQL Concepts
Working with Indexed view

T-SQL Concepts

T-SQL Concepts

T-SQL Concepts
If you need to decrypt a view better have the scripts backup. Because if you lose the script you need to get some
third party tool to decrypt it. If you need to decrypt then simply remove the ENCRYPTION keyword and then
alter the view.

T-SQL Concepts
Stored procedure purpose and usage

Stored procedure is the repository for sql code, which can be reused. It allows security with the help of encryption.
Stored procedure is pre complied, meaning, every time you execute the normal query it will compile for syntax
error or any other error, but in case of stored proc it is complied during the creatition or alteration of stored proc,
later when ever you execute the stored proc it will not be complied. Stored procedure allows you to organize your
sql code in an organized way.
You can pass parameters to stored proc and return the value from stored proc as well i.e., you can provide both
input parameters and output parameters.
NOTE: Whenever you use GO statement in your stored proc it means it is the end of stored proc. So try to avoid GO
statement while creating the stored proc.
Input and Output parameter
Sreehari Katageri, http://Skatageri.blogspot.com | Stored procedure purpose and usage 92

T-SQL Concepts
IF you want to do any modification then just use ALTER instead of CREATE
Use sp_helptext ‘Stored Proc ’ name to see the definition of stored proc.
sp_helptext usp_Test_Proc2
Nested stored proc
Sreehari Katageri, http://Skatageri.blogspot.com | Stored procedure purpose and usage 93

T-SQL Concepts
User defined function (UDF)

User defined function is used to perform complex logic, which accepts the input and returns a value or table. This is
just like the system functions. Then how it is different from the stored proc. Hope following points make it clear.
 In stored proc you can create temporary table but in UDF you cannot create temporary table.
 In UDF you cannot use PRINT command, but you can use in stored proc.
 We can use insert, update and delete only on the table variable within the function.
 TRY CATCH cannot be used in UDF, but can be used in stored proc.
 We cannot call stored proc from the UDF.
 You can call UDF in SELECT and HAVING statement, but you cannot call stored proc.
There are 3 types of UDF

 Scalar function
 Inline table valued function
 Multi statement table valued function
Scalar functions: Scalar function return only one value.
NOTE: always use dbo schema when calling a scalar function else you get below error.
Msg 195, Level 15, State 10, Line 1
'udf_Scalar_Example' is not a recognized built-in function name.
Inline table valued function: In the Inline table valued function, you can just select a table but you cannot
manipulate the data. So this just returns the table. You should always call the inline table valued function like a
table using SELECT * FROM
Sreehari Katageri, http://Skatageri.blogspot.com | User defined function (UDF) 94

T-SQL Concepts
Multi statement table valued function: In the type of function, you can do manipulation on the table data,
but no on the actual table instead you take a copy of table data into a table variable and do the necessary
modification. Here you can do insert, update and delete over a table variable.
Sreehari Katageri, http://Skatageri.blogspot.com | User defined function (UDF) 95

T-SQL Concepts
PIVOT and UNPIVOT

PIVOT
It provides an easy mechanism in Sql Server to transform rows into columns.

To understand PIVOT with extensive list of examples, let us first create Temporary Table #CourseSales with
sample records as depicted in the below image by using the following script:
--Create Temporary Table #CourseSales

CREATE TABLE #CourseSales
(Course VARCHAR(50),Year INT,Earning MONEY)
GO
--Populate Sample records
INSERT INTO #CourseSales VALUES('.NET',2012,10000)
INSERT INTO #CourseSales VALUES('Java',2012,20000)
INSERT INTO #CourseSales VALUES('Java',2013,30000)
GO
In this example the #CourseSales Table data is Pivoted so that the Course becomes the column headings.
In this example the #CourseSales Table data is Pivoted so that the Year becomes the column headings.
Sreehari Katageri, http://Skatageri.blogspot.com | PIVOT and UNPIVOT 96

T-SQL Concepts
CREATE TABLE #Sales

(SalesId INT IDENTITY(1,1), SalesDate DateTime)
GO
--Populate 1000 Sample Sales Records With
--Random past 0-1000 days as sales date
INSERT INTO #Sales(SalesDate)
VALUES(DATEADD(dd, - CONVERT(INT, (1000+1)*RAND()),GETDATE()))
GO 1000
Transforming Sales Table data to a Quarterly aggregated sales data with Quarters as the Columns in the result set.
Transforming Sales Table data to a Monthly aggregated sales data with Months as the Columns in the result set.

T-SQL Concepts
UNPIVOT
UNPIVOT is the reversal of the PIVOT operation. It basically provides a mechanism for transforming columns into
rows.

T-SQL Concepts
Temp table, Table variable, CTE

There might be a situation where you need to store some data temporarily; in that case u can use either temporary
table or table variable or CTE (Common Table Expression).
Temporary table
These are the tables created physically in the tempdb. You can create this table same as the normal table and also
this table can have constraints, index like normal table. Again this is classified into two types:
 Local Temporary table
 Global Temporary table
Local temporary tables are created with single hash i.e. #. Ex: #Temp or #Anyname. This table exists for a
particular session. Now what is a session? Session is, when u open the management studio you open a query
window. Each query window you open is a session. So this table created in one session cannot be accessed in other
session.
Now go to another query window and execute the below query,the session id changes and u will get an error
message.
SELECT * FROM tempdb.dbo.sysobjects WHERE name LIKE '%#TempSample%'

SELECT @@SPID AS SessionID
SELECT * FROM TempSample
(1 row(s) affected)
(1 row(s) affected)
Invalid object name '#TempSample'.
So once the query window where u created the #temp table is closed #temp table gets dropped by its own. So
#temp exists for a particular session until that session exists.
Sreehari Katageri, http://Skatageri.blogspot.com | Temp table, Table variable, CTE 99

T-SQL Concepts
Global temporary tables are created by double hash i.e. ## Ex: ##TempSample or ##Anyname. This table is
accessible in all the session untill the session the table was created is not closed. Once the query window where the
## table was created is closed the table gets dropped on its own.
Note: Scope of Temporary table is within the session.
Table variable
This is just like a variable. This table exists for a particular batch of execution. It gets dropped once it comes out of
batch. This table is created in tempdb but not the memory.
In the below example you can see that there is a GO after the first select * from sys.tables and then create the table
variable and then immediately again select * from sys.tables without GO. Since Go represents the batch the table
exists for a batch. So you notice that in the first select * from sys.tables there are 12 entries but in the second one
you notice 13 entries in sys.tables. So it means table variables are stored in the tempdb for that batch of execution
and later once batch execution is complete the table is dropped. Here the tempdb do not hold the table with its
original name instead you can see #BD763C6F.

T-SQL Concepts
Table variable allows to create primary key at the time of declaration as below:
DECLARE @TempSample TABLE (ID INT PRIMARY KEY, NAME VARCHAR(40))
Cannot create Non clustered index on Table variable.

Note: Scope of table variable is within the batch.
CTE (Common Table Expression)

This is just like a view which has a base table, mainly used to manipulate the base tables data. This exists for the
scope of statement. It can be used as recursion. This do not store data in tempdb rather it uses memory to store
data. You cannot create any index on CTE . In the below fig you can see tempdb sysobjects do not have any record
even though CTE table is created. In the below example you can see that the number of records in sys.tables
remains same before the CTE and after the CTE is created. This means when CTE is executed no entry is made in
the tempdb.

T-SQL Concepts
 CTE is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE
statement.
 SQL Server supports two types of CTEs—recursive and nonrecursive.
 CTE is just like a view. In case if we don’t want to store the definition in the database, we can go with CTE.
 When dealing with sub-queries, it is often required that you need to select a part of the data from a sub
query or even join data from a query with some other tables. In that case, either you have an option to
name your sub-queries with an alias or to use it directly. Gradually your requirement is getting more and
more complex and your query would look unmaintainable at any time. CTE allows you to define the sub
query at once, name it using an alias and later call the same data using the alias just like what you do with a
normal table.
 CTE can also be used in the view.
 The scope of the CTE is limited to very first SELECT statement

T-SQL Concepts
 Use MAXRECURSION to prevent infinite loop in recursive CTE. The recursion level ranges from 0 and
32,767.
 0 means max recursion.
Recursive CTE
Example 1:
;WITH CTE
AS
(
SELECT 1 AS val
UNION ALL
SELECT val+1
FROM CTE
WHERE val<=3
)
SELECT *
FROM CTE
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
Example 2:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Emp]') AND type in
(N'U'))
BEGIN
--Create the table
CREATE TABLE dbo.Emp
(
EmpID int PRIMARY KEY,
EmpName varchar(30),
MgrID int FOREIGN KEY REFERENCES Emp(EmpID)
)
--Create a non-clustered index for query performance

CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
--Populate the table with data
INSERT dbo.Emp SELECT 1, 'President', NULL
INSERT dbo.Emp SELECT 2, 'Vice President', 1
INSERT dbo.Emp SELECT 3, 'CEO', 2
T-SQL Concepts
INSERT dbo.Emp SELECT 4, 'CTO', 2

INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.Emp SELECT 12, 'Tester 1', 11
INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7
END
GO
----–Hierarchical Query using Common Table Expressions

WITH ReportingTree (EmpID, EmpName, MgrID, Lvl)
AS
(
--–Anchor Member
SELECT *, 0 as Lvl FROM emp WHERE MgrId IS NULL
UNION ALL
--–Recusive Member
SELECT emp.EmpID, emp.EmpName, emp.MgrId, ReportingTree.Lvl+1
FROM emp
INNER JOIN ReportingTree on emp.MgrID = ReportingTree.EmpID
)
SELECT * FROM ReportingTree
OPTION (maxrecursion 0)

T-SQL Concepts

T-SQL Concepts
And so on……

T-SQL Concepts

T-SQL Concepts
Cross Apply and Outer Apply

These are the special types of joins used with functions. These joins are used when there is a need of table
valued functions to be used in the join. The CROSS APPLY acts like INNER JOIN/ CROSS JOIN, whereas
OUTER APPLY acts as LEFT JOIN. This works like correlated query, for each record in the left query it
checks for the matching records from the table valued function. Note that you need to use the table
valued function at the right side.
Let’s see a simple example with the below given data set.

(
Emp_ID VARCHAR(6),
)

IF EXISTS(SELECT * FROM sys.tables WHERE name='tblDept')

DROP TABLE tblDept
CREATE TABLE tblDept

(
Dept_Id VARCHAR(6),
)
INSERT INTO tblDept VALUES ('D001','HR')

INSERT INTO tblDept VALUES ('D002','FINANCE')
INSERT INTO tblDept VALUES ('D003','IT')

SELECT * FROM tblDept
Sreehari Katageri, http://Skatageri.blogspot.com | Cross Apply and Outer Apply 108

T-SQL Concepts
We cannot join two tables with ON condition using CROSS APPLY. But we can use CROSS APPLY like
CROSS JOIN.

T-SQL Concepts
Now lets see how to use CROSS APPLY and OUTER APPLY. Before that lets create table valued function
which accepts an input deptid and return the department details.
CREATE FUNCTION dbo.Fn_Dept (@deptId VARCHAR(10))

RETURNS TABLE
AS
RETURN
SELECT *
FROM dbo.tblDept
WHERE Dept_Id=@deptId
Query to get the maximum value from date1, date2, date3, date4 for each of the product, where ProdId is
a unique column (Primary key column)

T-SQL Concepts
Dynamic SQL Query

Dynamic SQL queries are those queries which are built at runtime and then executed. It is use full to
construct the query dynamically when you need to decide the conditions, tables to be used etc. at the run
time.
Let’s understand with examples
Example 1: Suppose there is a table with the same table structure in multiple databases, but the data
within in it is different. Instead of running the query multiple times in different databases, we will write a
dynamic query to pull the data from multiple databases with only one execution.
USE [Test]
GO


(
Emp_ID VARCHAR(6),
)

USE [Temp]
GO


(
Emp_ID VARCHAR(6),
)

Sreehari Katageri, http://Skatageri.blogspot.com | Dynamic SQL Query 111

T-SQL Concepts
USE [Demo]
GO


(
Emp_ID VARCHAR(6),
)
Below is the dynamic SQL.
USE [Demo]
GO
DECLARE @SQL VARCHAR(MAX) ---- Variable to store the SQL query.

DECLARE @dbName VARCHAR(100) ---- Variable to hold the database name for each loop.
DECLARE @cnt INT ---- Initialize the counter and then for every loop increment it.
DECLARE @MaxCnt INT ---- Maximum number of loops.
IF OBJECT_ID('tempdb..#DB_List') IS NOT NULL

DROP TABLE #DB_List
CREATE TABLE #DB_List ---- Store the databases need to be looped.

(
id INT IDENTITY(1,1),
DBName VARCHAR(100)
)
INSERT INTO #DB_List

SELECT Name FROM sys.databases WHERE name IN ('Test','Temp')
SELECT @MaxCnt=COUNT(*) FROM #DB_List ---- get max number of database to be looped.
SET @cnt=1
WHILE (@cnt<=@MaxCnt)
BEGIN
SELECT @dbName=DBName FROM #DB_List WHERE id=@cnt
SET @SQL= 'INSERT INTO tblEmployee

SELECT *
FROM '+@dbName+'.dbo.tblEmployee'
--PRINT (@SQL) ----- Use the PRINT statement to validate the dynamic sql script output.
EXEC(@SQL) ----- Executes the dynamic SQL.
SET @cnt=@cnt+1 ----- Increment the counter.

END

T-SQL Concepts
Example 2: Suppose you need to create a stored proc which accepts the comma separated varchar
values, which in turn need to be passed to a scalar function. Results obtained from the scalar function
need to be used in the WHERE condition of a SQL query.
USE [Demo]
GO
CREATE FUNCTION [dbo].[Split]

(
@str VARCHAR(MAX),
@del VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @len INT
DECLARE @i INT
DECLARE @res VARCHAR(MAX)
SET @res=''
SET @len=LEN(@str)
SET @i=CHARINDEX(@del,@str)
WHILE @i>=1
BEGIN
SET @res=@res+','''+LEFT(@str,@i-1)+''''
SET @str=SUBSTRING(@str,@i+1,@len)
SET @i=CHARINDEX(@del,@str)
END
SET @res=@res+','''+@str+''''
SET @res=SUBSTRING(@res,2,LEN(@res))
RETURN @res
END
GO
CREATE PROC dbo.usp_dynamic_Sql

(@EmpId AS VARCHAR(MAX))
AS
BEGIN
DECLARE @SQL VARCHAR(MAX)
SET @SQL= 'SELECT *,''Dynamic SQL Example'' AS Col
FROM tblEmployee WHERE Emp_ID IN ('+dbo.Split(@EmpId,',')+')'
EXEC (@SQL)
END
GO
EXEC dbo.usp_dynamic_Sql 'E001,E006,E003'

T-SQL Concepts
Example 3: Using variables in dynamic SQL.

DECLARE @EmpName VARCHAR(MAX)
SET @EmpName='GGG,EEE'
DECLARE @SQL NVARCHAR(MAX) ----- Note that for parameter passing in dynamic sql always use NVARCHAR
DECLARE @cntOutput INT
SET @SQL='SELECT @cnt=COUNT(*)

FROM tblEmployee
WHERE Emp_Name IN ('+dbo.Split(@EmpName,',')+')'
EXECUTE sp_executesql @statement = @SQL,

@params = N'@cnt INT OUTPUT', --- defining the variables used in the dynamic SQL.
@cnt = @cntOutput OUTPUT --- Assigning value in @cnt to @cntOutput
PRINT @cntOutput

T-SQL Concepts
Table Valued Parameter (TVP)

TVP is used in the scenario where we need to pass a table data to Stored procedure. This was introduced
in SQL Server 2008 and further version.
Following points to be followed when using TVP:
 Create a Table type and define the table Structure.

 Create the Stored Procedure by passing the Table Type parameters.
 Declare the Table Type Variable.
 Insert the Table data to Table Type Variable.
 Exec the Stored Procedure by passing Table Type Variable.
Create the Sample table:
IF OBJECT_ID('SalesHistory')>0
DROP TABLE SalesHistory;
GO
CREATE TABLE [dbo].[SalesHistory]
(
[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
Step1: Create a Table type and define the table Structure
CREATE TYPE SalesHistoryTableType AS TABLE

(
[Product] [varchar](10) NULL,
[SaleDate] [datetime] NULL,
[SalePrice] [money] NULL
)
GO
NOTE: check the system table if Table Type is created or not

SELECT * FROM sys.table_types
Sreehari Katageri, http://Skatageri.blogspot.com | Table Valued Parameter (TVP) 115

T-SQL Concepts
Step2: Create the Stored Procedure by passing the Table valued parameters.
NOTE: When a Table Type is passed as parameter it should always be READONLY.
Cannot perform DML operation on Table valued Parameter
CREATE PROCEDURE usp_InsertBigScreenProducts

(
@TableVariable SalesHistoryTableType READONLY
)
AS
BEGIN
INSERT INTO SalesHistory
(
Product, SaleDate, SalePrice
)
SELECT Product, SaleDate, SalePrice
FROM @TableVariable
WHERE Product = 'BigScreen'
END
GO
Step3: Declare the Table Type Variable

DECLARE @DataTable AS SalesHistoryTableType
Step4: Insert the Table data to Table Type Variable. Note that not exactly the below insert to be used
(This is for example). Inserting the record into Table Type variable can be based on your scenario.
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=1000)

BEGIN
INSERT INTO @DataTable(Product, SaleDate, SalePrice)

VALUES ('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))

VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))

VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
SET @i = @i + 1
END
Step5: Exec the Stored Procedure by passing Table Type Variable.

EXECUTE usp_InsertBigScreenProducts @TableVariable = @DataTable
Sreehari Katageri, http://Skatageri.blogspot.com | Table Valued Parameter (TVP) 116

T-SQL Concepts
Merge
Merge is available in SQL Server 2008 and higher version. Merge combines INSERT, UPDATE and DELETE
in one single statement based on the conditions specified. Merge can be used for incremental inserts and
updates.
The Merge syntax looks like below:
MERGE [INTO] <target table>

USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>
Let’s understand with an example.

IF EXISTS(SELECT * FROM sys.tables WHERE name='SrcTableEmp')
DROP TABLE SrcTableEmp
GO
CREATE TABLE dbo.SrcTableEmp

(
EmpID VARCHAR(5),
EmpLoc VARCHAR(40),
EmpSal MONEY
)
INSERT INTO dbo.SrcTableEmp VALUES('E001','AAA','HR','Delhi',20000)

INSERT INTO dbo.SrcTableEmp VALUES('E002','BBB','IT','Bangalore',30000)
INSERT INTO dbo.SrcTableEmp VALUES('E003','CCC','IT','Delhi',15000)
INSERT INTO dbo.SrcTableEmp VALUES('E004','AAA','HR','Bangalore',10000)
SELECT * FROM dbo.SrcTableEmp
IF EXISTS(SELECT * FROM sys.tables WHERE name='TgtTableEmp')

DROP TABLE TgtTableEmp
GO
CREATE TABLE dbo.TgtTableEmp

(
EmpID VARCHAR(5),
EmpLoc VARCHAR(40),
EmpSal MONEY
)
SELECT * FROM dbo.TgtTableEmp
Sreehari Katageri, http://Skatageri.blogspot.com | Merge 117

T-SQL Concepts
MERGE INTO dbo.TgtTableEmp AS C ----- Target Table

USING dbo.SrcTableEmp AS CT ON C.EmpID = CT.EmpID --- Source Table
--- If the Employee ID exists in the target table, then do and update from the source columns
WHEN MATCHED THEN
UPDATE SET
C.EmpName = CT.EmpName,
C.EmpDept = CT.EmpDept,
C.EmpLoc = CT.EmpLoc,
C.EmpSal = CT.EmpSal
--- If the Employee ID do not exists in the target table, then insert the records from the source
WHEN NOT MATCHED BY TARGET THEN
INSERT (EmpID, EmpName, EmpDept,EmpLoc,EmpSal)
VALUES (CT.EmpID, CT.EmpName, CT.EmpDept,CT.EmpLoc,CT.EmpSal);
GO
SELECT * FROM dbo.TgtTableEmp
UPDATE dbo.SrcTableEmp
SET EmpLoc=''
WHERE EmpID='E003'
INSERT INTO dbo.TgtTableEmp VALUES('E005','AAA','HR','Bangalore',10000)
MERGE INTO dbo.TgtTableEmp AS C

USING dbo.SrcTableEmp AS CT ON C.EmpID = CT.EmpID
WHEN MATCHED THEN
UPDATE SET
VALUES (CT.EmpID, CT.EmpName, CT.EmpDept,CT.EmpLoc,CT.EmpSal)
---- When the source table do not have record and target table has the record then delete the record in
target
WHEN NOT MATCHED BY SOURCE THEN
DELETE OUTPUT $action, inserted.EmpID, deleted.EmpID;
GO

T-SQL Concepts
MERGE INTO dbo.TgtTableEmp AS C ----- Target Table

USING dbo.SrcTableEmp AS CT ON C.EmpID = CT.EmpID --- Source Table
--- If the Employee ID exists in the target table, then do and update from the source columns
WHEN MATCHED THEN
UPDATE SET
--- If the Employee ID do not exists in the target table, then insert the records from the source
VALUES (CT.EmpID, CT.EmpName, CT.EmpDept,CT.EmpLoc,CT.EmpSal)
OUTPUT $action, inserted.EmpID, deleted.EmpID;

T-SQL Concepts
Cursor and While loop

Cursor
Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL
commands that operate on all the rows in the result set at one time. We use cursor when we need to
update records in a database table in singleton fashion means row by row.
Life cycle of cursor:

 Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
 Open: A cursor is opened and populated by executing the SQL statement defined by the cursor.
 Fetch: When cursor is opened, rows can be fetched from the cursor one by one to do data
manipulation.
 Close: After data manipulation, we should close the cursor explicitly.
 Deallocate: Finally, we need to delete the cursor definition and release all the system resources
associated with the cursor.
Let’s understand this with examples

IF EXISTS(SELECT * FROM sys.tables WHERE name='Employee')
DROP TABLE Employee
GO

(
EmpName varchar (50) NOT NULL,
Salary int NOT NULL,
Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')

INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
Sreehari Katageri, http://Skatageri.blogspot.com | Cursor and While loop 120

T-SQL Concepts
---Step1: Declare the variables to hold the fetched record values.

DECLARE @EmpID INT,
@EmpName VARCHAR (50),
@Salary INT
---Step2: Declare Cursor

DECLARE Cur CURSOR FOR
SELECT EmpID, EmpName, Salary FROM Employee
---Step3: Open Cursor

OPEN Cur
---Step4: Fetch
FETCH NEXT FROM Cur INTO @EmpID,@EmpName,@Salary
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'ID : '+ CONVERT(VARCHAR(20),@EmpID)+', Name : '+@EmpName+ ', Salary :
'+convert(VARCHAR(20),@Salary)
END
---Step5: Close
CLOSE Cur
---Step6: Deallocate
DEALLOCATE Cur
Since we say that cursor is an object, it should be available in the system tables like tables, views, objects
etc. Suppose you don’t close and deallocate the cursor, it will be available in the system tables until you
close and deallocate it.

T-SQL Concepts
GLOBAL AND LOCAL Cursor
By default the cursor is global. To make the cursor local, you need to define LOCAL while declaring the
cursor. If GLOBAL is set, it means that scope of the cursor is global to the connection. Simply it can be
referenced in any stored procedure or batch executed by same connection. If LOCAL is used, it is only for
the batch which it was created
In the below example notice that cursor is declared in one database and is used in another database. Even
though you don’t use GLOBAL it can be accessed in Test DB.
---- Declare it in Temp DB

USE Temp
GO
DECLARE Cur CURSOR GLOBAL FOR

SELECT EmpID, EmpName, Salary FROM Employee
OPEN Cur
GO
---- Use it in Test DB

USE Test
GO
DECLARE @EmpID INT,

@Salary INT
FETCH NEXT FROM Cur INTO @EmpID, @EmpName, @Salary
BEGIN
END
CLOSE Cur
DEALLOCATE Cur

T-SQL Concepts
Below is an example for LOCAL cursor
--- Declare it in Temp DB

USE Temp
GO
DECLARE Cur CURSOR LOCAL FOR

SELECT EmpID,EmpName,Salary FROM Employee
OPEN Cur
GO
---- Use it in Test DB

USE Test
GO
DECLARE @EmpID INT,

@Salary INT
BEGIN
END
CLOSE Cur
DEALLOCATE Cur
Once you execute above query you will get the below error message.

A cursor with the name 'Cur' does not exist.

T-SQL Concepts
FORWARD_ONLY AND SCROLL Cursor
FORWARD_ONLY: Specifies that cursor can only fetch data sequentially from the first to the last row.
FETCH NEXT is the only fetch option supported. Note that by default the cursor is FORWARD_ONLY.
DECLARE @EmpID INT,
@Salary INT
DECLARE Cur CURSOR FORWARD_ONLY FOR

OPEN Cur
BEGIN
END
CLOSE Cur
DEALLOCATE Cur
SCROLL: The cursor can be scrolled back and forth with "FETCH NEXT", "FETCH PRIOR".
DECLARE @EmpID INT,
@Salary INT
DECLARE Cur CURSOR SCROLL FOR

OPEN Cur
FETCH LAST FROM Cur INTO @EmpID,@EmpName,@Salary
BEGIN
FETCH PRIOR FROM Cur INTO @EmpID,@EmpName,@Salary
END
CLOSE Cur
DEALLOCATE Cur

T-SQL Concepts
STATIC AND DYNAMIC Cursor
STATIC: Specifies that cursor will use a temporary copy of the data instead of base tables. This cursor
does not allow modifications and modifications made to base tables are not reflected in the data returned
by fetches made to this cursor.
DYNAMIC: Specifies that cursor reflects all data changes made to the base tables as you scroll around the
cursor.
DECLARE @EmpID INT,

@Salary INT
DECLARE Cur CURSOR STATIC FOR

OPEN Cur
BEGIN
END
CLOSE Cur
DEALLOCATE Cur
-------
DECLARE @EmpID INT,

@Salary INT
DECLARE Cur CURSOR DYNAMIC FOR

OPEN Cur
BEGIN
END
CLOSE Cur
DEALLOCATE Cur
For all the other cursor options and in depth knowledge please refer the below links.
http://www.databasejournal.com/features/mssql/article.php/1439731/Using-SQL-Server-Cursors.htm
http://www.dotnet-tricks.com/Tutorial/sqlserver/4L7I050512-SQL-Server-Basics-of-Cursors.html

T-SQL Concepts
While loop
It is also like cursor but not stored as an object. In case of cursor you no need to initialize the counter
explicitly for looping, but in case of while loop you have to set the counter explicitly.
We will take the above cursor example and try to convert it to while loop.
DECLARE @cnt INT

DECLARE @MaxCnt INT
DECLARE @EmpID INT
DECLARE @EmpName VARCHAR(50)
DECLARE @Salary INT
IF OBJECT_ID('tempdb..#temp') IS NOT NULL

DROP TABLE #temp
CREATE TABLE #temp

(
ID INT IDENTITY(1,1),
EmpID INT,
Salary INT
)
INSERT INTO #temp ----- Dump all the required data into a temp table
SELECT @MaxCnt=COUNT(*) FROM #temp ---- Get the maximum number of loop
SET @cnt=1 --- Initilize the counter
WHILE (@cnt<= @MaxCnt) ---- loop untill the counter reaches the maximum loop
BEGIN
SELECT @EmpID=EmpID,@EmpName=EmpName,@Salary=Salary FROM #temp WHERE ID=@Cnt ---- Fetch the

record one by one
SET @cnt=@cnt+1 --- Increment the counter

END

T-SQL Concepts
XML PATH
What is XML?
 XML stands for Extensible Markup Language

 XML is a markup language much like HTML
 XML was designed to describe data, not to display data
 XML tags are not predefined. You must define your own tags.
 XML is not the replacement of HTML. XML was designed to describe data, with focus on what data
is. HTML was designed to display data, with focus on how data looks
 HTML is about displaying information, while XML is about carrying information.
 XML is both human readable and machine readable format.
 XML is mostly helpful for data interchange over the internet.
 XML file extension is FileName.xml
Please refer the below link for more information on XML

http://www.w3schools.com/xml/xml_whatis.asp
Simple XML file is as shown below
<note>
<to>Tove</to>
<from>Jani</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend!</body>
</note>
If you notice above the tags are user defined, not like HTML which are predefined.
XML PATH
The normal SELECT statement will retrieve the record as rowset, but what if you need it in an XML
format. So you can go with FOR XML.
Different options available with FOR XML are:

 RAW
 AUTO
 PATH
Let’s understand this FOR XML with the examples.
Sreehari Katageri, http://Skatageri.blogspot.com | XML PATH 127

T-SQL Concepts
<root> ----- Root node

<row> -----Child Node
<EmpID>1</EmpID> ---- Element
<EmpName>Mohan</EmpName>
<Salary>12000</Salary>
<Address city="'Bangalore' zip='560001'"/>--- city and zip are attribute
</row>
<row>
<EmpID>2</EmpID>
<EmpName>Pavan</EmpName>
<Address city="'Bangalore' zip='560001'"/>
</row>
<row>
<EmpID>3</EmpID>
<EmpName>Amit</EmpName>
</row>
<row>
<EmpID>4</EmpID>
<EmpName>Sonu</EmpName>
</row>
<row>
<EmpID>5</EmpID>
<EmpName>Deepak</EmpName>
</row>
</root>
FOR XML RAW

As you can see, each <row> element maps to a row that is returned by the SELECT statement, and each
column, by default, is treated as an attribute of that element.

T-SQL Concepts
SELECT EmpID,EmpName,Salary
FROM Employee
FOR XML RAW ('Employee')
<Employee EmpID="1" EmpName="Mohan" Salary="12000" />

<Employee EmpID="2" EmpName="Pavan" Salary="25000" />
<Employee EmpID="3" EmpName="Amit" Salary="22000" />
<Employee EmpID="4" EmpName="Sonu" Salary="22000" />
<Employee EmpID="5" EmpName="Deepak" Salary="28000" />
FROM Employee
FOR XML RAW ('Employee'),ROOT
<root>
</root>
FROM Employee
FOR XML RAW ('Employee'),ROOT('Company')
<Company>
</Company>
FROM Employee
FOR XML RAW ('Employee'),ROOT('Company'),ELEMENTS
<Company>
<Employee>
<EmpID>1</EmpID>
</Employee>
<Employee>
<EmpID>2</EmpID>
</Employee>
<Employee>
<EmpID>3</EmpID>
</Employee>
<Employee>
<EmpID>4</EmpID>

T-SQL Concepts
</Employee>
<Employee>
<EmpID>5</EmpID>
</Employee>
</Company>
IF EXISTS(SELECT * FROM sys.tables WHERE name='Employee')

DROP TABLE Employee
GO

(
Address varchar (200) NOT NULL,
)
GO

INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,NULL)
SELECT EmpID, EmpName, Salary, Address

FROM Employee
FOR XML RAW ('Employee'), ROOT ('Company'), ELEMENTS XSINIL
<Company xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmpID>1</EmpID>
<Address>Noida</Address>
</Employee>
<Employee>
<EmpID>2</EmpID>
<Address>Delhi</Address>
</Employee>
<Employee>
<EmpID>3</EmpID>
<Address xsi:nil="true" /> ---- since the address is NULL in the DB.
</Employee>
<Employee>
<EmpID>4</EmpID>

T-SQL Concepts
</Employee>
<Employee>
<EmpID>5</EmpID>
<Address>Gurgaon</Address>
</Employee>
</Company>
FOR XML AUTO
SELECT EmpID,EmpName,Salary,Address
FROM Employee
FOR XML AUTO
<Employee EmpID="1" EmpName="Mohan" Salary="12000" Address="Noida" />

<Employee EmpID="2" EmpName="Pavan" Salary="25000" Address="Delhi" />
<Employee EmpID="4" EmpName="Sonu" Salary="22000" Address="Noida" />
<Employee EmpID="5" EmpName="Deepak" Salary="28000" Address="Gurgaon" />
FROM Employee
FOR XML AUTO, ROOT ('Employees')
<Employees>
<Employee EmpID="1" EmpName="Mohan" Salary="12000" Address="Noida" />
<Employee EmpID="2" EmpName="Pavan" Salary="25000" Address="Delhi" />
<Employee EmpID="4" EmpName="Sonu" Salary="22000" Address="Noida" />
<Employee EmpID="5" EmpName="Deepak" Salary="28000" Address="Gurgaon" />
</Employees>
FROM Employee
FOR XML AUTO, ROOT ('Employees'),ELEMENTS
<Employees>
<Employee>
<EmpID>1</EmpID>
</Employee>
<Employee>
<EmpID>2</EmpID>
</Employee>
<Employee>
<EmpID>3</EmpID>
<Salary>22000</Salary> ---- Here Address Element is missing as it is NULL.
</Employee>
<Employee>
T-SQL Concepts
<EmpID>4</EmpID>
</Employee>
<Employee>
<EmpID>5</EmpID>
</Employee>
</Employees>
FROM Employee
FOR XML AUTO, ROOT ('Employees'),ELEMENTS XSINIL
<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Employee>
<EmpID>1</EmpID>
</Employee>
<Employee>
<EmpID>2</EmpID>
</Employee>
<Employee>
<EmpID>3</EmpID>
<Address xsi:nil="true" />
</Employee>
<Employee>
<EmpID>4</EmpID>
</Employee>
<Employee>
<EmpID>5</EmpID>
</Employee>
</Employees>

T-SQL Concepts
FOR XML PATH
FROM Employee
FOR XML PATH ('Employee'),ROOT('Company'), ELEMENTS XSINIL
<Employee>
<EmpID>1</EmpID>
</Employee>
<Employee>
<EmpID>2</EmpID>
</Employee>
<Employee>
<EmpID>3</EmpID>
</Employee>
<Employee>
<EmpID>4</EmpID>
</Employee>
<Employee>
<EmpID>5</EmpID>
</Employee>
</Company>
SELECT EmpID AS 'EmployeeDetails/EmpID',

EmpName AS 'EmployeeDetails/EmpName',
Address AS 'EmployeeDetails/Address',
Salary AS 'SalaryDetails/Salary'
FROM Employee
FOR XML PATH ('Employee'),ROOT('Company'), ELEMENTS XSINIL
<Employee>
<EmployeeDetails>
<EmpID>1</EmpID>
</EmployeeDetails>
<SalaryDetails>
</SalaryDetails>

T-SQL Concepts
</Employee>
<Employee>
<EmployeeDetails>
<EmpID>2</EmpID>
</EmployeeDetails>
<SalaryDetails>
</SalaryDetails>
</Employee>
<Employee>
<EmployeeDetails>
<EmpID>3</EmpID>
</EmployeeDetails>
<SalaryDetails>
</SalaryDetails>
</Employee>
<Employee>
<EmployeeDetails>
<EmpID>4</EmpID>
</EmployeeDetails>
<SalaryDetails>
</SalaryDetails>
</Employee>
<Employee>
<EmployeeDetails>
<EmpID>5</EmpID>
</EmployeeDetails>
<SalaryDetails>
</SalaryDetails>
</Employee>
</Company>

T-SQL Concepts
How we can use FOR XML in our queries
Example 1: Get comma separated list of employee for each address.

DROP TABLE Employee
GO

(
Address varchar (200) NULL,
)
GO

INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(6,'Azar',12000,'Noida')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(7,'Akhul',25000,'Delhi')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(8,'Rohit',21000,'Noida')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(9,'Ranjan',22400,'Noida')
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(10,'Raghav',21570,'Delhi')
SELECT e2.Address, (SELECT ','+EmpName

FROM Employee e1
WHERE ISNULL (e1.Address,'') =ISNULL (e2.Address,'')
FOR XML PATH (''))
FROM Employee e2
http://blog.sqlauthority.com/2013/04/05/sql-server-group-by-rows-and-columns-using-xml-path-
efficient-concating-trick/

T-SQL Concepts
TRY CATCH FOR ERROR HANDLING

Error handling is a mechanism where we handle the error in such a way that, if any error occurs in the
query we address it with some logic or some message.
Syntax for TRY CATCH
BEGIN TRY
…… SQL code
…… SQL code
…….
END TRY
BEGIN CATCH
…… Logic or message to handle error
…… Logic or message to handle error
END CATCH
So when ever any error occurs in try block, the control will move to catch block.
Let’s see with some examples
BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
PRINT 'ErrorNumber :'+CAST(ERROR_NUMBER() AS VARCHAR(10))+CHAR(13)+
'ErrorSeverity :'+CAST(ERROR_SEVERITY() AS VARCHAR(10))+CHAR(13)+
'ErrorState :'+CAST(ERROR_STATE() AS VARCHAR(100))+CHAR(13)+
'ErrorLine :'+CAST(ERROR_LINE() AS VARCHAR(100))+CHAR(13)+
'ErrorMessage :'+CAST(ERROR_MESSAGE() AS VARCHAR(MAX))+CHAR(13)
END CATCH
Sreehari Katageri, http://Skatageri.blogspot.com | TRY CATCH FOR ERROR HANDLING 136

T-SQL Concepts
Here you see how TRY CATCH and TRANSACTION used in the query.
Below code is without Transactions.

DROP TABLE Employee
GO

(
Address varchar (200) NULL,
)
GO

INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(9,'Ranjan',null,'Noida')
Below code is using transactions. If no error the transaction commits. If error occurs then transaction
rolled back.
TRUNCATE TABLE Employee
BEGIN TRY
BEGIN TRAN
INSERT INTO Employee (EmpID,EmpName,Salary,Address) VALUES(9,'Ranjan',null,'Noida')
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Sreehari Katageri, http://Skatageri.blogspot.com | TRY CATCH FOR ERROR HANDLING 137

T-SQL Concepts PDF

Uploaded by

Copyright:

Available Formats

T-SQL Concepts PDF

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

T-SQL Concepts PDF

Uploaded by

Copyright:

Available Formats

What are the different types of SQL commands?

What are the different types of SQL commands?

What are the different types of joins in SQL?

What are the different types of joins in SQL?

T-SQL Concepts

Sreehari Katageri, http://Skatageri.blogspot.com | 2

System Functions .................................................................................................................................................................. 39

Sreehari Katageri, http://Skatageri.blogspot.com | 3

Temp table, Table variable, CTE ............................................................................................................................................ 99

Sreehari Katageri, http://Skatageri.blogspot.com | 4

In the below table Emp_Id is the PK as each row is uniquely identified.

CREATE TABLE Employee

CREATE TABLE Employee

CREATE TABLE Employee

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 5

ALTER TABLE Employee

CREATE TABLE Employee

ALTER TABLE Employee

/* Drop the Primary Key*/

ALTER TABLE Employee

INSERT INTO Employee VALUES(1,'AAA',1)

CREATE TABLE Employee

CREATE TABLE Employee

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 6

CREATE TABLE Employee

ALTER TABLE Employee

ALTER TABLE Employee

ALTER TABLE Employee

/* Drop the Unique Key*/

ALTER TABLE Employee

INSERT INTO Employee VALUES(1,1,1,'AAA',1)

Emp_Id Emp_Name Dept_Id

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 7

IF EXISTS(SELECT * FROM sys.tables WHERE name ='Dept')

CREATE TABLE Dept

IF EXISTS(SELECT * FROM sys.tables WHERE name ='Employee')

CREATE TABLE Employee

/*Throws error since there is no entry in the dept table*/

DELETE FROM Dept

/*Drop Foreign Key constraint*/

Cust_Id Prod_Id Amt

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 8

IF EXISTS(SELECT * FROM sys.tables WHERE name ='Employee')

CREATE TABLE Employee

INSERT INTO Employee VALUES(1,'AAA',NULL)

SELECT * FROM Employee

Difference between BLANK and NULL

Normalization and De-Normalization

 INF: No Repeating groups.

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 9

Apart from this we also have:

EmpID EmpName ProjectNum Time ProjectTitle ProjectMgr MgrPhn

EmpID EmpFName EmpLName ProjectNum Time ProjectTitle ProjectMgr MgrPhn

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 10

EmpID EmpFName EmpLName EmpID ProjectNum Time ProjectTitle ProjectMgr MgrPhn

EmpID EmpFName EmpLName EmpID ProjectNum Time

ProjectNum ProjectTitle ProjectMgr MgrPhn

Sreehari Katageri, http://Skatageri.blogspot.com | RDBMS Concepts 11

EmpID EmpFName EmpLName EmpID ProjectNum Time

/Throws error since there is no entry in the dept table/

/Drop Foreign Key constraint/

/ Create below table in Server1, which in my case is amsmsaapp02-prd.hq.netapp.com server/