THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

PASS Followup, Technical Edition

I have already given a rundown of most everything PASS here on my personal blog, including a series of posts with pictures too over the days of PASS) but since this blog is all about things technical with SQL Server, I wanted to just rundown of some of the cool stuff I learned about SQL Server 2008 that I hadn't heard before...

  • You can index a sql_variant (script in this post)- possibly the least important piece of information is the most interesting.  A person asked Dejan Sarka in his session if you could index them, and no one really expected that you could.  Turns out, you can....
  • Spatial Datatypes are going to rock! - Really cool stuff.  (good high level rundown here on the Virtual Earth team blog).  They will support a flat earth model (like you use on a road map) and a round earth (well, ellipsoidal really) model as well, for more precise modeling of large spaces. Initially I thought this was going to be a fringe datatype and while it still might be one day in the future, I saw a lot of ideas I could use this for, even in an OLTP database. 

    For example, if you have the longitude and latitude of the address of a customer/constituent, it is going to be easy to join that point to the shape that represents a time zone, a country, the zip code, a voting district, etc, etc.  Or you could also do it at a zip code level, and join the shape of a zip code to the the other shapes it intersects with.  Of course, just like XML, these values will not always be exactly relational in nature (though you can make a case these types of datatypes represent a scalar value (a shape) while internally they might be made up of tons of points.
  • Intellisense is a coming - Yes, without an add-in, SQL Server 2008 tools will have intellisense.  Can't wait to see just how this works out for them.  Red Gate's version is ok, but it can be kind of clumsy to work with. Might even still be useful to have both...who knows.  The folks at Red-Gate are smart cookies.
  • Analysis Services 2008 will be evolutionary, not revolutionary - This is a good thing really.  The change from 2000 to 2005 was big, huge, major.  The problem was, it was so huge that it was hard to figure out all of the bits and pieces.  Part of this was the tools were version one, and 2008 will take the tools to the next level.  The biggest neat stuff in there surrounded having the engine able to give warnings about how your cubes are designed.  Blue squiggly lines (Donald Farmer claimed this to be a new technical term: "squiggly" in his session on AS 2008) will show you warnings right in the UI, and red ones show errors.  Warnings can be ignored, errors not so much.  Apparently you can use the new tools to modify 2005 cubes too, though you won't be able to use any new functionality that does get added to 2008.
  • T-SQL is making small amounts of progress forward - As a person who basically makes his living on relational database design, and T-SQL, I am a bit disappointed by how little T-SQL is changing.  I am particularly desirous of lots of usability tweaks being done to T-SQL (like why do something about delayed name resolution on procedures or especially CREATE OR ALTER syntax for creating objects).  To me, the biggest wins for DBA could be done at what should be the least cost to the dev team.  Both of the ideas stated would only matter to the compiler, right? So nothing in the engine that executes queries need to change.  Just the compiler, and with delayed name resolution, we already have both situations implemented (functions do not allow delayed name resolution) so it should be mostly cosmetic.

    This however is only my perception, and if they came back and said on the connect site that it was too hard because, well, I would never disagree with them, since all I can program in is T-SQL.  And it isn't that they are completely ignoring T-SQl, there are a few interesting enhancements, such as the one's Jamie points out here involving variable initialization,  and row constructors to allow > 1 row in an insert statement. Check this post by Greg Duncan for a bit more coolness on that subject.
  • Change data capture - This is going to be a very interesting addition to the platform.  It will allow you to get information about what rows have been changed in your database (each and every time the row changes), much like we have done with triggers for years (they claim a great performance improvement over triggers (sounds like the race is on!)).  This will allow very finely grained ETL in a nice package.  (See post here for better overview) The downside of course, is that CDC is only going to be available in Enterprise Edition.  For all other versions, there will be a feature called Change Tracking that will keep up with what keys of rows that have been modified (even deleted).  This will give users of other editions a pretty good tool for seeing rows that have changed.

    I am probably the most excited about this feature, since it will allow ETL to be created in a natural way without building triggers or relying on date and time stamp values that you need to maintain.


This is certainly not an exhaustive list (I expect that if you are reading this blog, you have seen the new date types, if not try this post.  I saw a pretty exhaustive list in a private session.  Lots of really cool fun stuff is coming in the upcoming CTP's leading up to release sometime in 2008 (no scoop there, huh?)

 Edit: Forgot about change data capture

Published Tuesday, September 25, 2007 1:03 AM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Glenn Berry said:

I agree that Microsoft seems to be strongly favoring BI over "old fashioned" relational and T-SQL features in SQL Server 2008. I am very happy about things like Resource Governor, Plan Freezing, and the MERGE statement.

Still, I am already getting weary of hearing about "Beyond Relational" and "Pervasive Insight" from the marketing people...

September 27, 2007 8:41 PM

drsql said:

Amen to that.  It isn't that I am not grateful for the new stuff we get, but I really want to see the namesake product make equal "outward" leaps in each new version to the other products.  

There are lots of internal improvements for sure, but for the smaller companies out there, there needs to be some "wow" factor that makes it, if not necessary to upgrade, very inticing.  The scalability factors are definitely important, but for the smaller space that I usually work in, even 2000 is adequate performance wise, though we do have a few systems that need 2005 scalability enhancements.

Without the wow feature on the relational engine, many people won't upgrade.  I remember when 2000 came out and we got functions, I beat up my employers to UPGRADE IMMEDIATELY! Table valued functions saved me many lines of code due to some tasks I was working on.

September 27, 2007 10:44 PM

Leave a Comment


This Blog


Links to my other sites


Privacy Statement