Complete Guide to SQL Server Data Types

SQL Server provides a solid set of system data types that handle everything from storing tiny integers to massive text blobs. Understanding these types is an important part of designing efficient databases, mainly because picking the right data type can save storage space and improve query performance.

This article breaks down all the data types available in SQL Server (as of SQL Server 2025), organized by category. Each type includes its max length, precision, scale, and whether it can be nullable.

Exact Numerics

Exact numeric types store numbers without rounding. These are your go-to for financial calculations, inventory counts, and anywhere precision matters.

Name	Max Length	Precision	Scale	Nullable?
tinyint	1 byte	3	0	Yes
smallint	2 bytes	5	0	Yes
int	4 bytes	10	0	Yes
bigint	8 bytes	19	0	Yes
bit	1 byte	1	0	Yes
decimal	5-17 bytes	38	38	Yes
numeric	5-17 bytes	38	38	Yes
money	8 bytes	19	4	Yes
smallmoney	4 bytes	10	4	Yes

Notes:

• tinyint only stores 0-255, making it suitable for things like age or status codes.
• int is the most commonly used integer type. It handles -2 billion to +2 billion (-2,147,483,648 to 2,147,483,647 to be precise).
• decimal and numeric are functionally identical (complete synonyms).
• For decimal(p,s): p is total digits (max 38), s is digits after decimal point (max 38, but can’t exceed p).
• The precision and scale shown (38, 38) represent the maximum possible values.
• bit is typically used for Boolean values (0 or 1), and can store up to 8 bit columns in a single byte.
• money and smallmoney have fixed 4 decimal places.

Approximate Numerics

These types use floating-point representation, which means they’re fast but can have tiny rounding errors. Use them for scientific calculations where approximate values are acceptable, but avoid them for financial data.

Name	Max Length	Precision	Scale	Nullable?
float	8 bytes	53	N/A	Yes
real	4 bytes	24	N/A	Yes

Notes:

• float can be specified as float(n) where n is 1-53; float(1-24) uses 4 bytes, float(25-53) uses 8 bytes.
• real is equivalent to float(24) and always uses 4 bytes.
• The precision values (53 and 24) represent bits of mantissa precision.
• Not suitable for financial calculations due to rounding.

Date and Time

SQL Server offers several date/time types with different ranges and precision levels. The newer types (date, time, datetime2, datetimeoffset) are generally recommended over the legacy types.

Name	Max Length	Precision	Scale	Nullable?
date	3 bytes	10	0	Yes
time	5 bytes	16	7	Yes
datetime2	8 bytes	27	7	Yes
datetimeoffset	10 bytes	34	7	Yes
datetime	8 bytes	23	3	Yes
smalldatetime	4 bytes	16	0	Yes

Notes:

• date stores only the date (0001-01-01 to 9999-12-31).
• time stores only time; precision of 16 with scale of 7 means fractional seconds up to 7 decimal places.
• datetime2 is the recommended replacement for datetime, as it has better range and precision.
• The precision values shown represent the total precision including date and time components.
• Scale indicates fractional seconds precision (0-7 decimal places).
• datetimeoffset includes time zone offset (+14:00 to -14:00).
• Legacy datetime has 3.33 millisecond precision (scale of 3) and rounds values.
• smalldatetime rounds to nearest minute, always has :00 seconds.

Character Strings

Standard character strings store non-Unicode data. Each character typically uses 1 byte (depending on collation).

Name	Max Length	Precision	Scale	Nullable?
char	8,000 bytes	N/A	N/A	Yes
varchar	8,000 bytes	N/A	N/A	Yes
text	16 bytes	N/A	N/A	Yes

Notes:

• char(n) is fixed-length – always uses n bytes regardless of actual data (n: 1-8,000).
• varchar(n) is variable-length – uses only what’s needed plus 2 bytes overhead (n: 1-8,000).
• varchar(max) has a max_length of -1 in the sys.types system catalog view, and can store up to 2 GB (2^31-1 bytes).
• text shows 16 bytes in sys.types (this is a pointer). This type is deprecated, use varchar(max) instead.
• The Max Length values shown are for the base types; when declared with specific sizes, actual storage varies.
• n represents bytes, not necessarily characters (depends on collation)

Unicode Character Strings

Unicode strings store multilingual data using 2 bytes per character (or more for supplementary characters).

Name	Max Length	Precision	Scale	Nullable?
nchar	8,000 bytes	N/A	N/A	Yes
nvarchar	8,000 bytes	N/A	N/A	Yes
ntext	16 bytes	N/A	N/A	Yes

Notes:

