New sample database – AdventureWorks light

Microsoft have released a new sample database for SQL Server 2005 which is much simpler than AdventureWorks. It’s still based on the same scenario, and it still (frustratingly) uses singular table-names (like Person, not People), but this is certainly going to be useful for all kinds of demonstrations. It really helps to have a model that is easy to understand, and this database, with far fewer tables, is much easier to get your head around. I will probably start using this in the user-group, and any other times when I want to demonstrate a piece of functionality.

Download it from here

Coalesce is not the answer to string concatentation in T-SQL

I’ve seen many posts over the years about using the COALESCE function to get string concatenation working in T-SQL. This is one of the examples here (borrowed from Readifarian Marc Ridey).

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ‘,’,”) + Name
FROM Production.ProductCategory

SELECT @categories

This query can be quite effective, but care needs to be taken, and the use of COALESCE should be properly understood. COALESCE is the version of ISNULL which can take more than two parameters. It returns the first thing in the list of parameters which is not null. So really it has nothing to do with concatenation, and the following piece of code is exactly the same – without using COALESCE:

DECLARE @categories varchar(200)
SET @categories = ”

SELECT @categories = @categories + ‘,’ + Name
FROM Production.ProductCategory

SELECT @categories

But the unordered nature of databases makes this unreliable. The whole reason why T-SQL doesn’t (yet) have a concatenate function is that this is an aggregate for which the order of elements is important. Using this variable-assignment method of string concatenation, you may actually find that the answer that gets returned doesn’t have all the values in it, particularly if you want the substrings put in a particular order. Consider the following, which on my machine only returns ‘,Accessories’, when I wanted it to return ‘,Bikes,Clothing,Components,Accessories':

DECLARE @categories varchar(200)
SET @categories = NULL

SELECT @categories = COALESCE(@categories + ‘,’,”) + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)

SELECT @categories

Far better is to use a method which does take order into consideration, and which has been included in SQL2005 specifically for the purpose of string concatenation – FOR XML PATH(”)

SELECT ‘,’ + Name
FROM Production.ProductCategory
ORDER BY LEN(Name)
FOR XML PATH(”) 

In the post I made recently comparing GROUP BY and DISTINCT when using subqueries, I demonstrated the use of FOR XML PATH(”). Have a look at this and you’ll see how it works in a subquery. The ‘STUFF’ function is only there to remove the leading comma.

USE tempdb;
GO
CREATE TABLE t1 (id INT, NAME VARCHAR(MAX));
INSERT t1 values (1,’Jamie’);
INSERT t1 values (1,’Joe’);
INSERT t1 values (1,’John’);
INSERT t1 values (2,’Sai’);
INSERT t1 values (2,’Sam’);
GO

select
    id,
    stuff((
        select ‘,’ + t.[name]
        from t1 t
        where t.id = t1.id
        order by t.[name]
        for xml path(”)

    ),1,1,”) as name_csv
from t1
group by id
;

FOR XML PATH is one of the only situations in which you can use ORDER BY in a subquery. The other is TOP. And when you use an unnamed column and FOR XML PATH(”), you will get a straight concatenation, with no XML tags. This does mean that the strings will be HTML Encoded, so if you’re concatenating strings which may have the < character (etc), then you should maybe fix that up afterwards, but either way, this is still the best way of concatenating strings in SQL Server 2005.

Vista requiring ctrl-alt-del before login

help_ctrlaltdel So you want to tell Vista to require Ctrl-Alt-Del before you can logon. Just like you had in previous versions of Windows. And it’s easy to do, you just have to find the proper dialog box, like the one below. You tick the checkbox, and everything is done.

Now, this dialog box should be easier to find, and I’ll happily be told how to get to it normally…advanceduseraccounts

But if you search Windows Help for “ctrl-alt-del”, you’ll get a link to a page which takes you to a dialog box that lets you set this option. In the dialog, it’s on the Advanced Tab, at the bottom. If you can’t find it in the help, you can just run NetplWiz.exe – that will open it for you too.

I honestly can’t find any other way of opening this dialog box. But this dialog is definitely the place to set the option. I’ve asked Rocky Heckman (of Microsoft) how to get to this really-useful-dialog, and hopefully he’ll have something for me in the next day or so. Any of you readers know how to do it?

Oh, and the reason why you might want this enabled is that you should make sure that you don’t have something asking for your Windows password unless that thing is Windows. The Ctrl-Alt-Del combo will always force Windows to jump out of whatever it’s running, so that you won’t ever give your password away.

Some new CodeCamp MVPs

It’s great when a new quarter starts, because we get to find out who the new MVPs are.

This time, my good friend Peter Ward has received the gong in the SQL world, and in the Dev world, friends Grant Holliday and Paul Stovell (both from Readify) have been honoured. Great news guys – thoroughly deserved. They all found out on their way back from CodeCampOz. I didn’t go this time, because I had been away in Melbourne the week before. But now there’s talk of having a similar event in SA – probably somewhere just outside Adelaide – so watch this space for more news on that!

Meet the Robinsons in amazing 3D

Being a parent, I went to see the new Digital 3D movie Meet the Robinsons over the weekend. The film itself is a little predictable, and not much to really blog about, but the Digital 3D is really impressive. It uses Disney Digital 3D technology  (a rebranding of Real D) like Chicken Little and Monster House before it. Now I’m wishing I did though, because the 3D technology is really impressive!

In the olden days (read ‘1980s’), 3D meant wearing red and blue glasses, so that the stuff on the screen that was coloured red could only be seen by one eye, and similarly for blue. This meant that each eye would see something different, and hopefully you’d get the 3D effect happening. But of course, the colours were never done very well, and you’d see double images, have a hard time focussing, and generally get a sore head. But nevertheless it was a trend, and comic books, TV shows, and of course movies would all be shown in 3D. Even Hitchcock made a version of “Dial M for Murder” in 3D, which I had always hoped to see at Valhalla, when it wasn’t burning down.

Real D is done differently though and uses a similar technology to the the IMAX 3D experience. IMAX 3D was another thing that I had hoped to experience, but missed out on for one reason or another. The IMAX principle was that you would watch a higher-framerate movie, wearing special glasses which had LCD lenses. The lenses would blank over each eye at just the right time, so that your eyes only saw the frames they were meant to see. No funny colours here, your eye actually sees what the camera sees. Proper 3D.

Real D also works with a higher-framerate, but the LCD bit happens at the projector. It polarises the light in two directions, and then a cheap pair of glasses with polarised lenses will do the trick. The physics behind polarisation is that light which only has waves in a particular direction can be filtered out with a lense which is polarised in the opposite direction… ie, light-waves which only go up and down can be filtered out by a lense which only allows horizontal waves through. This is why polarised sunglasses reduce glare, because glare tends to be polarised light.

The effect is incredible. The whole movie is 3D, and I found myself noticing the distance in almost every scene. Some more than others, like when the hero is going through a tunnel. But of course, this is animation. I have read that U2’s recent tour was filmed in 3D as well, and I want to see how live-action goes in 3D.

So my next thought is… when will this become available on my PC? Surely a good quality monitor could be set up with a high-framerate, and whilst it would be hard to polarise the light coming off a screen, could I get some USB connected LCD glasses and have a 3D computing experience? It could be really good for visualising data.