I got to attend the second part of a Yukon review last week that was really informing and interesting.  It covered some of the basics which I had already seen, such as the additions to Transact-SQL (pivot, apply, etc).  The demonstator had a post-beta1, not quite beta2, version of Yukon, so we also got to see several new additions and many things that had been refined.

One of the exciting new features I really hope makes it to the final release is the ability to script commands in the Workbench.  Any command you run from the Workbench such as backups, restores, table creation can be scripted instead.  When you get ready to save the change, you can instead click on a Script tab and copy the script out.  I've always wanted something like this; and it looks like Microsoft is finally seeing the need for it also.

We also had a demonstration of the common table expressions.  I don't know how many of you have seen this.  One of the weaknesses in SQL Server is the ability to easily create hierarchies of data dynamically and use recursion.  Many times this requires jumping through loops, using temp tables, and processing things in loops or multiple sets.  With the table expressions, this type of manipulation has been greatly simplified.  Examples of CTE expressions can be seen here:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql_ovyukondev.asp

The integration of the CLR was also demonstrated again.  The ability to create custom datatypes and business logic with .NET holds some interesting possibilities.  The permissions mode of the CLR, which allows the CLR processes to run in safe mode(which is pretty limited in scope), external access mode (which gives access to files, directories, etc.), and unsafe access mode (which allows the processes to basically run anything) allows different levels of control for the processes you setup with the CLR. 

I also have a couple areas of concern, which hopefully Microsoft will address before the final release:

1.  The PIVOT function has to have values hardcoded:

            --Example: 

                        SELECT *

                        FROM ItemAttributes AS ATR

                          PIVOT

                          (

                            MAX(value)

                            FOR attribute IN([artist], [name], [type], [height], [width])

                          ) AS PVT

                        WHERE itemid IN(5,6)

            --Notice the IN([artist],[etc]) is hardcoded.  This really limits the usability of the PIVOT funtion.

            --Microsoft really needs to add the ability to handle IN (SELECT column_list FROM table)

2.  It would be nice if Microsoft would finally recognize the need to allow variables with column delimited strings to be used.

            --Example:

                        DECLARE @values VARCHAR(500)

                        SELECT @values = 'artist, name, type, height, width'

                        SELECT *

                        FROM ItemAttributes AS ATR

                          PIVOT

                          (

                            MAX(value)

                            FOR attribute IN(@values)

                          ) AS PVT

                        WHERE itemid IN(5,6)

3.  The CLR integration needs to have an administrative ability to restrict users/roles to only roll certain permission levels. 

            --With the CLR, I wouldn't want all my developers with the ability to create processes with unsafe access even on the Development SQL Server.

            --It would be nice to have a schema that allows users/roles to add processes only with certain access levels.

One of the biggest challenges will be measuring, benchmarking, and tuning the performance of SQL Server.  There are now many more variables to capture, measure, and optimize.  With any new release, there is also that painful learning curve for development.   The use of fucntions for example in SQL Server is a very powerful tool.  Many times they work great on small “development environements“ but have the ability to grind an entire enterprise production environment to a halt when implemented.  With proper use though they provide simplification , extensibility, and consolidation of code base.

As a last note, I think all SQL Server DBA's should be ramping up on the .NET skills if they are not already.  The SQL Server world is going to be turned upside-down with the release of SQL Server 2005.  It will be an exciting, trying, and challenging change to our world.  Make sure you're ready for the great ride.

Be sure to read up on SQL Server Yukon and watch the webcasts:

http://www.microsoft.com/sql/yukon/productinfo/

Favorite words used: 0 () --That's a first.

Mean level (1-10):  0 --I'm loosing my touch.

Education level (1-10):  5 (If you have researched Yukon, you shouldn't have learned anything.  If not, you should start now.)

Entertainment level (1-10): 1 --For the hopeless geek out there.