Christian Kleinerman: SQL Server 2008 for Developers

Posted By: Dan Fernandez | Feb 29th @ 9:50 AM

If you work with SQL Server on a regular basis, either writing custom queries, designing databases, or optimizing queries, this is a must see video as it covers a number of the enhancements to SQL Server 2008 including:

- SQL Management Studio improvements including IntelliSense
- New Data Types for just dates or times (no more storing time when you only need the date)
- New Hierarchical data support .IsDescendent(), that can automatically pull a hierarchical view of data (no more custom recursive queries)
- New Grouping Sets statement which enables you to automatically group dimensions in a query for easy aggregation and reporting
- New Merge statement which provides automatic insert/update semantics for keeping multiple data sources in sync
- New FileStream attribute that enables you to include files that are stored in the server file system, but can be managed by SQL Server
- New data types and support for Geodata and geometry
- New support for optimizing "empty" or row/column tables using the sparse keyword

Tags: Entity Framework, SQL Server, Virtual Earth

Rating:

Views (18,664)

Comments (15)

Downloads

Verdant

Looks like there is some interesting stuff in this release.

i am certainly interested in the hierarchical and geo-spatial features.

Is intellisense target for the sql tool or for within vs as well?

Cool, laptop, i love mine, however the tiny screen is extremely hard to see on the video most of the time.

I really like this style of video, i hope to see more, there seemed to be a lot more of this style "back in the day" - i would certainly appreciate a return to this.... personality and code in a fly-by-night kind of way, to me that is awesome, learn a little about ms and some new stuff.

aravindmc

Hey guys

You can get the transcript of this SQL Server 2008 video from this blog post. At first glance, it all looks a bit like junk, but please read the instructions on the right hand side to use it. I have created an app which can play screen capture videos with their transcripts (sort of a manual-hack combo at this point).

And I welcome any feedback about my app!

radekg

This is just amazing! All these features will make best database engine even better.

MetaGunny

Awesome!!!!!! Hierchaires, Paging, etc, have always been some what of a pain in sql server. I think adding these types and support directly into t-sql is a very good idea.

The row_id I think it is called that was added in sql server 2005 really made my paging code cleaner.

It would be REAL nice if you can add something to help with sorting. I haven't tried in awhile the different options, but having multiple sort options in sql server using a stored procedure would be a good feature. I tried in the past to do an ORDER BY @SORTCOLUMN type of method, and also multiple IF statements with duplicate sql statements isn't ideal either. The only other option using sql server is to have multiple stored procedures and in your .net code putting the IF statement.

Also, is microsoft sql server ever going to have something like oracle's rac for load balanced sql servers? I know MSFT prefers the scale out approach, and a rac type approach is technically different and ripe with problems, but just curious.

(One last thing, I found a bug in sql server 2k related to when you have two columns with certain types right next to each other. It was really weird and was relating to data storage. I tried several testing options, and the bottom line is when I changed the column order, the data came back correctly. I tried posting awhile ago on C9 and sending msft an e-mail, but they wanted me to snail mail it in. If you want me to send you the script with the table def and data let me know. I think I might have it.)

sokhaty

Hierarchies are exciting only when you are watching a demo.
I mean the idea of a dedicated type to store node's materialized path seems sound (though nothing prevents one from using varchar for the same purposes).
But the implementation is weak at best.

In fact, as soon as you try to write anything with more than a single user modifying hierarchyID columns you quickly realize that all concurrency control is your personal responsibility.

Without proper locking of ancestry nodes hierarchyID is a recipe for disaster. And built-in hierarchyID methods are in essence just substring equivalents.

Ironically, there is nothing in SQL Server to manage concurrency explicitly, which in this case means ability to place explicit row locks in a non-blocking manner.

Ideally, it should be possible to place shared read or exclusive locks on rows using T-SQL. For starter only exclusive locking would suffice (though it has some serious performance implications). But they did provide even that.

IMO this feature should have not been included at all. People will try to use it in real life and get burned. Microsoft should have added means to ensure proper use of hierarchyID first and produced clear guidelines on how to do that.

Re: table types in T-SQL, compare this to complex type support in Oracle PL/SQL. T-SQL is still 10 years away from where PL/SQL was 10 years ago.

Yankee

Well, this looks really interesting, I hope I'll finish my C# 3.0 book soon (reading, not writing Big Smile ) and search for one about MS SQL 2008 Wink

JoshRoss
JoshRoss

Sometimes it's the simpler things in life that make the most difference, declare @i int=1, etc. I've been passing xml documents into procs as a substitute for @t table variables. Allowing table variables to be passed into stored procs will save me a lot of headaches. WIll this work with linq to sql?

ZippyV
Soapbox = Fail

How much of the sql 2006 standard has been implemented?

staceyw
Before C# there was darkness...

JoshRoss wrote:

Sometimes it's the simpler things in life that make the most difference, declare @i int=1, etc. I've been passing xml documents into procs as a substitute for @t table variables. Allowing table variables to be passed into stored procs will save me a lot of headaches. WIll this work with linq to sql?

I have been wanting linq "inside" sql for some time. Not sure why they keep ignoring this. Your clr procs would then be a great place for BL and we could stay in .net.

Thanks for the vid.

OT. Why does silverlight only have two size options for videos - small and full-screen? Sometime neither is the right viewing size. It also does not show detail (right-click) info of the video encoding like MP does.