A migration can finish with every row present and several values subtly changed. Data type mapping decides what precision, length, and date detail survive. Verify the target against an approved baseline before calling the move complete.
Capture the Source Before Data Type Mapping
Capture source column names, types, lengths, precision, scale, and nullability before conversion. Keep a source-shaped validation extract for important values. A target table alone can't reveal what was cut away earlier.
Row counts answer whether rows arrived. They don't answer whether each value preserved its intended meaning. Validation of data type mapping needs both the structural contract and a comparable data baseline.
I review the mapping before investigating total differences. A rounded decimal and a shortened string can both be valid target values under the wrong definition. Don't rely on a loader's success status to discover that difference.
Use the approved mapping as the authority. The sample below deliberately chooses narrower target types to make the inspection concrete. Its values are invented input, not an observed migration result.
Compare Exact Counts and Checksum Screens
COUNT_BIG provides an exact count for the data read. Compare source and target under equivalent filters and a stable capture boundary. Concurrent changes can otherwise create a false mismatch.
A checksum aggregate gives a quick screening comparison, but collisions are possible. Matching counts and checksums don't prove equality. Keep a keyed value comparison for the fields whose correctness matters to the application.
Use the same column order and comparable typed expressions for checksum screens. Type conversions themselves can affect the checksum, even when displayed values look alike. Don't use BINARY_CHECKSUM over arbitrary unsupported large-value columns and assume complete coverage.
The sample keeps explicit ordinary fields in its screen. Report that scope beside the result so the checksum doesn't become an accidental certificate of the whole dataset.
CREATE TABLE dbo.MappingSourceDemo
(Id int PRIMARY KEY,Label varchar(20),Amount decimal(19,8),EventAt datetime2(7));
CREATE TABLE dbo.MappingTargetDemo
(Id int PRIMARY KEY,Label varchar(10),Amount decimal(19,4),EventAt datetime);
INSERT dbo.MappingSourceDemo VALUES (1,'Long sample label',1.23456789,'2024-01-01T12:00:00.1234567');
INSERT dbo.MappingTargetDemo
SELECT Id,CONVERT(varchar(10),Label),Amount,EventAt FROM dbo.MappingSourceDemo;
SELECT N'Source' AS DataSide,COUNT_BIG(*) AS TotalRows,
CHECKSUM_AGG(BINARY_CHECKSUM(Id,Label,Amount,EventAt)) AS ScreeningChecksum
FROM dbo.MappingSourceDemo
UNION ALL
SELECT N'Target',COUNT_BIG(*),CHECKSUM_AGG(BINARY_CHECKSUM(Id,Label,Amount,EventAt))
FROM dbo.MappingTargetDemo;In my run, both sides counted one row, but the screening checksums differed. The target had kept a shortened label, a rounded amount, and a rounded time.
Inspect Data Type Mapping for Length and Precision
The sys.columns view exposes max_length in bytes, not always characters. Unicode columns need that distinction. Precision and scale describe numeric capacity.
Join sys.types to identify the declared type and keep schema and table names with each column. A generic list without object identity is hard to reconcile against the baseline. The catalog tells you what the target permits, not what the original source required.
The query scopes the sample objects so you can compare their declarations side by side. For a real migration, load the approved source metadata into a validation table and join by the agreed object mapping. Names themselves can change during migration.
Preserve that mapping explicitly rather than assuming identical names prove corresponding columns. A structural mismatch needs a business decision before a data correction proceeds.
SELECT OBJECT_NAME(c.object_id) AS TableName,c.name AS ColumnName,
t.name AS TypeName,c.max_length,c.precision,c.scale,c.is_nullable
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id IN (OBJECT_ID(N'dbo.MappingSourceDemo'),OBJECT_ID(N'dbo.MappingTargetDemo'))
ORDER BY c.name,TableName;The output put varchar(20) beside varchar(10) and decimal scale 8 beside scale 4. It also showed datetime2 with scale 7 beside datetime with scale 3.
Check Dates Beyond Their Display Format
The datetime2 type can preserve finer fractional precision than datetime. A migration to datetime can round away that detail. A result grid showing only seconds hides the difference.
Compare values after converting the target into the source's wider comparison type. That exposes the representation the target actually retained. Converting the source down first would erase the very difference you are trying to find.
I test dates around the required range boundaries and fractional values. Include time-zone semantics as a separate rule. Converting a value to a valid datetime doesn't establish that a UTC instant or local business time retained its meaning.
The migration contract needs both type capacity and interpretation. A timestamp without that context can survive every character and still represent the wrong moment.
Detect Truncation and Rounding by Key
Join source and target on a stable identity and compare at the source's required fidelity. A FULL JOIN also reveals missing rows on either side. For text, use a comparison rule that doesn't hide significant differences through insensitive collation or trailing-space semantics.
DATALENGTH helps inspect byte preservation, and binary comparison can be useful for a declared text contract. Keep encoding changes in that interpretation.
The query below tests the declared sample fields directly. It widens the target decimal and timestamp for comparison instead of narrowing the source. In a real audit, include NULL differences explicitly with a null-safe comparison or paired IS NULL checks.
SQL Server 2022's IS DISTINCT FROM supplies that definite comparison. Earlier engines need the longer equivalent. A concise equality predicate alone can miss missing-value changes.
SELECT COALESCE(s.Id,t.Id) AS Id,s.Label AS SourceLabel,t.Label AS TargetLabel,
s.Amount AS SourceAmount,t.Amount AS TargetAmount,
s.EventAt AS SourceTime,t.EventAt AS TargetTime
FROM dbo.MappingSourceDemo AS s
FULL JOIN dbo.MappingTargetDemo AS t ON t.Id = s.Id
WHERE s.Id IS NULL OR t.Id IS NULL
OR s.Label IS DISTINCT FROM t.Label
OR s.Amount IS DISTINCT FROM CONVERT(decimal(19,8),t.Amount)
OR s.EventAt IS DISTINCT FROM CONVERT(datetime2(7),t.EventAt);It returned the one sample row with all three losses side by side. The label became Long sampl, 1.23456789 became 1.2346, and the time lost everything after .123 seconds.
Build a Data Type Mapping Checklist Query
Compare max_length, precision, and scale against the source metadata before checking values at scale. Some changes are deliberate and approved. Others reveal an accidental default type. Keep exceptions as explicit mapping decisions.
A blanket rule that every smaller declaration is wrong ignores justified transformations. A blanket acceptance of successful conversions ignores loss. The checklist should point to the difference and its approved rule.
Which column needs exact preservation rather than a documented transformation? Prioritize that column in the keyed audit. For dates and decimals, select edge values that expose the target's limits.
For strings, include the longest allowed input and significant trailing characters. Keep the fixture small enough to inspect. Include changes that row counts cannot reveal.
SELECT s.name AS ColumnName,s.max_length AS SourceBytes,t.max_length AS TargetBytes,
s.precision AS SourcePrecision,t.precision AS TargetPrecision,
s.scale AS SourceScale,t.scale AS TargetScale
FROM sys.columns AS s
JOIN sys.columns AS t ON t.name = s.name
WHERE s.object_id = OBJECT_ID(N'dbo.MappingSourceDemo')
AND t.object_id = OBJECT_ID(N'dbo.MappingTargetDemo')
AND (t.max_length < s.max_length OR t.precision < s.precision OR t.scale < s.scale);On the sample tables, the checklist flagged Label, Amount, and EventAt, and left Id alone.
Close With Exceptions You Can Explain
Retain counts, screening results, metadata differences, and keyed exceptions together. Record the source capture boundary and any approved transformation. Resolve unexpected changes before switching application ownership to the target.
A rerun needs the same mapping authority and comparison rules. Otherwise, a changed validation query can make the report green without preserving another byte of source information.
Use data type mapping as a correctness contract throughout migration. Check structure before loading and compare values afterward. Treat a checksum match as a screen, then verify critical fields by key.
The loader can put every box on the truck. Your audit still needs to check whether the contents were shortened to make the boxes fit.
Related reading on this blog: Identify the column(s) responsible for "String or binary data would be truncated." and Datatype Decimal Explained: Datatype Numeric.
A completed load is not preserved data, it is rows that still need a mapping audit.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
