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

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)

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;

С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);

Work with T-SQL effortlessly using dbForge SQL Complete

If you want to give a considerable boost to the speed and quality of your daily T-SQL coding, do it with SQL Complete, one of the top SSMS add-ins on the market, and part of our SQL Tools bundle. Besides instant, context-aware code completion capabilities, it has much more to offer:

  • Code formatting with predefined and custom profiles
  • Smart renaming of database objects and variables
  • Alias refactoring and custom alias mapping
  • Search for invalid objects
  • Versatile data management in the SSMS results grid
  • Easy data visualization
  • Query execution warnings, notifications, and SQL history
Get it for a FREE 2-week trial and explore these capabilities today!

Frequently Asked Questions

Try dbForge SQL Complete and double the speed of your SQL coding! Try now