SQL Server : T-SQL features in SQL Server 2008 (Part 1)

Welcome to SQL Server 2008 T-SQL features buddy :)

Assign Default Values to Local Variables in SQL Server 2008

SQL Server 2008 allow us to assign a default value to a Local Variable when we DECLARE a variable itself instead of using separate SET statement :

DECLARE @iTest AS INT = 100, @vText AS VARCHAR(25) = 'SQL Server 2008';

SELECT @iTest AS Result1,@vText AS Result2;

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT, @vText AS VARCHAR(25);

SET @iTest = 1;

SET @vText = 'SQL Server 2005';

SELECT @iTest AS Result1,@vText AS Result2;

Compound assignment operators in SQL Server 2008

SQL Server 2008 introduces new 5 compound assignment operators :-

(a) += (plus equals)

(b) -= (minus equals)

(c) *= (multiplication equals)

(d) /= (division equals)

(e) %= (modulo equals)

These operators can be used in the SET clause of an UPDATE statement Or in a SET statement that assigns values to variables as shown below :

DECLARE @iTest AS INT = 10;

SET @iTest += 15;

SELECT @iTest AS Result1;

The result would be 25.

The same logic can be done in SQL Server 2005 as below:

DECLARE @iTest AS INT;

SET @iTest = 10;

SET @iTest = @iTest + 15;

SELECT @iTest AS Result1;

You could see in SQL Server 2008 directly we assigned 10 as default value to a Variable (@iTest)and added 15 to @iTest directly, whereas in SQL Server 2005 first declared a Variable and assigned 10 as initial value using SET statement and again added 15 to @iTest variable.

Using the above features, we can reduce some unnecessary code to improve the performance.

Keep follow my blog for other new SQL Server 2008 T-SQL features....... :)

SQL Server : Combining multiple DML (Data Manipulation Language) actions (INSERT, DELETE, UPDATE) into single SQL Statement using MERGE in SQL Server 2008

One of my SQL buddy got confused yesterday about MERGE functionality in SQL Server 2008, he rang me up and asked MERGE is used for merging multiple records into single?

I said NO, this confusion is not only with him which has with many SQL buddies. I have finally clarified his doubt with some examples which I would like to share with you all. :)

First of all, what is MERGE statement in SQL Server 2008?

MERGE is combining multiple DML (INSERT, UPDATE, DELETE) actions statements into single action, which improves performance and simplifying transactions. (This option is called UPSERT in other products like ORACLE, Sybase, DB2 etc.,).

Now, Let me explain, how to use MERGE statement instead of our normal DML statements. :)

We will assume that we have a table with the following records.

Sample table creation script :

CREATE TABLE [DBO].[Employee]
(
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL CONSTRAINT DF_Employee_Active DEFAULT 1, --1. ACTIVE 0. INACTIVE
CONSTRAINT [PK_Employee] PRIMARY KEY ([EmpCode]),
CONSTRAINT [CK_EEmployee_Active] CHECK ([Active] IN (0,1))
)
GO

Sample records creation script :

INSERT INTO
[DBO].[Employee] ([EmpCode],[EmpName])
VALUES
('E001','Sudhar'),
('E002','Siva'),
('E003','Areef'),
('E004','Jonathan'),
('E005','Matt'),
('E006','Mahesh')
GO

SELECT [EmpCode],[EmpName],[Active]  FROM  [DBO].[Employee] WITH (NOLOCK)
GO

After executing the above scripts, the output would be :

Performing INSERT and UPDATE operations on a table using single MERGE statement :

Let us see, how to use MERGE statement to update a record. For example I am going to rename the employee's name for employee  'E003'.

DECLARE @Input_EmpCode VARCHAR(10) = 'E003'
DECLARE @Input_EmpName VARCHAR(100) ='Areef Ali Sheik'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);

The output would be :

MERGE statement in Stored Procedure :

CREATE PROCEDURE [DBO].[STP_Employee]
(
@Input_EmpCode VARCHAR(10) , @Input_EmpName VARCHAR(100),
@Input_Active BIT = 1
)
AS

SET NOCOUNT ON;

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active]);

GO

Execute the below scripts to INSERT or UPDATE records :
EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E003',@Input_EmpName ='Areef Ali Sheik', @Input_Active =1
GO

EXEC [DBO].[STP_Employee] @Input_EmpCode = 'E007',@Input_EmpName ='Sathish', @Input_Active =1
GO

Output would be :

Displaying affected records using MERGE OUTPUT clause statement :

DECLARE @Input_EmpCode VARCHAR(10) = 'E008'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date;

Recording DML changes into history table using MERGE OUTPUT clause statement :

History table creation script :

CREATE TABLE [DBO].[EmployeeHistory]
(
[Action] VARCHAR(10) NOT NULL,
[EmpCode] VARCHAR(10) NOT NULL,
[EmpName] VARCHAR(100) NOT NULL,
[Active] BIT NOT NULL,
[Previous_EmpName] VARCHAR(100) NULL,
[Previous_Active] BIT NULL,
[Modified_Date] DATETIME NOT NULL,
[Modified_By] NCHAR(100) NOT NULL
)
GO

For this example, I am going to update one existing employee and inserting one new employee record.

Updating existing employee :

DECLARE @Input_EmpCode VARCHAR(10) = 'E002'
DECLARE @Input_EmpName VARCHAR(100) ='Sivakumar Vellingiri'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_User INTO [EmployeeHistory];

Creating new employee :

DECLARE @Input_EmpCode VARCHAR(10) = 'E009'
DECLARE @Input_EmpName VARCHAR(100) ='Krishna Priya'
DECLARE @Input_Active BIT = 1

MERGE INTO [DBO].[Employee] AS [Target]
USING (VALUES (@Input_EmpCode,@Input_EmpName,@Input_Active))
AS [Source] (New_EmpCode,New_EmpName,New_Active)
ON [Target].[EmpCode] = [Source].[New_EmpCode]
WHEN MATCHED THEN
UPDATE SET [EmpName] = [Source].[New_EmpName],[Active] = [Source].[New_Active]
WHEN NOT MATCHED BY TARGET THEN
INSERT ([EmpCode],[EmpName],[Active]) VALUES ([New_EmpCode],[New_EmpName],[New_Active])
OUTPUT $action as 'Action',Inserted.EmpCode,Inserted.EmpName,Inserted.Active,Deleted.EmpName as Previous_EmpName,
Deleted.Active as Previous_Active,Getdate() as Modified_Date,SYSTEM_USER AS Modified_By INTO [EmployeeHistory];

Displaying records from history table :

SELECT
[Action],[EmpCode],[EmpName],[Active],[Previous_EmpName],[Previous_Active],[Modified_Date],[Modified_By]
FROM
[EmployeeHistory] WITH (NOLOCK)
GO

As we all know that the above functionality can be achieved using TRIGGERS, but MERGE OUTPUT made us very simple and improves the performance too. :)

Note :
(a). This example will work only in SQL Server 2008 and above versions.
(b). Target Table : The table or view against which the data rows from SOURCE TABLE are matched based on search condition. The target is the target of any insert, update, or delete operations specified by the WHEN clauses of the MERGE statement. 
(c). USING : Specifies the data source that is matched with the data rows in target table based on merge search condition. The result of this match dictates the actions to take by the WHEN clauses of the MERGE statement. Table source can be a remote table or a derived table that accesses remote tables.
(d). OUTPUT : Returns a row for every row in TARGET TABLE that is updated, inserted or deleted.

For additional information, please refer http://technet.microsoft.com/en-us/library/bb510625.aspx