Understanding SQL Server Data Types: What You Need to Know 

SQL Server Data Types Reference Guide

SQL Server Data Types Reference Guide

This comprehensive reference guide provides detailed information about all data types available in Microsoft SQL Server. Use this guide to choose the most appropriate data type for your database design, optimizing for both storage efficiency and query performance.

Category Data Type Storage Size Range/Precision Examples & Usage Notes
Exact Numerics
Exact Numerics bit 1 bit 0, 1, or NULL 0, 1 – Boolean values, flags, toggle settings. Efficiently stores boolean values; 8 bits stored in 1 byte.
tinyint 1 byte 0 to 255 0, 42, 255 – Small positive integers, age, counts, status codes. Unsigned integer; useful for lookup tables with few entries.
smallint 2 bytes -32,768 to 32,767 -1024, 8000 – Medium-range integers, product quantities. Balance between storage and range; use instead of int when possible.
int 4 bytes -2.1B to 2.1B -10000000, 42 – Standard integer values, primary keys, foreign keys. Most commonly used integer type; default choice for integer data.
bigint 8 bytes -9.2E+18 to 9.2E+18 -9000000000000000 – Large integer values, row counts in huge tables. Use only when exceeding int range to save storage.
decimal(1-9,s) or numeric(1-9,s) 5 bytes Precision 1-9, Scale 0-p 9.9, 123.45 – Financial data with low precision, percentages. Exact decimal representations; no rounding errors. Scale (s) cannot exceed Precision (p).
decimal(10-19,s) or numeric(10-19,s) 9 bytes Precision 10-19, Scale 0-p 1234567.89 – Financial data with medium precision, currency values. Storage increases with precision; Scale (s) must be ≤ Precision (p).
decimal(20-28,s) or numeric(20-28,s) 13 bytes Precision 20-28, Scale 0-p 12345678901234567.89012 – Financial data with high precision, scientific measurements. Prefer smaller precision when possible for better performance.
decimal(29-38,s) or numeric(29-38,s) 17 bytes Precision 29-38, Scale 0-p 123456789012345678901234567.89 – Financial data with very high precision, astronomical values. Maximum precision available; rarely needed.
money 8 bytes -922T to 922T $1,234.56, -$999.99 – Currency values, accounting systems. Fixed precision (4 decimal places); faster than decimal for financial calculations.
smallmoney 4 bytes -214,748.3648 to 214,748.3647 $199.99 – Small currency values, retail pricing. Compact storage for monetary values with limited range; fixed 4 decimal places.
uniqueidentifier 16 bytes GUID 6F9619FF-8B86-D011-B42D-00C04FC964FF – Primary keys, unique IDs, distributed systems. Globally unique; useful for distributed databases.
Approximate Numerics
Approximate Numerics float(1-24) 4 bytes -3.40E+38 to 3.40E+38 3.14159, -1.23E+15 – Scientific calculations, physics simulations (single precision). Can have precision issues; not for financial data.
float(25-53) 8 bytes -1.79E+308 to 1.79E+308 3.1415926535897932 – Scientific calculations, statistical analysis (double precision). Higher precision but still approximate.
real 4 bytes -3.40E+38 to 3.40E+38 3.14159 – Scientific data, sensor readings, 3D coordinates. Synonym for float(24); useful for scientific applications.
Date & Time
Date & Time date 3 bytes 0001-01-01 to 9999-12-31 2025-05-19 – Birth dates, calendar events, deadlines. Efficient storage when time isn’t needed.
time(0) 3 bytes 00:00:00 through 23:59:59 13:45:00 – Schedule times, business hours (second precision). Smallest storage for time values.
time(1) 3 bytes 00:00:00.0 through 23:59:59.9 13:45:30.5 – Time with 0.1 second precision. Balance between precision and storage.
time(2) 3 bytes 00:00:00.00 through 23:59:59.99 13:45:30.55 – Time with 0.01 second precision. Hundredth of a second precision; useful for race timing.
time(3) 4 bytes 00:00:00.000 through 23:59:59.999 13:45:30.555 – Time with millisecond precision, application logging. Common choice for most applications.
time(4) 4 bytes 00:00:00.0000 through 23:59:59.9999 13:45:30.5555 – Time with 0.1 millisecond precision. Higher precision with moderate storage.
time(5) 5 bytes 00:00:00.00000 through 23:59:59.99999 13:45:30.55555 – Time with 0.01 millisecond precision. Very high precision time values.
time(6) 5 bytes 00:00:00.000000 through 23:59:59.999999 13:45:30.555555 – Time with microsecond precision, high-frequency trading. Used for scientific or high-frequency data.
time(7) 5 bytes 00:00:00.0000000 through 23:59:59.9999999 13:45:30.5555555 – Time with 0.1 microsecond precision. Maximum precision available.
datetime2(0) 6 bytes 0001-01-01 through 9999-12-31 2025-05-19 13:45:30 – Date and time with second precision. Modern replacement for datetime with better range.
datetime2(3) 7 bytes 0001-01-01 through 9999-12-31 2025-05-19 13:45:30.555 – Date and time with millisecond precision, logs. Common choice for most applications; recommended default.
datetime2(7) 8 bytes 0001-01-01 through 9999-12-31 2025-05-19 13:45:30.5555555 – Date and time with 0.1 microsecond precision. Default precision when not specified.
datetimeoffset(3) 9 bytes With time zone 2025-05-19 13:45:30.555 +10:00 – Global date/time with millisecond precision. Common choice for global applications.
smalldatetime 4 bytes 1900-01-01 through 2079-06-06 (1 minute) 2025-05-19 13:45:00 – Compact date/time storage, general business data. Limited range but efficient storage; rounds to minutes.
datetime 8 bytes 1753-01-01 through 9999-12-31 (0.00333 second) 2025-05-19 13:45:30.123 – Legacy date and time type. Older data type with odd precision (3.33ms); prefer datetime2.
Character Strings
Character Strings char(n) n bytes Fixed-length string 'USA', 'ABC ' (padded) – Known-length text, country codes, fixed identifiers. Padded with spaces; use only when length is constant.
varchar(n) actual + 2 bytes Variable-length string 'Hello', 'John Smith' – Variable-length text, names, descriptions. Efficient storage for variable-length strings.
varchar(max) up to 2GB Large text 'This is a long text...' – Document storage, long descriptions, articles. For text exceeding 8000 characters; stored outside row.
text up to 2GB Large text 'Large document content...' – Legacy document storage. DEPRECATED; use varchar(max) instead; poor performance.
Unicode Strings
Unicode Strings nchar(n) n × 2 bytes Fixed-length Unicode N'北京', N'ABC ' (padded) – International fixed-length text, language codes. Double storage of char but supports all languages.
nvarchar(n) actual × 2 + 2 bytes Variable-length Unicode N'Москва', N'München' – International text, multilingual user input. Preferred for international character support.
nvarchar(max) up to 2GB Large Unicode text N'大きいドキュメント...' – International document storage, long translations. For Unicode text exceeding 4000 characters.
ntext up to 2GB Large Unicode text N'Long multilingual content...' – Legacy international document storage. DEPRECATED; use nvarchar(max) instead.
Binary Data
Binary Data binary(n) n bytes Fixed-length binary 0x1234567890, 0x0000ABCDEF (padded) – Hash values, encryption keys. Fixed-length; padded with zeros; use only when length is constant.
varbinary(n) actual + 2 bytes Variable-length binary 0x12345, 0xABCDEF012345 – Binary data, small images, serialized objects. Efficient storage for variable-length binary data.
varbinary(max) up to 2GB Large binary data 0x[long binary data...] – Files, images, documents, large objects. For binary data exceeding 8000 bytes; stored outside row.
image up to 2GB Large binary data 0x[long binary data...] – Legacy image and file storage. DEPRECATED; use varbinary(max) instead; poor performance with large data.
Spatial Data
Spatial Data geometry varies Planar spatial data POINT(3 4), LINESTRING(0 0, 3 4) – Maps, locations (flat), CAD drawings, local areas. For 2D spatial data; flat-earth model.
geography varies Geodetic spatial data POINT(-122.35 47.65) – GPS coordinates, global locations, navigation. For GPS and global coordinates; round-earth model.
Other Types
Other Types sql_variant up to 8016 bytes Varies CAST(123 AS sql_variant) – Variable type storage. Can store different data types; not recommended; causes performance issues.
timestamp / rowversion 8 bytes Auto-generated binary number 0x0000000000000001 – Concurrency control for row updates. Not for time storage; used for version tracking.
hierarchyid ~5 bytes avg (varies) Hierarchical structure /1/3/2/, /1/1/3/ – Organization charts, product categories. For representing position in hierarchy; more efficient than parent-child tables.

Note: This reference guide covers SQL Server data types as of SQL Server 2019. For the most current information, always refer to the official Microsoft documentation.

Leave a Reply

Your email address will not be published. Required fields are marked *