SQL: DATA TYPE
When we defined columns in tables, parameters in procedures and functions, variables in T-SQL batches, we have to choose appropriate data type for those.
- The data type contains the data that is supported. SQL server has an extensive set of data type that we can choose to match any need.
- Exact Numeric Data type
- Approximate Numeric Data type
- Date and Time Data Type
- Non-Unicode character string Data type
- Unicode character string Data type
- Binary Data type
- Other Data type
- The important aspect for selecting the proper data type is to remember that a type is a constraint. It has a certain domain of supported values and does not allow values outside the domain.
Exact Numeric Data Type: The exact numeric data types are bit, tinyint, smallint, int, bigint, decimal, money, smallmoney.
- Bit: It can take a value of 1, 0, NULL. Usually used as a pseudo-boolean by using 1=true, 0=false, NULL=unknown. It’s size in bytes is 1 byte.
- Tinyint: It’s range is between 1 to 255 and size is 1 byte.
- Smallint: It’s range is between -32,768 to 32,768 and size is 2 bytes.
- Int : It’s range is between -2,147,483,648 to 2,147,483,648 and size is 4 bytes.
- Bigint: It’s range is between -9,222,372,036,854,775,808 to 9,222,372,036,854,775,807 and size is 8 bytes.
- Decimal(or Numeric which are the same but decimal is standard): decimal(p, s) where p is precision and s is scale. it’s size is between 5 and 17 bytes.
Declare @d as decimal(6,3)
Explanation: decimal(6,3) is a number that has 3(p-s) digits before the decimal and 3 digit after the decimal.
- Money: It’s range is between -922,337,203,685,477 ,5808 through 922,337,203,685,477,5808 and size is 8 bytes.
- Smallmoney: It’s range is between -214,748,3648 through 214,748,3648 and size is 4 bytes.
Approximate Numeric Data type: The approximate numeric data type contains float, real.it allows to store large range of values with a high amount of precision.
- Float(n): floating point numeric data. N is the number of bits that is used to store the mantissa of the float number. The range of n is 1 to 53. The default value of n is 53.
- Real: The real is an ISO synonym for float(24) data type and it’s size is 4 bytes and range is -3.40E+308 through 3.4+308.
Date and Time Data type:The Data and time data type contains date, time, datetime2, datetimeoffset, smalldatetime, datetime.
- Date: The date data type contains only date not time. The range of date is January 1,0001, to December 31,9999. And the size of date is 3 bytes.
- Time: It contains time of day only with fractional part of second that can be stored. And size is 3 to 5 bytes. time(6) is HH:MM:SS.000001.
- Datetime2: This type store a date with time. The range is from January 1,0001, to December 31,9999. And size is 6 to 8 bytes.
- Smalldatetime: The range is from January 1,1900, to June 6,2079 with accuracy to 1 minute. It’s size is 4 bytes.
- Datetimeoffset: it is same as datetime2, but it also includes an offset for timezone. It’s size is 8 to 10 bytes.
- Datetime: The range is from January 1,1753, to December 31,9999 and accuracy to 3.33 minute and size is 8 bytes.
Binary Data:Strings of bits used for storing files, encrypted values etc.. It contains Binary, varbinary, varbinary(max).
- Binary(N): it store fixed length binary data with maximum value of N is 8000.
- Varbinary(N): it store variable length binary data with maximum value of N is 8000.
- Varbinary(max): it store variable length binary data up to(2^31)-1 bytes long.
Character(or string) data: String are used to store text values. Storage is specified in number of characters in the string. The character includes char(n), varchar(n), varchar(max), nchar, nvarchar, nvarchar(max).
- Char(N): fixed-length character data up to 8000 characters. When using fixed length data type, it is best if the values in the column are the same.
- Varchar(N): Variable-length character data up to 8000.
- Varchar(max): Variable-length character data type up to(2^31) -1 bytes long.
- nchar, nvarchar, nvarchar(max): It stores Unicode equivalents of char, varchar and varchar(max).
other data type: It contains some more data type like sql_variant, rowversion (timestamp), uniqueidentifier.
- sql_variant: stores nearly any data type, other than CLR based like heirachyId, spatial types.
- rowversion(timestamp): used for optimistic locking to version-stamp in a row. The values are based on data type changes on every modification of the rows.
- uniqueidentifier: stores a globally unique identifier(GUID)value. GUID is a commonly used data type for artificial key.
- XML: allows to store xml document in a column value. The xml datatype gives a rich set of functionality.
- Spatial types: used for storing spatial data like shapes, maps, lines etc..
- heirarchyId: used to store data about a hierarchy along with methods for manipulating the hierarchy.
Keep Visiting TechwithR.com for more upcoming top technical articles.