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

Next Post
By Admin at 22 May 2010, 16:22 PM
  • Before Continuing 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:

    1. CREATETABLE #Customer(CustID INT, NameVARCHAR(100))
    2. INSERTINTO #Customer(CustID, Name)
    3. VALUES
    4. (1, 'Cust1'),
    5. (2, 'Cust2'),
    6. (3, 'Cust3'),
    7. (4, 'Cust4');
    8. CREATETABLE #Points(CustID INT, Point INT)
    9. INSERTINTO #Points(CustID, Point)
    10. VALUES
    11. (1, 2000),
    12. (2, 3000),
    13. (3, 11000);

    Result:

    undefined

    Note: Semicolon is mandatory after the merge statement.

    Merge Script:

    1. MERGE #Points AS Pnt
    2. USING (SELECT CustID, NameFROM #Customer) AS Cust ON Pnt.CustID = Cust.CustID
    3. --Delete statement
    4. WHEN MATCHED AND Pnt.Point > 10000 THENDELETE
    5. --Insert statement
    6. WHENNOT MATCHED THENINSERT(CustID, Point) VALUES(Cust.CustID,0)
    7. --Update statement
    8. WHEN MATCHED THENUPDATESET Pnt.Point = Pnt.Point + 1000;

    Result

    undefined

    • 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:

    1. MERGE #Points AS Pnt
    2. USING (SELECT CustID, NameFROM #Customer) AS Cust ON Pnt.CustID = Cust.CustID
    3. --Insert statement
    4. WHENNOT MATCHED THENINSERT(CustID, Point) VALUES(Cust.CustID,0)
    5. --Update statement
    6. WHEN MATCHED THENUPDATESET Pnt.Point = Pnt.Point + 1000
    7. --Delete statement
    8. WHEN MATCHED AND Pnt.Point > 10000 THENDELETE;

    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:

    1. Use the script,
    2. CREATETABLE Product
    3. (
    4. [ID] [int] IDENTITY(1,1) NOTNULLPRIMARYKEY,
    5. Category VARCHAR(150) NULL,
    6. SubCategory VARCHAR(100) NULL,
    7. Product VARCHAR(100) NULL,
    8. Price FLOATNULL
    9. )
    10. INSERTINTO Product(Category, SubCategory,Product, Price)
    11. VALUES ('Category1','Sub1','P1',120)
    12. INSERTINTO Product(Category, SubCategory,Product, Price)
    13. VALUES ('Category1','Sub2','P2',100)
    14. INSERTINTO Product(Category, SubCategory,Product, Price)
    15. VALUES ('Category1','Sub3','P3',125)
    16. INSERTINTO Product(Category, SubCategory,Product, Price)
    17. VALUES ('Category1','Sub1','P4',160)
    18. INSERTINTO Product(Category, SubCategory,Product, Price)
    19. VALUES ('Category2','Sub1','P5',165)
    20. SELECT * FROM Product

    Result

    undefined

    Usual group by clause:

    1. SELECT Category, SUM(Price) AS Price FROM Product GROUPBY Category

    Result

    undefined

    Grouping Sets:

    1. SELECT Category, SUM(Price) AS Price FROM Product
    2. GROUPBYGROUPING SETS((Category),()) ORDERBY Category DESC

    undefined

    Another Example:

    1. SELECT Category,SubCategory, SUM(Price) AS Price FROM Product
    2. GROUPBYGROUPING SETS((Category),(SubCategory)) ORDERBY Category DESC,SubCategory DESC

    Result:

    undefined


    Contuinue reading other feature like Table value parameters, Large user defined Types, Constructor support, The hierarchy id data type, DDL trigger enhancements and User defined Aggregate functions in

    T-SQL Programmability Features in SQL server 2008 - Part 4.


    Comments

     

    Post a comment

    Please correct the following: