October 2003 - Posts

Bob B, Dan S and myself have for the last year (almost) been writing a book about what's new for developers in SQL Server Yukon. One chapter from the book was to be released during the PDC and I was pretty peeved off when I couldn't find it among the material give aways.

However, it seems that it was handed out on CD during the PDC by the SQL team together with sample chapters from other books. So if you are interested in reading about Service Broker here is a link to a downloadable PDF of that chapter. This is the link to the page informing about all the downloadable chapters from the various books.

By reading mine and other bloggers entries about Yukon you may come to the conclusion that the big enhancements in Yukon are the inclusion of the CLR, XML and Web Services and the Service Broker. Yes, thay are big enhancements and very exciting, but...

T-SQL has been greatly enhanced as well, and one of my favorite new features is the Common Table Expression; the CTE. A CTE is a temporary named result set or view, and a CTE can reference itself, in which case it's called a recursive query. In other words, the CTE allows you to execute recursive (hierarchical) queries in a really easy way.

Assume you have a table looking something like so:

create table emps (
  id int identity primary key,
  [name] varchar(15),
  mgrid int)

The table above is self-referencing and holds employee names with an additional column (mgrid) pointing to who the immediate manager is (the id column). By inserting some data in the table:

insert into emps values('Don', null)
insert into emps values('Bob', null)
insert into emps values('Niels', 1)
insert into emps values('Keith', 2)
insert into emps values('John', 4)
insert into emps values('Steve', 1)
insert into emps values('Tim', 2)

we can see that we get a hierarchical relationship with 'Niels' and 'Steve' reporting to 'Don'

  • Don
    • Niels
    • Steve

and the chain for Bob looks something like this:

  • Bob
    • Keith
      • John
    • Tim

Trying to create a SQL statement wich retrieves the relation ship for - for example - Bob, would in previous versions of SQL Server be somewhat hard, as we do not really know the depth of the graph. We would probably resort to cursors and loop the resultsets etc.

In Yukon however we can use a self referencing CTE:

