Understanding SQL data types is fundamental for anyone developing with a SQL database. Every column in a database table requires both a name and a data type. The data type serves as a guide for SQL, indicating the nature of the data within each column and determining how SQL interacts with it.
SQL Server Data Types
In MySQL, data types are primarily categorized into three groups: string, numeric, and date and time.
String Data Types
| Data Type | Description | Storage |
|---|---|---|
char | Fixed-width character string | Defined by data (up to 8,000 bytes) |
varchar | Variable-width character string | Length of data (up to 8,000 bytes) |
text | Variable-width character string (large) | 2GB of text data |
nchar | Fixed-width Unicode character string | Defined by data (up to 4,000 characters) |
nvarchar | Variable-width Unicode character string | Length of data (up to 4,000 characters) |
ntext | Variable-width Unicode character string (large) | 2GB of text data |
Character string data types in SQL Server store character-based data. char and nchar are fixed-width, while varchar and nvarchar are variable-width. The text and ntext data types are used for storing large amounts of text data. The Unicode variants (nchar, nvarchar, ntext) support Unicode character sets.
Numeric Data Types
| Data Type | Description | Storage |
|---|---|---|
bigint | Large integer | 8 bytes |
numeric | Fixed precision and scale numbers | 5-17 bytes |
bit | Bit-value type | 1 byte |
smallint | Small integer | 2 bytes |
decimal | Fixed precision and scale numbers | 5-17 bytes |
smallmoney | Monetary data | 4 bytes |
int | Integer | 4 bytes |
tinyint | Very small integer | 1 byte |
money | Monetary data | 8 bytes |
These exact numeric data types provide a range of options for representing whole numbers and fixed precision values in SQL Server.
Approximate numerics
| Data Type | Description | Storage |
|---|---|---|
float | Floating-point number | 4 or 8 bytes |
real | Floating-point number | 4 bytes |
The approximate numeric data types float and real are used for representing floating-point numbers with varying precision. float can store larger numbers than real and can have either 4 or 8 bytes of storage. real uses 4 bytes for storage.
Date and Time
| Data Type | Description | Storage |
|---|---|---|
date | Date without time | 3 bytes |
datetimeoffset | Date and time with time zone offset | 8-10 bytes |
datetime2 | Date and time | 6-8 bytes |
smalldatetime | Date and time with less precision | 4 bytes |
datetime | Date and time | 8 bytes |
time | Time without date | 3-5 bytes |
The date and time data types in SQL Server allow the storage and manipulation of date and time information. Each data type has its own characteristics, such as precision and storage size, catering to different use cases.
Binary Strings
| Data Type | Description | Storage |
|---|---|---|
binary | Fixed-width binary string | Defined by data (up to 8,000 bytes) |
varbinary | Variable-width binary string | Length of data (up to 8,000 bytes) |
image | Variable-width binary string (large) | 2GB of binary data |
Binary string data types in SQL Server store binary data. binary and varbinary can store fixed or variable-width binary data, respectively. The image data type is used for storing large amounts of binary data.
Other Data Types
| Data Type | Description | Storage |
|---|---|---|
cursor | Reference to a cursor used for database operations | 4 bytes |
rowversion | Automatically updated binary value for versioning | 8 bytes |
hierarchyid | Represents position in a hierarchy | 8 bytes |
uniqueidentifier | Globally unique identifier (GUID) | 16 bytes |
sql_variant | Stores values of different data types, except text, ntext, and timestamp | Varies |
xml | Stores XML formatted data | Up to 2GB |
| Spatial Geometry Types | Geometric shapes and spatial data (e.g., Point, LineString) | Varies |
| Spatial Geography Types | Geographic shapes and spatial data (e.g., GeographyPoint) | Varies |
table | Stores a result-set for later processing | Varies (depending on data stored) |
Other data types in SQL Server cover a range of specialized purposes. cursor is a reference to a cursor used for database operations. rowversion is a binary value for versioning, automatically updated. hierarchyid represents a position in a hierarchy. uniqueidentifier is a globally unique identifier (GUID). sql_variant stores values of different data types. xml is used for storing XML formatted data. Spatial Geometry and Geography Types are used for representing geometric and geographic shapes, respectively. table is used to store a result-set for later processing.