A generous column declaration tells you little about the values actually stored. Profiling a table's data answers that question before tuning, migration, or cleanup starts. Count missing values, inspect distinct values, and compare declared capacity with the content you really store.
Start Profiling a Table's Data With One Column You Understand
A data profile describes the contents of a column, not merely its declared type. COUNT(*) counts all rows. COUNT(column) counts non-NULL values. Their difference gives the missing-value count. COUNT(DISTINCT column) counts distinct non-NULL values under the column's comparison rules.
I begin with one important column and check its business meaning. An empty string is different from NULL. A status code repeated across many rows is not automatically poor data. A supposedly unique identifier with repeated values deserves a different review. The same aggregate numbers mean different things for those two columns.
The demonstration below uses a small temporary table with deliberate test values. It includes NULL, repeated text, and a trailing space. Those inputs make the aggregate behavior visible without claiming anything about a production table. A declared length is a promise about capacity. It has never promised that the data would use it sensibly.
CREATE TABLE #ProfileSample(ID int,Code nvarchar(40));
INSERT #ProfileSample VALUES(1,N'A'),(2,N'A'),(3,N'Longer'),(4,NULL),(5,N'X ');
SELECT COUNT(*) AS TotalRows,COUNT(Code) AS PopulatedRows,
COUNT(*)-COUNT(Code) AS NullRows,COUNT(DISTINCT Code) AS DistinctValues,
MIN(Code) AS MinimumValue,MAX(Code) AS MaximumValue,
MAX(LEN(Code)) AS MaximumCharacters,
MAX(DATALENGTH(Code)) AS MaximumBytes
FROM #ProfileSample;When profiling a table's data, keep missing values and short valid values as separate questions.
Read Ranges and Lengths in Context
MIN and MAX describe the value range according to the data type and collation. For text, they describe comparison order rather than shortest and longest content. MAX(LEN(column)) answers the length question separately. LEN excludes trailing spaces, so add DATALENGTH when stored bytes or trailing padding matter.
For nvarchar, bytes and characters are different units. sys.columns.max_length is expressed in bytes, and a value of minus one identifies a max-length type. Do not compare that metadata directly with a character count and call the difference wasted storage. Variable-length columns store the content they need, with related row overhead.
I use the observed lengths to start a design conversation, not to shrink every declaration immediately. Future valid input, supplementary characters, and application assumptions also matter. A narrow current sample does not prove a narrower definition is safe. Preserve the profile's scope and capture time beside any recommendation that comes from it.
Generate Comparable-Column Checks From Metadata
The next block generates a separate query for every supported ordinary column in an existing table. Replace dbo.ProfileTarget with your target. It covers character, numeric, date, time, and uniqueidentifier columns. Convert bit to int for MIN and MAX. XML, legacy large-object types, and CLR types such as geography need separate, type-specific checks.
STRING_AGG requires SQL Server 2017 or later. The aggregated expression is cast to nvarchar(max), and each identifier is protected by QUOTENAME. The generated statements include the column name as a literal so the result sets remain understandable when several queries execute together.
The block prints the generated SQL through SELECT before executing it with sp_executesql. Review that text first. A metadata-driven query is still a query against real data. Confirm the table name, column scope, and expected scan cost before running the execution line on a large production table.
DECLARE @ObjectID int=OBJECT_ID(N'dbo.ProfileTarget');
DECLARE @TableName nvarchar(517)=QUOTENAME(OBJECT_SCHEMA_NAME(@ObjectID))+N'.'+QUOTENAME(OBJECT_NAME(@ObjectID));
DECLARE @SQL nvarchar(max);
SELECT @SQL=STRING_AGG(CONVERT(nvarchar(max),
N'SELECT N'+QUOTENAME(c.name,'''')+N' AS ColumnName, COUNT_BIG(*) AS TotalRows, COUNT_BIG('
+QUOTENAME(c.name)+N') AS PopulatedRows, COUNT(DISTINCT '+QUOTENAME(c.name)
+N') AS DistinctValues, MIN('+v.Comparable+N') AS MinimumValue, MAX('
+v.Comparable+N') AS MaximumValue, MAX(LEN(CONVERT(nvarchar(max),'+QUOTENAME(c.name)
+N'))) AS MaximumCharacters, CASE WHEN COUNT_BIG('+QUOTENAME(c.name)
+N')=0 THEN 1 ELSE 0 END AS AllNullCandidate FROM '+@TableName+N';'),CHAR(10))
WITHIN GROUP(ORDER BY c.column_id)
FROM sys.columns AS c
CROSS APPLY(SELECT CASE WHEN c.system_type_id=104
THEN N'CONVERT(int,'+QUOTENAME(c.name)+N')' ELSE QUOTENAME(c.name) END AS Comparable) AS v
WHERE c.object_id=@ObjectID
AND c.system_type_id IN(36,40,41,42,43,48,52,56,58,59,60,61,62,104,106,108,122,127,167,175,231,239);
SELECT @SQL AS GeneratedSQL;
IF @SQL IS NOT NULL EXEC sys.sp_executesql @SQL;
Extend Profiling a Table's Data by Column Type
For numeric columns, retain count, distinct count, minimum, and maximum. The generated length describes a text conversion for noncharacter types, so use it for width decisions only on character columns. Add sums or averages when their business meaning and precision are safe. Dates also deserve checks for impossible business dates.
Show the metadata beside the observed results. The next query lists type, nullability, and declared capacity for the target table. That makes always-NULL columns and surprisingly sparse wide declarations easier to investigate. It also exposes computed columns, whose values are derived rather than directly supplied by the application.
AllNullCandidate also flags an empty table, so check TotalRows before calling a column unused. Compare declared character capacity with MaximumCharacters to flag wide candidates for review.
Which columns are truly unused, and which are reserved for valid future input? The profile supplies candidates, while application behavior supplies the answer. Do not remove or narrow a column solely because one observation period did not use it. Check imports, reports, defaults, and historical retention rules before turning an observation into DDL.
SELECT c.name,TYPE_NAME(c.user_type_id) AS DataType,c.max_length,
c.precision,c.scale,c.is_nullable,c.is_computed
FROM sys.columns AS c
WHERE c.object_id=OBJECT_ID(N'dbo.ProfileTarget')
ORDER BY c.column_id;Control Data Profiling Work on a Large Table
COUNT(DISTINCT) can require sorting or hashing, and the generated approach scans the table repeatedly. That is expensive on a large table with many columns. Run it during an approved low-activity window or on a suitable restored copy when a full profile is needed.
Sampling answers a narrower question. TOP without an intentional ordering can examine an unrepresentative slice, while a time-based sample excludes older patterns. Label the sample and retain its selection rule. Do not report a sampled maximum length as a guaranteed limit for the whole table.
A profile also needs a consistent observation point. If rows change between separate column scans, totals can differ. Use an appropriate isolation and reporting strategy for the environment rather than applying NOLOCK as a shortcut. Dirty, missing, or repeated reads defeat the purpose of checking whether the data is trustworthy.
Turn Candidates Into Focused Follow-Up
An always-NULL column deserves a query that examines the loading path. A low distinct count deserves a look at allowed values and business categories. An unexpectedly long string deserves inspection of the actual rows, with sensitive content handled through the normal administrative process.
Save the aggregate results from profiling a table's data, and their scope, before changing the table. Then validate any cleanup or migration against the original profile. Changes in NULL counts or distinct values should have an explanation tied to the intended transformation. A successful load alone does not prove the values retained their meaning.
Keep profiling a table's data targeted enough to be repeatable. Start with important columns, expand by type, and pay for full scans only when the decision needs them. The useful output is a set of concrete questions about the stored values, followed by checks that answer those questions.
Related reading on this blog: Where Should a Data Quality Check Live? Gates, Controls, and Quarantine and Stop Blaming the User: Let Constraints Catch Bad Data.
A column definition is not a data profile, it is a container for values you still need to inspect.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
