T-SQL 2005
Posted by bsstahl on 2006-05-08 and Filed Under: development
The first session I attended was T-SQL 2005 Enhancements, Tricks and Tips. David Lundell was the instructor and did a nice job.
Some key points were:
- Common Table Expression (CTE) - A way to alias a query for use within another query. This can also be used to execute queries recursively.
- nTile - Allows a resultset to be "partitioned" into segments. i.e. 100 segments make it a percentile, 4 segments is a quartile, etc.
- Rank - Similar to nTile except the values are ranked in order. If two or more items tie for a rank, subsequent ranks are skipped. That is, if two items are tied for first, the next item would have a rank of 3.
- Dense_Rank - Same as Rank only no ranks are skipped, regardless of ties
- Row_Number - Same as Dense_Rank only with no duplicates, just lists the items in order from 1..n. Ties are arbitrarily broken and consistant order by broken ties is not guaranteed.
- Pivot - Allows the creation of Pivot Tables but in my opinion, won't be very useful until we can set the columns dynamically. Currently in SQL 2005 the Pivot columns have to be explicitly defined.