SQL Data Types
Data types define the kind of data that can be stored in a database column. Choosing the correct data type is crucial for efficient storage, performance, and data integrity. Microsoft SQL Server supports a wide range of data types, categorized as follows:
Numeric Data Types
Used for storing numeric values. The choice depends on the range and precision required.
| Data Type | Description | Storage | Range/Precision |
|---|---|---|---|
BIT |
Integer that can be 0, 1, or NULL. | 1 byte | 0, 1 |
TINYINT |
Unsigned integer with a range of 0 to 255. | 1 byte | 0 to 255 |
SMALLINT |
Signed integer with a range from -32,768 to 32,767. | 2 bytes | -32,768 to 32,767 |
INT |
Signed integer with a range from -2,147,483,648 to 2,147,483,647. | 4 bytes | -2,147,483,648 to 2,147,483,647 |
BIGINT |
Signed integer with a large range. | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
DECIMAL / NUMERIC |
Exact numeric value with a fixed precision and scale. | Variable (up to 17-34 bytes) | Precision: 1-38, Scale: 0-38 |
FLOAT / REAL |
Approximate floating-point number. | 4 or 8 bytes | Approximate values |
MONEY / SMALLMONEY |
Currency values. | 8 or 4 bytes | Fixed precision |
Date and Time Data Types
Used for storing date and time information.
| Data Type | Description | Storage | Range/Precision |
|---|---|---|---|
DATE |
Stores date values (year, month, day). | 3 bytes | 0001-01-01 to 9999-12-31 |
TIME |
Stores time values (hour, minute, second, fractional seconds). | 3-5 bytes | 00:00:00.0000000 to 23:59:59.9999999 |
DATETIME |
Stores date and time values. | 8 bytes | 1753-01-01 00:00:00.000 to 9999-12-31 23:59:59.997 |
DATETIME2 |
More precise and supports a larger date range than DATETIME. |
6-8 bytes | 0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 |
SMALLDATETIME |
Stores date and time values with less precision. | 4 bytes | 1900-01-01 00:00:00 to 2075-06-06 23:59:59 |
DATETIMEOFFSET |
Stores date and time values with time zone information. | 8-10 bytes | Similar to DATETIME2, with offset |
String Data Types
Used for storing character strings and text.
| Data Type | Description | Storage | Max Length |
|---|---|---|---|
CHAR |
Fixed-length non-Unicode character string. | n bytes |
8,000 characters |
VARCHAR |
Variable-length non-Unicode character string. | n + 2 bytes |
8,000 characters |
VARCHAR(MAX) |
Variable-length non-Unicode character string for large data. | 2 GB | 2^31-1 characters |
NCHAR |
Fixed-length Unicode character string. | 2n bytes |
4,000 characters |
NVARCHAR |
Variable-length Unicode character string. | 2n + 2 bytes |
4,000 characters |
NVARCHAR(MAX) |
Variable-length Unicode character string for large data. | 2 GB | 2^31-1 characters |
TEXT |
Obsolete. Use VARCHAR(MAX). |
Variable | 2 GB |
Binary Data Types
Used for storing binary data, such as images or files.
| Data Type | Description | Storage | Max Length |
|---|---|---|---|
BINARY |
Fixed-length binary string. | n bytes |
8,000 bytes |
VARBINARY |
Variable-length binary string. | n + 2 bytes |
8,000 bytes |
VARBINARY(MAX) |
Variable-length binary string for large data. | 2 GB | 2^31-1 bytes |
IMAGE |
Obsolete. Use VARBINARY(MAX). |
Variable | 2 GB |
Other Data Types
Includes specialized types for specific needs.
UNIQUEIDENTIFIER: Stores a GUID (Globally Unique Identifier).XML: Stores XML data.GEOMETRY: Stores geometric shapes.GEOGRAPHY: Stores geographic data.ROWVERSION(orTIMESTAMP): Binary number that increments automatically.
Important Considerations
When choosing a data type, consider:
- The range of values the data will hold.
- The precision required for numeric or decimal values.
- Whether the data is fixed or variable in length.
- Whether the data is Unicode or non-Unicode.
- Storage space and performance implications.