SQL Server 2008 introduces some T-SQL enhancements which is used to improve the database performance and maintenance.
There are many T-SQL Enhancemnets have been introduced in SQL Server 2008 to improve database performance and maintenance. Lets look at them one by one.
1. Declaring and Initializing variables :
1.1 In SQL Server 2008(Katmai), We can declare and initialize the variables
Use master
DECLARE @intA INT = 100
SELECT @intA
Result:
100
1.2 In SQL Server 2005(Yukon), We can declare and initialize the variables in separate lines, Otherwise It will through an Error
Use master
DECLARE @intA INT = 100
SELECT @intA
Error:
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@intA".
DECLARE @intA INT
SELECT @intA = 100 --(or) SET @intA = 100
SELECT @intA
Result:
100
2. Compound Assignment Operators :
The compound assignment operators are: +=, -=, *=, /=, %=
DECLARE @intA INT = 100
SELECT @intA += 1000 --(or) SELECT @intA = @intA + 1000
SELECT @intA [@intA]
@intA
-----
1100
DECLARE @intA INT = 100
SELECT @intA -= 1000 --(or) SELECT @intA = @intA - 1000
SELECT @intA [@intA]
@intA
-----
-900
DECLARE @intA INT = 100
SELECT @intA *= 1000 --(or) SELECT @intA = @intA * 1000
SELECT @intA [@intA]
@intA
------
100000
DECLARE @intA INT = 100
SELECT @intA /= 1000 --(or) SELECT @intA = @intA / 1000
SELECT @intA [@intA]
@intA
-----
0
DECLARE @intA INT = 100
SELECT @intA %= 1000 --(or) SELECT @intA = @intA % 1000
SELECT @intA [@intA]
@intA
-----
100
3. TABLE Value constructor :
When Inserting value to the Table we can use constructor like structure.
IF OBJECT_ID('TBL_TABLE1','U') IS NOT NULL
DROP TABLE TBL_TABLE1
GO
CREATE TABLE TBL_TABLE1
(
ID INT IDENTITY(1,1) ,
COL1 VARCHAR(10) ,
COL2 VARCHAR(15)
)
GO
INSERT TBL_TABLE1 (COL1, COL2)
VALUES
('A' ,'AA'),
('B' ,'BB'),
('C' ,'CC')
GO
Insteadof:
INSERT TBL_TABLE1 (COL1, COL2) VALUES('A' ,'AA')
INSERT TBL_TABLE1 (COL1, COL2) VALUES('B' ,'BB')
INSERT TBL_TABLE1 (COL1, COL2) VALUES('C' ,'CC')
GO
SELECT ID, COL1,COL2 FROM TBL_TABLE1
Result:
ID COL1 COL2
----------- ---------- ---------------
1 A AA
2 B BB
3 C CC
(3 row(s) affected)
4. DATE and TIME Data type :
SQL Server 2008 introduces Four DATE and TIME Data types : DATE, TIME, DATETIME2, DATATIMEOFFSET.
It provides the splited value of DATE and TIME. DATETIME2 is an improved version of DATETIME datatype. It provides the Large Data and accuracy. DATATIMEOFFSET is the DATETIME2 like a data type in addition with time zone component.
SQL Server 2008:
DATE à 3 Bytes à January-01-0001 to December-01-9999 (YYYY-MM-DD)
à 1947-08-15
TIME à 3 to 5 Bytes (hh:mm:ss.nnnnnnn)
à 12:02:13.1234567
DATETIME2 à 6 to 8 Bytes à January-01-0001 to December-01-9999 ( DATE + TIME ) (YYYY-MM-DD
hh:mm:ss.nnnnnnn)
à 1947-08-15 12:02:13.1234567
DATETIMEOFFSET à 8 to 10 Bytes à January-01-0001 to December-01-9999 (YYYY-MM-DD hh:mm:ss.nnnnnnn +/- hh:mm)
à 1947-08-15 12:02:13.1234567 + 02:05
SQL Server 2005:
DATETIME à 4 Bytes à January-01-1753 to December-01-9999
SMALLDATETIME à 2 Bytes à January-01-1900 to June-06-2079
The default/maximum nanoseconds format is 7.
DECLARE @A DATETIME2(7)
SELECT @A = '1947-08-15 12:02:13.9370000'
SELECT @A
Result:
1947-08-15 12:02:13.9370000
DECLARE @A DATETIME2(4)
SELECT @A = '1947-08-15 12:02:13.9370000'
SELECT @A
Result:
1947-08-15 12:02:13.9370
If the nanosecond is not needed
DECLARE @A DATETIME2(0)
SELECT @A = '1947-08-15 12:02:13.9370000'
SELECT @A
Result:
1947-08-15 12:02:14
The SMALLDATETIMEOFFSET Data type based on 24 hour clock. It can be local (or) UTC date time.
DECLARE @A DATETIMEoffset
SELECT @A = '1947-08-15 12:02:13.9370000-13:39'
e.g:
SELECT
CAST(@A AS time(7)) AS 'Time'
,CAST(@A AS date) AS 'Date'
,CAST(@A AS smalldatetime) AS 'Smalldatetime'
,CAST(@A AS datetime) AS 'Datetime'
,CAST(@A AS datetime2(7)) AS 'Datetime2'
,CAST(@A AS datetimeoffset(7)) AS 'Datetimeoffset'
Result:
Time Date Smalldatetime Datetime
12:02:13.9370000 1947-08-15 1947-08-15 12:02:00 1947-08-15 12:02:13.937
Datetime2 Datetimeoffset
1947-08-15 12:02:13.9370000 1947-08-15 12:02:13.9370000 -13:39
5. DATE and TIME Functions :
To support the new data type DATE and TIME in SQL Server 2008, Some new functions are there
SYSDATETIME, SYSUTCDATETIME, SYSDATETIMEOFFSET(System time zone) , SWITCHOFFSET and TODATETIMEOFFSET
SYSDATETIME ()
SELECT SYSDATETIME() [SYSDATETIME()]
SYSDATETIME()
2010-04-13 23:49:29.2343750
Similar to SQL Server 2005 : GETDATE(),CURRENT_TIMESTAMP except nanoseconds 7 format.
GETDATE() à 2010-04-13 23:49:29.230
CURRENT_TIMESTAMP à 2010-04-13 23:49:29.230
SYSUTCDATETIME ()
SELECT SYSUTCDATETIME() [SYSUTCDATETIME()]
SYSUTCDATETIME()
2010-04-13 18:23:02.6718750
Similar to SQL Server 2005 : GETUTCDATE()except nanoseconds 7 format.
GETUTCDATE() à 2010-04-14 05:19:29.230 (Actual DateTime + Timezone(05:30))
SYSDATETIMEOFFSET ()
SELECT SYSDATETIMEOFFSET() [SYSDATETIMEOFFSET()]
SYSDATETIMEOFFSET()
2010-04-13 23:54:46.9843750 +05:30
SWITCHOFFSET (DATETIMEOFFSET, time_zone)
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-02:12') [SWITCHOFFSET()]
It will adjust and return the DatetimeOffset with Timezone(-02:12) given.
SWITCHOFFSET()
2010-04-14 13:48:41.8906250 -02:12
TODATETIMEOFFSET (expression , time_zone)
SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(), '-02:12') [TODATETIMEOFFSET()]
It will return the DatatimeOffset with Timezone (-02:12) given.
TODATETIMEOFFSET()
2010-04-14 21:43:08.5937500 -02:12
6. HIERARCHYID Data Type
- This type is internally stored as a VARBINARY value that represents the position of the current node in the hierarchy.
- The encoding limit on hierarchyid type is up to 892 bytes. SQLHierarchyId is an CLR type of hierarchyid.
- Hierarchyid value can be converted to Nvarchar(4000) type using ToString().
- Hierarchyid value can be converted to Varbinary type using Read(), Write().
- Conversion from HeirarchyId to XML is not supported.
The following code demonstrates how to use the HIERARCHYID type, creating a table named Tbl_Structure
IF OBJECT_ID('dbo.Tbl_Structure', 'U') IS NOT NULL
DROP TABLE dbo.Tbl_Structure;
GO
CREATE TABLE dbo.Tbl_Structure
(
FunctionID INT NOT NULL,
Hierarchy_ID HIERARCHYID NOT NULL,
Hierarchy_Level AS Hierarchy_ID.GetLevel() PERSISTED,
FunctionName VARCHAR(50) NOT NULL,
CONSTRAINT PK_Tbl_Structure PRIMARY KEY NONCLUSTERED(FunctionID)
);
GO
CREATE UNIQUE CLUSTERED INDEX idx_One ON dbo.Tbl_Structure(Hierarchy_ID);
CREATE UNIQUE INDEX idx_Two ON dbo.Tbl_Structure(Hierarchy_Level, Hierarchy_ID);
GO
To insert a node into the hierarchy, you must first produce a new HIERARCHYID value that represents the correct position in the hierarchy. Use the HIERARCHYID::GetRoot() method to produce the value for the root node. You use the GetDescendant method to produce a value below a given parent. The GetDescendant method accepts two optional HIERARCHYID input values representing the two nodes between which you want to position the new node.
Note that the GetDescendant method does not guarantee that HIERARCHYID values are unique. To enforce uniqueness, you must define a primary key, a unique constraint, or a unique index on the column.
For example, the following code creates the usp_AddStructure stored procedure, which adds a new node to the hierarchy:
IF OBJECT_ID('dbo.usp_AddStructure', 'P') IS NOT NULL
DROP PROC dbo.usp_AddStructure;
GO
CREATE PROC dbo.usp_AddStructure
@FunctionID AS INT,
@CategoryID AS INT = NULL,
@FunctionName AS VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON
DECLARE
@Hierarchy_ID AS HIERARCHYID,
@Category_Hierarchy_ID AS HIERARCHYID,
@last_child_Hierarchy_ID AS HIERARCHYID;
IF @CategoryID IS NULL
SET @Hierarchy_ID = HIERARCHYID::GetRoot();
ELSE
BEGIN
SET @Category_Hierarchy_ID = (SELECT Hierarchy_ID FROM dbo.Tbl_Structure WHERE FunctionID = @CategoryID);
SET @last_child_Hierarchy_ID =
(SELECT MAX(Hierarchy_ID) FROM dbo.Tbl_Structure
WHERE Hierarchy_ID.GetAncestor(1) = @Category_Hierarchy_ID);
SET @Hierarchy_ID = @Category_Hierarchy_ID.GetDescendant(@last_child_Hierarchy_ID, NULL);
END
INSERT INTO dbo.Tbl_Structure(FunctionID, Hierarchy_ID, FunctionName)
VALUES(@FunctionID, @Hierarchy_ID, @FunctionName);
END
GO
The following procedure inserts the Root and hierarchy node.
EXEC dbo.usp_AddStructure 1, NULL,'Director'
EXEC dbo.usp_AddStructure 2, 1, 'Project Manager - I'
EXEC dbo.usp_AddStructure 4, 2, 'Project Lead - I'
EXEC dbo.usp_AddStructure 5, 4, 'Team Lead - I'
EXEC dbo.usp_AddStructure 6, 5, 'Application Developer - IA'
EXEC dbo.usp_AddStructure 7, 5, 'Application Developer - IIA'
EXEC dbo.usp_AddStructure 8, 5, 'Application Developer - IIIA'
EXEC dbo.usp_AddStructure 9, 5, 'SQL Developer - IA'
EXEC dbo.usp_AddStructure 3, 1, 'Project Manager - II'
EXEC dbo.usp_AddStructure 10,3, 'Project Lead - II'
EXEC dbo.usp_AddStructure 11,10, 'Team Lead - II'
EXEC dbo.usp_AddStructure 12,11, 'Application Developer - IB'
EXEC dbo.usp_AddStructure 13,11, 'Application Developer - IIB'
EXEC dbo.usp_AddStructure 14,11, 'Application Developer - IIIB'
EXEC dbo.usp_AddStructure 15,11, 'SQL Developer - IB'
The following query produce the hierarchy node.
SELECT
REPLICATE(' |..', Hierarchy_Level) + FunctionName AS FunctionName,
Hierarchy_ID.ToString() AS path
FROM dbo.Tbl_Structure
ORDER BY Hierarchy_ID;
Result
Director
|..Project Manager - I
|.. |..Project Lead - I
|.. |.. |..Team Lead - I
|.. |.. |.. |..Application Developer - IA
|.. |.. |.. |..Application Developer - IIA
|.. |.. |.. |..Application Developer - IIIA
|.. |.. |.. |..SQL Developer - IA
|..Project Manager - II
|.. |..Project Lead - II
|.. |.. |..Team Lead - II
|.. |.. |.. |..Application Developer - IB
|.. |.. |.. |..Application Developer - IIB
|.. |.. |.. |..Application Developer - IIIB
|.. |.. |.. |..SQL Developer - IB
7. Table Type and Table-Valued Parameter :
In SQL Server 2008, We have Table type parameter that helps to pass table definition as a parameter to Stored procedure and Functions.
Table Type : (User define table type)
Table type is used to define the table structure and re-use it later. So we can avoid the inconsistency of Err.
Creating Table type :
CREATE TYPE Typ_Sample AS TABLE
(
ID INT ,
COL1 VARCHAR(10) NULL
)
We can identity the defined table type
SELECT * FROM sys.Types WHERE [name] = 'Typ_Sample'
(OR)
SELECT * FROM sys.Table_types WHERE [name] = 'Typ_Sample'
Whenever we declare the Table variable we can use the defined Table type name as a data type.
DECLARE @tblValue Typ_Sample
INSERT @tblValue VALUES
(1,'Aequea'),
(2,'Salino'),
(3,'Calcalino'),
(4,'Setaceo'),
(5,'Socaprio'),
(6,'Alumino'),
(7,'Vitriolic')
SELECT * FROM @tblValue

