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.

IF DATEPART (DW, GETDATE ()) = 7 OR DATEPART (DW, GETDATE ()) = 1
PRINT 'This is the weekend.' /if GETDATE =7 then
ELSE
PRINT 'This is a weekday.' /otherwise

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

Next TopicT-SQL Create Table

← prev next →