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…
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…
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.
SELECT CAST(1500 AS VARCHAR(2)) AS Varchar2, CAST(1500 AS CHAR(3)) AS Char3;