with emph(id, name,mgrid) as ( 
-- this is the anchor member
select id, name,mgrid from emps
where id = 2

union all
-- this is the recursive member
select e.id, e.name,e.mgrid from
emps e
join emph eh
on e.mgrid = eh.id
select * from emph
order by id, manid

The with clause defines the CTE, which is followed by something caled the anchor member. The anchor member defines the "root" of the query and is followed by a union all query which is the recursive member. The recursive member executes a recursive query until no more results are available. At that stage we can then retrieve the result. Simple or what?!

The PDC is over, but this is just the beginning of something very exciting. I have worked with some of the technologies announced at the PDC for over a year now, and I can't wait to interact with everyone else out there and see what you think about the new technologies.

Some final thoughts about the PDC:

  • As so many other says,(Sam is just an example), this PDC was probably one of the best ever. Thank you Microsoft for bringing it on, and THANK YOU all attendees for turning up and showing such enthusiams!!
  • This PDC was all about Longhorn and technologies within Longhorn: Avalon, Aero, Indigo (yes I know, Indigo may be released before Longhorn, but anyway...).
  • Whidbey was second and Yukon way behind in third.
  • Personally I am very disappointed with the relative sparse coverage of Yukon, and especially developer related topics. At least we got the bits.
  • Speaking of bits; we should all be aware that the bits we received ranges from pre-Alpha to Beta 1. In oher words:
    • things may not work
    • things may work differently in later releases
    • things may be cut! Anyone remember IMDB back in the days of the first release of COM+?!

For me personally the big thing at this PDC was - besides Yukon of course - Indigo. I have always been interested in message based applications/achitectures so this is extremely interesting for me.

I am right now sitting listening to Roger Wolter talking about one of the features (besides the CLR) I was most excited about when I first saw Yukon; The SQL Server Service Broker.

SSB is a framework for creating reliable and asynchronous database applications, utilizing message queues. Instead of using external queue framework, the queu is built into Yukon.

This is a really exciting technology and I can't wait to se whatyou developers out there thinks about it.

I may be the last person to know this but anyway. In SQL 2K sp3 Microsoft included a new UDF fn_get_sql(). This UDF returns among other things the text of the excuting statement for a particular spid.

You may ask what's the big deal with this; pre sp3 you used DBCC INPUTBUFER to achieve this. Well, the big deal is that DBCC INPUTBUFFER only returns the outermost statement executing. So if you have a proc that calls another proc you will only see the first proc, regardless if the actual executing proc is proc 2. Also, DBCC INPUTBUFFER only shows the first 256 characters of the proc or batch.

To test out fn_get_sql() do this:

1. create a table in your favorite database and add some data, like so:

create table test(id int identity, name varchar(15))
insert into test values('Niels')

2. Create and catalogue a stored procedure to retrieve data from the test table:

create procedure getTest @id int
select * from test where id = @id

3. Open a new session and check what the spid is for that session: select @@spid

4. Open yet a new session and execute the following code:

begin tran
update test
set name = 'Bob' 
where id = 1

5. Note that you haven't commited or rolled back the tx in step 4, so there is at the moment a lock on the data with id 1. Go now back to the session for which you noted the spid and execute the proc you created in step 2. As there still is a write lock on the data, the proc will be locked.

6. To check what statement is executing you can now use the UDF fn_get_sql(). The UDF takes a statement handle which can be retrieved from the sysprocesses table and the sql_handle column. The following code does just that:

--declare a handle variable
declare @h binary(20)
select @h = sql_handle 
from master..sysprocesses
where spid = [spid noted in step 3]
--get the text
select text from ::fn_get_sql(@h)

7. You should now see the text from the stored procedure. Do not forget to roll back or commit the tx in step 4.

I am sitting in, listening on Jose Blakely and co talking about Yukon and the CLR. Jose has covered how easy it is to use Visual Studio.NET to create a user defined function in C#. In the Whidbey release there is a SQL Server project allowing you to directly create code that runs inside of Yukon. When you build and deploy the assemblies are automatically deployed into Yukon - very cool!

I have had the chance to work with this for over a year now and it is amazing the difference between now and a year ago. Granted, there are still many, many rough edges but they will be softened until release.

Something really intersting and useful is how you can now create your own user defined types in Yukon. Sure, you could do that in previous versions of SQL as well, but the types had to be based on existing data types in SQL. In Yukon you can (ouch) create CLR types and they are treated as native Yukon types. The types you create can have properties and methods as any other types and they can be manipulated both fom T-SQL as well as data access API's.

Now back to the talk.

Let's rock 'n roll! Just got back to the hotel after having been at the Convention Center and registered for the PDC. I got my goodie bag and was "chuffed" to see that Bob Beauchemin had an article in the MSDN supplement about XML in Yukon. The article is an excerpt from an forthcoming Yukon book Bob, Dan S and myself have been writing.

I managed to catch part of the pre-con gig Don, Tim and Gudge did. It was great to hear those guys present together again!

For you who aren't on the Yukon beta or not attending the PDC, you can still get to test out one if Yukons new exciting features; The SQL Server Reporting Services, running under SQL Server 2000. Beta 2 of Rosetta (the code name for reporting services) for Sql2K starts in October and you can nominate yourseld at betaplace.

For more information look at this site.

So my first try of x-posting didn't work; I assume it some config issue on the hosting server. For the time being I just give it a rest and do my x-postings manually.

Over to something much more interesting: Clemens blogged about Steve Swartz giving blogging a new try. For you who don't know who Steve is, go over to his blog and get the inside. Suffice to say that we can expect loads of interestin information about Indigo from now on. Subscribed!

SQLXML enables XML support  for SQL Server databases (in addition to what FOR XML does natively in SQL 2K) In the SP1 release of SQLXML 3 Microsoft enabled (among other things) SOAP support. You can in other words expose your stored procedures, functions and templates as web services - multo cool! There were however "issues" with this if you ran Win Server 2003, and you had to jump through quite a few hoops to make it work.

Today I gave a lecture about SQLXML and I talked about Web Services and stored procs. I had a couple of days ago installed the new version, but not done any work with it, so I thought it'd be fun to check out if Microsoft had done anything to fix the Win 2003 issues. Guess what - I'm happy to report that exposing stored procs as web services now works "out of the box", wahoo!!!

The SP2 release also fixes other issues, and if you are using SQLXML this release is a MUST. Get it from here.