SQL Data Types
This section provides a comprehensive overview of the data types supported by SQL Server, enabling you to store and manipulate various kinds of information effectively. Choosing the appropriate data type is crucial for data integrity, performance, and storage efficiency.
Numeric Data Types
These data types are used to store numeric values.
| Data Type | Description | Storage |
|---|---|---|
TINYINT |
Unsigned integer, range 0 to 255. | 1 byte |
SMALLINT |
Signed integer, range -32,768 to 32,767. | 2 bytes |
INT |
Signed integer, range -2,147,483,648 to 2,147,483,647. | 4 bytes |
BIGINT |
Signed integer, range -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. | 8 bytes |
DECIMAL / NUMERIC |
Fixed-precision and scale number. | Variable (up to 17-34 bytes) |
FLOAT |
Approximate-number data type for floating-point numbers. | 4 or 8 bytes |
REAL |
Approximate-number data type for floating-point numbers (Synonym for FLOAT(24)). |
4 bytes |
MONEY |
Monetary values. | 8 bytes |
SMALLMONEY |
Monetary values. | 4 bytes |
Date and Time Data Types
These data types are used for storing date and time values.
| Data Type | Description | Storage |
|---|---|---|
DATE |
Stores date values (YYYY-MM-DD). | 3 bytes |
TIME |
Stores time values (HH:MM:SS.nnnnnnn). | 3 to 5 bytes |
DATETIME |
Stores date and time values. | 8 bytes |
DATETIME2 |
Extends the range of DATETIME. |
6 to 8 bytes |
SMALLDATETIME |
Stores date and time values, with less precision than DATETIME. |
4 bytes |
DATETIMEOFFSET |
Stores date and time values, including a time zone offset. | 8 to 10 bytes |
SMALLDATETIME |
Stores date and time values. | 4 bytes |
String Data Types
These data types are used for storing character strings.
| Data Type | Description | Storage |
|---|---|---|
CHAR |
Fixed-length non-Unicode character data. | N bytes (padded with spaces) |
VARCHAR |
Variable-length non-Unicode character data. | N + 2 bytes |
NCHAR |
Fixed-length Unicode character data. | 2N bytes (padded with spaces) |
NVARCHAR |
Variable-length Unicode character data. | 2N + 2 bytes |
TEXT |
Variable-length non-Unicode character data (deprecated). | Large objects |
NTEXT |
Variable-length Unicode character data (deprecated). | Large objects |
VARCHAR(MAX) and NVARCHAR(MAX) should be used instead of TEXT and NTEXT respectively.
Binary Data Types
These data types are used for storing binary data.
| Data Type | Description | Storage |
|---|---|---|
BINARY |
Fixed-length binary data. | N bytes |
VARBINARY |
Variable-length binary data. | N + 2 bytes |
IMAGE |
Variable-length binary data (deprecated). | Large objects |
VARBINARY(MAX) should be used instead of IMAGE.
Other Data Types
Includes various other data types for different purposes.
| Data Type | Description | Storage |
|---|---|---|
BIT |
Integer bit, values of 0, 1, or NULL. | 1 bit (stored as 1 byte) |
UNIQUEIDENTIFIER |
Globally unique identifiers (GUIDs). | 16 bytes |
XML |
Stores XML data. | Variable |
GEOMETRY |
Spatial data type. | Variable |
GEOGRAPHY |
Geographic spatial data type. | Variable |
HIERARCHYID |
Hierarchical data representation. | Variable |
SQL_VARIANT |
Stores values of various data types. | Variable |
TABLE |
User-defined table type. | Variable |
Considerations for Choosing Data Types
- Data Integrity: Choose types that accurately represent your data.
- Storage Efficiency: Smaller types use less disk space and memory.
- Performance: Some operations are faster with certain data types.
- Range and Precision: Ensure the type can accommodate the full range and required precision of your values.
- Compatibility: Consider compatibility with other systems or applications.
Always refer to the official SQL Server documentation for the most up-to-date and detailed information.