Output Clause:

Output is a new clause which can be paired off with Insert, Update & Delete statements to get the before and after image of the date being modified. It basically uses 2 magic tables
called as Inserted, Deleted for its operation.

Inserted table would contain a row for each row that is inserted and deleted table would contain a row each row that is deleted whereas both inserted and deleted would contain 1
row for row which is updated where the deleted holds the pre-image of data and inserted holds the new data that is inserted.

For Ex:
Col1 Col2
1 Peter

So if I update Peter to Peter Drucker then inserted table would have(Post Image)
Col1 Col2
1 Peter Drucker

And Deleted table would have(pre-image)
Col1 Col2
1 Peter

This feature was basically available only inside Triggers in SQL 2000, but however in SQL 2005 it is exposed and can be used even inside a stored procedure.

The ability to immediately access data changes without requiring additional reads from the database can enhance application throughput. When data modification statements
are executed in SQL Server 2005, you can capture the data that has been modified and store it for use in subsequent transactions.

One can use this technique
ü Trips to database from .Net or Java application to the database is reduced as its combined with INSERT/UPDATE/DELETE
ü To prevent the unnecessary reading of your online transaction processing (OLTP) tables which enhances application performance.
ü To implement custom data auditing without the use of triggers.

Let us see some examples on how to use various OUTPUT clauses.

Using Output clause to return the last value inserted into Identity, which is possible only by means of executing @@IDENTITY clause in SQL 2000

CREATE TABLE [dbo].[DOCS_HISTORY]
( [DOC_ID] [int] IDENTITY(1,1) NOT NULL,
[DOC_NAME] [varchar](100) )

INSERT INTO [DOCS_HISTORY]
OUTPUT INSERTED.[DOC_ID],INSERTED.[DOC_NAME]
SELECT 'test docs'

Using Output and Output INTO to show output and capture befor and after image to audit table

DECLARE @Tbl_Docs TABLE
( [DOC_ID_NEW] INT
, [DOC_NAME_NEW] VARCHAR(100),
[DOC_ID_OLD] INT ,
[DOC_NAME_OLD] VARCHAR(100),
CR_DATE DATETIME)

UPDATE [DOCS_HISTORY]
SET DOC_NAME = 'test documents'
OUTPUT INSERTED.[DOC_ID],
INSERTED.[DOC_NAME],
DELETED.[DOC_ID],
DELETED.[DOC_NAME],
GETDATE()
INTO @Tbl_Docs
OUTPUT INSERTED.[DOC_ID],
INSERTED.[DOC_NAME],
DELETED.[DOC_ID],
DELETED.[DOC_NAME]
WHERE DOC_ID = 100001

This is a very useful feature for auditing the pre and post image of data which was only possible by means of trigger in SQL 2000

Similarly OUTPUT can be used with DELETE statement as well to capture the data that was deleted from DELETED table.

Using Schema Binding Option in User Defined Functions

Using Schema Binding Option in User Defined Functions:

General notion is using User Defined functions(UDF) slows down your query performance and so was not a ideal method for choosing a function when the same does not
touch any tables inside the function.

If one looks at the execution plan of where UDFs are involved you could find a item named Spooler which by normal terms means as stores data from input into a temp table
to optimise the rewinds and this would be used only when Schemabinding is not set for a function and this causes a overhead in terms of performance.

Before going to much detail into our example let us understand what is schema binding?

Let us suppose I decide to drop or rename a table from a database then all of my existing procedures/functions/views would get affected while executing with error as “ Object doesnot exist” to avoid this SQL has a feature called as Schema Binding which helps not to drop tables when the same is referred in procedures/functions/views and
the same can be specified while creating proc as
Ex: Create Procedure A with SchemaBinding as begin -- Code Logic End
Let us see the above in terms of a example.
1) First step we create DOCS_HISTORY table with two columns(DocID and DocName)
2) Insert some 10000 rows into the table
3) Create 2 functions one with Schemabinding and other none and which just adds value of ‘WO’ or ‘W’ for the input
4) Call the 2 functions in UPDATE and use getdate function before and after to track which function performs better.

SET NOCOUNT ON

-- Create DOCS_HISTORY Table
IF EXISTS(SELECT 'X' FROM sysobjects WHERE type = 'U' and name = 'DOCS_HISTORY')
DROP TABLE DOCS_HISTORY
GO

CREATE TABLE DOCS_HISTORY
(
DOC_ID INT NOT NULL IDENTITY (1, 1),
DOC_NAME VARCHAR(100) NOT NULL
)
GO


ALTER TABLE DOCS_HISTORY ADD CONSTRAINT PK_DOCS_HISTORY PRIMARY KEY CLUSTERED(DOC_ID)
GO

--Insert 10000 rows
Declare @I int
Select @I = 1

WHILE @I < i =" @I+1" type =" 'FN'" name =" 'Return_WO_SchemaBinding')" type =" 'FN'" name =" 'Return_W_SchemaBinding')" doc_name =" dbo.Return_WO_SchemaBinding(DOC_NAME)" doc_name =" dbo.Return_W_SchemaBinding(DOC_NAME)">With Schemabinding took 2 seconds to complete when compared to 5 seconds which gives amazing 60% better performance

