SQL Server provides a variety of data types to store different kinds of data efficiently. Here are some commonly used SQL Server data types:
CHAR(size): A FIXED length string (can contain letters, numbers, and special characters). The size parameter specifies the column length in characters - can be from 0 to 255 (2 bit). Default is 1
VARCHAR(size):A VARIABLE length string (can contain letters, numbers, and special characters). The size parameter specifies the maximum string length in characters - can be from 0 to 65535. Default is 1
BIT(size): Boolean data type, stores 0 for false and 1 for true. Converting to bit promotes any nonzero value to 1. The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.
int, bigint, smallint, and tinyint:
| Data type | Range | Range expression | Storage |
|---|---|---|---|
| bigint | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 extremely large numbers or when dealing with identity columns that require a larger range | -2^63 to 2^63-1 | 8 Bytes |
| int | -2,147,483,648 to 2,147,483,647 Suitable for most general-purpose integer storage needs. | -2^31 to 2^31-1 | 4 Bytes |
| smallint | -32,768 to 32,767 Suitable for cases where the range of int is excessive or when storage space needs to be optimized. | -2^15 to 2^15-1 | 2 Bytes |
| tinyint | 0 to 255 It's particularly useful when space optimization is a concern or when dealing with binary data. | 2^0-1 to 2^8-1 | 1 Byte |
Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type. The following example shows that when the threshold value is exceeded, the data type of the result changes from an int to a decimal.
SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2 ;
Decimal and Numeric:
- Precision and Scale: Both
DECIMALandNUMERICrequire two parameters: precision (total number of digits) and scale (number of digits to the right of the decimal point). - Range: They can store values from -10^38 +1 to 10^38 -1.
- Storage: Storage size depends on the precision and scale specified.
- Usage: They are typically used for monetary values, quantities, or any numeric data where exact precision is required.
money and smallmoney:
| Data type | Range | Storage |
|---|---|---|
| money | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. ) It is commonly used for storing large monetary values with up to four decimal places of precision. | 8 bytes |
| smallmoney | -214,748.3648 to 214,748.3647 It is used for storing smaller monetary values, typically with up to four decimal places of precision. | 4 bytes |
Float and Real:
| FLOAT: | Precision: The FLOAT data type is a floating-point number with a user-specified precision.Storage: Requires 4 or 8 bytes of storage, depending on the precision specified. Range: The range of values that can be stored in a FLOAT column is typically large, providing flexibility for scientific calculations or when dealing with extremely large or small numbers.Usage: Suitable for cases where an approximation of real numbers is acceptable, such as scientific or engineering calculations. It's important to note that FLOAT is approximate and might not always represent exact values. |
| REAL: | Precision: The REAL data type is a synonym for FLOAT(24).Storage: Requires 4 bytes of storage. Range: Similar to FLOAT, REAL provides a wide range of values.Usage: Historically, REAL has been used for compatibility reasons with other systems or applications that expect this data type. However, it's recommended to use FLOAT instead for new development. |
| Data Type | Description | Range | Storage | Example | Typical Usage |
|---|---|---|---|---|---|
| DATE | Stores date values without a time component | January 1, 0001 CE to December 31, 9999 CE | 3 bytes | '2024-05-04' | Birthdates, event dates |
| TIME | Stores time values without a date component | 00:00:00.0000000 through 23:59:59.9999999 | 3 to 5 bytes | '14:30:00.000' | Recording time durations, working hours |
| DATETIME | Stores date and time values with millisecond precision | January 1, 1753 CE to December 31, 9999 CE | 8 bytes | '2024-05-04 14:30:00.000' | Timestamps, scheduling |
| SMALLDATETIME | Stores date and time values with minute precision | January 1, 1900 CE to June 6, 2079 CE | 4 bytes | '2024-05-04 14:30:00' | Legacy systems, where minute precision is sufficient |
| DATETIME2 | Stores date and time values with high fractional seconds precision | January 1, 0001 CE to December 31, 9999 CE | 6-8 bytes | '2024-05-04 14:30:00.1234567' | Scientific, financial applications requiring high precision |
| DATETIMEOFFSET | Stores date and time values with time zone information | Similar to DATETIME2 | 10 bytes | '2024-05-04 14:30:00.1234567 +05:30' | Global or distributed systems, where time zone information is needed |
| Data type | Output |
|---|---|
| time | 12:35:29.1234567 |
| date | 2007-05-08 |
| smalldatetime | 2007-05-08 12:35:00 |
| datetime | 2007-05-08 12:35:29.123 |
| datetime2 | 2007-05-08 12:35:29.1234567 |
| datetimeoffset | 2007-05-08 12:35:29.1234567 +12:15 |
