DATA TYPES
The data type of a column defines what value the column can hold: integer, character, money, date and time, binary, and so on.
SQL Data Types
Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each column when creating a table. The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data.
SQL Server Data Types
String data types:
Data type | Description | Max size | Storage |
char(n) | Fixed width character string | 8,000 characters | Defined width |
varchar(n) | Variable width character string | 8,000 characters | 2 bytes + number of chars |
text | Variable width character string | 2GB of text data | 4 bytes + number of chars |
Number data types:
Data type | Description | Storage |
tinyint | Allows whole numbers from 0 to 255 | 1 byte |
smallint | Allows whole numbers between -32,768 and 32,767 | 2 bytes |
int | Allows whole numbers between -2,147,483,648 and 2,147,483,647 | 4 bytes |
bigint | Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 | 8 bytes |
decimal(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 | 5-17 bytes |
numeric(p,s) | Fixed precision and scale numbers. Allows numbers from -10^38 +1 to 10^38 –1. The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18. The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0 | 5-17 bytes |
float(n) | Floating precision number data from -1.79E + 308 to 1.79E + 308. 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. | 4 or 8 bytes |
real | Floating precision number data from -3.40E + 38 to 3.40E + 38 | 4 bytes |
Date data types:
Data type | Description | Storage |
datetime | From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds | 8 bytes |
datetime2 | From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds | 6-8 bytes |
date | Store a date only. From January 1, 0001 to December 31, 9999 | 3 bytes |
time | Store a time only to an accuracy of 100 nanoseconds | 3-5 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 |
Last updated