8. Table-Values parameters :
We can use table type parameters as an Input for Stored procedure and Functions. The Table value parameter is Read-Only, So we have to define as READONLY.
But, In previous version of SQL Servers, We have to use some special character value to separate the bulk values, XML type data and we have to use Split function inside the Stored procedure and functions. Using the XML, separated value is non-relational and It will produce the SQL Injection and It will not provide the effective plan for re-use the optimizer.
In SQL Server 2008, By using the table value parameter we can avoid the SQL Injection
We created a table with data
IF OBJECT_ID('Tbl_One','U') IS NOT NULL
DROP TABLE Tbl_One
GO
CREATE TABLE Tbl_One
(
ID INT,
COL1 VARCHAR(50)
)
GO
INSERT Tbl_One VALUES
(1,'Table'),
(2,'View'),
(3,'Stored Procedure'),
(4,'Trigger'),
(5,'Cursor')
GO
Creating Table type for the actual table
IF NOT EXISTS(SELECT 1 FROM SYS.TYPES WHERE [NAME] = 'Typ_One')
CREATE TYPE Typ_One AS TABLE
(
ID INT
)
GO
We have created a Stored procedure with table value type parameter
IF OBJECT_ID('USP_GetData','P') IS NOT NULL
DROP PROC USP_GetData
GO
CREATE PROC USP_GetData
(
@IDs AS Typ_One READONLY
)AS
BEGIN
SET NOCOUNT ON
SELECT ID, Col1 FROM Tbl_One WHERE ID IN(SELECT ID FROM @IDs)
END
Accessing the stored procedure
DECLARE @Para Typ_One
INSERT @Para VALUES(2),(3),(4)
EXEC USP_GetData @Para
We have created one variable : @Para for table type : Typ_One and Inserting 3 values. The table value parameter passed as an Input to the Stored procedure and inside the procedure will perform based on the table value parameters’ value.

