Sql Server: datetime vs datetime2 recap

The MSDN documentation for datetime recommends using datetime2. datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage. Furthermore, datetime2 has a date range of “0001 / 01 / 01” through “9999 / 12 / 31” while the datetime type […]

Sql Server: Delete duplicate rows with CTE

Here’s a code snippet to remove duplicate rows in a table: WITH CTE (Column1, Column2, DuplicateCount) AS ( SELECT Column1,Column2, ROW_NUMBER() OVER(PARTITION BY Column1,Column2 ORDER BY Column1) AS DuplicateCount FROM TheTable ) DELETE FROM CTE WHERE DuplicateCount > 1 GO More on Common Table Expressions(CTE) can be found here: http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Sql Server: Get all tables containing column with a specified name

A little helper query I came accross, very useful if you want to find all the tables that contain a specific column: SELECT c.name AS ColName, t.name AS TableName FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%ColumnName%’   What it basically does is that it will do a join on […]