• nchar(n) is fixed-length Unicode – always uses n×2 bytes (n: 1-4,000 characters).
• nvarchar(n) is variable-length Unicode – uses (actual length × 2) + 2 bytes (n: 1-4,000 characters).
• nvarchar(max) has max_length of -1 in sys.types and can store up to 2 GB (2^31-1 bytes).
• ntext shows 16 bytes in sys.types (this is a pointer). This type is deprecated, use nvarchar(max) instead.
• The Max Length of 8,000 bytes represents 4,000 characters (since Unicode uses 2 bytes per character).
• Use Unicode types when storing international characters or emoji.
• n represents characters, not bytes (but storage is approximately n×2 bytes).

Binary Strings

Binary types store raw binary data like images, files, or encrypted data.

Name	Max Length	Precision	Scale	Nullable?
binary	8,000 bytes	N/A	N/A	Yes
varbinary	8,000 bytes	N/A	N/A	Yes
image	16 bytes	N/A	N/A	Yes

Notes:

• binary(n) is fixed-length – always uses n bytes (n: 1-8,000).
• varbinary(n) is variable-length – uses actual data length + 2 bytes (n: 1-8,000).
• varbinary(max) has max_length of -1 in sys.types and can store up to 2 GB.
• image shows 16 bytes in sys.types (this is a pointer). This type is deprecated, use varbinary(max) instead.
• Commonly used for storing files, hashes, or encrypted data.

Other Data Types

Name	Max Length	Precision	Scale	Nullable?
cursor	N/A	N/A	N/A	N/A
geography	-1	N/A	N/A	Yes
geometry	-1	N/A	N/A	Yes
hierarchyid	892 bytes	N/A	N/A	Yes
json	-1	N/A	N/A	Yes
vector	8,000 bytes	N/A	N/A	Yes
rowversion	8 bytes	N/A	N/A	No
sql_variant	8,016 bytes	N/A	N/A	Yes
sysname	256 bytes	N/A	N/A	No
table	N/A	N/A	N/A	N/A
timestamp	8 bytes	N/A	N/A	No
uniqueidentifier	16 bytes	N/A	N/A	Yes
xml	-1	N/A	N/A	Yes

Notes:

• cursor is a reference to a cursor. It’s only used in variables and stored procedure parameters.
• geography and geometry are spatial types for location data. Max Length of -1 means variable/unlimited.
• hierarchyid stores hierarchical relationships. Max Length of 892 is the maximum possible size.
• json is a native binary JSON type introduced in SQL Server 2025 (also available in Azure SQL Database and Azure SQL Managed Instance). Prior to SQL Server 2025, JSON was stored as nvarchar(max) with JSON functions for manipulation. The native json type offers better performance, native indexing support, and structural validation.
• vector was Introduced in SQL Server 2025 to support AI-driven workloads. It’s an optimized binary format designed to store mathematical embeddings (arrays of numbers) directly within your tables. It supports up to 1,998 dimensions per column, with each element stored as a single-precision (4-byte) or half-precision (2-byte) floating-point number. While stored in binary for high-performance similarity searches (such as those using the VECTOR_DISTANCE() function or DiskANN indexing) vectors are exposed to developers as standard JSON arrays (e.g., [0.1, 2.5, -1.0]) to ensure compatibility with existing application drivers and languages.
• rowversion (formerly timestamp) is automatically updated on row changes. Always 8 bytes, cannot be NULL.
• sql_variant can store various data types (except text, ntext, image, timestamp, and max types).
• sysname is a system-supplied alias for nvarchar(128) NOT NULL. Used for object names (tables, columns, etc).
• timestamp is a deprecated synonym for rowversion. They’re identical, but use rowversion in new code.
• table is used for table-valued parameters and local variables.
• uniqueidentifier stores GUIDs (globally unique identifiers). Always 16 bytes.
• xml stores XML documents. Max Length of -1 indicates it can exceed 8,000 bytes (up to 2 GB).
• Types with Max Length of -1 can store data exceeding the standard 8,000-byte row limit.

Understanding the Table Columns

In case you’re wondering what all the columns mean:

• Max Length: Storage size in bytes. A value of -1 indicates variable-length types that can exceed 8,000 bytes (like varchar(max), nvarchar(max), varbinary(max), json, and xml).
• Precision: For numeric types, this is the maximum total number of decimal digits. For date/time types, it indicates the precision level. A value of N/A means precision doesn’t apply to that type.
• Scale: For numeric types with decimal places, this is the maximum number of digits after the decimal point. A value of N/A means scale doesn’t apply to that type.
• Nullable?: Whether the type can store NULL values. Almost all types are nullable except sysname and rowversion (which default to NOT NULL).

Special System Types

SQL Server includes a few special types that deserve extra attention:

sysname

sysname is a system-supplied user-defined type that’s functionally equivalent to nvarchar(128) NOT NULL. It’s specifically designed for storing database object names (table names, column names, procedure names, etc.).

Main characteristics:

• Always NOT NULL by default (unlike other types)
• Maximum 128 Unicode characters (256 bytes in storage)
• Used throughout SQL Server’s system tables and views
• Future-proof – if SQL Server ever increases the max identifier length, sysname will automatically adapt

