Data Types

4.1 Exact Numerics

1 bit (packed 8 per byte) -32,768 to 32,767 ±9.2 quintillion DECIMAL(p,s) / NUMERIC(p,s) Precision up to 38 SMALLMONEY / MONEY Currency (prefer DECIMAL)

4.2 Approximate Numerics

FLOAT(n) (8 bytes by default) and REAL (4 bytes). Never use them for money. Rounding errors will creep in.

4.3 Character Strings

Fixed length, up to 8,000 bytes VARCHAR(n) / VARCHAR(MAX) Variable length, MAX = 2 GB

Fixed-length Unicode (UTF-16), up to 4,000 chars NVARCHAR(n) / NVARCHAR(MAX) TEXT, NTEXT

Deprecated. Use (N)VARCHAR(MAX) instead.

Use the N'...' prefix for Unicode literals: N'नमस्ते'.

4.4 Date and Time

0001–9999 (recommended) Includes time-zone offset

4.5 Binary

BINARY(n), VARBINARY(n), VARBINARY(MAX). IMAGE is deprecated.

4.6 Other Types

GUID (16 bytes). Generate with NEWID() or NEWSEQUENTIALID().

Native JSON type (SQL Server 2025 / Azure SQL) Embedding vectors for AI (SQL Server 2025) Holds values of various types GEOMETRY, GEOGRAPHY ROWVERSION (TIMESTAMP)

Auto-incrementing binary row version, used for optimistic concurrency CURSOR, TABLE Used only for variables and parameters

4.7 Choosing Data Types: Best Practices

Pick the smallest type that safely fits the data. Smaller rows mean more rows per page and less I/O.

Use DATE when you don't need a time portion. Use DATETIME2 rather than DATETIME.

Use DECIMAL for money.

Avoid unnecessary NVARCHAR(MAX).

Keep join columns on the same data type to avoid implicit conversions.

Data type precedence decides implicit conversions. For example, comparing an INT with a VARCHAR converts the VARCHAR to INT.

4.8 User-Defined Data Types (Alias Types)

CREATE TYPE dbo.Phone FROM VARCHAR(20) NOT NULL;
CREATE TABLE Contacts (ContactID INT, Mobile dbo.Phone);