T-Sql Programmability Features in Sql server 2008 - Part 3 - SQL Programmers

T-sql Programmability features in Sql server 2008 - Part-3

Before Continung this article we suggest to read
New T-Sql  Features in Sql server 2008 - Part 1
New T-Sql Features in Sql server 2008 - Part 2

6. The MERGE statement:

In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions. Now using the MERGE statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it, and when unmatched then insert it. One of the most important advantages of MERGE statement is all the data is read and processed only once.

Example:

CREATE TABLE #Customer(CustID INT, Name VARCHAR(100))    
    INSERT INTO #Customer(CustID, Name)  
    VALUES  
        (1, 'Cust1'),  
        (2, 'Cust2'),  
        (3, 'Cust3'),  
        (4, 'Cust4');  
    CREATE TABLE #Points(CustID INT, Point INT)  
    INSERT INTO #Points(CustID, Point)  
    VALUES  
        (1, 2000),  
        (2, 3000),  
        (3, 11000);

Result:

Note: Semicolon is mandatory after the merge statement.

Merge Script:  

   MERGE #Points AS Pnt  
    USING (SELECT CustID, Name FROM #Customer) AS Cust ON Pnt.CustID = Cust.CustID  
    --Delete statement  
    WHEN MATCHED AND Pnt.Point > 10000 THEN DELETE  
    --Insert statement  
    WHEN NOT MATCHED THEN INSERT(CustID, Point) VALUES(Cust.CustID,0)  
    --Update statement  
    WHEN MATCHED THEN UPDATE SET Pnt.Point = Pnt.Point + 1000;

Result

• CustID 3’s record is deleted in the “#Points” table, because it matchs the condition “Point > 10000”.
• CustID 1, 2’s record is updated.
• CustID 4’s record is inserted in the “#Points” table, because there is no matching record found in the “#Points” table.

Note: When there is a MATCH clause used along with some condition, it has to be specified first amongst all other WHEN MATCH clause.

Example:

MERGE #Points AS Pnt  
USING (SELECT CustID, Name FROM #Customer) AS Cust ON Pnt.CustID = Cust.CustID  
--Insert statement  
WHEN NOT MATCHED THEN INSERT(CustID, Point) VALUES(Cust.CustID,0)  
--Update statement  
WHEN MATCHED THEN UPDATE SET Pnt.Point = Pnt.Point + 1000  
--Delete statement  
WHEN MATCHED AND Pnt.Point > 10000 THEN DELETE;

The above script result the following error message:

In a MERGE statement, a 'WHEN MATCHED' clause with a search condition cannot appear after a 'WHEN MATCHED' clause with no search condition.

7. Grouping Sets

SQL Server 2008 introduces a new feature called GROUPING SETS for SQL Server Database Developers. When a GROUP BY clause is used with the GROUPING SETS feature in SQL Server 2008 it will help you generate a result set which will be equivalent to that generated by a UNION ALL of multiple simple transact SQL Group By statements. This feature allows you to easily specify combinations of field groupings in your queries to see different levels of aggregated data.

The GROUP BY clause allows you to specify aggregations for a single set of database columns.  The new SQL Server 2008 GROUPING SETS clause expands upon the GROUP BY functionality, allowing you to specify different field combinations to return aggregate data.

This functionality can enhance reporting requirements and data analysis by allowing you to retrieve aggregation data through one statement, rather than several distinct queries.  GROUPING SETS also allows for “Grand total” data for the entire set of data, or just for sections of aggregations.

Example:

Use the script,  
CREATE TABLE Product  
(    
[ID] [int] IDENTITY(1,1) NOT NULL  PRIMARY KEY,  
Category VARCHAR(150) NULL,  
SubCategory VARCHAR(100) NULL,  
Product VARCHAR(100) NULL,  
Price FLOAT NULL  
)  
INSERT INTO Product(Category, SubCategory,Product, Price)  
VALUES ('Category1','Sub1','P1',120)  
INSERT INTO Product(Category, SubCategory,Product, Price)  
VALUES ('Category1','Sub2','P2',100)  
INSERT INTO Product(Category, SubCategory,Product, Price)  
VALUES ('Category1','Sub3','P3',125)  
INSERT INTO Product(Category, SubCategory,Product, Price)  
VALUES ('Category1','Sub1','P4',160)  
INSERT INTO Product(Category, SubCategory,Product, Price)  
VALUES ('Category2','Sub1','P5',165)  
SELECT * FROM Product

Result

Usual group by clause:

SELECT Category, SUM(Price) AS Price FROM Product GROUP BY Category 

Result

 Grouping Sets:

SELECT Category, SUM(Price) AS Price FROM Product  
    GROUP BY GROUPING SETS((Category),()) ORDER BY Category DESC

Another Example:

    SELECT Category,SubCategory, SUM(Price) AS Price FROM Product  
    GROUP BY GROUPING SETS((Category),(SubCategory)) ORDER BY Category DESC,SubCategory DESC 

Result:

 Contuinue reading other feature like Table value parameters, Large user defined Types, Constructor support, The hieararcy id data type, DDL trigger enhancements and User defined Aggregate functions in T-Sql Programmability Features in Sql server 2008 - Part 4.