If we not inserting and passing any data to the table value parameter, The parameter will have empty table structure without any data. But We cannot set the table value parameter as NULL.
9. MERGE Statement
The Merge statement is used to perform INSERT, UPDATE & DELETE operations as a single based on condition. The Merge operation is better when comparing with individual operations.
The Merge statement uses two tables (Source and Target tables). The Source table is specified with USING clause. The Target table is specified with MERGE INTO clause.
Merge statement is similar to OUTER joins. We can use condition with Merge statement like WHEN MATCHED THEN, WHEN NOT MATCHED [BY TARGET] THEN , WHEN NOT MATCHED BY SOURCE THEN.
If the record is already there in target table then we can update the data, If its not there in target table then we an insert.
The OUTPUT clause can be used with Merge statement to return an output as INSERTED / DELETED like magic tables. We can use $action function to identity that what kind of action taken place like INSERT / UPDATE / DELETE.
We can synchronize the difference on tables (source / target) by INSERT/UPDATE/DELETE operations.
The Merge statement comprises Five clauses
- MERGE - Used to specify the Target tables to be inserted/updated/deleted.
- USING - Used to specify the Source table.
- ON - Used to specify the join condition on source and target tables.
- WHEN - Used to specify the action to be taken place based on the result of the ON clause.
- OUTPUT - Used to return the value of Insert/Update/Delete operations using the INSERTED / DELETED magic tables.
USE master
GO
Creating a table named : TB_SAMPLE1 :
IF OBJECT_ID('TB_SAMPLE1','U') IS NOT NULL
DROP TABLE TB_SAMPLE1
GO
CREATE TABLE TB_SAMPLE1
(
ID INT,
COL1 VARCHAR(100),
AMOUNT INT
)
GO
Command(s) completed successfully.
Inserting record to table : TB_SAMPLE1 :
INSERT TB_SAMPLE1
VALUES
(1,'Aequea',1000),
(2,'Salino',2000),
(1,'Calcalino',3000),
(3,'Setaceo',4000),
(4,'Socaprio',5000),
(4,'Alumino',6000),
(5,'Vitriolic',7000)
GO
(7 row(s) affected)
Creating a table named : TB_SAMPLE2 :
IF OBJECT_ID('TB_SAMPLE2','U') IS NOT NULL
DROP TABLE TB_SAMPLE2
GO
CREATE TABLE TB_SAMPLE2
(
ID INT ,
TOTAL INT
)
GO
Command(s) completed successfully.
Inserting record to table : TB_SAMPLE2 :
INSERT TB_SAMPLE2(ID)
VALUES
(2),
(4),
(7)
(3 row(s) affected)
SELECT * FROM TB_SAMPLE1
GO

