The last session I attended for the day was about the TSQL enhancements in Yukon. The recursive queries I mentioned in the earlier post are actually Common Table Expressions, based on the SQL99 specification, and apply to non-recursive queries as well. MDX uses a similar construct. You essentially define an inline view for a SQL statement. The recursive version uses a union statement as well. I’ll wait to describe it for when I see the actual specification; the important thing to note today is that it can be done.
Yukon also includes a number of ranking statements that can be themselves grouped (i.e. you relatively rank them for all values that match some other criteria). The functions, Rank, Dense_Rank, and NTile, are based on SQL99 OLAP extensions.
Have you ever had to display rows, or some data value that varies in a column, as individual columns themselves (to look like a pivot table)? Have you searched the newsgroups only to find that Access has a nifty function for this (transform) but to do the same thing in SQL you have to dynamically create a SQL statement based on cased sum statements? Finally, SQL Server supports this as a new relational operator, pivot, as well as its inverse, unpivot, and a funky apply operator that sounds like it does neat things with table variables (but I’m not yet sure what).
TSQL data manipulation statements (DML, i.e. insert, update, and delete) will support an output clause. This clause can be used to a) return the values inserted/deleted or b) return anything else. For example, maybe you have implemented a queue and when you delete an element from the queue, you want to return the remaining members. Now you can.
More on full-text indexing: rebuilding the indices is an order of magnitude faster and queries are 30-50% faster (I said 30% earlier). They have also improved language support to be configurable and to do stuff with accents and the like, you can do full text queries across linked servers, you can full text search indexed views, you can backup/restore, and manipulate the catalogs along with the database (right now, those are all separate operations), it runs in the same security context as SQL Server, and it runs one index engine instance per SQL Server instance (instead of one per machine as is today).
The varchar(max) type I talked about earlier supports up to 2 GB of data and can be manipulated like any other varchar variable: comparisons, triggers, concatenation, aggregations, parameters, and variables (unlike its text counterpart). The update syntax now supports “chunk” updates for this large type, in case you want to modify only a subset of the data stored for the value. It works a lot like the replace function.
Yukon will have a new meta-data store, the sys database, and new meta-data catalogs. It sounds like interfaces that mimic the existing meta-data tables will be supported. Permissions on this catalog have been modified. If you don’t have permissions to query a table, it is likely you can’t see it in the catalog either. Apparently, you can now execute stored procedures and functions as other users (i.e. the owner). But they must have changed other things about security if this change was needed.
Finally, they have made a number of performance tuning/trouble shooting enhancements. For example, the sys.request_stats virtual table can be queried to return the top n queries that consume the most resources. Neat, eh?
Tomorrow promises to be interesting as well. I’ll be attending Yukon talks on XML, web services, Biz Talk, as well as a few ASP.Net talks. I’ll post the good stuff when I can.