SQL Data Types
Data type is type of data or it is a predefined keyword which specifies that, variable hold which types of value. In SQL server there are lots of data such as SQL server data type and user define data types. For describing brief here we have categorized data types in some category which are namely as follows:
· Exact Numeric
· Approximate Numeric
· Date Time
· Character String
· Unicode Character String
· Binary String
· Other Data types
Let’s see brief description of above category data types.
Exact Numeric data types:
Exact Numeric data types store numeric values where you wish to specify the precision of the variable. They may include integer or decimal numbers and are the most common category of data type used for numeric information. There are lots of exact numeric data types in which some are given as follows:
· bigint: Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).
· int: Integer (whole number) data from -2^31 (-2,147,483,648) to 2^31 - 1,147,483,647).
· smallint: Integer data from -2^15 (-32,768) to 2^15 - 1 (32,767).
· tinyint: Integer data from 0 to 255.
· bit: Integer data with either a 1 or 0 value.
· decimal: Fixed precision and scale numeric data from -10^38 +1 to 10^38 –1.
· money: Monetary data values from -2^63 (-922,337,203,685,477.5808) to 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
· smallmoney: Monetary data values from -214,748.3648 to +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Example: Demonstration of some exact SQL data types
------DEMONSTRATION OF EXACT DATA TYPES-----
CREATE TABLE TEST_EXACT
------INSERT QUERY TO INSERT DATA INTO TABLE-----------
INSERT INTO TEST_EXACT VALUES( 10145,0,850.15,9635)
SELECT * FROM TEST_EXACT
Approximate Numeric data types:
Approximate numeric 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.
There is lots of approximate numeric available in SQL server in which some are given as follows:
· float: Floating precision number data with the following valid values: -1.79E + 308 to -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
· real: Floating precision number data with the following valid values: -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
· double: A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter.
Example: Demonstration of approximate numeric SQL data types
----DEMONSTRATION OF APPROXIMATE DATA TYPES----
CREATE TABLE TEST_APPROXIMATE
---INSERT QUERY TO INSERT DATA INTO TABLE ----
INSERT INTO TEST_APPROXIMATE VALUES( 152,546)
INSERT INTO TEST_APPROXIMATE VALUES( 152.145896532,546.258741)
SELECT * FROM TEST_APPROXIMATE
Date Time data types:
Date time data type allows the storage of timestamp. It is an important data type which plays an important role in SQL server.
Date Time category has only two main data type namely:
· datetime: Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of three-hundredths of second or 3.33 milliseconds.
· Smalldatetime: Date and time data from January 1, 1900, to June 6, 2079, with an accuracy of one minute.
· datetime2: From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds.
· date: Store a date only. From January 1, 0001 to December 31, 9999.
· time: Store a time only to an accuracy of 100 nanoseconds.
· datetimeoffset: The same as datetime2 with the addition of a time zone offset.
Example: Demonstration of DateTime in SQL date types
------DEMONSTRATION OF DATE TIME DATA TYPES-----
CREATE TABLE TEST_DATETIME
------INSERT QUERY TO INSERT DATA INTO TABLE-----
INSERT INTO TEST_DATETIME VALUES (GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())
SELECT * FROM TEST_DATETIME
Character String Data Types:
Character string data types play an important role in SQL server, basically character string data types are used to store text values in Microsoft SQL Server databases.
There are lots of character string data types in SQL server which are namely as follows:
· char: Char data types store variables in fixed-length character strings. They are limited to 8,000 characters in size.
· varchar: In varchar data types variables store non-fixed length character strings consisting of approximately n characters. They are limited to 8,000 characters in size.
· varchar(max): In varchar(max) data types variables store non-fixed length character strings consisting of up to 1,073,741,824 characters.
· nvarchar(max): In nvarchar(max) variables store non-fixed length Unicode character strings consisting of up to 536,870,912 characters.
· text: In text data types variables store up to 2GB of text data Therefore, they are usually only used to support legacy applications and have been replaced by the varchar(max) and nvarchar(max) data types.
Example: Demonstration of Character String in SQL date types
---- DEMONSTRATION OF CHARACTER STRING DATA TYPES IN SQL
CREATE TABLE TEST_CHARACTER
INSERT INTO TEST_CHARACTER VALUES ('A','CALIFORNIA US','HELLO TEXT DATA TYPE IN SQL SERVER')
SELECT * FROM TEST_CHARACTER
Unicode Character String data types:
Unicode character string data types are used to store Unicode character strings. Unicode require 2 bytes for each character to store, This is important because of the row size limitations of SQL Server is the same as the page size limit, which is 8060 bytes.
There are lots of Unicode character string data types in SQL server in which some important data types are given as follows.
· nchar: In nchar data types 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.
· nvarchar: In nvarchar data types 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.
· ntext: Unicode character strings with a variable length up to 2^31-1 (2,147,483,647) bytes. ntext is equivalent to nvarchar(max), ntext has a synonym of NATIONAL TEXT.
Difference between varchar and nvarchar:
Varchar means Variable-length Character string. Nvarchar will store Unicode characters. Both will be used all most for the same purpose but with little difference. Varchar will store the 8-bit data in database where as Nvarchar will be stored as 16-bit data in Database.
Maximum size of a VARCHAR is 8,000 bytes. Varchar stores ASCII data. Nvarchar is identical to VARCHAR but it supports two-byte characters. Simply Nvarchar stores Unicode data and takes twice space than Varchar.
Binary String Data types:
Binary strings data types are used to store binary string. Binary data types allow you to store any type of binary data, including entire files of up to 2GB. There are lots of binary String data types in SQL server in which some are given as follows:
· binary: Binary data types are used to variables store n bytes of fixed-size binary data. They may store a maximum of 8,000 bytes.
· varbinary: Varbinary data types are used to variables store variable-length binary data of approximately n bytes. They may store a maximum of 2 gigabytes.
· image: Image data types are used to 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:
There are lots of data types in which some data types are categorized in Other Data types which contain following data types.
· XML: The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them.
There is some limitation to use xml data type in SQL server which is given as:
· The stored representation of xml data type instances cannot exceed 2 GB.
· It cannot be used as a subtype of a sql_variant instance.
· It does not support casting or converting to either text or ntext. Use varchar(max) or nvarchar(max) instead.
· It cannot be compared or sorted. This means an xml data type cannot be used in a GROUP BY statement.
· It cannot be used as a parameter to any scalar, built-in functions other than ISNULL, COALESCE, and DATALENGTH.
· It cannot be used as a key column in an index. However, it can be included as data in a clustered index or explicitly added to a non-clustered index by using the INCLUDE keyword when the non-clustered index is created.
· Sql_varriant: You can use the sql_variant to store data of an unspecified or inconsistent type or to store data of almost any SQL Server data type. A sql_variant can hold any data type except text, ntext, image, and timestamp without conversion.
Example: Demonstration of sql_variant data types in SQL server
----DEMONSTRATION OF SQL_VARAINT DATA TYPES IN SQL SERVER
DECLARE @SQL_VARRIANT1 SQL_VARIANT
DECLARE @SQL_VARRIANT2 SQL_VARIANT
DECLARE @ID INT
DECLARE @NAME VARCHAR(20)
SET @ID = 101
SET @NAME = 'ARUN SINGH'
SELECT @SQL_VARRIANT1 = @ID ,
@SQL_VARRIANT2 = @NAME
SELECT @SQL_VARRIANT1 AS INT_VARIANT,
@SQL_VARRIANT2 AS VARCHAR_VARIANT
· 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.