Visual Studio 2010 beta 2 side-effect - SQL2008 autoupgrade

I went wild the other day and installed VS2010 Beta 2 on my development machine.

Without asking it upgraded my \SQLEXPRESS instance to MS SQL2008 fro MSSQL2005.

I then had to jump through flaming hoops to install the update MSSQL Management studio express, installing 2 pre-requisites, Windows Installer 4.5 and Windows Powershell 1.0. (I'm on XP , I hope these are on Vista or Win 7) and then when I eventually could run the installer I perhaps the most confusing installation screen in the world, ever.

I think I went for a full installation on this page and it just installed me Studio, I can't be sure as I went round the houses several times and now I can't even find out how to uninstall the little s***t to test.

 

R2 Management Objects?

Very naughty VS2010.

It also prompted me to restart mid installation with this gem of a dialogue box.

SQL union Gotcha! Its a feature.

I spoke last time about the strange behaviour if SQL where if you run

SELECT 'X' AS line
UNION
SELECT 'X ' AS line

you will only get 1 value returned.

Well this is not a bug apparently it's a feature of the ANSI SQL-92 standard.

From section 8.2

 If the length in characters of X is not equal to the length
in characters of Y, then the shorter string is effectively
replaced, for the purposes of comparison, with a copy of
itself that has been extended to the length of the longer
string by concatenation on the right of one or more pad char-
acters, where the pad character is chosen based on CS. If
CS has the NO PAD attribute, then the pad character is an
implementation-dependent character different from any char-
acter in the character set of X and Y that collates less
than any string under CS. Otherwise, the pad character is a
.

Blimey so SQL pads the shorter string with spaces!

Thanks to StackOverflow for that one.

SQL union Gotcha!

This one got me the other day. What would you expect the following to return?

SELECT 'X' AS line
UNION
SELECT 'X ' AS line


Notice the space in the second SELECT.
Well apparently SQL 2000 and 2005 both return 1 result. Even though its a UNION (and not a UNION ALL).

There is nothing I can see in Books on line about this. Why does it happen? I'm guessing it's a bug.

EDIT it's NOT a bug. See my follow up to this post for the reason why.