The ALTER TABLE has been running for six hours. It is 2am, the log file has grown to 400GB, the Always On secondary is 90 minutes behind, and you are narrowing one column on a table with 900 million rows because somebody typed nvarchar(4000) in 2019 for a field that has never held a character outside ASCII.
Nothing else in a schema is this expensive to undo. You add an index at 3pm and drop it at 4pm. A SQL Server data type is the one design decision with no cheap way back: changing a column type rewrites every row, invalidates every plan that touches the table, breaks every foreign key pointing at it, and holds a schema modification lock throughout. The decision costs nothing the day you make it and costs a weekend two years later.
What a wrong column costs, with numbers
Take dbo.OrderHeader at 200 million rows, in the version a developer wrote in a hurry next to the version a DBA would have written:
| Column | Declared | Bytes | Should be | Bytes | Saved |
|---|---|---|---|---|---|
OrderID (clustered PK) |
uniqueidentifier |
16 | bigint |
8 | 8 |
CustomerID |
uniqueidentifier |
16 | int |
4 | 12 |
CustomerEmail (avg 28 chars) |
nvarchar(200) |
58 | varchar(320) |
30 | 28 |
OrderStatus |
nchar(20) |
40 | tinyint FK to lookup |
1 | 39 |
CurrencyCode |
nchar(3) |
6 | char(3) |
3 | 3 |
OrderDate |
datetime |
8 | datetime2(0) |
6 | 2 |
Amount |
money |
8 | decimal(11,4) |
9 | -1 |
| 152 | 61 | 91 |
decimal(11,4) caps at 9,999,999.9999, so check your own ceiling before you copy it. decimal(9,4) costs 5 bytes and stops at 99,999.9999.
Add 100 bytes for the record header, null bitmap, offset array and the columns both versions share. The wide row is 252 bytes, the narrow row 161.
Microsoft's sizing formula is Rows_Per_Page = 8096 / (Row_Size + 2), rounded down. The 8,096 is the free bytes on a page after the header, and the 2 is the row's entry in the page slot array. Note that 8,060 is the maximum size of a single row, not the space a page gives you. That puts 31 wide rows on a page and 49 narrow ones. At 200 million rows: 6,451,613 pages (49.2GB) against 4,081,633 (31.1GB). Multiply that 18.1GB gap by everywhere a page lives.
Buffer pool. A server with 48GB of buffer pool holds 6,291,456 pages. The narrow clustered index fits inside it with 2.2 million pages to spare. The wide one does not fit. The boundary between "this table is cached" and "this table reads from disk on every report" fell between two column declarations.
I/O. Every scan, wide range seek, CHECKDB and FULLSCAN statistics update reads 58% more pages against the wide table, forever.
Backups. Five fulls in retention is 90.4GB of extra backup storage before compression, doubled if you ship a copy offsite.
Nonclustered indexes. Microsoft's CREATE INDEX page: "Nonclustered indexes always implicitly contain the clustered index columns if a clustered index is defined on the table." Six of them, with the clustered key dropping from 16 bytes to 8, gives back another 8.9GB at the leaf level alone. Kimberly Tripp measured the same effect on one million rows with six nonclustered indexes: 106MB clustered on a GUID, 25MB clustered on an int. Her comment on that figure was that it is "JUST overhead."
Find your own version:
SELECT
s.name AS SchemaName,
t.name AS TableName,
c.name AS ColumnName,
ty.name AS DataType,
c.max_length AS DeclaredBytes,
pc.rows AS RowCount_,
CAST(pc.rows * c.max_length / 1048576.0 AS decimal(18,1)) AS WorstCaseMB
FROM sys.columns AS c
JOIN sys.tables AS t ON t.object_id = c.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id
CROSS APPLY (
SELECT SUM(p.rows)
FROM sys.partitions AS p
WHERE p.object_id = t.object_id
AND p.index_id IN (0, 1)
) AS pc(rows)
WHERE ty.name IN ('nchar', 'nvarchar', 'char', 'varchar')
AND c.max_length > 0
ORDER BY pc.rows * c.max_length DESC;
For nvarchar, max_length is already doubled: nvarchar(200) reports 400. The max_length > 0 filter drops (max) columns, which report -1. The CROSS APPLY sums partitions so a partitioned table reports once rather than once per partition.
NVARCHAR vs VARCHAR
Storage tracks what you stored, not what you declared: an nvarchar value costs two bytes per stored character plus a 2-byte offset entry, a varchar value one byte per stored character plus the same 2. So nvarchar doubles the bill on email addresses, account numbers, SKUs and postcodes, and buys nothing. Prove the column does not need Unicode before you touch it:
SELECT
COUNT(*) AS Rows_,
MAX(DATALENGTH(CustomerEmail)) AS MaxBytes,
AVG(DATALENGTH(CustomerEmail) * 1.0) AS AvgBytes,
SUM(CASE WHEN CAST(CustomerEmail AS varchar(400)) COLLATE Latin1_General_BIN2
<> CustomerEmail COLLATE Latin1_General_BIN2
THEN 1 ELSE 0 END) AS RowsLosingCharacters
FROM dbo.OrderHeader;
Any row counted in RowsLosingCharacters holds a character the code page cannot represent. Inspect those rows before deciding. DATALENGTH returns bytes, so halve it for character counts on nvarchar. The COLLATE sits outside the CAST, so this tests against the column's own code page. To test a different target, move it inside: CAST(CustomerEmail COLLATE <target> AS varchar(400)).
The implicit conversion that kills the seek
This costs more than the storage. Precedence is fixed and documented: when an operator combines two types, "the data type with the lower precedence is first converted to the data type with the higher precedence." nvarchar sits at position 26 in that list and varchar at 28. If you are reading docs that predate the json type, you will see 25 and 27, because adding json shifted every ordinal below it by one. The gap between them has never moved: nvarchar wins.
So a varchar(50) column compared against an nvarchar parameter converts the column, not the parameter. CONVERT_IMPLICIT wraps the column, the predicate stops being SARGable, and the seek becomes a scan. .NET clients send string parameters as nvarchar unless you set SqlParameter.SqlDbType to VarChar, which is the common shape of this bug.
Find every cached plan where it is already happening:
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP (50)
DB_NAME(qp.dbid) AS DatabaseName,
qs.execution_count,
qs.total_logical_reads,
w.value('@ConvertIssue', 'varchar(200)') AS ConvertIssue,
w.value('@Expression', 'varchar(4000)') AS ConvertExpression,
st.text AS BatchText
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY qp.query_plan.nodes('//PlanAffectingConvert') AS n(w)
WHERE w.value('@ConvertIssue', 'varchar(200)') = 'Seek Plan'
ORDER BY qs.total_logical_reads DESC;
The PlanAffectingConvert element arrived in the showplan schema in SQL Server 2012. Seek Plan means the conversion cost you the seek. Cardinality Estimate means it cost you the row estimate, which is a slower kind of damage. Run that query against a production plan cache before you go looking for a schema to fix, because it ranks the damage by logical reads and names two or three columns you were not thinking about: finding the implicit conversions already in your plans.
UTF-8 collations change the arithmetic
SQL Server 2019 introduced UTF-8 collations, identified by a _UTF8 suffix, for char and varchar. Microsoft is direct about the scope: "The nchar and nvarchar data types allow UCS-2 or UTF-16 encoding only, and they remain unchanged." UTF-8 is a varchar feature, and whether it saves anything depends on what the column holds:
| Unicode range | UTF-8 bytes | UTF-16 bytes |
|---|---|---|
| 0 to 127 (ASCII) | 1 | 2 |
| 128 to 2,047 | 2 | 2 |
| 2,048 to 65,535 | 3 | 2 |
| Supplementary | 4 | 4 |
For pure ASCII, moving nchar(10) to char(10) under a UTF-8 collation halves the storage. Add accented characters and char(10) still means ten bytes, so a ten-character value carrying one accent no longer fits. For Korean, Chinese and Japanese, UTF-8 costs 3 bytes per character where UTF-16 costs 2, and you go backwards.
The conversion trap: n in varchar(n) counts bytes, not characters. Microsoft's own example is an nvarchar(100) column holding 180 bytes of Japanese text, which needs varchar(270) after conversion or it truncates. Measure with DATALENGTH before you size the target. UTF-8 applies to Windows collations that already support supplementary characters, plus BIN2, and not to the SQL_* or older BIN collations.
Fixed vs variable length, and what NULL costs
char(n) occupies n bytes in every row. varchar(n) occupies the stored bytes plus a 2-byte entry in the variable-length offset array. Variable length wins as soon as the average length drops below n minus 2.
The part people miss is NULL. Paul Randal's breakdown of the record structure covers it: fixed-length columns store their full width whether or not they hold a value, and the null bitmap records nothing but whether the value is NULL. A nullable char(100) that is NULL in 95% of rows costs 100 bytes 95% of the time. As varchar(100) it costs 2, or nothing at all when no later variable-length column in the row holds a value. Nullable plus fixed width is the worst pairing in a schema. You pay full price for absence.
char still earns its place twice. Fixed-width codes where every value is the same length: ISO country and currency codes, US state codes. And columns updated in place, because growing a varchar value past what fits on its page forces a page split in a clustered index or a forwarded record in a heap, where a char update writes over the existing bytes. One behavior to know: under ANSI_PADDING ON, the default, char pads with trailing spaces and varchar does not, and most collations ignore trailing spaces in comparisons, so LEN and = hide a difference DATALENGTH shows.
The (MAX) types
With sp_tableoption 'large value types out of row' at its default of 0, a varchar(max), nvarchar(max), varbinary(max) or xml value lives in the row when it fits inside 8,000 bytes and the record has room for it. Both conditions matter: an 8,000-byte value has nowhere to sit in an 8,060-byte row that already holds other columns. When it does not, the row keeps a 16-byte pointer to a tree of LOB pages and the 8,060-byte row limit stops applying to that column. Flip the option and everything moves out:
EXEC sys.sp_tableoption 'dbo.OrderHeader', 'large value types out of row', 1;
Read the caveat first: "existing varchar(max), nvarchar(max), varbinary(max), and xml values aren't immediately converted. The storage of the strings is changed as they are updated later." The option governs new and updated values, so to move old rows you have to touch them. Turn it on when the table gets scanned often and the LOB column gets read seldom: every byte of in-row LOB data is a byte of page the scan reads whether the query selected that column or not.
Three more constraints:
No index keys. From the documentation: "Columns that are of the large object (LOB) data types ntext, text, varchar(max), nvarchar(max), varbinary(max), xml, or image can't be specified as key columns for an index." They are legal as INCLUDE columns. A predicate against a (max) column is a scan and a residual filter, every time.
24 bytes during sorts. Microsoft: "Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation, which counts against the 8,060 byte row limit during a sort operation."
Inflated memory grants. SQL Server sizes a grant from the declared width, assuming every row holds half the declared maximum. Aaron Bertrand tested four tables holding identical email data, average 35 characters, on the same 89 pages of disk. The grants scaled with the declaration, and varchar(max) drew one based on 4KB per row. His conclusion where the real bound is known: "there is nothing to gain by over-sizing" and "there is plenty to potentially lose." Oversized grants push other queries into RESOURCE_SEMAPHORE waits and spill sorts to tempdb.
Declare the real maximum. An email address fits varchar(320), a 64-octet local part plus @ plus a 255-octet domain, which is the bound Bertrand sized his test against. A UK postcode is 8. Neither is nvarchar(max).
Numeric data types: INT, DECIMAL, FLOAT and MONEY
INT against BIGINT: four bytes and what they buy
int is 4 bytes and stops at 2,147,483,647. bigint is 8 and stops at 9,223,372,036,854,775,807. An IDENTITY(1,1) climbs from 1, so it uses the positive half only: the practical int ceiling on a surrogate key is 2.1 billion values, not 4.3.
Price the upgrade on the 200-million-row table from the top of this article. Four extra bytes on the key costs 763MB in the clustered index, plus 4 bytes on every row of every nonclustered index that carries it, which across six of them is another 4.5GB. Set that against what bigint buys: at one million inserts per second it lasts about 292,000 years.
For a table that will take sustained writes, bigint is the cheapest insurance in this article, and the cost is knowable before you create the table. Working out how long an int has left on a table that already exists, and widening it once the counter is already climbing, is a different job: how fast an int identity runs out and what to do about it.
DECIMAL and its storage cliffs
Storage moves in steps:
| Precision | Bytes |
|---|---|
| 1 to 9 | 5 |
| 10 to 19 | 9 |
| 20 to 28 | 13 |
| 29 to 38 | 17 |
decimal(9,4) costs 5 bytes and holds up to 99,999.9999. decimal(10,4) costs 9: same four decimal places, 80% more storage, one extra digit left of the point. Precision 19 to 20 is the same cliff again.
Bare decimal means decimal(18,0): 9 bytes and zero decimal places. It rounds every price to a whole number and does not warn you. Microsoft's own example is CAST(10.6496 AS NUMERIC), which returns 11. Numeric to numeric rounds; only numeric to int and float to int truncate.
FLOAT, REAL, and the comparison that returns nothing
float(1..24) and real are 4 bytes with about 7 digits of precision. float(25..53) is 8 bytes with about 15. Both follow IEEE 754 and store an approximation. Microsoft is unambiguous on two points. On use: "don't use these data types when exact numeric behavior is required. Examples that require precise numeric values are financial or business data, operations involving rounding, or equality checks." On queries: "Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators."
The second line is the one that bites. WHERE Amount = 19.99 against a float column returns zero rows on data that looks like it matches, because 19.99 has no exact binary representation and the stored value is not bit-identical to the literal the optimizer built.
MONEY rounds where you cannot see it
money is 8 bytes and smallmoney is 4, which is why people reach for them. Both are "accurate to a ten-thousandth of the monetary units that they represent," and a money divided by anything comes back a money, cut to that fourth decimal place before the next operator sees it. Run this on your own instance:
SELECT
money_third = CONVERT(money, 10) / 3,
money_result = CONVERT(money, 10) / 3 * 3,
decimal_third = CONVERT(decimal(19,10), 10) / 3,
decimal_result = CONVERT(decimal(19,10), 10) / 3 * 3;
The money columns come back 3.3333 and then 9.9999, which follows from the documented four-decimal accuracy and matches the output published at sqlserverscience.com. The decimal columns do not behave that way, because division widens the scale instead of holding it. Microsoft's precision and scale rules give e1 / e2 a result scale of max(6, s1 + p2 + 1), so decimal(19,10) over an int lands on decimal(30,21) and carries 21 decimal places into the multiply.
Compare where each pair stops being exact. money stops at the fourth decimal, which is a hundredth of a cent, inside the currency unit you are reporting on. The decimal pair stops far enough out that no auditor reaches it. A report that divides then multiplies across ten million rows turns that fourth decimal into a variance your finance team finds before you do.
Microsoft says it on the money page: "Avoid using this data type if your money or currency values are used in calculations. Instead, use the decimal data type with at least four decimal places." Use decimal(19,4) at 9 bytes, or decimal(9,4) at 5 bytes when values stay under 100,000.
DATETIME vs DATETIME2: bytes per row
| Type | Bytes | Resolution |
|---|---|---|
date |
3 | 1 day |
smalldatetime |
4 | 1 minute |
datetime2(0) |
6 | 1 second |
datetime2(3) |
7 | 1 millisecond |
datetime |
8 | 3.33 milliseconds |
datetime2(7) |
8 | 100 nanoseconds |
Read that table against the resolution your business records. An order table that timestamps to the second pays 8 bytes for datetime and 6 for datetime2(0). A ShipDate that never carries a time pays 8 for datetime and 3 for date. On 200 million rows those are 381MB and 954MB of clustered index, repeated in every nonclustered index that carries the column.
Note the two 8-byte rows. Moving to datetime2 saves nothing when you let the precision default, so the saving comes from declaring the scale, not from the type name.
Storage is the smaller half of this decision. Precision also governs how literals parse, how ranges behave at the boundary, and whether a stored value comes back as the value you wrote, and those decide whether the report is correct rather than how much it costs: the rounding trap and the half-open range.
UNIQUEIDENTIFIER as a clustered key
A uniqueidentifier is 16 bytes: four times an int, twice a bigint. As a clustered key it does three things.
It fragments the table. NEWID() produces values with no relationship to the ones before them, so every insert lands at a random point in the index. A full page splits, SQL Server allocates a new page, moves half the rows and logs all of it. You get half-empty pages, high fragmentation, and transaction log volume no other key choice generates. Measuring that before you argue about it is a separate skill: what a random clustered key does to page density.
It fragments the nonclustered indexes too. Paul Randal tested this with 10,000 inserts. Clustered on NEWID(), a nonclustered index came out 30.97% fragmented at 64.1% page density, so a third of every page was wasted. Clustered on NEWSEQUENTIALID(), the same index came out at 1.88% fragmented and 99.61% dense. The storage engine uses the cluster key to differentiate nonclustered records with matching key values, so a random cluster key randomizes the nonclustered insert point too, worst when the nonclustered key has low granularity.
It widens every nonclustered index, by the 106MB-against-25MB margin above.
NEWSEQUENTIALID() fixes the first two and not the third. It "creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started," which keeps the insert point at the end of the index. What it does not do:
- Make the key narrower. Still 16 bytes, still in every nonclustered index.
- Work anywhere but a
DEFAULTconstraint on auniqueidentifiercolumn. You cannot call it in aSELECTor wrap it in a function, so applications generating GUIDs client-side get no benefit. - Stay monotonic across a restart. "After restarting Windows, the GUID can start again from a lower range," which gives you a fresh cluster of splits in the middle of the index.
- Survive a move. It carries hardware dependencies, so relocating the database or failing over an availability group starts a new cluster of values.
- Stay private. Microsoft's warning: "It's possible to guess the value of the next generated GUID and, therefore, access data associated with that GUID." Keep it out of anything a user sees in a URL.
The usual right answer keeps both keys: cluster on a bigint identity for physical ordering, and put a unique nonclustered constraint on the GUID for the logical key the application needs.
CREATE TABLE dbo.OrderHeader
(
OrderID bigint IDENTITY(1,1) NOT NULL,
OrderGuid uniqueidentifier NOT NULL
CONSTRAINT DF_OrderHeader_Guid DEFAULT NEWID(),
-- ...
CONSTRAINT PK_OrderHeader PRIMARY KEY CLUSTERED (OrderID),
CONSTRAINT UQ_OrderHeader_Guid UNIQUE NONCLUSTERED (OrderGuid)
);
SQL_VARIANT
sql_variant sits at position 3 in the precedence list, above datetimeoffset and above every numeric and string type, so a comparison between a sql_variant column and anything else converts the other side. (Position 2 in documentation predating the json type. What it outranks has not changed.) The rest of the case against it:
- Maximum 8,016 bytes total, 8,000 for the base value.
- It cannot hold
varchar(max),nvarchar(max),varbinary(max),xml,text,ntext,image,rowversion,geography,geometry,hierarchyidor UDTs, which rules out most of what people build EAV tables to store. - Conversions into it are implicit. Conversions out are not. Every read needs an explicit
CAST, and aCASTon a column kills the seek. - ODBC does not fully support it. Through MSDASQL the column comes back as binary:
'PS2091'arrives as0x505332303931. - In an index key the data values are still bounded at 900 bytes.
Statistics on a column holding a mix of base types describe a distribution that means nothing, so cardinality estimation on it is a guess. Use typed columns with a discriminator, or an xml or json column where the shape varies for real.
What to stop doing
- Stop defaulting to
nvarchar. A defensible default for free-text user input, a bad one for codes, identifiers, email addresses, or anything an upstream system produced in ASCII. - Stop declaring
nvarchar(max)to be safe. It removes your indexing options, inflates memory grants to 4KB per row, and costs 24 bytes against the sort row limit. - Stop using
money. Microsoft tells you not to on its own documentation page. - Stop writing bare
decimal. It meansdecimal(18,0), and it rounds every value to a whole number without saying so. - Stop clustering on a random GUID. Keep the GUID if you need it and cluster on something else.
- Stop oversizing
varcharin case. The disk cost is identical, the memory grant cost is not. - Stop using
intfor an identity on a table sustaining more than 50 inserts per second. That is a heuristic off the table above, not a law.
Every one of those is free on the day you create the table. When the table already exists and the type is already wrong, the fix is a different piece of work with its own dependency checks and its own failure modes: the shadow-column procedure for changing a type on a live table.
If you would rather hand this off, it is the kind of work I do. See SQL Server DBA services, review client results, or get in touch.