liveBook · Manning

Smallint

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

SMALLINT is a SQL Server integer data type that stores signed whole-number values using two bytes.

Mistake #6: defaulting every whole-number column to INT. SMALLINT stores signed whole numbers in two bytes, so it can reduce storage when values will remain within its range. section 3.1

Use the range deliberately:

  • Choose it when growth is understood; exceeding its range can make an apparently efficient design operationally costly to change. section 3.1
  • Savings compound across large fact tables, foreign keys, indexes, and concurrent queries. section 3.1

Check the bytes before choosing

SELECT DATALENGTH(CAST(1 AS SMALLINT)) AS SmallIntSize; returns 2. An Employees table can use SMALLINT for EmployeeID and nullable ManagerID when the expected employee count fits the range.

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

How deep it goes
Explains range and byte size, demonstrates DATALENGTH, and applies the choice to keys without detailing overflow behavior.
Examples
Demonstrates byte measurement and SMALLINT EmployeeID and ManagerID columns in an Employees table.
What it assumes
Assumes basic SQL Server table and column vocabulary while teaching integer ranges, sizes, and key-storage trade-offs.
What you can do after
Evaluate integer-column ranges and storage consequences before selecting SMALLINT instead of a wider type.
How it connects
Connects SMALLINT with integer data types, primary keys, foreign keys, indexes, and data warehouse fact tables.
Cover of 100 SQL Server Mistakes and How to Avoid Them
liveBookmanning.com
Read 100 SQL Server Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

Compare how 3 books explain smallint →

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.