Category Archives: SQL

Hidden pitfall in SQL Server String Comparison

SELECT CASE WHEN ‘a’ = ‘a ‘ THEN 1 ELSE 0 END => 1
SELECT CASE WHEN ‘a’ = ‘ a’ THEN 1 ELSE 0 END => 0


i.e. SQL server trims any trailing space in the string before comparison, but not any space in front


Reset Identity Seed


from StackOverflow

This is useful for resetting the identity column if you ever make a mistake by inserting duplicated rows and end up deleting one of them. My use-case was that this identity column needs to be in-sync with the other databases and it is used as foreign keys in some other tables.

Alternatively, you can turn it off:

SET IDENTITY_INSERT sometableWithIdentity ON

INSERT sometableWithIdentity (IdentityColumn, col2, col3, ...)
VALUES (AnIdentityValue, col2value, col3value, ...)

SET IDENTITY_INSERT sometableWithIdentity OFF