Home » Data types T-SQL

Data types T-SQL

by Online Tutorials Library

Data types T-SQL

Data type in SQL server is an attribute, which generates the data of the object. Each variable, column, and expression is related to data type in T-SQL. The data types will be used when we create the tables. We select a specific data type for the column-based table according to our requirements.

SQL Server has seven categories, including many categories of data types.

Exact Numeric Types

Type From To
Bigint -9,223,372,036,854,775,808 9,223,372,036,854,775,807
Int -2,147,483,648 2,147,483,647
Smallint -32,768 32,767
Tinyint 0 255
Bit 0 1
Decimal -10^38 +1 10^38 -1
Numeric -10^38 +1 10^38 -1
Money -922,337,203,685,477.5808 +922,337,203,685,477.5807
small money -214,748.3648 +214,748.3647

Numeric and decimal are fixed precision and scale data types.

Numeric Type

Type From
Real -3.40E + 38–3.40E + 38
Float -1.79E + 308–1.79E + 308

Date and Time types

Type From– To
datetime(3.3millisecondsaccuracy) January 1, 1753, to December 31, 9999
smalldatetime(1-minute accuracy) January 1, 1900, to June 6, 2079
date(1-day accuracy.) January 1, 1 to December 31, 9999
datetimeoffset(100 nanoseconds accuracy) January 1, 0001 to December 31, 9999
datetime2(100 nanoseconds accuracy) January 1, 0001 to December 31, 9999
time(100 nanoseconds accuracy which is Introduced in SQL Server 2008) 00:00:00.0000000 to 23:59:59.9999999

Character Strings

S. no Type Desc
1 Char It is the Non-Unicode fixed-length character data with a length of 8,000 characters.
2 varchar It is a Non-Unicode variable-length data with a maximum of 8,000 characters.
3 Varchar (max) It is a Non-Unicode variable-length data with a maximum length of 231 characters.
4 text It is a Non-Unicode data with a range of 2,147,483,647 characters.

Unicode Character String

S. no Type Description
1 Nchar It is Fixed-length data with the range of 4,000 characters maximum.
2 nvarchar It is a type of Variable-length Unicode data with a length of 4,000 characters maximum.
3 Nvarchar(max) Nvarchar is the Variable-length with 230 characters (which is Introduced in SQL Server 2005).
4 Ntext Unicode data with a maximum length of 1,073,741,823 characters is variable length.

Binary Strings

S.No type Description
1 Binary Binary is fixed-length data with the length of maximum 8,000 bytes.
2 Varbinary Varbinary binary data is used with a length of 8,000 bytes.
3 Varbinary(max) Varbinary(max) is a Binary data of variable length with a maximum period of 231 bytes (Introduced in SQL Server 2005).
4 Image type In Image type data type, Variable-length of binary data with maximum length of 2,147,483,647 bytes.

Remaining data types used in T-SQL

Timestamp– It stores a vast number of databases. It is updated every time a row is updated.

Sql_variant- It stores the value of most SQL servers, which support the data types except the ntext and timestamp datatype.

Unique identifier– We store XML instances in the column when it saves the XML data.

Table – It saves a result set for processing after some time.

Cursor– Cursor is a reference.

Hierarchy– The data type is a variable-length and used to represent the position in a hierarchy.

Keywords of flow control in T-SQL


IF and ELSE allows the conditional execution. The batch statement will print “This is the weekend,” if the current date is the weekend date, or “this is a weekday,” if the current date is the weekday.

Note: The code assumes that the Sunday is the first day of the week in the @DATEFIRST setting.

BEGIN and END in flow control generates the block of the statement.

Next TopicT-SQL Create Table

You may also like