TSQL enhancements in SQL server 2005 # Part 1

1. TOP clause with TIES option : Select TOP(4) with TIES * from test order by idkey desc

2. Supports an OUTPUT clause so a single trip to the server performs the data updateand returns the results.

3. PIVOT operator provides the ability to quickly and easily generate cross tab queries. A cross tab query rotates rows data into columns data. : select VENDORID , [7],[8],[9] from MonthlyPurchaseOrders pivot (sum(subtotal) for Ordermonth in ( [7],[8],[9],[10])) as a

4. Exception Handling with TRY/CATCH

5. ROW_NUMBER() function creates a column that displays a number corresponding the row's position in the query result : select name, row_number() over (order by name) from test

6. RANK() function works much like the ROW_NUMBER() function in that it numbers records in order. They differ in the way they work when duplicate values are contained in the ORDER BY expression. : select name, row_number() over (order by name) , rank() over ( order by name) from test

7. DENSE_RANK() works the same way as RANK() does but eliminates the gaps in the numbering.

8. NTILE() breaks the result set into a specified number of groups and assigns the same number to each record in a group

9. Common Table Expressions (CTE) A Common Table Expression (CTE) is a temporary result set created from a simple query