In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, date and time data, binary strings, and so on.
SQL Server supplies a set of system data types that define all the types of data that can be used with SQL Server. You can also define your own data . Here the list of commonly used data types is provided;
| Data type | Description |
| char(n) | Fixed-length character string. Maximum 8,000 characters |
| varchar(n) | Variable-length character string. Maximum 8,000 characters |
| varchar(max) | Variable-length character string. Maximum 1,073,741,824 characters |
| text | Variable-length character string. Maximum 2GB of text data |
Unicode strings:
| Data type | Description |
| nchar(n) | Fixed-length Unicode data. Maximum 4,000 characters |
| nvarchar(n) | Variable-length Unicode data. Maximum 4,000 characters |
| nvarchar(max) | Variable-length Unicode data. Maximum 536,870,912 characters |
| ntext | Variable-length Unicode data. Maximum 2GB of text data |
Binary types:
| Data type | Description |
| bit | Allows 0, 1, or NULL |
| binary(n) | Fixed-length binary data. Maximum 8,000 bytes |
| varbinary(n) | Variable-length binary data. Maximum 8,000 bytes |
| varbinary(max) | Variable-length binary data. Maximum 2GB |
| image | Variable-length binary data. Maximum 2GB |
Number 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 |
| 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.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 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 |
| smalldatetime | From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute | 4 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 |
| 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 | |
Other data types:
| Data type | Description |
| sql_variant | Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp |
| uniqueidentifier | Stores a globally unique identifier (GUID) |
| xml | Stores XML formatted data. Maximum 2GB |
| cursor | Stores a reference to a cursor used for database operations |
| table | Stores a result-set for later processing |
No comments:
Post a Comment