Yukon...Let the ride begin!!!!

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.

 

 

 

 

 

Print | posted on Saturday, May 15, 2004 4:56 PM

Comments on this post

# re: <b>Yukon...Let the ride begin!!!!</b>

Requesting Gravatar...
hard-coded pivot values? Why bother then!? that's a pretty big disapointment ... it's simply short than doing a lot of SUM(CASE...) statements, but that's about the only advantage ...

I agree, you should at least be able to specify a SELECT statement for the column list in the IN clause.
Left by Jeffs on May 17, 2004 3:57 PM

# RE: no.2

Requesting Gravatar...
Delimited lists by their very nature must be text, so any data represented in delimited list form must be plaintext, and thus must be re-parsed into the appropriate data type to be used.

Although I agree that the ability to use delimited lists is long overdue, surely what is *really* needed is the ability to pass a 1D array of values to stored procedures, and to use them in IN clauses. That way, datatypes are preserved saving parsing mentioned above, avoiding problems with escaping single quotes, etc. etc.

I am an ASP (classic) developer and would like to neatly encapsulate all my SQL as Stored Procedures, but when I need to pass a variable number of values for a particular field in a query (e.g. categories selected from a multiple-select OPTION element in a form) I find that the only way to do so relatively easily is to dynamically construct an SQL statement in my script and execute it.

It would be far better if I could simply pass a 1D array of values as an SP parameter and then use an IN to perform the matching. This would be both more secure AND more efficient.

It's very frustrating to be well aware that dynamic queries are bad practice (for oh-so-many reasons) but to have to use them anyway! Unfortunately, without a viable alternative, dynamic SQL statements are here to stay.

Array support please!!
Left by Marcus Tucker on Jun 17, 2004 9:24 AM
Comments have been closed on this topic.