What is T-SQL?

T-SQL (Transact-SQL) is an extension to Structured Query Language, which is just as well used to interact with relational databases. It expands the capabilities of SQL by delivering procedural programming, local variables, a variety of support functions, as well as enhanced DELETE and UPDATE statements. T-SQL is widely used in SQL Server environments. For instance, communication between an app and a SQL Server instance involves sending T-SQL statements to the server.

To set up effective daily work with Transact-SQL, you need specialized database tools such as SQL Complete, an SSMS add-in for code completion, formatting, and refactoring. Not only does it provide full support for T-SQL, but it also makes your routine coding several times faster and far more accurate.

The benefits of using T-SQL

Now we'd like to focus on the general advantages of T-SQL that secured its reputation as a popular solution for SQL Server databases.

Speed

Fast processing of queries—even those
that involve large volumes of data

Performance

Server traffic minimization due to data processing with minimum overhead

Security

Secure data access and transactions

Simplicity

Even complex logic in T-SQL is rather easy to read and understand

What are the differences between T-SQL and other languages?

Every language has its advantages all right, but what about the characteristics that make Transact-SQL different from other SQL-based languages and database systems? Let's have a look at the three most commonplace examples.

T-SQL vs SQL
T-SQL vs MySQL PL
T-SQL vs PL/SQL

T-SQL vs SQL

The main differences between SQL and T-SQL are as follows:

SQL is designed as a data definition (DDL) and a data manipulation language (DML). T-SQL is a Control-of-Flow extension; some of its commands define the order in which statements are executed in a stored procedure or command batch.
SQL is standardized for use in multiple relational database systems, including SQL Server, Oracle, MySQL, and PostgreSQL. Meanwhile, T-SQL is compatible with SQL Server and Azure SQL only.
SQL statements are processed one at a time, while T-SQL processes your code as a block, in a logical, well-structured, procedural way.
Finally, T-SQL has a few keyword differences and offers functions that are absent from the regular SQL.

T-SQL vs MySQL PL

Like we said previously, T-SQL is generally associated with SQL Server, which is, like MySQL, a relational database management system with SQL at its core. That's why a brief overview of both makes sense.

Microsoft SQL Server is a commercial relational DBMS supporting Windows and Linux platforms. It offers a highly developed infrastructure and a multitude of services.
MySQL is an open-source relational DBMS that supports multiple platforms. It is noted to have a bit more complicated syntax, but overall, they deliver a similar level of speed and performance.
The crucial advantage of T-SQL over MySQL PL is that it allows executing not only DML, but also DDL statements in transactions.

T-SQL vs PL/SQL

Now let's move on to yet another widely used DBMS called Oracle. It utilizes PL/SQL, an Oracle-specific procedural language whose functionality gets improved with each successive release of Oracle Database. Have a look at the key differences between T-SQL and PL/SQL.

First and foremost, T-SQL and PL/SQL are associated with different database systems - SQL Server and Oracle, respectively.
The syntax of PL/SQL is noted to be more complex than that of T-SQL. By the way, the entire Oracle database system is typically recognized for its complexity.
PL/SQL supports the concepts of object-oriented programming, such as inheritance, function overloading, and data encapsulation.
Just like in the previous case, the crucial advantage of T-SQL over PL/SQL is that it allows executing not only DML, but also DDL statements in transactions.

Data types in Transact-SQL

T-SQL allows operating with numerous data types, including numeric, date and time, character strings, and binary strings. Here are these types, conveniently divided into categories.

Numerics

Data type	Description
Int	The primary integer data type with 4 bytes of storage
Tinyint	Defines an integer data type with 1 byte of storage
Smallint	Defines an integer data type with 2 bytes of storage
Bigint	Defines an integer data type with 8 bytes of storage
Bit	An integer data type that can take a value of 1, 0, or NULL
Decimal	Defines a numeric data type with fixed precision and scale numbers
Numeric	Is identical to the decimal type
Float	Defines approximate numeric values
Real	Defines approximate numeric values with 4 bytes of storage

Date and time

Data type	Description
Date	Defines a date with 3 bytes of storage
Time	Defines time with 5 bytes of storage
Datetime	Defines a date with time with 8 bytes of storage
Smalldatetime	Defines a date with time with 4 bytes of storage
Datetime2	The datetime type with an extended date range of 6 bytes of storage
Datetimeoffset	Defines a time zone-aware date with time; has 10 bytes of storage

Character strings

Data type	Description
Char	Fixed-length string data
Varchar	String data of varying length

Binary strings

Data type	Description
Binary	Fixed-length binary data
Varbinary	Binary data of varying length

Unicode character strings

Data type	Description
Nchar	Fixed-length unicode string data
Nvarchar	Unicode string data of varying length