When to use it: Use sysname in your code when storing or passing object names, especially in dynamic SQL or metadata queries. It ensures consistency with SQL Server’s internal handling of identifiers.

timestamp / rowversion

timestamp is the old name for rowversion (they’re completely identical). Both are 8-byte binary values that SQL Server automatically generates and updates whenever a row is modified.

Main characteristics:

• Always NOT NULL (cannot be NULL)
• Automatically generated and updated
• Cannot insert explicit values (except NULL during insert, which gets replaced)
• Unique within the database, monotonically increasing
• Used for optimistic concurrency control

Important: Despite the name “timestamp”, this type has nothing to do with dates or times. It’s purely a version marker. Use datetime2 or datetimeoffset for actual timestamps.

Recommendation: Always use rowversion in new code. The timestamp data type is maintained only for backward compatibility.

json (SQL Server 2025+)

The json data type is a true native type introduced in SQL Server 2025 (and earlier in Azure SQL Database/Managed Instance). It stores JSON documents in an optimized binary format rather than as plain text.

Main characteristics:

• Stores JSON in native binary format (UTF-8 encoded internally)
• Much more efficient than storing JSON as nvarchar(max) (previous approach)
• Supports native JSON indexes for fast querying
• Provides automatic JSON validation at write time
• Can be nullable
• Works with JSON functions like JSON_VALUE(), JSON_QUERY(), JSON_CONTAINS()

Important version note: Prior to SQL Server 2025, there was no native json type. SQL Server 2016-2022 supported JSON through functions (OPENJSON(), JSON_VALUE(), etc.) that worked with JSON stored in varchar(max) or nvarchar(max) columns. The SQL Server 2025 native json type is a significant upgrade that offers better performance and native indexing.

If you’re on SQL Server 2025 or Azure SQL Database, use the native json type for new JSON storage. If you’re on older versions, continue using nvarchar(max) with JSON functions.

Choosing the Right Data Type

When designing tables, consider these factors:

• Storage efficiency: Use the smallest type that reliably holds your data. An int uses half the space of bigint, and tinyint uses a quarter. This adds up with millions of rows.
• Precision requirements: Use exact numerics (decimal/numeric) for financial data. Approximate numerics (float/real) are faster but can have rounding errors.
• String length: Fixed-length types (char, nchar) are slightly faster but waste space if data varies. Variable-length types (varchar, nvarchar) save space but add a bit of overhead.
• Unicode support: If you’re storing international text, emoji, or multilingual content, use Unicode types (nchar, nvarchar). They use twice the space but support all characters.
• Date/time precision: If you need timezone support, use datetimeoffset. For high precision, use datetime2(7). For simple date storage, date is the most efficient.
• Nullability: Almost all types can be nullable, but you should explicitly define this based on your business rules. The rowversion type is the main exception – it can’t be null since it’s auto-generated.

Deprecated Types to Avoid

Microsoft has marked some types as deprecated, which means they will be removed in future versions:

• text, ntext, image – Use varchar(max), nvarchar(max), varbinary(max) instead
• timestamp – Use rowversion instead (they’re functionally identical, but rowversion is the proper name)

If you’re working on legacy databases with these types, plan to migrate them in your modernization efforts. The (max) types offer the same capabilities with better integration into modern SQL Server features.

Max Types (varchar/nvarchar/varbinary)

The (max) variants deserve special mention. They can store up to 2 GB of data:

• varchar(max) and nvarchar(max) for large text
• varbinary(max) for large binary objects

These types automatically optimize storage. If your data is under 8,000 bytes, it’s stored in-row for fast access. Larger values are stored off-row as LOB (Large Object) data. This makes them flexible, though there are some limitations (like not being able to use them as index keys).

User-Defined Data Types

Beyond the built-in system types, SQL Server allows you to create User-Defined Data Types (UDDTs). These are essentially custom aliases based on existing system types that allow you to enforce data consistency across your entire database schema. For instance, if you have a specific standard for a “PostalCode” or “EmployeeID,” you can define a UDDT once and apply it to multiple tables, ensuring that the underlying length, precision, and nullability remain identical everywhere.

For more complex scenarios, you can also create User-Defined Table Types. These allow you to define a schema for an entire table structure that can then be passed into stored procedures or functions as a single parameter. This is particularly useful for “bulk” operations where you need to send multiple rows of data from an application to the database in a single call, rather than executing individual inserts.

About the sys.types System Catalog View

Most of the values shown in this article’s tables come from the sys.types system catalog view, which is how SQL Server internally tracks data types. To see this data yourself, run:

SELECT name, max_length, precision, scale, is_nullable
FROM sys.types
WHERE is_user_defined = 0
ORDER BY name;

This returns most of the above data types, along with their max length, precision, scale, and nullable status. Regarding the precision and scale columns, these return 0 if it’s not applicable to the type. I’ve used N/A in the above tables so as to avoid any confusion.