Checking Data Fits Before Narrowing a Column Data Type

A smaller data type looks like an easy space saving. Before narrowing a column, check every stored value and decide which information you are prepared to lose.

Check the Values Before Narrowing a Column

Changing a definition takes one line. Establishing that the change preserves your data takes more thought. An integer overflow and a discarded time component are different failures, even when both start with the same ALTER statement.

I check the values before reviewing the proposed replacement type. A successful conversion does not prove the business meaning survived. Converting a timestamp to a date succeeds while removing its time.

Start in a disposable database. The example uses ordinary SQL Server types and built-in checks. TRY_CAST requires SQL Server 2012 or later. Replace the sample table with your own only after understanding each test.

CREATE TABLE dbo.NarrowingDemo
(
    RecordID int NOT NULL,
    ExternalID bigint NULL,
    Description nvarchar(max) NULL,
    RecordedAt datetime2(7) NULL
);
INSERT dbo.NarrowingDemo
VALUES (1, 120, N'Short description', '2026-01-10T09:30:00'),
       (2, 2147483648, REPLICATE(CAST(N'x' AS nvarchar(max)), 201), '2026-01-11T00:00:00'),
       (3, NULL, NULL, NULL);
EXEC sys.sp_spaceused N'dbo.NarrowingDemo';

The sample deliberately includes values that fail your proposed rules. These are input values, not a measured result from a server. Your production distribution supplies the evidence that matters.

An attractive column definition does not negotiate with the existing rows. SQL Server applies conversion and storage rules, not the intention written in a change request. Give those rules an explicit check.

Test the Integer Boundary

An int supports values from negative 2,147,483,648 through positive 2,147,483,647. A bigint supports a wider range. Reducing the range requires checking both ends, including negative identifiers or adjustment values.

SELECT MIN(ExternalID) AS LowestID,
       MAX(ExternalID) AS HighestID,
       COUNT_BIG(*) AS TotalRows
FROM dbo.NarrowingDemo;
SELECT RecordID, ExternalID
FROM dbo.NarrowingDemo
WHERE ExternalID IS NOT NULL
  AND TRY_CAST(ExternalID AS int) IS NULL;

The second query identifies failed conversions without stopping at the first failure. Its null test excludes existing null values. Otherwise, a valid nullable column gets mixed into your exception list.

Do not stop at the current maximum. Ask how the identifier is assigned and how quickly it advances. A value that fits today still needs room for future writes. Check imported identifiers as well as locally assigned ones.

If the column is an identity, review its seed, increment, and current identity value. Check application parameters and related foreign key columns. Narrowing one table while leaving dependent interfaces unchanged creates another kind of mismatch.

Keep the exception query with the change request. Run it again immediately before the change. A check performed last week cannot account for rows inserted since then.

Measure String Storage, Including Spaces

LEN reports characters while ignoring trailing spaces. DATALENGTH reports stored bytes and includes those spaces. For nvarchar(200), the limit is 400 bytes, expressed as 200 UTF-16 code units.

Supplementary characters can use two code units. Depending on the collation, LEN treats a surrogate pair differently from the storage limit. Byte length is therefore essential when checking the target capacity.

SELECT MAX(LEN(Description)) AS LongestVisibleLength,
       MAX(DATALENGTH(Description)) AS LargestByteLength
FROM dbo.NarrowingDemo;
SELECT RecordID, LEN(Description) AS VisibleLength,
       DATALENGTH(Description) AS StoredBytes
FROM dbo.NarrowingDemo
WHERE DATALENGTH(Description) > 400;

Do not use TRY_CAST to nvarchar(200) as a truncation detector. Explicit conversion to a shorter string can return a shortened value successfully. The null test used for integers does not answer this question.

Trailing spaces deserve a business decision. Removing them first changes data. That is appropriate only when the application considers them insignificant. Check exports, comparisons, and fixed-format documents before choosing that cleanup.

An empty exception list establishes fit for the current values. It does not prevent tomorrow's application from sending a longer string. Update input validation alongside the database definition.

Decide Whether Removing Time Is Acceptable