BLOB

Data type	Description
Image	Variable-length binary data from 0 through 2^31-1 (2,147,483,647) bytes
Text	Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647) bytes
Ntext	Variable-length Unicode data with a maximum string length of 2^30-1 (1,073,741,823) bytes
Varchar(max)	Character string data of maximum storage size 2^31-1 bytes
Nvarchar(max)	Unicode string data of maximum storage size 2^31-1 bytes

Other

Data type	Description
Uniqueidentifier	A 16-byte Global Unique Identification number (GUID)
Rowversion	Exposes unique autogenerated binary numbers
Cursor	Works for variables or stored procedure OUTPUT parameters that contain a reference to a cursor
SQL_VARIANT	Stores values of other data types
XML	Stores XML data in a column
Table	Temporarily stores a result set for later processing

The most widely used T-SQL statements in SQL Server

If you want to get a comprehensive list of the most widely used T-SQL statements, you are in the right place.

And if you want to speed up your daily work with these statements, you can use dbForge SQL Complete, which helps you double your coding speed by autocompleting these statements in the blink of an eye.

DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

DDL (Data Definition Language)

DDL statements let you create, alter, and drop objects in your databases.

Statement	Description	Example
CREATE	Creates new objects: databases, tables, views, indexes, triggers, functions, procedures, etc.	CREATE PROCEDURE HumanResources.uspGetEmployees @LastName NVARCHAR(50), @FirstName NVARCHAR(50) AS SET NOCOUNT ON; SELECT FirstName, LastName, HireDate FROM HumanResources.EmployeeHireDate WHERE FirstName = @FirstName AND LastName = @LastName; GO
ALTER	Modifies objects	ALTER VIEW HumanResources.EmployeeHireDate AS SELECT p.FirstName, p.LastName, e.HireDate FROM HumanResources.Employee AS e JOIN Person.Person AS p ON e.BusinessEntityID = p.BusinessEntityID WHERE HireDate < CONVERT(DATETIME,'20220101',101); GO
DROP	Drops objects	DROP LOGIN JordanTempLogin;
COLLATE	Defines the collation of a database or a table column	SELECT Place FROM Locations ORDER BY Place COLLATE Latin1_General_CS_AS_KS_WS ASC;
ENABLE TRIGGER	Sets the trigger status to active	ENABLE TRIGGER Purchasing.dVendor;
DISABLE TRIGGER	Sets the trigger status to inactive	DISABLE TRIGGER HumanResources.dEmployee;
UPDATE STATISTICS	Updates query optimization statistics on a selected table or indexed view	UPDATE STATISTICS Sales.SalesOrderDetail WITH SAMPLE 50 PERCENT;
TRUNCATE TABLE	Deletes all rows from a table	TRUNCATE TABLE HumanResources.JobCandidate;

DML (Data Manipulation Language)

DML statements allow inserting, updating, and deleting records in your tables.

Statement	Description	Example
INSERT	Inserts records into a table	INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES (N'Square Yards', N'Y2', GETDATE());
UPDATE	Modifies records in a table	UPDATE HumanResources.Department SET GroupName = N'Public Relations' WHERE DepartmentID = 4;
DELETE	Deletes records from a table	DELETE FROM Production.ProductCostHistory WHERE StandardCost > 1000.00;
MERGE	Inserts, updates, or deletes records from a table in a single transaction	MERGE Products AS TARGET USING UpdatedProducts AS SOURCE ON (TARGET.ProductID = SOURCE.ProductID) WHEN MATCHED AND TARGET.ProductName <> SOURCE.ProductName OR TARGET.Rate <> SOURCE.Rate THEN UPDATE SET TARGET.ProductName = SOURCE.ProductName, TARGET.Rate = SOURCE.Rate WHEN NOT MATCHED BY TARGET THEN INSERT (ProductID, ProductName, Rate) VALUES (SOURCE.ProductID, SOURCE.ProductName, SOURCE.Rate) WHEN NOT MATCHED BY SOURCE THEN DELETE OUTPUT $action, DELETED.ProductID AS TargetProductID;
BULK INSERT	Imports multiple records from a data file into a table or a view	BULK INSERT Sales.Invoices FROM '\\share\Jordan\inv-2016-07-25.csv' WITH (FORMAT = 'CSV');

DCL (Data Control Language)

DCL statements are associated with permissions and let you control user access to data.

Statement	Description	Example
GRANT	Grants privileges, roles or permissions to access and manage database objects	GRANT INSERT ON Person.Address TO guest;
REVOKE	Revokes permissions	REVOKE UPDATE ON Sales.Currency TO guest;
DENY	Denies permissions	DENY SELECT ON Sales.Customer TO guest;

