SQL Cheat Sheet: Data Cleaning (SQL Server / T-SQL)

Messy data breaks reports, and fixing it by hand takes hours.

This 2-page cheat sheet gives you ready-to-use T-SQL queries to find duplicates, gaps and junk in your data, then fix it without losing anything.

What’s inside

• Profile a column in one query: how many, how unique, how long
• Count missing values per column, and catch NULLs and blanks in one check
• Find duplicates, see the full duplicate rows, and spot near-duplicates with different capitals or stray spaces
• Delete duplicates safely while keeping the newest row
• Find leading and trailing spaces that a normal comparison misses
• Catch rows that break a rule, like orders shipped before they were ordered
• Spot out-of-range dates and suspicious email addresses
• Standardise messy values into one agreed code
• Why ISNUMERIC lies, and what to use instead
• Back up a table before you fix it, and restore one column if something goes wrong
• Two comparison traps in SQL Server that catch out even experienced developers

Who it’s for

• Analysts and developers cleaning data in SQL Server
• Office workers moving from Excel to SQL
• Anyone preparing for a SQL interview

Format

A 2-page A4 PDF, ready to print or keep open on a second screen.

Every query uses the same example tables, so you can follow along easily.

For personal use only.

From Rosleecia Productions.