DataBrighten

    Messy customers sample database for SQL Server

    1,000rows · 1 table · 6 questions with answers

    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.

    textnumberdateyes/no

    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.sql

    SSMS 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

    ColumnSQL Server type
    customer_idNVARCHAR(10)
    first_nameNVARCHAR(20)
    last_nameNVARCHAR(20)
    emailNVARCHAR(40)
    phoneNVARCHAR(20)
    cityNVARCHAR(20)
    stateNVARCHAR(20)
    signup_dateNVARCHAR(20)
    segmentNVARCHAR(20)
    total_ordersNVARCHAR(10)
    total_spentNVARCHAR(20)
    notesNVARCHAR(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_countdistinct_ids
    1000960
    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_distinctcleaned_distinct
    838827
    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

    staten
    New York13
    Texas12
    Florida8
    California7
    Illinois7
    Pennsylvania5
    Georgia4
    Indiana4
    Massachusetts4
    Nevada4
    Ohio4
    Oregon4
    Utah3
    Wisconsin3

    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.