I was recently given a du-duping task which was much more difficult than I anticipated and taxed my SQL brain to its limits. I thought of using a CTE to do this but all of the examples I could find for deleting records with a CTE wouldn’t have worked in my situation. Essentially the table [...]

Today a colleague was running into some difficulties matching football team names containing accented characters. For example Olympique Alès and Olympique Ales were not matching when he wanted them to. The issue here is all to do with collations. We can use the Latin1_General_CI_AI collation in our queries to force the comparison to ignore accents [...]

Not the sexiest blog title in the world but I thought I’d knock up a little post on the behaviour of MySQL and SQL Server with integer subtraction. How would you expect a database system to behave with positive and negative data types? Microsoft SQL Server doesn’t really have unsigned data types. All integer types [...]

Colleague: “Can you have foreign keys referencing other databases?” Me: “Erm… I don’t know, but it I’ll find out.” I’ve always thought tables with these relationships should exist in the same database so I’ve never attempted this. But I don’t like not knowing things so I set to find out. The answer, as it often [...]

I’ve always been aware of SQLCMD mode in SQL Server Management Studio but until a few days ago I never considered using it. So what is SQLCMD? SQLCMD is a command line application that comes with Microsoft SQL Server, and exposes the management features of SQL Server. It allows SQL queries to be written and [...]

This error message really irritates me. Msg 8152, Level 16, State 2, Line 1 String or binary data would be truncated. The statement has been terminated. I should probably open a Microsoft Connect item about this but would it really be that hard to tell you the column name? When you’re importing a data from [...]

Recently I needed to setup a SQL Server box so it had access to a mapped drive to support a legacy application. I created the below stored procedure, which utilises the subst command. to get this done. ?View Code TSQLCREATE PROCEDURE usp_mapDDrive AS BEGIN EXEC master.dbo.xp_cmdshell ‘Subst d: c:\’, no_output; END GO I needed a [...]

There’s few SQL techniques you seem to keep in the cupboard gathering dust. I don’t think I’ve ever needed to use RIGHT JOIN outside of the classroom. I can recall using FULL OUTER JOIN, just once, to show an employer how not-in-sync their "integrated system" was. Today I broke my professional Non-Equi JOIN cherry! I [...]

I’ve blogged before about documenting databases. I’m very much a fan of extracting documentation from systems themselves so it’s as up-to-date as it can be. That’s probably why I’m such a big fan of Powershell a tool that excels at this task. This week I was thinking about how to get at the comments often [...]

If you ever encounter this SQL Server error when selecting from a view then somebody has probably dropped columns from the base table. Here’s a quick run through of the problem. ?View Code TSQLCREATE TABLE dbo.Contact ( Id INTEGER IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DOB DATETIME [...]