SQL Data Types
Each column in a database has a data type.
A data type is a set of values. There are different categories for data types:
Approximate numeric: It stores approximations of the numbers based on IEEE 754 standard.
- float(N): Values in the range from -1.79E+308 to 1.79E+308.
- real: Values in the range from -3.40E+38 to 3.40E+38.
Exact numeric: It stores number based data.
- bit: It has domain of 0,1, or NULL.
- smallint: Integers between -32,768 to 32,767.
- tinyint: Integers between 0 to 255.
- int: 4 bytes
- bigint: 8 bytes
- decimal or numeric: All numbers between -10^38-1 to 10^38-1.
- smallmoney: Values from -214,748.3648 to 214,748.3647.
- money: 8 bytes
Character strings: It is used to store text values.
- char(N): Fixed length character data with maximum length of 8,000 characters.
- varchar(N): Variable length character data with maximum length of 8,000 characters.
- varchar(max): Variable length character data up to (2^31)-1 bytes.
Unicode character strings: It is used to store text values. Unicode is a double byte (and in some case triple byte) character set that allows for more than 256 characters at a time.
- nvarchar, nvarchar(max), nchar: Unicode equivalents of varchar, varchar(max), char.
Binary strings: Strings of bits used for storing things like encrypted values, files etc.
- binary(N): Fixed length binary data with maximum length of 8,000 bytes.
- varbinary(N): Variable length binary data with maximum length of 8,000 bytes.
- varbinary(max): Variable length binary data up to (2^31)-1 bytes.
Date and Time: It stores values that deal storing a point in time.
- date: It represents a date. Format: yyyy-mm-dd.
- time(N): It stores a time of day.
- datetime: It points in time from January 1, 1753 to December 31, 9999.
- smalldatetime: It points in time from January 1, 1900 to June 6, 2079.
- datetime2(N): It stores a point in time from January 1, 0001 to December 31, 9999.
- datetimeoffset: It is same as datetime2. It includes an offset for time zone offset.
Other data types:
- sql_variant: It stores nearly any data type other than CLR based ones like spatial types,hierarchyId.
- uniqueidentifier: It stores a globally unique identifier(GUID).
- rowversion: It’s synonym is timestamp. It is used for optimistic locking to version stamp in row.
- XML: It allows you to store an XML document in a column value.
- cursor: Reference to a cursor object.
- Spatial types: It is used for storing spatial data like for maps, lines, shapes etc.
- table: It stores a result set for later processing.
- hierarchyId: It is used to store data about hierarychy.
Keep visiting my site ask2tech.com for upcoming top technical article