SQL Server 2008 T-SQL Enhancements

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

1. MERGE             - Used to specify the Target tables to be inserted/updated/deleted.
2. USING              - Used to specify the Source table.
3. ON                   - Used to specify the join condition on source and target tables.
4. WHEN              - Used to specify the action to be taken place based on the result of the ON clause.
5. 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 :

      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.