SQL Server Datatype
SQL Server Data Types
- Data Type ensures the type of data column or variable will store. For Example, Integers, float, varchar and so on.
- We specify datatype in advance to ensure which type of data can be stored.
Types of Datatypes
Datatypes are categorized as Exact Numeric, Approximate Numeric, Date and time, Character strings, Unicode character strings, Binary strings and other Data types.
- Exact Numeric data types stores exact number. For Example integer, decimal and money. It has its lower and upper limits and memory requirements. This data type is again categorized into nine types:
| Data Type | Lower Range | Upper Range | Storage |
| bigint | -2^63 (-9,223,372,036,854,775,808) | 2^63-1 (9,223,372,036,854,775,807) | 8 bytes |
| Numeric | - 10^38 +1 | 10^38 - 1. | 5 to 17 bytes |
| Bit | 0 | 1 | 1 byte |
| smallint | -2^15(-32,768) | 2^15-1(32,767) | 2 bytes |
| decimal | - 10^38 +1 | 10^38 - 1. | 5 to 17 bytes |
| small money | - 214,748.3648 | 214,748.3647 | 4 bytes |
| int | -2^31 (-2,147,483,648) | 2^31-1 (2,147,483,647) | 4 bytes |
| tinyint | 0 | 255 | 1 byte |
| money | -922,337,203,685,477.5808 | 922,337,203,685,477.5807 | 8 bytes |
- Approximate numeric data types store floating-point and real values. It is mainly used for scientific calculation. This is categorized in mainly two:
| Data Type | Lower Range | Upper range | Storage | Precision |
| Float | -1.79E+308 | 1.79E+308 | 4 or 8 bytes | 7 digit |
| Real | -3.40E+38 | 3.40E+38 | 4 bytes |
15 digit |
- Date and Time data types are used for date and time. This data type is again categorized into nine types:
| Data Type | Description | Lower Range | Upper range | Storage |
| date | Its format is YYYY-MM-DD and is used to store dates on the server. | 0001-01-01 |
9999-12-31 |
3 bytes |
| datetime | It defines a date combined with a time of day with fractional seconds | 1753-01-01 | 9999-12-31 | 8 bytes |
| datetime2 | It defines a date combined with a time of day with fractional second and accuracy of 100 nanoseconds. Its format is YYYY-MM-DD hh:mm: ss[.fractional second] | 0001-01-01 00:00:00 | 9999-12-31 23:59:59.9999999 | 6 to 8 bytes |
| datetimeoffset | It is the same as datetime2 with the timezone offset. Timezone offset value is between -14:00 to +14:00 | 0001-01-01 00:00:00 | 9999-12-31 23:59:59.9999999 | 10 bytes |
| smalldatetime | It defines a date combined with a time of day and an accuracy of 1 minute. | 1900-01-01 00:00:00 | 2079-06-06 23:59:59 | 4 bytes |
| time | It defines time data with an accuracy of 100 nanoseconds. | 00:00:00:0000000 |
23:59:59:9999999 |
3 to 5 bytes |
- Character string data type defines the character data type only. This is categorized in mainly three:
| Data Type | Lower Range | Upper Range | Storage |
| char(n) | 0 characters | 8000 characters | n bytes |
| varchar(n) | 0 characters | 8000 characters | n bytes +2 bytes |
| text | 0 characters | 2,147,483,647 characters | n bytes +4 bytes |
- Unicode character string data types define the full range of Unicode character sets encoded in the UTF-16 character set.
| Data Type | Lower Range | Upper Range | Storage |
| nchar | 0 characters | 4000 characters | 2 times n bytes |
| nvarchar | 0 characters | 4000 characters | n bytes +2 bytes |
| ntext | 0 characters | 1,073,741,823 characters | 2 times the string length |
- Binary string data type stores images, audio, and video files of fixed and variable length. It stores in 0 and 1 format
| Data Type | Lower Range | Upper Range |
| binary | 0 characters | 8000 bytes |
| varbinary | 0 characters | 8000 bytes |
| image | 0 characters | 2,147,483,647 bytes |
- Other data types
| Data Type | Description |
| cursor | A data type for variables contains a reference to a cursor. It cannot be used for columns in create table statement. |
| rowversion | It returns an automatically generated unique binary number within a database. |
| hierarchyid | Data type with variable length representing a position in a tree hierarchy. |
| uniqueidentifier | It converts the string into the form of XXXXXXXX-XXXX where x is a hexadecimal digit. |
| XML | It stores XML data. |
| Spatial Geometry types | It is used to represent data in Euclidean(flat) coordinate system. |
| Spatial Geography types | It is used for storing ellipsoidal(round-earth) such as GPS latitude. |
| sql_variant | It stores the value of various SQL Server data types. |
| table | It is used to store the temporary results in the table-valued function. |
