Numeric Data Types
| Type name | Type Description |
| BIT | Stores a value from 0 to 16
It can act as an analogue of a boolean type in programming languages (in this case, the value of true corresponds to 1, and the value of false corresponds to 0) For values up to 8 (inclusive) takes 1 byte. For values from 9 to 16 – 2 bytes |
| TINYINT | Stores numbers from 0 to 255
Good for storing small numbers. Take 1 bytes |
| SMALLINT | Stores numbers from -32768 to 32767
Will be enough to store most numbers Take 2 bytes |
| INT | Stores numbers from -2,147,483,648 to 2,147,483,647
The most used type for storing numbers. Take 4 bytes |
| BIGINT | Stores numbers from -9 223 372 036 854 775 808 to 9 223 372 036 854 775 807
Used for very large numbers. Take a lot of memory Take 8 bytes
|
| DECIMAL | Stores fixed-precision numbers
This type can take two parameters precision and scale: DECIMAL(precision, scale). The precision parameter represents the maximum number of digits that the number can store. This value must be between 1 and 38. The default is 18. The scale parameter represents the maximum number of digits that a number after the decimal point can contain. This value must be between 0 and the value of the precision parameter. It defaults to 0. Used for numbers with commas Example: CREATE TABLE dbo.Patients ( Name varchar(10), Gender varchar(2), Height decimal (3,2), Weight decimal (5,2) ) Takes from 5 to 17 bytes, depending on the number of numbers after the decimal point. |
| NUMERIC | This type is similar to the DECIMAL type |
| SMALLMONEY | Stores fractional values from -214 748.3648 to 214 748.3647
Equivalent to DECIMAL (10.4). Created for storing currency Take 8 bytes |
| MONEY | Stores fractional values from -922 337 203 685 477.5808 to 922 337 203 685 477.5807
Equivalent to DECIMAL (19.4). Created for storing currency Take 8 bytes |
| FLOAT | Stores numbers from -1.79E+308 to 1.79E+3087
May be defined as FLOAT(n), where n represents the number of bits that are used to store the decimal part of the number (the mantissa). By default n = 53. Designed for maximum values\ Takes 4 to 8 bytes |
| REAL | Stores numbers from -340E+38 to 3.40E+38.
Equivalent to the FLOAT(24) type. Takes 4 bytes. |
Date and time
| Type name | Type Description |
| DATE | Stores dates from 0001-01-01 (January 1, 0001) to 9999-12-31 (December 31, 9999)
Used to set the date Takes 3 bytes. |
| TIME | Stores time in the range from 00:00:00.0000000 to 23:59:59.9999999
Used to set the time Takes 3 to 5 bytes |
| DATETIME | Stores dates and times from 01/01/1753 to 12/31/9999
Used to set the date (registration, post creation, etc.) Takes 8 bytes. |
| DATETIME2 | stores dates and times in the range from 01/01/0001 00:00:00.0000000 to 12/31/9999 23:59:59.9999999
Used to set the date (registration, post creation, etc.) Takes from 6 to 8 bytes depending on the time precision. |
| SMALLDATETIME | Stores dates and times in the range from 01/01/1900 to 06/06/2079, that is, the nearest dates.
Used to set the date (registration, post creation, etc.) More economical but with less interval Takes 4 bytes. |
| DATETIMEOFFSET | Stores dates and times in the range 0001-01-01 to 9999-12-31
Stores detailed time information accurate to 100 nanoseconds. Used to store accurate data Takes 10 bytes. |
Date formats:
yyyy-mm-dd- 2017-07-12
dd/mm/yyyy – 12/07/2017
mm-dd-yy – 07-12-17
In this format, two-digit numbers from 00 to 49 are treated as dates in the range 2000-2049. And the numbers from 50 to 99 as a range of numbers 1950 – 1999.
Month dd, yyyy – July 12, 2017
Time formats:
hh:mi – 13:21
hh:mi am/pm – 1:21 pm
hh:mi:ss – 1:21:34
hh:mi:ss:mmm – 1:21:34:12
hh:mi:ss:nnnnnnn – 1:21:34:1234567
String data
| Type Name | Type Description |
| CHAR | Stores a string from 1 to 8,000 characters long
!Not suitable for many languages because it stores non-Unicode characters. !The number of characters a column can store is passed in parentheses. For example, for a CHAR(10) column, 10 bytes will be allocated. And if we store a string of less than 10 characters in a column, then it will be padded with spaces. Used for logins, names 1 symbol takes 1 byte |
| VARCHAR | Stores a string
VARCHAR(MAX) if string have more 8000 sumbols You can specify a specific length for a column – from 1 to 8,000, for example, VARCHAR(10) !Not suitable for many languages because it stores non-Unicode characters. !Unlike the CHAR type, if a 5-character string is stored in a VARCHAR(10) column, exactly five characters will be stored in the column. Most commonly used for string type 1 symbol takes 1 byte VARCHAR(MAX) up to 2 GB |
| NCHAR | Stores a Unicode string between 1 and 4,000 characters long
You can specify a specific length for a column. Example NCHAR(15) Used for strings where Unicode support is required 1 symbol takes 2 byte |
| NVARCHAR | Stores a Unicode string between 1 and 4,000 characters long
1 symbol takes 2 byte NVARCHAR(MAX) up to 2GB |
| TEXT | Outdated
Replaced by NCHAR |
| NTEXT | Outdated
Replaced by NVARCHAR |
Binary data types
| Type Name | Type Description |
| BINARY | Stores binary data as a sequence from 1 to 8,000 bytes |
| VARBINARY | Stores binary data as a sequence from 1 to 8,000 bytes
VARBINARY(MAX) takes 2^31–1 byte |
| IMAGE | Outdated
Recommend replace by VARBINARY |
Other data types
| UNIQUEIDENTIFIER | Stores a string GUID with a unique value
Takes 16 bytes |
| TIMESTAMP | Stores a number that stores the version number of the row in the table
Takes 8 bytes |
| CURSOR | string set |
| HIERARCHYID | position in the hierarchy |
| SQL_VARIANT | Сan store data of any other data type |
| XML | Stores XML documents or fragments of XML documents
Takes up to 2GB |
| TABLE | Represents the table definition. |
| GEOGRAPHY | stores geographic data such as latitude and longitude |
| GEOMETRY | stores the coordinates of the location on the plane. |