Yukon, the next release of SQL Server has a ton of new stuff in it. In addition to what I mentioned earlier, it has:
The ability to create stored procedures, functions, triggers, types, and aggregate functions expressed in any .Net language. Basically, you create the thing in your .Net language of choice, you register the assembly in SQL via the "create assembly" command, and then you create the thing in SQL server referring to the assembly. For example, for a function you would still write the create function syntax including its signature (input/output) and then refer to the assembly for its implementation. These managed extensions require you to implement certain interfaces inside of your managed code. For example, if you wanted to create a type, that type (class) would have to implement the INullable interface (because a column of that type could be nullable).
If you need to do some data access inside of your managed code called from SQL, you could use a special in-process version of ADO.Net. The advantage of this is that you would not be traversing the network again to query a table; it is smart enough to know that you are already inside of the SQL process space.
However, for the most part you’ll want to reserve managed code for calculations that are close to the data. If you have to do a lot of lookups ad data traversal, it will still be better to do that in TSQL.
Yukon will have tight integration with Visual Studio. There will be a SQL Server project type and you can set breakpoints, debug, and single step anywhere: managed code, TSQL, whatever.
There is a new datatype, Varchar(any), that sounds like it will replace the Text datatype. The text datatype is a pain in the butt. It is inefficient because the data is stored separately from the table thus requiring extra I/O to get the text data. Plus, many functions (e.g. replace() ) cannot use a text-typed parameter. This new datatype stores some of the data with the table and if there is too much data only then will the server store it in a secondary location. As I understand it, this will allow you to index text columns, get better I/O performance when the data in that column happens to be small (rather than paying the price for the second lookup even if the data is only 8 characters long in some instances), and perhaps be able to use some of the instances in functions that can be used for varchar columns.
Yukon also supports statement level compilation. Currently in TSQL, if you do certain things in the middle of a stored procedure (say, create an index or a temporary table), the optimizer forces a recompilation of the whole SP. In Yukon, only the applicable statements will get recompiled.
Yukon also supports recursive statements which will make a difference when doing things like hierarchy lookups. The compiler can optimize for those sorts of things better when it is a single statement.
Yukon will support DDL triggers. If you want to write code that fires upon create, modify, or drop of schema elements, you can.
The spiffy new acronym for Yukon is MARS – Multiple Active Result Sets. I didn’t see a demo of this, so I’m not sure exactly how you would use this, but it sounds like it allows for pipelining or result sets whose data is used in subsequent statements. For example, if the results of one set were used as filter criteria in another, it will use what it has so far for the second statement while the first statement is still executing.
Yukon will also have support for Object-Relational mapping (known as Object Spaces). Again, I didn’t see a demo but it sounds like you can specify the mapping logic between your objects and the resulting tables and then do things like issuing OPath queries: the server knows how the mapping works and will return a collection of the corresponding objects.
Yukon supports query notifications (which you can use to update a cache when the data changes in the source), server cursors, asynchronous client access, and bulk update, paging, and batching (for example, the TSQL statement: bulk insert [Table] from [File Path]). The speaker was running out of time so I didn’t see these in action, however.
Those of you familiar with full text indexing in SQL Server, should be pleased with the enhancements in Yukon. Aside from an estimated 30% performance improvement, they have added thesaurus-based searching, multi-column "contains" statements, and have made it easier to administer (e.g. added DDL statements such as create full text index).
The XML data type I mentioned earlier can be optionally enforced by a schema. Not enforcing it would better aid in the storage of so-called "unstructured" data (data, by definition, has structure so that term makes no sense) or for XML schemas whose schema may change over time. I was pleased to see that they consider XML to be a wire format and would prefer most storage to happen in traditional data schemas.
Along with Yukon’s XML support is XML views which can combine, somehow, XML and non XML data into one view. I didn’t see how it works nor can I imagine a scenario for this in the business world, but it’s there if you want it.
As I mentioned, Yukon supports XQuery. You can embed XQuery within SQL statements and you can embed SQL within XQuery statements. If you are like me, you find XML query languages asininely difficult so Yukon thoughtfully includes an XQuery builder.
Finally, Yukon has made lots of enhancements to the data transformation services (DTS). It is completely integrated with Visual Studio, including breakpoints and visual debugging. The latter has some neat GUI tricks (on the diagram) like displaying row counts for each step, color coding diagram elements that have succeeded or failed, showing resulting data sets in grids, etc. The locals window will display all package variables and it supports other data viewers (e.g. instead of a gird showing sample data it could show a chart) when visual debugging. DTS now also supports looping (difficult in the current DTS), scoped variables, precedence constraints, and custom tasks written in the .Net language of your choice.
There is another session on TSQL enhancements later this afternoon. I’ll post again if anything new is talked about.