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.