A datetime2 value converts to date without preserving the clock portion. TRY_CAST confirms conversion, but it cannot tell you whether the time was important to an invoice, an audit record, or a booking.

SELECT RecordID, RecordedAt,
       TRY_CAST(RecordedAt AS date) AS ProposedDate
FROM dbo.NarrowingDemo
WHERE RecordedAt IS NOT NULL
  AND RecordedAt <> CONVERT(datetime2(7), CONVERT(date, RecordedAt));

This query exposes rows carrying a non-midnight time. Even midnight timestamps still have a type change that affects application code. Compare the returned values with the business purpose of the column.

Would two events on the same day remain distinguishable after this change? Check unique constraints and ordering requirements. A date alone cannot preserve an event sequence that depended on its timestamp.

I treat discarded information as a separate approval decision. Space savings are easy to explain. Reconstructing an erased time from an old application log is considerably less enjoyable.

Find Dependencies Before Narrowing a Column

Inspect indexes, keys, check constraints, computed columns, and schema-bound views before changing the definition. SQL Server rejects changes that conflict with dependent objects. Script the affected definitions before adjusting them.

SELECT i.name AS IndexName, c.name AS ColumnName,
       ic.key_ordinal, ic.is_included_column
FROM sys.indexes AS i
JOIN sys.index_columns AS ic
  ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN sys.columns AS c
  ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE i.object_id = OBJECT_ID(N'dbo.NarrowingDemo');
SELECT OBJECT_SCHEMA_NAME(d.referencing_id) AS ReferencingSchema,
       OBJECT_NAME(d.referencing_id) AS ReferencingObject,
       d.is_schema_bound_reference
FROM sys.sql_expression_dependencies AS d
WHERE d.referenced_id = OBJECT_ID(N'dbo.NarrowingDemo');

Metadata visibility depends on permissions. Missing rows do not establish that no dependencies exist. Review application code and dynamic SQL separately, because dependency metadata does not describe every caller.

Preserve nullability when writing the replacement definition. Also preserve the intended collation for text. A space-saving exercise should not quietly change whether a column accepts nulls or how comparisons behave.

Plan the Change and Check the Allocation

After the exception list is resolved, the sample alteration looks like this. Run these statements only on a prepared copy where every value fits and the time loss is approved. On the unchanged sample, the first ALTER fails on purpose with an arithmetic overflow error, because row 2 still holds 2147483648.

-- Run only after resolving the sample's exceptions.
ALTER TABLE dbo.NarrowingDemo ALTER COLUMN ExternalID int NULL;
ALTER TABLE dbo.NarrowingDemo ALTER COLUMN Description nvarchar(200) NULL;
ALTER TABLE dbo.NarrowingDemo ALTER COLUMN RecordedAt date NULL;
EXEC sys.sp_spaceused N'dbo.NarrowingDemo';

A large ALTER COLUMN operation can require substantial logging, data movement, and locking. Rehearse with representative data and concurrent activity. Estimate the maintenance window from your own run, not from the short example.

For a busy table, evaluate a new-column approach with controlled backfill and an application cutover. That approach still needs consistency, validation, and dependency work. Additional columns do not make concurrency disappear.

Compare sp_spaceused before and after under comparable conditions. Page allocations do not always fall immediately after a narrower definition. Empty space inside existing pages and other allocations influence what the command reports.

Keep a recovery plan and a usable backup before discarding information. Confirm row totals, constraints, application writes, and representative reads afterward. The change is complete when the data and its consumers still agree.

Check replication, data capture, and any process that copies the column elsewhere. A downstream table can reject the new representation even when the local alteration succeeds.

Keep the exception counts and dependency review together, then repeat both against the final deployment copy. That record explains why the selected type remains appropriate after the immediate space check. Before narrowing a column, agree on which values the new type must preserve. After narrowing a column in the test copy, compare those values and dependent objects with the original contract.

Related reading on this blog: ALTER Column from INT to BIGINT: Error and Solutions and Identify the column(s) responsible for "String or binary data would be truncated.".

A smaller column is not an automatic saving, it is a data contract that needs proof.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.