liveBook · Manning

Variable-length data

As explained in 100 SQL Server Mistakes and How to Avoid Them

A variable-length string is a character data type whose stored size can vary with the length of its value.

Mistake #7: using variable-length strings for every text column. A VARCHAR column seems space-efficient, but a value with a consistent length may be better represented by CHAR or NCHAR. Fixed-length types pad unused characters with whitespace, so the declared width—and the consistency of stored values—matters. section 3.2

The address-table choice

An address table might use VARCHAR(17) for city names and VARCHAR(48) for state names because their lengths vary. A ZIP code that is always 10 characters is a different case: use a fixed-length string when the column's values consistently have that length, and use a variable-length string when they do not. section 3.2 section 3.6

The safer rule is simple: choose the most restrictive type that stores every required value. Do not assume that VARCHAR(10) and CHAR(10) have identical storage behavior merely because both accept ten-character values; fixed-length strings occupy their declared width, including padding. section 3.2 section 3.6

Listing 3.5 Updating the DepartmentCode column to a fixed-length string
ALTER TABLE dbo.Employees
   ALTER COLUMN DepartmentCode NCHAR(2) ;

Use the ALTER TABLE example to convert DepartmentCode to NCHAR(2) when its values have a consistent fixed length. section 3.2

How 100 SQL Server Mistakes and How to Avoid Them covers it

How deep it goes
Explains padding, declared widths, and storage considerations, but does not detail the complete SQL Server row-storage mechanism.
Examples
Uses US address columns, ZIP codes, city and state names, and a DepartmentCode alteration.
What you can do after
Operate/configure SQL Server schemas by selecting fixed-length or variable-length strings for each column.
How it connects
Connects variable-length strings with fixed-length strings, VARCHAR, CHAR, NCHAR, columns, and storage overhead.

Related concepts

This explainer was generated by Manning's knowledge graph from the text of 100 SQL Server Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.