Data Types That Waste Space

A table grows faster than its row count suggests. Data types that waste space can increase I/O, memory grants, and index size long before anyone checks the schema.

Find Data Types That Waste Space Before Changing Them

Start with the actual table definition and data profile. A bigint occupies more fixed space than int, but int is only safe when the full future value range fits. A type change made for current rows can become a production failure after growth. The purpose is to fix data types that waste space without narrowing the business contract.

I review keys, foreign keys, indexes, and application parameter types together. A wide key appears in every child table and related index. That multiplies its cost. But changing a primary key type can be a large migration, so quantify the benefit before scheduling it.

Ask which columns dominate row width or index pages. Use catalog views and sample data lengths, then compare with the workload. A long text column that is rarely selected has a different cost from one included in every report query.

SELECT c.name, t.name AS data_type, c.max_length,
       c.precision, c.scale
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID(N'dbo.OrderLine')
ORDER BY c.column_id;

Use int When the Range Fits

int stores a smaller fixed value than bigint. A surrogate key can be a good candidate for int if the lifetime row count and import rules fit its range. Do not estimate lifetime from a quiet month alone. Include retained history, bulk loads, and future tenants.

A bigint key flows into nonclustered indexes when it is the clustering key, and into foreign keys on child tables. That is where a small per-row difference can become material. Use sys.dm_db_partition_stats and index size views to see the actual footprint on your server.

I keep bigint when the range uncertainty is real. Saving bytes is not worth an identity exhaustion emergency. Record the range assumption next to the type decision so a later DBA knows when to revisit it.

SELECT MIN(OrderLineId) AS MinimumId,
       MAX(OrderLineId) AS MaximumId,
       COUNT_BIG(*) AS RowTotal
FROM dbo.OrderLine;

Stop Declaring Every String as max

nvarchar(max) is useful for genuinely long text. It is a poor default for names, codes, and short descriptions. The declared max length affects indexing options, row handling, and query memory estimates. Pick a length from the source contract and observed valid data, with room for legitimate growth.

Do not choose nvarchar(50) solely because the current maximum is 49. Ask the business what the field represents and what the source permits. A product code and a free-form note deserve different bounds. Keep Unicode when needed rather than switching to varchar to save space without checking characters.

I use DATALENGTH to inspect stored bytes, not LEN when trailing spaces matter. Then I look for outliers before proposing a narrower type. A single very long valid value can determine whether a separate note column is appropriate.

SELECT MAX(DATALENGTH(CustomerName)) AS MaxBytes,
       AVG(CONVERT(decimal(19,2), DATALENGTH(CustomerName))) AS AvgBytes
FROM dbo.Customer;

Pick datetime2 Precision Deliberately

datetime2 supports fractional second precision from zero through seven digits. Higher precision can require more storage. Choose precision based on the source and business need. A daily event date does not need fractional seconds. A diagnostic event stream needs more.

Precision is not the same as accuracy. A source clock that records whole seconds does not become more accurate when stored as datetime2(7). Extra digits can create the appearance of detail that never existed. A datetime2(3) value can be a good fit for millisecond input, but confirm the source contract.

I compare the stored values and the queries that use them. If reports group by day, an indexed date projection or explicit date key can matter more than shaving a byte from the timestamp. Optimize the actual access pattern, not only the column declaration.

Measure How Data Types Waste Space in Storage and Queries

Use sys.dm_db_partition_stats to inspect used pages for the table and its indexes. Save a baseline, change a representative copy, and compare page count, row size, index depth, and query reads. Compression can change the result, so measure under the same settings as production.

A narrower column can reduce memory grants and I/O, but it can also trigger data conversion in joins if the application still sends a wider type. Inspect actual plans and parameter declarations. The end-to-end type contract includes client code and related tables.

I avoid claiming a specific savings figure without running the test on the target instance. A byte saved in a key used everywhere can matter. A byte saved in a tiny lookup table can be irrelevant. Let the query and storage evidence decide the migration priority.

SELECT i.name, ps.index_id,
       SUM(ps.used_page_count) * 8.0 / 1024 AS used_mb
FROM sys.dm_db_partition_stats AS ps
JOIN sys.indexes AS i
  ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE ps.object_id = OBJECT_ID(N'dbo.OrderLine')
GROUP BY i.name, ps.index_id
ORDER BY used_mb DESC;

Plan Fixes to Data Types That Waste Space as Migrations

Changing a column type can rebuild indexes, block writers, and require updates to foreign keys or dependent objects. Rehearse on a copy with representative data. Check for values that do not fit, then plan the schema change and rollback. Small logical changes can be large physical operations.

For a key type, the migration can touch many tables. For an nvarchar(max) column, check indexes, computed columns, and application parameter lengths. For datetime2, verify that rounding or truncation does not change equality behavior in queries. A change is safe only when the consumers agree.

I inspect writes after the migration. An application sending values outside the new range or length should fail clearly in testing, not appear later as silent truncation. The type should enforce a documented contract.

Spend Space Where It Serves a Purpose

Space is not the only design goal. Unicode support, future key range, and precise event ordering can justify a wider type. Data types that waste space come from using the widest option without a reason. Record why a type is chosen and when the assumption should be reviewed.

Look at the full row and index shape. A table with several large included columns can be costly even if its base row is modest. A narrow clustered key can help many indexes. A selective reporting index can still be worth its storage if it prevents repeated scans.

The best type is the smallest one that reliably represents the domain and fits the workload. Measure candidates on your server, test application values, and change the schema deliberately. The result should save work without shrinking the meaning of the data.

Which date values must this column represent, and what precision do the source systems actually supply? That question prevents a default type choice from spreading through every index and archive. A wider type can increase memory grants and page reads when it appears in many rows. Check the real range and scale before changing it. A type migration also needs a plan for conversion failures and dependent code.

Related reading on this blog: Varchar vs Nvarchar: Storing Non-English Characters: SQL in Sixty Seconds #126 and Measuring the Length of VARCHAR and NVARCHAR Columns with COL_LENGTH.

A data type is not a storage guess, it is a promise about valid values and their cost.

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