What is Transact SQL (T-SQL) and Its Type of Functions?

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.

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore 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.

Learn more core software topics in our Caltech Coding Bootcamp, designed in collaboration with Caltech and IBM to help you get a good boost in your career.

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:

Numeric Data Types:

Date and Time data types:

Learn the Ins & Outs of Software Development

Caltech Coding BootcampExplore Program

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;

Here's How to Land a Top Software Developer Job

Full Stack Developer - MERN StackExplore Program

Here's How to Land a Top Software Developer Job

Full Stack Developer - MERN StackExplore Program

Comparison Chart

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!