T-SQL: Here's What You Need to Know

T-SQL or Transact SQL is the query language specific to the Microsoft SQL Server product. It can help perform operations like retrieving the data from a single row, inserting new rows, and retrieving multiple rows. It is a procedural language that is used by the SQL Server.

What Is T-SQL?

T-SQL is a procedural language used by Microsoft in SQL Server. It adds declared variables, transaction control, error and exception handling, and row processing to SQL. The syntax of the T-SQL is distinct from PL-SQL. However, it has the same functionality and gives similar results to other languages.

Business Analyst Master's Program

Gain expertise in Business analytics toolsExplore Program
Business Analyst Master's Program

Features of T-SQL

T-SQL provides some unique features to the users.

  • T-SQL has a BULK INSERT statement that allows users to import a file into the database table or view in a user-specified format.
  • T-SQL provides various support functions for string processing and data processing.

Types of Function in T-SQL

There are four types of functions in T-SQL.

  • Aggregate Functions: It operates on a collection of values but returns a single value.
  • Ranking Function: It returns a ranking value for each partitioning row.
  • Rowset Functions: It returns an object that can be used in a place of table reference in the SQL statement.
  • Scalar Functions: It operates on a single value and returns a single value.

Data Types in T-SQL

String Data Types:

Data type

Description

Max size

char(n)

Fixed length non-Unicode characters

8,000 characters

varchar(n)

Variable width character string 

8,000 characters

varchar(max) 

Variable width character string

1,073,741,824 characters

text

Variable width character string

2GB of text data

nchar

Fixed width Unicode string 

4,000 characters

Numeric Data Types:

Data Types

Description 

Storage

bit

The integer that can be 0, 1, or NULL

tinyint

Allows whole numbers from 0 to 255

1 byte

smallint

Allows whole numbers between -32,768 and 32,767

2 bytes

int

Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,854,775,807

4 bytes

real

Floating precision number data from -3.40E + 38 to 3.40E + 38

4 bytes

Date and Time data types:

Data type

Description

Storage

datetime

From January 1, 1753, to December 31, 9999 with an accuracy of 3.33 milliseconds

8 bytes

datetime2

From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds

6-8 bytes

date

Store a date only. From January 1, 0001 to December 31, 9999

3 bytes

time

Store a time only to an accuracy of 100 nanoseconds 

3-5 bytes

timestamp

Stores a unique number that gets updated every time a row gets created or changed. It bases the timestamp value upon an internal clock and does not correspond to real-time.

Introduction To Data Analytics (FREE)

Mastery to Data Analytics Basics is a Click Away!Start Learning
Introduction To Data Analytics (FREE)

Create a Table in T-SQL

The CREATE TABLE statement is used to create a new table in the database.

Syntax: 

CREATE TABLE table_name

  column1 datatype [ NULL | NOT NULL ],

  column2 datatype [ NULL | NOT NULL ],

  ...

);

The following example will create a table ‘employees’ that contains four columns.

Example:

CREATE TABLE employees

( employee_id INT NOT NULL,

  last_name VARCHAR(50) NOT NULL,

  first_name VARCHAR(50),

  city VARCHAR(50)

);

Insert records in T-SQL

The INSERT INTO statement is used to insert a new record in T-SQL.

Syntax:

INSERT INTO table

(column1, column2, ... )

VALUES

(expression1, expression2, ... ),

(expression1, expression2, ... ),

...;

Example:

INSERT INTO employees

(employee_id, last_name, first_name)

VALUES

(01, 'Jack', 'Brown');

Select records in T-SQL

The T-SQL SELECT statement is used to retrieve the records from the table.

Syntax:

SELECT column1, column2, ...

FROM table_name;

Example:

SELECT employee_id, first_name 

FROM employees;

If you want to select all the columns available in the table, use the following syntax:

SELECT * FROM table_name;

Example:

SELECT * FROM employees;

Comparison Chart

T-SQL

SQL

T-SQL is a procedural extension used by SQL Server

A domain-specific language used in programming and created for managing data held in RDBMS

Stands for Transact Structured Query Language

Stands for Structured Query Language

Developed by Microsoft

Developed by IDM

T-SQL writes a program in such a way that all commands are submitted to the server in a single go

SQL queries submitted individually to the database server

It is not possible to embed T-SQL into SQL

It is possible to embed SQL into T-SQL

Gain expertise in the latest Business analytics tools and techniques with the Post Graduate Program in Business Analysis. Enroll now!

Conclusion

SQL is a base programming language, while T-SQL is derived from SQL. The choice of which database to use will depend on your project requirements.

If you wish to learn more about SQL, then check out our SQL certification course. The course will equip you with all you need to work with SQL databases and use them in your applications. From structuring your database correctly to authoring efficient SQL statements and clauses, managing your SQL database for scalable growth, and getting excellent work-ready training on SQL, it has a multitude of applications at work.

If you have any questions, please feel free to ask them in our comments section, and our experts will promptly answer them for you!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.