INTERSECT and EXCEPT Clause

INTERSECT and EXCEPT Clause:

These are keywords used in SET Theory in Mathematics and the same name is adopted in SQL Server to make coding style easier for developers.

INTERSECT and EXCEPT are one of the exciting feature which will save you tons of time on coding as it is a replacement to IN,EXISTS,NOT IN & NOT EXISTS.

It takes the input and combines them to make a single set.Same as UNION columns in the sets must be equal in type and name.

Syntax:




Where the SET_A and SET_B are select statements, and the SET_OPERATOR is one of the following below

UNION - include all values in SET_A or SET_B.
INTERSECT - include values that exist in SET_A and SET B.
EXCEPT - include values that exist in SET_A and not in SET_B or viceversa.

Let us see a example to better understand this.

Example:-
Consider Users who are defined in managers role whose entries are present in MGR_PERM table and users who are defined in Admin role
are present in ADM_PERM table , we can use the SET Operators to find users who are in either one,both or only one.

CREATE TABLE MGR_PERM
(
USERID VARCHAR(50),
USERNAME VARCHAR(100)
)

CREATE TABLE ADM_PERM
(
USERID VARCHAR(50),
USERNAME VARCHAR(100)
)

INSERT INTO MGR_PERM(USERID)
VALUES ('E0001')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0002')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0003')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0004')
INSERT INTO MGR_PERM(USERID)
VALUES ('E0005')

INSERT INTO ADM_PERM(USERID)
VALUES('E0003')
INSERT INTO ADM_PERM(USERID)
VALUES('E0004')
INSERT INTO ADM_PERM(USERID)
VALUES('E0006')
INSERT INTO ADM_PERM(USERID)
VALUES('E0007')

SELECT * FROM MGR_PERM
SELECT * FROM ADM_PERM

-- List users who have Manager permissions as well as Admin Permissions
SELECT * FROM MGR_PERM
UNION
SELECT * FROM ADM_PERM
-- Result E0001, E0002, E0003, E0004, E0005, E0006, E0007

-- List users who have both Manager and Admin Permissions
SELECT * FROM MGR_PERM
INTERSECT
SELECT * FROM ADM_PERM
-- Result E0003, E0004

-- List users who are managers but not administrators
SELECT * FROM MGR_PERM
EXCEPT
SELECT * FROM ADM_PERM
-- Result E0001, E0002,E0005

-- List users who are administrators but not managers
SELECT * FROM ADM_PERM
EXCEPT
SELECT * FROM MGR_PERM
-- Result E0006, E0007

Difference Between Inner Join and INTERSECT
SELECT * FROM ADM_PERM
INTERSECT
SELECT * FROM MGR_PERM
-- Returns E0003, E0004

SELECT * FROM ADM_PERM
INNER JOIN MGR_PERM
ON ADM_PERM .USERID = MGR_PERM.USERID
AND ADM_PERM.USERNAME = MGR_PERM.USERNAME
-- Returns 0 rows as One NULL is not equal to other NULL

Common Table Expressions (CTE)

What is Common Table Expressions(CTE)

A CTE is similar to a derived table that is not stored as an object and lasts only for the duration of the query. It is basically useful for creating a recursive query where in SQL 2000 we need to write multiple lines of code for finding the Manager/Employee Hierarchy and the same can be achieved optimally from SQL 2005 using CTE.

Below mentioned is an coding example for the same.


/* Table which had Employee and Their Manager Names */
CREATE TABLE EMP
( EMP_ID varchar(10),
EMP_NAME varchar(50),
MGR_ID varchar(10)
)

/* Sample Data to be inserted */

/* Insert CEO Data who doesnt report to anyone and is the boss */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0001','PAUL - CEO',NULL

/* Insert President Data who reports to CEO */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0002','SAM - PRES','E0001'

/* Insert VP Data who reports to President */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0003','MIKE - VP','E0002'

/* Insert another VP Data who reporting is not finalised yet and is so null */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0004','ROB - VP',NULL

/* Insert Director data who is reporting to new VP who doesnt have reporting */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0005','NATE - DIRECTOR','E0004'


/* Usage of Common Table Experessions(CTE)
This lists the Employee with their respective
managers and the levels and the Root Manager IDs */
WITH EMP_HIER(EMP_ID, EMP_NAME, MGR_ID, LEVELS, ROOT_MGR_ID)
AS
(
SELECT EMP_ID, EMP_NAME, MGR_ID, 0, EMP_ID
FROM EMP
WHERE MGR_ID IS NULL
UNION ALL
SELECT e.EMP_ID, e.EMP_NAME, e.MGR_ID, LEVELS + 1, eh.ROOT_MGR_ID
FROM EMP e JOIN EMP_HIER eh
ON eh.EMP_ID = e.MGR_ID
)
SELECT * FROM EMP_HIER


Output

EMP_ID EMP_NAME MGR_ID LEVELS ROOT_MGR_ID
E0001 PAUL - CEO NULL 0 E0001
E0004 ROB - VP NULL 0 E0004
E0005 NATE - DIRECTOR E0004 1 E0004
E0002 SAM - PRES E0001 1 E0001
E0003 MIKE - VP E0002 2 E0001