List of Data Types in SQL Server Transact-SQL

Q

How many data types supported in SQL Server Transact-SQL? I want a list of all data types.

✍: FYIcenter.com

A

Here is a list of all Transact-SQL data types divided into 6 categories:

1. Exact Number - Used to hold numeric values truncated to a specific precision and scale.

  • BIGINT - Used to hold big integers.
  • INT (or INTEGER) - Used to hold normal integers.
  • SMALLINT - Used to hold small integers.
  • TINYINT - Used to hold tiny integers.
  • BIT - Used to hold single bit values.
  • DECIMAL (or NUMERIC) - Used to hold values with different precisions and scales.
  • MONEY - Used to hold large currency values.
  • SMALLMONEY - Used to hold small currency values.

2. Approximate Number - Used to hold numeric values with floating scales.

  • DOUBLE PRECISION - Use to hold values with a large precision and floating scales.
  • REAL - Use to hold values with a normal precision and floating scales.
  • FLOAT - Used to hold values with different precisions and floating scales.

3. Data and Time - Used to hold dates and times.

  • DATETIME - Use to hold dates and times with a large precision.
  • SMALLDATETIME - Use to hold dates and times with a small precision.
  • DATE - Use to hold dates.
  • TIME - Use to hold times.
  • DATETIMEOFFSET - Use to hold dates and times with timezone offsets.
  • DATETIME2 - Use to hold dates and times with extended range and scales.

4. Single-Byte Character Strings - Used to hold ASCII character strings.

  • CHAR (or CHARACTER) - Used to hold single-byte character strings of a fixed length.
  • VARCHAR (or CHAR VARYING, or CHARACTER VARYING) - Use to hold single-byte character strings of variable lengths.
  • TEXT (or VARCHAR(MAX)) - Use to hold single-byte character strings of very large lengths.

5. Unicode Character String - Used to hold Unicode character strings.

  • NCHAR (or NATIONAL CHAR, or NATIONAL CHARACTER) - Used to hold Unicode character strings of a fixed length.
  • NVARCHAR (or NATIONAL CHAR VARYING or NATIONAL CHARACTER VARYING) - Use to hold Unicode character strings of variable lengths.
  • NTEXT (or NVARCHAR(MAX), or NATIONAL TEXT) - Use to hold Unicode character strings of very large lengths.

6. Binary String - Used to hold binary strings.

  • BINARY - Used to hold binary strings of a fixed length.
  • VARBINARY (or BINARY VARYING) - Use to hold binary strings of variable lengths.
  • IMAGE - Use to hold binary strings of very large lengths.

7. Others - Used to hold other special types of data.

  • CURSOR - Used to hold pointers to scroll through query result.
  • SQL_VARIANT - Used to hold values of different data types.
  • TABLE - Used to hold tables with values stored in rows and columns.
  • UNIQUEIDENTIFIER - Used to hold unique identifiers generated by the system.
  • TIMESTAMP - Used to hold binary sequence identifiers to track table modifications.
  • XML - Used to hold XML documents.
  • HIERARCHYID - Used to hold positions in a hierarchy.

By the way, Transact-SQL supports the same list of data types as the SQL Server table columns.

For more information, see "Data Types (Transact-SQL)" at Microsoft Website.

 

Variables and Data Types in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-04-22, 275👍, 0💬