Surprising SQL String comparison with untrimmed string

I’m a bit big-headed about my SQL abilities generally, and must admit that when it came to doing straight string equality comparisons in T-SQL I thought that LIKE and = were identical (ok, I should have really read BOL). Thus which of the following would you expect to return a row and which wouldn’t?

select 'Oh yeah' where 'a' = 'a  '           -- Statement 1

select 'Oh yeah' where 'a' = RTRIM('a  ')    -- Statement 2

select 'Oh yeah' where 'a' LIKE 'a  '        -- Statement 3

select 'Oh yeah' where 'a' LIKE RTRIM('a  ') -- Statement 4

-- 

select 'Oh yeah' where 'a' = 'a'             -- Statement 5

select 'Oh yeah' where 'a' LIKE 'a'          -- Statement 6

Give it a try, I’m not going to spoil it for you…I’ve included statements 5 & 6 so that you can look at the execution plan and relative costs, I never thought that LIKE could be less expensive than =. Learn some new SQL every day…

Taking Screenshots in Windows Server 2008 / OneNote 2007

I was reading about the ‘Snipping Tool’ which is available as part of the Tablet PC tools in Vista.  However there doesn’t appear to be a way to get this on Windows Server 2008.

Happily, and rather surprisingly there actuall IS a use for OneNote which is part of Office 2007.  Simple do WindowsKey+S and you can cut-out a section of the screen which will promptly go in to OneNote from where you can then copy+paste it elsewhere.  Admittedly not as slick as the Vista tool but useful none the less.

Scripting SQL Server diagrams to files for source control

We have recently started storing our SQL Server 2005 database schema and test data in a SubVersion repository but have an issue that diagrams are not supported by any of the commercial products out there that we’ve found.

I came across some code on CodeProject by Craig Dunn at http://www.codeproject.com/KB/database/ScriptDiagram2005.aspx. Which in turn was based on some SQL Server 2000 code by Clay Beatty.

However I found some issues with this, the first was that the output SQL script was piped to the SQL messages (e.g. PRINT rather than SELECT) which I couldn’t work out how to get at using .NET.  If anyone knows how to get at the messages I would be most interested!  The second issue was that it only worked for a single diagram per-call.

Thus I’ve tweaked it to output a two column table with the first column storing the diagram name and the second column the SQL script to generate the diagram.  The Stored Procedure can take a @name parameter if you wish to script a single diagram in which case you’ll only get one row in the resultset.

Included is a PowerShell script which then puts each row in to its own file which should be somewhat suitable for inclusion in Source Control.  I would like to say this is my first ever PowerShell script and all of this was done in a hurry.  Edit the PowerShell script to specify your database connection string and the path you want the SQL scripts placed in.  To run the PowerShell script you must allow unsigned code (http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx) e.g.

 Set-ExecutionPolicy RemoteSigned

Code is at: http://www.provenworks.com/Blogs/Joel/ScriptDBDiagrams.zip 

If you find it useful please comment below!