Database Engine

SQL Server – Dangers of Delete

To minimise the risks that go with deleting data, I’m in the habit of writing out and testing the WHERE  clause in a separate query first. Couple that with an explicit BEGIN TRANSACTION / ROLLBACK / COMMIT  and your data should be pretty safe from unintentional deletions. A piece of buggy T-SQL code that was shared with me from a production Stored Procedure illustrated an…

pgSQL vs T-SQL – Enforce uniqueness on a column but allow multiple nulls

A Unique constraint is used to enforce unique values across one or many columns, often applied against Alternate keys of a table. Sometimes you want uniqueness enforced only where data for a column is present, and not where values are Null, for example, when storing optional Person attributes such as National Insurance numbers or Driving Licence numbers. Section 5.3.3. Unique…

SQL Server – Casting Integers to Asterisk

An odd ‘feature’ of T-SQL that once sent me on a wild goose chase. If you try to cast an integer to a smaller length char/varchar data type, the database engine silently casts the value to an asterisk ‘*’. This is also true for when you rely on an implicit cast such as during an Insert.  

   …

Missing Sql Server Startup parameters

I once came across an issue with a local SQL Server instance that refused to start on my laptop. The Windows Event viewer reported ‘initerrlog: Could not open error log file ”. Operating system error = 3(The system cannot find the path specified.).‘ At the time I searched around on the net but couldn’t find any thing quick so I…