SQL Server Data Types
Understanding SQL Server data types is crucial for efficient database design, accurate data storage, and effective querying. This section covers the various data types available in SQL Server.
Exact Numeric Data Types
These data types store exact numeric values, such as integers and decimals.
| Data Type | Description | Storage |
|---|---|---|
TINYINT |
Unsigned integer, 0 to 255 | 1 byte |
SMALLINT |
Signed integer, -32,768 to 32,767 | 2 bytes |
INT |
Signed integer, -2,147,483,648 to 2,147,483,647 | 4 bytes |
BIGINT |
Signed integer, -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 | 8 bytes |
DECIMAL and NUMERIC |
Fixed-precision and scale number. Precision up to 38 digits. | Variable |
DECIMAL(p,s) |
Decimal value with specified precision (p) and scale (s). | Variable |
MONEY |
Monetary values, fixed point with 4 decimal places. | 8 bytes |
SMALLMONEY |
Smaller monetary values, fixed point with 4 decimal places. | 4 bytes |
Approximate Numeric Data Types
These data types store approximate numeric values, such as floating-point numbers.
| Data Type | Description | Storage |
|---|---|---|
FLOAT |
Floating-point number. Approximate precision. | 4 or 8 bytes |
REAL |
Floating-point number. Approximate precision (equivalent to FLOAT(24)). | 4 bytes |
Date and Time Data Types
Store date and time values.
| Data Type | Description | Storage |
|---|---|---|
DATE |
Stores date values (YYYY-MM-DD). | 3 bytes |
TIME |
Stores time values (HH:MM:SS.fraction). | 3 to 5 bytes |
DATETIME |
Stores date and time values. | 8 bytes |
DATETIME2 |
An ANSI SQL-compliant date and time type with higher precision and a larger date range. | 6 to 8 bytes |
SMALLDATETIME |
Stores date and time values from 1753 to 1970. | 4 bytes |
DATETIMEOFFSET |
Stores date and time values with time zone offset. | 10 bytes |
SMALLDATETIMEOFFSET |
Stores date and time values with time zone offset, smaller range. | 8 bytes |
Character String Data Types
Store text data.
CHAR(n): Fixed-length non-Unicode character string.VARCHAR(n): Variable-length non-Unicode character string.VARCHAR(MAX): Variable-length non-Unicode character string, up to 2 GB.NCHAR(n): Fixed-length Unicode character string.NVARCHAR(n): Variable-length Unicode character string.NVARCHAR(MAX): Variable-length Unicode character string, up to 2 GB.
Binary String Data Types
Store binary data.
BINARY(n): Fixed-length binary string.VARBINARY(n): Variable-length binary string.VARBINARY(MAX): Variable-length binary string, up to 2 GB.IMAGE: Deprecated. Stores large binary objects. UseVARBINARY(MAX)instead.
Other Data Types
BIT: Stores 0, 1, or NULL. Used for Boolean values.UNIQUEIDENTIFIER: Stores a globally unique identifier (GUID).XML: Stores XML data.CURSOR: Placeholder for cursor variables.TABLE: Placeholder for table variables.SQL_VARIANT: Stores values of various SQL Server data types.HIERARCHYID: Represents a node in a hierarchy.GEOMETRY: Stores geometric data.GEOGRAPHY: Stores geographic data.VARBINARY(MAX): For large binary objects.NVARCHAR(MAX): For large Unicode text.VARCHAR(MAX): For large non-Unicode text.
Choosing the Right Data Type
Consider the following when selecting a data type:
- Data range: Ensure the data type can accommodate the expected values.
- Storage space: Choose the smallest data type that meets your needs to optimize storage.
- Performance: Certain data types can impact query performance.
- Precision: Use exact numeric types for financial data and approximate types for scientific calculations where precision is less critical.
- Character encoding: Use Unicode (
NCHAR,NVARCHAR) for international character support.
Refer to the official SQL Server documentation for detailed specifications and usage examples for each data type.