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
It has BEGIN and END, BREAK, CONTINUE, GOTO, IF-ELSE, WAITFOR, RETURN, and WHILE keywords.
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.
|