Tony Bain (from Red Rock SQL Services), a Microsoft SQL Server MVP, spoke at Wednesday night's SQL Server SIG at the Microsoft offices in Melbourne. His primary topic was improvements in the core technology of SQL Server over the 2000 version. Tony has been using the software for the last 12 months - since the first beta - and had lots of insights to show for it.
Tony started off explaining that this was a "What's New"-type session, which meant a brief overview of the improvements without any real advice on how to implement them in the real world. Left out of the night's discussion were .NET integration, Reporting Services, Analysis Services, XQuery, and more (which Tony labelled as the "cool" parts of Yukon), and the focus was on the sort of improvements that a DBA is going to spend time in, namely the relational and storage engines and the changes there. Tony discussed (this is not an exhaustive list):
- replacements for TEXT, NTEXT and IMAGE (BLOB) datatypes: VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) which can be searched in WHERE clauses
- the ability to take a read-only snapshot of a database at a particular point in time and run queries against them (he admitted one of the shortcomings of these snapshots were that they could not be backed up)
- referring to queries as variables (Tony called these run-time views), although I didn't see much difference from the current TABLE datatype except that declaration and population can be done at the same time
- recursive queries, with the afore-mentioned run-time views...I liked the sound of this!
- inbuilt function to see how often your indexes were being used
- ability to include columns in an index, that are not part of the actual index but are stored with the index to save the query going back to the table to get the field
- triggers on Data Definition Language (DDL) statements, e.g. every time a table was added to your database, a trigger could fire
- more consistent DDL statements - basically a CREATE, ALTER and DROP for almost any object you can think of - to reconcile DBCC commands, extended stored procedures and normal DDL statements
- function to return the SPID, Transaction name and running time of currently open transactions
- TRY...CATCH blocks - Tony explained that they currently only caught one type of exception, a transaction fail exception
- select a random number of rows from a table (very fast)
There was more, but I honestly can't remember all the new things. Suffice to say there is a lot to learn!
I was amazed at how many changes and improvements Microsoft had worked into SQL Server 2005, even given the limited range that Tony covered. Greg Linwood from the user group mentioned that last night's event was the first of a series of Yukon presentations, with the August SQL SIG in Melbourne titled Changes to Backup and Recovery in SQL Server 2005. Looking forward to it...