Data types for SQL server.
| Datatype | Description | Max Size in Database Table: |
Max Size: TSQL |
|---|---|---|---|
| CHAR(bytes) | Fixed length character data of length size bytes. In SQL Server 2019 and above this supports UTF-8. This should be used for fixed length data. Such as codes A100, B102… |
8,000 bytes This equates to 8,000 characters for single-byte encoding character sets such as Latin. |
8,000 bytes Default and minimum size is 1 byte. |
| VARCHAR(bytes) | Variable length character string having maximum length size
bytes. You must specify size. In SQL Server 2019 and above this supports UTF-8. |
8,000 bytes This equates to 8,000 characters for single-byte encoding character sets such as Latin. |
|
| VARCHAR(MAX) | Variable-length non-Unicode character data You cannot create indexes on varchar(MAX) columns. |
2^31-1 bytes (2 GB) minimum is 1 |
2^31-1 bytes (2 GB) minimum is 1 |
| NCHAR(bytes) | Fixed length Unicode data of length size bytes. This should be used for fixed length data. Such as codes A100, B102… | 4,000 bytes Default and minimum size is 1 byte. This equates to a max size of 1000 characters for UTF16, or 2000 characters if the character set is UTF8. |
4,000 bytes Default and minimum size is 1 byte. |
| NVARCHAR(bytes) | Variable length Unicode character string having maximum length size bytes. You must specify size. |
Maximum size is: 4000 characters (8,000 bytes + 2 bytes) |
|
| NVARCHAR(MAX) | Variable-length Unicode character data You cannot create indexes on varchar(MAX) columns. |
2^31-1 bytes (2 GB) minimum is 1 |
2^31-1 bytes (2 GB) minimum is 1 |
| binary(bytes) | Fixed-length binary data (size must be between 1 and 8000 bytes) | 8,000 bytes | |
| varbinary(bytes) | Variable-length binary data (size must be between 1 and 8000 bytes) | 8,000 bytes + 2 bytes |
|
| bit | Integer that can be 0, 1, or NULL | ||
| tinyint | Whole numbers from 0 to 255 | 1 byte | |
| int | Whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes | |
| bigint | Whole numbers between -(263)-1 to 2^63-1 | 8 bytes | |
| decimal(p,s) | Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1. |
5-17 bytes | |
| numeric(p,s) | Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1. |
5-17 bytes | |
| smallmoney | Monetary data from -214,748.3648 to 214,748.3647 | 4 bytes | |
| money | Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes | |
| float(n) | Floating precision number data from -1.79e308 to 1.79e308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53. float is used to store approximate values, not exact values. |
4 or 8 bytes | |
| real | Floating precision number data from -3.40e38 to 3.40e38 | ||
| datetime | From 1st-January-1753 to 31-December-9999 with an accuracy of 3.33 milliseconds | 8 bytes | |
| datetime2 | From 1st-January-0001 to 31-December-9999 with an accuracy of 100 nanoseconds | 6-8 bytes | |
| smalldatetime | From 1st-January-1900 to 6th-June-2079 with an accuracy of 1 minute | 4 bytes | |
| date | Stores a date only, from 1st-January-0001 to 31-December-9999 | 3 bytes | |
| time | Stores a time only, to an accuracy of 100 nanoseconds | 3-5 bytes | |
| datetimeoffset | The same as datetime2 with the addition of a time zone offset | 8-10 bytes | |
| timestamp | Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable | ||
| uniqueidentifier | Stores a globally unique identifier (GUID) | 16-byte GUID | |
| xml | Stores XML formatted data. | Maximum 2GB |
Enabing UTF8 support in an existing database may require you to Set or Change the Column Collation.
Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number.
For example, the number 123.45 has a precision of 5 and a scale of 2.
In SQL Server, the default maximum precision of numeric and decimal data types is 38.
The value 7456123.89 will display as follows NUMERIC 7456123.89 NUMERIC(9) 7456124 NUMERIC(9,1) 7456123.9 NUMERIC(*,1) 7456123.9 NUMERIC(9,2) 7456123.89 NUMERIC(6) [not accepted exceeds precision]
Also consider the maximum length of a table name (or column name) and the maximum size of an SQL statement - these limits vary considerably between products and versions.
“For me, I don't think about size, I focus more on being powerful and confident” ~ Simone Biles
Related
CREATE TABLE
Oracle Data Types - Oracle data types for comparison.