TCL (Transaction Control Language)

TCL statements let you manage transactions in your databases.

Statement	Description	Example
COMMIT	Permanently saves a transaction to the database	BEGIN TRANSACTION; DELETE FROM HumanResources.JobCandidate WHERE JobCandidateID = 13; COMMIT TRANSACTION;
ROLLBACK	Restores the database to last committed state	BEGIN TRANSACTION; INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode, ModifiedDate) VALUES (N'Square Yards M', N'Y3', GETDATE()); ROLLBACK TRANSACTION;

Statement

Description

Example

COMMIT

Permanently saves a transaction to the database

BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT TRANSACTION;

ROLLBACK

Restores the database to last committed state

BEGIN TRANSACTION;
INSERT INTO Production.UnitMeasure (Name, UnitMeasureCode,
ModifiedDate)
VALUES (N'Square Yards M', N'Y3', GETDATE());
ROLLBACK TRANSACTION;

Сonvert functions

T-SQL delivers two functions that let you convert one data type to another. Called CAST and CONVERT, they work to deliver a similar result, yet have a crucial difference. Let's have a brief overview of each.

CAST is a universal conversion function that works the same way for different SQL-based database management systems.

Its basic syntax is simple: CAST (Column or Expression AS Target Data Type).

Here's an example. Let's convert an integer to a decimal and specify the precision (the total number of digits to be stored) and scale (the number of digits to be stored to the right of the decimal point).

That said, if we want our precision to be, for instance, 9, and we need only 2 digits after the decimal point, our CAST function for an integer '919' will be as follows: SELECT CAST (919 AS DECIMAL (9,2));

CONVERT is a T-SQL-specific function, so it is not going to be the standard solution.

The other difference from CAST is that you can specify an optional style number in order to avoid any ambiguity in the output value. The basic syntax for CONVERT is as follows: CONVERT (Target Data Type, Column or Expression, Optional Style Number).

Let's illustrate it with a similar example and convert an integer 919 to a decimal: SELECT CONVERT (DECIMAL (9,2), 919);

What is a style then and why would we need to specify it? Well, here's another example that will make it simple for you. Let's say that we want to convert a string '6/27/1999' to a DATETIME in the British format (which is style 103, according to the full list of styles in the official Microsoft documentation). That said, we run SELECT CONVERT (DATETIME, '6/27/1999', 103) and get the following output: 27/06/1999.

Examples of T-SQL pivot tables in SQL Server

Pivot tables are used to summarize data and present it in tabular form for subsequent analysis and reporting.

The general syntax of a typical pivot table in T-SQL is as follows:

SELECT <non-pivoted column>, [first pivoted column] AS <column name>, [second pivoted column] AS <column name>, ... [last pivoted column] AS <column name> FROM (<SELECT query that produces the data>) AS <alias for the source query> PIVOT ( <aggregation function>(<column being aggregated>) FOR [<column that contains the values that will become column headers>] IN ( [first pivoted column], [second pivoted column], ... [last pivoted column]) ) AS <alias for the pivot table> <optional ORDER BY clause>;

Let's illustrate it with the following example of a static pivot table:

SELECT StudID, 
   ISNULL( [MATH], 0 ) AS [MATH], 
   ISNULL( [PHYSICS], 0 ) AS [PHYSICS], 
   ISNULL( [SCIENCE], 0 ) AS [SCIENCE], 
   ISNULL( [ART], 0 ) AS [ART] 
FROM (SELECT sm.StudID, 
             s.SubjectName, 
             sm.Score 
  FROM StudentMarks sm 
  INNER JOIN 
  Subjects s 
  ON sm.SubjectID=s.SubjectID) AS tbl 
PIVOT 
(SUM( Score ) FOR SubjectName IN ([MATH], [PHYSICS], [SCIENCE], [ART]))
AS pvt

Alternatively, you can create pivot tables based on dynamic data sources; that is, if new columns are added, or if the existing data is modified or removed, the query will adjust the output automatically.

DECLARE 
    @colnameList VARCHAR(200), 
    @SQLQuery NVARCHAR(MAX); 
SET @colnameList=NULL; 
SELECT @colnameList=COALESCE( @colnameList+',', '' )+SubjectName 
FROM Subjects; 
SET @SQLQuery= 
'SELECT StudID , '+@colnameList+' 
FROM (SELECT sm.StudID, s.SubjectName, sm.Score 
FROM StudentMarks sm INNER JOIN Subjects s 
ON sm.SubjectID = s.SubjectID) AS tbl 
PIVOT 
( Sum(Score) FOR SubjectName IN ('+@colnameList+') ) as pvt'; 
EXEC (@SQLQuery);