Data Types and Table Design

Choosing the right data types and designing tables properly is crucial for performance, storage efficiency, and data integrity. This lesson covers SQL Server's data types, constraints, identity columns, and computed columns.

---

Numeric Data Types

Data Type	Range	Storage
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 bytes
INT	-2.1 billion to 2.1 billion	4 bytes
BIGINT	-9.2 quintillion to 9.2 quintillion	8 bytes
DECIMAL(p,s)	Fixed precision and scale	5-17 bytes
FLOAT	Approximate, double-precision	8 bytes
REAL	Approximate, single-precision	4 bytes
MONEY	-922 trillion to 922 trillion	8 bytes
SMALLMONEY	-214,748 to 214,748	4 bytes
BIT	0, 1, or NULL	1 bit

Tip: Use DECIMAL for financial calculations (exact precision). Use FLOAT only for scientific or approximate calculations.

---

String Data Types

Data Type	Description	Max Length
CHAR(n)	Fixed-length, non-Unicode	8,000 characters
VARCHAR(n)	Variable-length, non-Unicode	8,000 characters
VARCHAR(MAX)	Variable-length, non-Unicode, large	2 GB
NCHAR(n)	Fixed-length, Unicode	4,000 characters
NVARCHAR(n)	Variable-length, Unicode	4,000 characters
NVARCHAR(MAX)	Variable-length, Unicode, large	2 GB

CHAR vs VARCHAR

Feature	CHAR(10)	VARCHAR(10)
Storage	Always 10 bytes	2 + actual length bytes
Padding	Right-padded with spaces	No padding
Best for	Fixed-length codes (e.g., ISO country codes)	Variable-length text

Unicode vs Non-Unicode

Feature	VARCHAR	NVARCHAR
Character set	Single-byte (ASCII)	Unicode (UTF-16)
Storage per char	1 byte	2 bytes
Use when	Data is English only	Data may include multiple languages

Tip: Default to NVARCHAR if your application may handle international characters.

---

Date and Time Data Types

Data Type	Range	Precision	Storage
DATE	0001-01-01 to 9999-12-31	1 day	3 bytes
TIME	00:00:00.0000000 to 23:59:59.9999999	100 ns	3-5 bytes
DATETIME	1753-01-01 to 9999-12-31	3.33 ms	8 bytes
DATETIME2	0001-01-01 to 9999-12-31	100 ns	6-8 bytes
DATETIMEOFFSET	Same as DATETIME2 + time zone	100 ns	8-10 bytes
SMALLDATETIME	1900-01-01 to 2079-06-06	1 minute	4 bytes

Tip: Use DATETIME2 instead of DATETIME for new applications — it has better precision, a wider range, and smaller storage.

---

Other Data Types