SELECT * FROM TB_SAMPLE2
GO

Inserting the Unmatched record from the TB_SAMPLE1 table to TB_SAMPLE2 : (Without MERGE)
INSERT TB_SAMPLE2
SELECT SOURCE.* FROM
(SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE LEFT JOIN TB_SAMPLE2 [TARGET]
ON (SOURCE.ID = [TARGET].ID)
WHERE [TARGET].ID IS NULL
GO
SELECT * FROM TB_SAMPLE2
GO

Inserting the Unmatched record from the TB_SAMPLE1 table to TB_SAMPLE2 : (Without MERGE + OUTPUT Clause)
INSERT TB_SAMPLE2 OUTPUT Inserted.*
SELECT SOURCE.* FROM
(SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE LEFT JOIN TB_SAMPLE2 [TARGET]
ON (SOURCE.ID = [TARGET].ID)
WHERE [TARGET].ID IS NULL
GO
SELECT * FROM TB_SAMPLE2
GO

Inserting the Unmatched record from the TB_SAMPLE1 table to TB_SAMPLE2 : (With MERGE)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES(SOURCE.ID, SOURCE.AMOUNT);
GO
SELECT * FROM TB_SAMPLE2
GO

Inserting the Unmatched record from the TB_SAMPLE1 table to TB_SAMPLE2 : (With MERGE + OUTPUT Clause)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES(SOURCE.ID, SOURCE.AMOUNT)
OUTPUT Inserted.*;
GO
SELECT * FROM TB_SAMPLE2
GO

Update SUM of amount from TB_SAMPLE1 to TB_SAMPLE2 comparing with ID from the both table : (Without MERGE)
UPDATE [TARGET] SET [TARGET].TOTAL = SOURCE.AMOUNT FROM
(
SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID
)AS SOURCE, TB_SAMPLE2 [TARGET]
WHERE SOURCE.ID = [TARGET].ID
GO
SELECT * FROM TB_SAMPLE2
GO

Update SUM of amount from TB_SAMPLE1 to TB_SAMPLE2 comparing with ID from the both table : (Without MERGE + OUTPUT Clause)
UPDATE [TARGET] SET [TARGET].TOTAL = SOURCE.AMOUNT OUTPUT Inserted.*, Deleted.*
FROM
(
SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID
)AS SOURCE, TB_SAMPLE2 [TARGET]
WHERE SOURCE.ID = [TARGET].ID
GO
SELECT * FROM TB_SAMPLE2
GO

Note: When we update the data, The already existing record will be there in DELETED magic table, The Updated record will be there in INSERTED magic table.
Update SUM of amount from TB_SAMPLE1 to TB_SAMPLE2 comparing with ID from the both table : (With MERGE)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED THEN
UPDATE SET TARGET.TOTAL = SOURCE.AMOUNT;
GO
SELECT * FROM TB_SAMPLE2
GO

Update SUM of amount from TB_SAMPLE1 to TB_SAMPLE2 comparing with ID from the both table : (With MERGE + OUTPUT Clause)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN MATCHED THEN
UPDATE SET TARGET.TOTAL = SOURCE.AMOUNT
OUTPUT Inserted.*, Deleted.*;
GO
SELECT * FROM TB_SAMPLE2
GO

Deleting the Unmatched record from the TB_SAMPLE2 table : (Without MERGE)
DELETE [TARGET] FROM TB_SAMPLE2 [TARGET] LEFT JOIN TB_SAMPLE1 [SOURCE]
ON ([SOURCE].ID = [TARGET].ID)
WHERE [SOURCE].ID IS NULL
GO
SELECT * FROM TB_SAMPLE2
GO

Deleting the Unmatched record from the TB_SAMPLE2 table : (Without MERGE + OUTPUT Clause)
DELETE [TARGET] OUTPUT Deleted.* FROM TB_SAMPLE2 [TARGET] LEFT JOIN TB_SAMPLE1 [SOURCE]
ON ([SOURCE].ID = [TARGET].ID)
WHERE [SOURCE].ID IS NULL
GO
SELECT * FROM TB_SAMPLE2
GO

Deleting the Unmatched record from the TB_SAMPLE2 table : (With MERGE)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
GO
SELECT * FROM TB_SAMPLE2
GO

Deleting the Unmatched record from the TB_SAMPLE2 table : (With MERGE + OUTPUT Clause)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT Deleted.*;
GO
SELECT * FROM TB_SAMPLE2
GO

Inserting, Updating & Deleting the record from the TB_SAMPLE2 table : (With MERGE + OUTPUT Clause
We can use $Action to identity that what kind of action taken place (Insert, Update, Delete)
MERGE TB_SAMPLE2 AS TARGET
USING (SELECT ID,SUM(AMOUNT) AMOUNT FROM TB_SAMPLE1 GROUP BY ID) AS SOURCE
ON (TARGET.ID = SOURCE.ID)
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES(SOURCE.ID, SOURCE.AMOUNT)
WHEN MATCHED THEN
UPDATE SET TARGET.TOTAL = SOURCE.AMOUNT
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $Action, Inserted.*,Deleted.*;
GO
SELECT * FROM TB_SAMPLE2
GO

10. GROUPING SET :
SQL Server 2008 introduces to have multiple grouping in a single GROUP BY Clause. We can combine the GROUP BY clause with GROUPING SET, CUBE, ROLLUP.
We can use grouping set for single result set instead of using UNION ALL with multiple queries for various grouping sets for various calculations. SQL Server optimizes the data for access and grouping.
Creating sample table with data
USE master
GO
IF OBJECT_ID('TB_SAMPLE1','U') IS NOT NULL
DROP TABLE TB_SAMPLE1
GO
CREATE TABLE TB_SAMPLE1
(
ID INT IDENTITY,
FunctionCategory VARCHAR(100),
FunctionName VARCHAR(100)
)
GO
INSERT TB_SAMPLE1(FunctionCategory,FunctionName)
VALUES
('Aggregate Functions','AVG'),
('Aggregate Functions','MIN'),
('Aggregate Functions','CHECKSUM_AGG'),
('Aggregate Functions','SUM'),
('Aggregate Functions','COUNT'),
('Aggregate Functions','STDEV'),
('Aggregate Functions','COUNT_BIG'),
('Aggregate Functions','STDEVP'),
('Aggregate Functions','GROUPING'),
('Aggregate Functions','VAR'),
('Aggregate Functions','MAX'),
('Aggregate Functions','VARP'),
('Configuration Functions','@@DATEFIRST'),
('Configuration Functions','@@OPTIONS'),
('Configuration Functions','@@DBTS'),
('Configuration Functions','@@REMSERVER'),
('Configuration Functions','@@LANGID'),
('Configuration Functions','@@SERVERNAME'),
('Configuration Functions','@@LANGUAGE'),
('Configuration Functions','@@SERVICENAME'),
('Configuration Functions','@@LOCK_TIMEOUT'),
('Configuration Functions','@@SPID'),
('Configuration Functions','@@MAX_CONNECTIONS'),
('Configuration Functions','@@TEXTSIZE'),
('Configuration Functions','@@MAX_PRECISION'),
('Configuration Functions','@@VERSION'),
('Configuration Functions','@@NESTLEVEL'),
('Cryptographic Functions','EncryptByKey'),
('Cryptographic Functions','DecryptByKey'),
('Cryptographic Functions','EncryptByPassPhrase'),
('Cryptographic Functions','DecryptByPassPhrase'),
('Cryptographic Functions','Key_ID'),
('Cryptographic Functions','Key_GUID'),
('Cryptographic Functions','EncryptByAsmKey'),
('Cryptographic Functions','DecryptByAsmKey'),
('Cryptographic Functions','EncryptByCert'),
('Cryptographic Functions','DecryptByCert'),
('Cryptographic Functions','Cert_ID'),
('Cryptographic Functions','AsymKey_ID'),
('Cryptographic Functions','CertProperty'),
('Cryptographic Functions','SignByAsymKey'),
('Cryptographic Functions','VerifySignedByAsmKey'),
('Cryptographic Functions','SignByCert'),
('Cryptographic Functions','VerifySignedByCert'),
('Cryptographic Functions','DecryptByKeyAutoCert'),
('Cursor Functions','@@CURSOR_ROWS'),
('Cursor Functions','CURSOR_STATUS'),
('Cursor Functions','@@FETCH_STATUS'),
('Ranking Functions','RANK'),
('Ranking Functions','NTILE'),
('Ranking Functions','DENSE_RANK'),
('Ranking Functions','ROW_NUMBER')
GO
Fetching No. of functions inside the each category :
SELECT FunctionCategory,COUNT(1) 'Counts' FROM TB_SAMPLE1
GROUP BY FunctionCategory

Fetching No. of functions with starting characters inside the each category :
SELECT SUBSTRING(FunctionCategory,1,1) FunctionCategory,
COUNT(1) 'Counts' FROM TB_SAMPLE1
GROUP BY SUBSTRING(FunctionCategory,1,1)

If we need the both result set as a whole, We have to use UNION ALL :
SELECT FunctionCategory,COUNT(1) 'Counts' FROM TB_SAMPLE1
GROUP BY FunctionCategory
UNION ALL
SELECT SUBSTRING(FunctionCategory,1,1) FunctionCategory,
COUNT(1) 'Counts' FROM TB_SAMPLE1
GROUP BY SUBSTRING(FunctionCategory,1,1)
GO

Is there any way to combine the two result set to single without using UNION ALL :
SELECT GROUPING_ID(FunctionCategory,[First Character Wise]) 'Grouping',
FunctionCategory,[First Character Wise],
COUNT(1) 'Counts'
FROM
(
SELECT FunctionCategory,
SUBSTRING(FunctionCategory,1,1) 'First Character Wise'
FROM TB_SAMPLE1
)AS SubQuery
GROUP BY GROUPING SETS
(
(SubQuery.FunctionCategory),
(SubQuery.[First Character Wise])
)
GO

The function GROUPING_ID is used to identify the Level of grouping.
11. DDL Trigger Enhancements :
In SQL Server 2008, We can create DDL triggers enhanced to some stored procedures. Many stored procedure performs DDL like operations. In previous version of SQL Server we can not capture these kind of invocations.
The stored procedure sp_rename triggers with RENAME event.
Creating a Trigger with RENAME event :
IF EXISTS(SELECT * FROM sys.triggers WHERE [name] ='Trg_Rename')
DROP TRIGGER Trg_Rename ON DATABASE
GO
CREATE TRIGGER Trg_Rename ON DATABASE FOR RENAME
AS
BEGIN
DECLARE
@SchemaName AS SYSNAME =
EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
@TargetObjectName AS SYSNAME =
EVENTDATA().value('(/EVENT_INSTANCE/TargetObjectName)[1]', 'sysname'),
@ObjectName AS SYSNAME =
EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
@NewObjectName AS SYSNAME =
EVENTDATA().value('(/EVENT_INSTANCE/NewObjectName)[1]', 'sysname');
DECLARE @msg AS NVARCHAR(1000) =
N'RENAME event occurred.
SchemaName: ' + @SchemaName + N'
TargetObjectName: ' + @TargetObjectName + N'
ObjectName: ' + @ObjectName + N'
NewObjectName: ' + @NewObjectName;
PRINT @msg;
END
GO
Creating a Table : Tb_Table1 :
CREATE TABLE Tb_Table1
(id INT, col1 VARCHAR(10))
GO
We just want to rename a column Col1 to Col2 :
sp_rename 't1.col1','col2','column'

When we try to rename a column of table (or) name of a table the trigger fires.
The following are the system stored procedures which triggers in various EVENTS (ie: Rename)

12. Sparse Columns :
Sparse columns are normal columns that have an optimized storage for NULL values. It reduces the space requirements for NULL values. The Sparse columns are defined by CREATE TABLE (or) ALTER TABLE.
Sparse columns are used with columns and filtered indexes.
The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.
The Sparse should be used for Nullable columns( If it’s a Large percentage of NULLs ). However, when a column is marked as sparse, storage of non-NULL values becomes more expensive than normal storage.
Inserting some amount of data into a table : Tb_Table2 ( Without SPARSE column )
We have created one table with normal definition and inserted 100000 rows.
IF OBJECT_ID('Tb_Table2','U') IS NOT NULL
DROP TABLE Tb_Table2
GO
CREATE TABLE Tb_Table2
(
ID INT,
COL1 VARCHAR(50),
COL2 CHAR(4000) NULL
)
GO
DECLARE @I INT = 1
WHILE(@I <= 100000)
BEGIN
INSERT Tb_Table2(ID, COL1) VALUES
(@i,'DotnetFunda' + CAST(@I AS VARCHAR))
SELECT @I += 1
END
GO
Validating the space allocated and used :
SP_SPACEUSED Tb_Table2

Inserting some amount of data into a table : Tb_Table1 ( With SPARSE column )
We have created one table with SPARSE definition and inserted 100000 rows.
IF OBJECT_ID('Tb_Table1','U') IS NOT NULL
DROP TABLE Tb_Table1
GO
CREATE TABLE Tb_Table1
(
ID INT,
COL1 VARCHAR(50),
COL2 CHAR(4000) SPARSE NULL
)
GO
DECLARE @I INT = 1
WHILE(@I <= 100000)
BEGIN
INSERT Tb_Table1(ID, COL1) VALUES
(@i,'DotnetFunda' + CAST(@I AS VARCHAR))
SELECT @I += 1
END
GO
Validating the space allocated and used :
SP_SPACEUSED Tb_Table1


Microsoft Books Online : Restrictions for using Sparse columns :
1. A sparse column must be NULLable and cannot have the ROWGUIDCOL or IDENTITY properties.
2. A sparse column cannot be of the following data types: text, ntext, image, timestamp, user-defined data type, geometry, or geography; or have the FILESTREAM attribute.
3. A sparse column cannot be bound to a rule, default value
4. Computed column can contain a sparse column. But it cannot be marked as SPARSE.
5. A sparse column cannot be part of a clustered index or a unique primary key index.
6. A sparse column cannot be used as a partition key of a clustered index or heap. However, a sparse column can be used as the partition key of a nonclustered index.
7. A sparse column cannot be part of a user-defined table type, which are used in table variables and table-valued parameters.
8. Using sparse columns reduces the maximum size of a row from 8,060 bytes to 8,018 bytes.
9. Adding or removing a sparse column can fail when the row size is near 4,009 bytes. When an existing table is modified to add the first sparse column, the rows are modified by making a copy of them on the data page and then deleting the original row. Rows that are larger than 4,009 bytes cannot be written to the page while the existing row is present. This causes the addition of the column to fail.
10. The same problem exists when the last sparse column is removed from the table. If any rows have more than 4,009 bytes, the removal of the column will fail. This restriction does not apply to new tables that contain no data.
11. To change the rows in a table in which the data exceeds 4,009 bytes per row, create a new table, and transfer the data into the new table. Then, either delete the original table and rename the new table; or truncate the original table, modify the rows in the original table, and then move the data back into it.
12. When you change a nonsparse column to a sparse column, the sparse column will consume more space for nonnull values. When a row is close to the maximum row size limit, the operation can fail.
13. Filtered Index :
SQL Server 2008 introduces Filtered Index to optimize the non-clustered index. It is used to reduce the index maintenance cost, Storage costs compare with full table index.
Computed columns, Joins with Multiple Tables, Views, Complex logics can not be used with Filtered Index.
IF OBJECT_ID('Tb_Table1','U') IS NOT NULL
DROP TABLE Tb_Table1
GO
CREATE TABLE Tb_Table1
(
ID INT,
COL1 VARCHAR(50),
COL2 CHAR(4000) NULL
)
GO
DECLARE @I INT = 1
WHILE(@I <= 100000)
BEGIN
INSERT Tb_Table1(ID, COL1) VALUES
(@i,'DotnetFunda' + CAST(@I AS VARCHAR))
SELECT @I += 1
END
GO
Creating a Non-Clustered Filtered index :
CREATE NONCLUSTERED INDEX NCI_ID ON Tb_Table1(ID)
WHERE ID < 5000
GO
14. Object Dependencies :
SQL Server object dependencies are by-name reference. An entity that references another entity in its definition and that definition is stored in the system catalog is called a referencing entity.
Database engine tracks the dependencies in two ways
1.Schema-bound
2.Non-Schema-bound
A schema-bound dependency is created when a view or user-defined function is created by using the WITH SCHEMABINDING clause, Table referencing another entity.
A non-schema-bound dependency is a relationship between two entities that does not prevent the referenced entity from being dropped or modified.
In SQL Server 2008, There are three DMVs introduced for tracking the object dependencies.
1. sys.sql_expression_dependencies
2. sys.dm_sql_referenced_entities
3. sys.dm_sql_referencing_entities
Creating a sample table : Tb_Sample
IF OBJECT_ID('Tb_Sample','U') IS NULL
CREATE TABLE Tb_Sample
(
ID INT,
COL1 VARCHAR(50)
)
GO
Creating Stored procedure references the table :
IF OBJECT_ID('USP_PROC1','P') IS NOT NULL
DROP PROC USP_PROC1
GO
CREATE PROC USP_PROC1
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM Tb_Sample
END
GO
Creating View references the table :
IF OBJECT_ID('VW_VIEW1','V') IS NOT NULL
DROP VIEW VW_VIEW1
GO
CREATE VIEW VW_VIEW1 AS SELECT * FROM Tb_Sample
GO
Creating Stored procedure references the View :
IF OBJECT_ID('USP_PROC2','P') IS NOT NULL
DROP PROC USP_PROC2
GO
CREATE PROC USP_PROC2
AS
BEGIN
SET NOCOUNT ON
SELECT * FROM VW_VIEW1
END
GO
Identifying the referencing objects :
SELECT OBJECT_NAME(Referencing_id) 'Referencing Object'
FROM sys.sql_expression_dependencies
WHERE referenced_entity_name = 'Tb_Sample'
The table Tb_Sample being refered by the following objects

Identifying the referenced objects :
SELECT referenced_entity_name,
referenced_minor_name
FROM sys.dm_sql_referenced_entities('dbo.USP_PROC2','object')

Identifying the referencing objects :
SELECT referencing_schema_name,
referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.Tb_Sample','object')
The table Tb_Sample being refered by the following objects

15. Change Data Capture – CDC :
Change data capture is designed to capture INSERT, UPDATE, and DELETE activity applied to SQL Server tables.
The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Change data capture provides information about DML changes on a table and a database. By using change data capture, you eliminate expensive techniques such as user triggers, timestamp columns, and join queries.
Here, We going to see a simple process of capturing changes of an Tb_Sample1 table in a database called Master.
Change data capture is only available in the Enterprise, Developer, and Enterprise Evaluation editions.
Before we start to capture the table changes, we have to enable the Change Data Capture (CDC) by using the System stored procedure: sys.sp_cdc_enable_db.
CREATE DATABASE DotnetFunda
GO
USE DotnetFunda
GO
IF OBJECT_ID('Tb_cdcTable','U') IS NULL
CREATE TABLE Tb_cdcTable
(
ID INT,
COL1 VARCHAR(50)
)
GO
Recommendation :
1. The Database must be enabled for Change Data Capture : sys.sp_cdc_enable_db
2. SQL Server Agent must be running
3. Table must be enable which we want to capture the changes : sys.sp_cdc_enable_table
1. Enabling Change Data Capture for a Database :
EXEC sys.sp_cdc_enable_db
We can query and determine the CDC is already enabled by using the Is_CDC_Enabled column of Sys.Databases catalog view.
SELECT [Name],[compatibility_level],is_cdc_enabled FROM SYS.DATABASES

Once enable the Change Data Capture (CDC), Some system table will be created under current database.

2. Enabling Change Data Capture for a Table :
Enabling Change data capture for a table : Tb_cdcTable
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Tb_cdcTable',
@role_name = N'cdc',
@capture_instance='dbo_Tb_cdcTable'

We can query and determine the CDC is already enabled by the column is_tracked_by_cdc of using SYS.tables the table catalog view
SELECT is_tracked_by_cdc,[Name] FROM SYS.tables WHERE [Name] ='Tb_cdcTable'
Once enabled the change data capture for a table, One more table will be created.

Record inserted into the tables CDC.change_tables and CDC.captured_columns

SELECT * FROM cdc.dbo_Tb_cdcTable_CT

Job also created for Capture and Cleanup activity.

Inserting data to a table : Tb_cdcTable
INSERT Tb_cdcTable VALUES(1,'SQL Server')
Monitoring the data capture :
SELECT * FROM cdc.dbo_Tb_cdcTable_CT

INSERT Tb_cdcTable VALUES(2,'SQL Server 2008')
SELECT * FROM cdc.dbo_Tb_cdcTable_CT

Updating the existing record
UPDATE Tb_cdcTable SET COL1 = 'SQL Server 2008 Enterprise Edition' WHERE id = 2
SELECT * FROM cdc.dbo_Tb_cdcTable_CT

UPDATE Tb_cdcTable SET COL1 = 'SQL Server 2008 DBA' WHERE id = 2
SELECT * FROM cdc.dbo_Tb_cdcTable_CT

DELETE Tb_cdcTable WHERE ID = 2
SELECT * FROM cdc.dbo_Tb_cdcTable_CT


The column _$operation indicates that what kind of operations taken place on the table.

16. Collations :
SQL Server 2008 adds new collations by finding the number 100 in their names.
SELECT * FROM fn_helpcollations() WHERE [name] LIKE '%100%'

Conclusion
The new and enhanced features in SQL Server 2008 help you improve the performance of your database. Please let me know your feedback, if any on this article to improve it further. Thank you.