SQL Data Types
SQL Data Types
Selecting appropriate data types is one of the most important considerations when designing a SQL Server database. Choices you make in the database design phase may have a significant impact on the efficiency, performance and storage requirements of your database. SQL data type is an attribute that specifies type of data of any object. Each column, variable and expression has related data type in SQL.
SQL provides six categories of data types, they are
• Exact numeric data types
• Approximate numeric data types
• Data and Time data types
• Character string data types
• Binary data types
• Other data types
Exact numeric data types:
The exact numeric data types are the most common SQL Server data types used to store numeric information. The approximate numeric data types provide less precision and are less commonly used. This numeric data types store numeric values where you wish to specify the precision of the variable. They may include integer or decimal numbers.
Data types in the exact numeric category are,
• int variables store 4-byte whole numbers ranging from -2,147,483,648 to 2,147,483,647.
• bigint variables store 8-byte whole numbers ranging from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
• smallint variables store 2-byte whole numbers ranging from -32,768 to 32,767.
• tinyint variables store 1-byte whole numbers ranging from 0 to 255.
• decimal and numeric variables are functionally equivalent and store numbers of fixed precision and scale. Precision indicates the maximum number of digits that may be stored (including those before and after the decimal point. Scale indicates the number that may be stored to the right of the decimal point.
• money variables store 8-byte currency values ranging from -922,337,203,685,477.5808 to 922,337,203,685,477.5807. They may reflect any currency type.
• smallmoney variables store 4-byte currency values ranging from -214,748.3648 to 214,748.3647. They may reflect any currency type.
Approximate numeric data types:
The approximate numeric data types are not as commonly used as other SQL Server data types used to store numeric information. This data types are less precise than exact numeric data types. They allow for the specification of the number of digits to store precisely while the remainder of a variable’s value is subject to rounding error.
Data types in the approximate numeric category are,
• float variables store 4-byte or 8-byte floating point numbers. They are specified as float (p), where p is 24 for a 4-byte number and 53 for an 8-byte number.
• real variables store 4-byte floating point numbers and are functionally equivalent to float (24) variables.
Date and Time data types:
These new data types allow designers to easily work with time zones, dates without times and vice versa and dates in ancient history and far into the future. This data types allow the storage of timestamps.
Data types in the date and time category are,
• date time variables store 8-byte time and date values ranging from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds.
• datetime2 variables use between 6-8 bytes to store dates and times between January 1, 0001 and December 31, 9999 with an accuracy of 100 nanoseconds.
• smalldatetime variables store 4-byte time and date values ranging from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute.
• date variables use 3 bytes to store a date only (with no time information) in the range January 1, 0001 through December 31, 9999.
• time variables use between 3-5 bytes to store a time only (with no date information) to an accuracy of 100 nanoseconds.
• datetimeoffset variables store the date and time using between 8-10 bytes. The values stored are the same as those stored by the datetime2 data type with the addition of a time zone offset.
• timestamp variables are automatically populated by SQL Server with the time that a row is 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.
Character String data types:
Character string data types are used to store text values in Microsoft SQL Server databases.
Data types in the character string category are,
• char(n) variables store fixed-length character strings consisting of exactly n characters (and, therefore, n bytes). They are limited to 8,000 characters in size.
• nchar(n) variables store fixed-length Unicode character strings consisting of exactly n characters (and, therefore, 2*n bytes). They are limited to 4,000 characters in size.
• varchar(n) variables store non-fixed length character strings consisting of approximately n characters. They consume l+2 bytes of space, where l is the actual length of the string. They are limited to 8,000 characters in size.
• nvarchar(n) variables store non-fixed length Unicode character strings consisting of approximately n characters. They consume 2*l+2 bytes of space, where l is the actual length of the string. They are limited to 4,000 characters in size.
• varchar(max) variables store non-fixed length character strings consisting of up to 1,073,741,824 characters. They consume l+2 bytes of space, where l is the actual length of the string.
• nvarchar(max) variables store non-fixed length Unicode character strings consisting of up to 536,870,912 characters. They consume l*2+2 bytes of space, where l is the actual length of the string.
• text and ntext variables store up to 2GB of text data (ANSI and Unicode, respectively), but cannot be used in many text operations. Therefore, they are usually only used to support legacy applications and have been replaced by the varchar (max) and nvarchar (max) data types.
Binary data types:
Binary data types allow you to store any type of binary data, including entire files of up to 2GB.
Data types in the binary category are,
• bit variables store a single bit with a value of 0, 1 or NULL.
• binary(n) variables store n bytes of fixed-size binary data. They may store a maximum of 8,000 bytes.
• varbinary(n) variables store variable-length binary data of approximately n bytes. They may store a maximum of 8,000 bytes.
• varbinary(max) variables store variable-length binary data of approximately n bytes. They may store a maximum of 2 gigabytes.
• image variables store up to 2 gigabytes of data and are commonly used to store any type of data file not just images.
Other data types:
Other data types allow for the storage of unique identifiers, cursors, tables and XML.
Data types in the binary category are,
• cursor variables store references to cursors used for database operations. A table may not contain a cursor variable.
• sql_variant variables store up to 8,000 bytes of data from any SQL Server data type other than varchar(max), nvarchar(max), text, image, sql_variant, varbinary(max), xml, ntext and timestamp.
• table variables store temporary tables used during database operations. SQL Server database tables may not contain variables of type table.
• xml variables store XML formatted data. They may store a maximum of 2 gigabytes.
• unique identifier variables store 16-bit globally unique identifiers. They may be instantiated with a new GUID using the NEWID function.