Mistake: treating NVARCHAR like a wider VARCHAR. NVARCHAR stores variable-length Unicode data with UTF-16, using two bytes per character. That supports the full UTF-16 range, but consumes more space and fits fewer characters on a data page than 8-bit character types. section 3.2
Keep these storage rules in mind:
- Each variable-length column adds a 2-byte offset, even when its value is NULL. section 3.2
- NVARCHAR and VARCHAR(MAX) can hold up to 2 GB; values exceeding the data page use row-overflow storage. section 3.2
- Fixed-length UTF-16 data is limited to 4,000 characters, compared with 8,000 characters for fixed-length 8-bit data. section 3.2
Choosing a fixed-length alternative
A two-character department code can use a fixed-length Unicode column: ALTER TABLE dbo.Employees ALTER COLUMN DepartmentCode NCHAR(2);
