The conference started with a bang with a full day workshop with Itzik Ben-Gan on advanced T-SQL querying and programming. The advanced adjective wasn’t a joke, if anything it was an understatement.
The workshop started with a look at the OVER clause, new in 2005 and unfortunatly not enhanced in 2008. Itzik covered the OVER clause as it applies to aggregates and also how it works with the ranking functions that it’s usually associated with. He also discussed some of the aspects of OVER that exist in the SQL standard but are not implemented (yet) in the product. Hopefully in the next release…… Itzik also looked at using the row number function to create a more powerful TOP.
He looked at ways to create custome aggregations, including cursors, CLR user-defined aggreates and T-SQL tricks to compute aggregates that there aren’t built-in commands for, like median and product.
Next up was a brief look at creating a more powerful pivot (requiring dynamic SQL) to remove the need to hard code the pivot values, splitting arrays with a numbers table (and the new table-type parameter that removes the need to do that) and randomisation of rows and values.
We spent a fair bit of time going over custom sequences, including ones that don’t have gaps (unlike identity) or apply over multiple tables (unlike identity) and looked at blocking and non-blocking versions, the blocking been the ones that prevent gaps in the sequence from appearing.
Most of the afternoon went to a look at graphs, trees and hierarchies, including a little bit of graph theory relating to the differences between cyclic and non-cyclic graphs, directed graphs and trees. Several different ways of representing graphs and/or trees in a table were shown, including the advantages, disadvantages and types of graph allowed by both. Among the things covered here were recursive CTEs, hierarchyid and some custom-built graph implementations.
Finally Itzik went over the merge statement, which allows the ‘upsert’-type operation to be done in a single atomic statement, with the additional option of allowing for a delete and the grouping sets which replace and greatly extend the old cube and rollup commands that have been around in SQL for ages.
Off to a function now, hopefully at least a couple of hours of sleep later. I think tomorrow’s going to be just as intense….
Sounds like an awesome afternoon that bordered on information overload.
It was. Felt like an information firehose.