Messy customers sample database for SQL Server
A ready-to-run SQL Server script with a customer list with duplicates, blanks, mixed dates and text in number columns. Tested on SQL Server 2022 16.0.4325.4. Updated 19 November 2026.
Messy customers database for SQL Server
The customer table with problems added on purpose: repeated customer IDs, names in the wrong case, trailing spaces, blank emails, phone numbers in three formats, state names mixed with codes, dates in three formats, and $ signs or N/A in the spend column.
This page has it as a SQL Server script: one table and 1,000 rows, with column types chosen for SQL Server, primary keys. The script drops the table if it exists, creates it and inserts the rows in one transaction, so you can run it again to reset the data. It is the same fictional data, seed 42, as on the messy customers dataset page, where it is also available as CSV and Excel.
We loaded this exact file into SQL Server 2022 16.0.4325.4 and ran every question below on it; the answers shown are that output. The same questions, written for SQLite, PostgreSQL, MySQL, SQL Server and DuckDB, return the same results on all five.
Load it
sqlcmd
sqlcmd -S localhost -Q "CREATE DATABASE practice"
sqlcmd -S localhost -d practice -i messy-customers-sqlserver.sqlSSMS or Azure Data Studio
Create a database, open the .sql file in a new query window connected to it, and press Execute.
Tables and columns
messy_customers 1,000 rows
| Column | SQL Server type |
|---|---|
| customer_id | NVARCHAR(10) |
| first_name | NVARCHAR(20) |
| last_name | NVARCHAR(20) |
| NVARCHAR(40) | |
| phone | NVARCHAR(20) |
| city | NVARCHAR(20) |
| state | NVARCHAR(20) |
| signup_date | NVARCHAR(20) |
| segment | NVARCHAR(20) |
| total_orders | NVARCHAR(10) |
| total_spent | NVARCHAR(20) |
| notes | NVARCHAR(20) |
Practice questions
answers from SQL Server 2022 16.0.4325.4
How many rows and how many distinct customer_id values are there? (duplicates show up as the difference)
SQL
SELECT COUNT(*) AS row_count, COUNT(DISTINCT customer_id) AS distinct_ids
FROM messy_customers;Result
| row_count | distinct_ids |
|---|---|
| 1000 | 960 |
How many rows have a missing or blank email?
SQL
SELECT COUNT(*) AS blank_emails
FROM messy_customers
WHERE (email IS NULL OR TRIM(email) = '');Result
| blank_emails |
|---|
| 44 |
After lower-casing and trimming, how many distinct emails remain, compared with the raw values?
SQL
SELECT COUNT(DISTINCT email COLLATE Latin1_General_CS_AS) AS raw_distinct, COUNT(DISTINCT LOWER(TRIM(email))) AS cleaned_distinct
FROM messy_customers
WHERE NOT (email IS NULL OR TRIM(email) = '');The usual default collation is case-insensitive, so COLLATE Latin1_General_CS_AS is added to count raw emails case-sensitively.
Result
| raw_distinct | cleaned_distinct |
|---|---|
| 838 | 827 |
Which state values are not a clean two-letter code? List them with counts.
SQL
SELECT state, COUNT(*) AS n
FROM messy_customers
WHERE LEN(state) <> 2
GROUP BY state
ORDER BY n DESC, state;LEN counts characters and ignores trailing spaces.
Result
| state | n |
|---|---|
| New York | 13 |
| Texas | 12 |
| Florida | 8 |
| California | 7 |
| Illinois | 7 |
| Pennsylvania | 5 |
| Georgia | 4 |
| Indiana | 4 |
| Massachusetts | 4 |
| Nevada | 4 |
| Ohio | 4 |
| Oregon | 4 |
| Utah | 3 |
| Wisconsin | 3 |
First 14 of 26 rows.
How many signup dates are not in ISO format (YYYY-MM-DD)?
SQL
SELECT COUNT(*) AS non_iso_dates
FROM messy_customers
WHERE signup_date NOT LIKE '____-__-__';Result
| non_iso_dates |
|---|
| 225 |
How many rows have a blank total_orders value?
SQL
SELECT COUNT(*) AS blank_total_orders
FROM messy_customers
WHERE (total_orders IS NULL OR TRIM(total_orders) = '');Result
| blank_total_orders |
|---|
| 69 |
SQL Server differences used here
- The usual default collation is case-insensitive, so COLLATE Latin1_General_CS_AS is added to count raw emails case-sensitively.
- LEN counts characters and ignores trailing spaces.