Christian Kleinerman: SQL Server 2008 for Developers

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.

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!

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

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.)

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.

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

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?

How much of the sql 2006 standard has been implemented?

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.

No low bandwidth version?  It is not that I have a slow network or a tiny PC -- I just cannot see the point of using a gig of download to I can have it 2"x2" (they are guys speaking, why have it bigger) on screen.

Use the MP3 link.

So it seems the Geo Spatial data is specific to 2d data? I'm guessing this was done for the effiency benefits as compared to a lookup for which dimensions are being used, but is there an option for 3d? I guess I could create either a table of multiple angles to pull from as the third axis, but that doesn't seem to be the easiest solution (though perhaps only). Any response regarding the "nature" of geo data as supported by SQL?

Thanks,
-Scott

Hi everyone

Thanks for all the feedback... I'll address some of the questions:

·         Intellisense: it’s currently targeted for SQL Management Studio… over time this should reach VS.

·         Dynamic sorting: I don’t like recommending dynamic SQL but that is one option…  Assuming your stored proc has a single SELECT statement, another alternative (slightly less encapsulated) would be to use a table-valued function instead of a stored proc and specify the ORDER BY from your middle tier code. In SQL Server 2008 we also added an ORDER clause to SQL/CLR functions inside the engine to have the optimizer  eliminate unnecessary sorts if the function is producing a well known sort order.

·         Hierarchy management: it is true that we didn’t implement the highest possible level of abstraction, but an intermediate lower level building block. There were reasons to do so, mostly around flexibility of the solutions you can build and keeping the type closer to other types rather than to higher level constructs that automatically impose constraints and concurrency semantics on tables. However, we’ve included in Books Online the common patterns to make it easy… feedback welcome on missing patterns.

·         LINQ: no for support for table-valued parameters… and in SQL 2008 we enabled XLinq inside the engine. We will enable other stacks in future releases… feedback on scenarios and priorities most welcome.

·         ANSI ISO SQL standard: since SQL 99 achieving the core package has been a huge collection of features and it removed the levels of compliance like SQL 92 did… We have a large subset of the core standard all the way to SQL 2006 and as we continue to add capabilities, we always look at the standard to make sure that where applicable, we’re following the standard.

·         Spatial: we support 2.5D, carrying around elevation values. We’re considering 3D for a future release.

For other feedback (or for the bug report mentioned) either use connect.microsoft.com or send me mail… Christian.Kleinerman@microsoft.com.

Thanks for all the feedback and keep it coming!

-          Christian

Awesome!!!!!!  It looks pretty easy to the developers... and especially adding new types like Hierarchy & Paging etc support directly into t-sql is a very good idea. And is the Hierarchy will work only on Int datatype or even on String.

Really great! I love every new feature, except for the spatial data part which I probably wont use but what the heck, its cool. Great job! declare @i int = 1 about time