Francois Ajenstat has published news about the SQL Server 2008 release roadmap. Summarized:

• Feature complete CTP for the launch wave in Q1 2008
• Release Canidate (RC) in Q2 2008
• Release to manufacturing (RTM) expected in Q3 2008

Exciting!

When living on the edge (read: running the latest betas of everything) as I do, you get trouble from time to time. This time it was Visual Studio 2008 "Data Dude" combined with SQL Server 2008. For those of you not familiar with it, "Data Dude" is an extension to Visual Studio for working with databases; unit testing of your database, automatic data generation etc.

My problem was that I kept getting a message saying "Object reference not set to an instance of an object." all the time when I tried to create a new database project. I suspected that SQL 2008 was the cause, and I was right. It turns out that "Data Dude" creates a temporary database when you're opening or creating a project. In my case this happened in the default SQL Server instance on the machine, which is SQL Server 2008 CTP5 Dev Edition. "Data Dude" didn't like that very much.

The solution (at least in my case) was to configure it to use the installed SQL Server 2005 Express Edition instead. You do that by going to Tools -> Options -> Database Tools -> "Data Connections" and
"Design-time Validation Database". Fill in the instance name of a SQL 2005 instance in the text boxes. So now you know!

I've been working a little with SQL Server Compact Edition (which is a *very* cool product, by the way) lately. For those of you that haven't heard about SQL Compact: It's a small version of SQL Server that you host in-process in your .NET applications (it's just a couple of MBs) with full support for tables, indexes, transactions and so on (but no views, stored procedures or triggers). Much like SQLLite and Apache Derby, just better integrated with .NET.

So far I've been using Visual Studio to create and manage databases. This works great, but I didn't like to start up VS and everything just to peek into a database, and I've always felt more at home in SQL Server Management Studio (SSMS) when working with databases. Well, it turns out that SSMS can be used to manage SQL Compact databases as well! Cool, I didn't know that.

This is basically what you do:

Robert has some more on this on his blog.

Yep, the November CTP was released just hours, maybe minutes ago! As of now, they still haven't posted the link on the SQL Server home page. The downloads have been posted on the Microsoft Download Center for easier availability, follow this direct link to get to the bits:
http://www.microsoft.com/downloads/details.aspx?FamilyId=3BF4C5CA-B905-4EBC-8901-1D4C1D1DA884&displaylang=en

There you can find DVD images and self extracting executables for x86, x64 and IA64. This CTP includes SQL Server Express Edition as well.

Happy downloading!

While we're waiting for the next SQL Server CTP, it thought I could mention a couple of small things already in the current CTP: Multiple row Insert and Declare-Set combined.

To insert multiple rows  in SQL Server 2005 and before, you had to execute multiple INSERT statements, or use UNION. The first would look like this:

INSERT INTO MyTable (Col1, Col2)
VALUES (1, 'One')
INSERT INTO MyTable (Col1, Col2)
VALUES (2, 'Two')
INSERT INTO MyTable (Col1, Col2)
VALUES (3, 'Three')

And using UNION it would be

INSERT INTO MyTable (Col1, Col2)
          SELECT 1, 'One'
UNION ALL SELECT 2, 'Two'
UNION ALL SELECT 3, 'Three'

In SQL Server 2008, this can be done like this:

INSERT INTO MyTable (Col1, Col2)
VALUES (1, 'One'), (2, 'Two'), (3, 'Three')

I did some quick performance testing and found that UNION and multiple inserts runs on about the same time, while the single inserts are slower. But this is only true as long as the numbers of rows being inserted isn't to high. If inserting 10 000 rows in one go, for instance, the individual inserts are actually faster, probably because of the amount of in-memory data the unions and the multiple row inserts produce, as well as that the last two commands run the whole batch within on large transaction.

Then, Declare-Set combined: It has long irritated me that I had to use two lines to declare a variable and assign a value to it. This is what it looks like i SQL Server 2005 and earlier:

DECLARE @var int
SET @var = 50

DECLARE @var int = 50

DECLARE @var1 int         = 50,
        @var2 varchar(10) = 'Hello!'

...does not work, it looks like.

I upgraded my installation (hosting this site) of Windows Server 2008 from Beta 3 (June CTP) to RC0 today - over remote desktop! My server is located in a locked server room, so I wanted to try a remote upgrade before asking for the key, and it went well. I extracted the ISO onto the hard drive, started the installation from remote desktop and selected the upgrade option. It turns out that the Windows setup doesn't (luckily) show any dialogs where you have to click next during the upgrade. I had a continuous ping trace running and observed the server going up and down a few times, before it completed the installation and enabled incoming remote desktop connections again. Pretty cool!

But, back to the title. Everything worked well after the upgrade, except SQL Server 2008 July CTP. I kept getting error messages from Management Studio when connecting saying "No process is on the other end of pipe", and this led me into thinking that I had a certificate problem. See this blog post.

But, it turns out that this wasn't the issue. The issue is that Windows Server 2008 RC0 ships with a version of SQL Native Client that is newer than what the SQL Server 2008 July CTP supports, so it just doesn't work. This thread says it will be addressed in the next SQL Server CTP. Until then you have two options:

• Use Management Studio 2008 from another machine to administer SQL Server. This will probably still give you problems if you run SharePoint on the same server, at least my SharePoint installation gets the "No process in end of pipe"-error
• Install SQL Server 2005

For now, I'm going with SQL Server 2005 while I'm waiting for the next CTP of SQL Server 2008. I read somewhere that it is expected in the next one to two weeks. It will probably contain lots of new stuff, like spatial data support, Intellisense in Management Studio and so on. Looking forward to it!

School is out for Christmas (I had my last exam last Friday), so now I have time do fun things, like coding (surprise).

I think algorithmic programming and databases and SQL queries are cool things, so why not combine them? Yesterday I got an idea of implementing some well-known algorithm in SQL, and I figured out that Dijkstra's Shortest Path algorithm should be fun to implement.

Dijkstra's shortest path algorithm finds, well, the shortest path from one vertex to the other vertexes in a weighted graph. The edges have lengths (or costs or whatever), and the shortest path from one vertex to another is the path where the sum of these lengths are as small as possible. Take a look at the illustration below, showing a graph with some Norwegian cities. The shortest path from Trondheim to Fredrikstad has been highlighted (for those of you that know Norway, not very realistic, but let's pretend it is just for the fun of it).

The algorithm works like a breadth first search that takes the edge weights into account, starting at one vertex and traversing through the graph.

So, how do we implement this in Transact-SQL (MS SQL Server's SQL dialect)? Well, first we need some way to represent the graph. I've created two tables:

City

Road

The City table is pretty straightforward. The Road table contains one row for every road from one city to another, and the length of that road. Notice that we have two rows for every two cities we have a road between them, one each way. But, now to the real stuff: The implementation of the algorithm:

If we run it with Trondheim as start city (@StartCity = 1), we get this result table:

This says that from Trondheim, we have a distance 0 to Trondheim, 2 to Bergen and so on, and 6 to Fredrikstad. The Predecessor column says what city we came from when we went to each city. We can see that to get to Fredrikstad, we came from Oslo, and to get to Oslo, we came from Bergen. To get to Bergen, we came from Trondheim. Therefore, to get to Fredrikstad, we took the path Trondheim, Bergen, Oslo, Fredrikstad.

I have included the SQL script to create the database:

Dijkstra.txt (8,03 KB)
TestScript.txt (1,26 KB)