What Is T-SQL? A Beginner’s Guide to Transact-SQL for SQL Server

Functions in T-SQL

T-SQL includes both built-in functions and user-defined functions (UDFs) used to transform data.

Built-in functions

These functions are already available in SQL Server and can be used directly in queries.

String function

This is a function that transforms string data types. For example, the query below retrieves all first names from the Customers table and converts them to uppercase.

-- Converts text to uppercase
SELECT UPPER(FirstName)  
FROM Customers;

Date function

This function transforms date functions. The query below returns the current system date and time.

-- Returns current date and time
SELECT GETDATE();  

Aggregate function

These are functions used to aggregate values in a table. For example, the query below counts the total number of rows (customers) in the Customers table.

-- Counts rows
SELECT COUNT(*) AS TotalCustomers
FROM Customers;  

User-defined functions (UDFs)

You can also create your own functions to reuse logic in multiple queries. 

Scalar function

A scalar function returns a single value and is useful when you need a single computed value, such as formatting or calculations. In the query below, the function takes a first name and last name as input and returns them as a single full name.

-- Create a function that combines the first and last name
CREATE FUNCTION GetFullName 
(@FirstName VARCHAR(50), @LastName VARCHAR(50))
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName; -- Concatenates the two values
END;

Table-valued functions

Table-valued functions return a table. For example, the function below returns a table of all customers who belong to the specified country.

-- Create a table-valued function named GetCustomersByCountry
CREATE FUNCTION GetCustomersByCountry (@Country VARCHAR(50))
RETURNS TABLE -- Specifies that the function returns a table
AS
RETURN
(
    -- Select all columns from the Customers table
    SELECT *
    FROM Customers
    WHERE Country = @Country -- Filter rows by the given country
);

I recommend trying our full SQL Server Developer career track, which will not only equip you with the skills to create, update, and execute stored procedures, but will also help you with aggregate functions, joining, inserting, and deleting tables, and much more.

Error Handling in T-SQL

T-SQL uses TRY...CATCH blocks to handle errors without stopping the entire script. This is important as it prevents scripts from crashing unexpectedly and allows you to return meaningful messages for debugging or logging.

For example, the query below tries to run a calculation that causes an error, catches it, and prints a readable error message instead of crashing.

BEGIN TRY
    -- Attempt to run this code
    SELECT 1 / 0;  -- This causes a divide-by-zero error
END TRY
BEGIN CATCH
    -- Runs if an error occurs in the TRY block
    PRINT 'An error occurred: ' + ERROR_MESSAGE(); -- Displays the error message
END CATCH;

Transactions in T-SQL

A Transaction ensures that a group of operations either all succeed or all fail. This follows the ACID principle, which guarantees that your database stays consistent even if the power goes out mid-update.

In the example below, the transaction transfers money from one account to another and saves both changes together.

BEGIN TRANSACTION; -- Start the transaction

-- Deduct money from Account 1
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Add money to Account 2
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

COMMIT; -- Save all changes permanently

If something goes wrong, you can use the ROLLBACK function to cancel the transaction and restore the data to its previous state.

-- Undo all changes made in the current transaction
ROLLBACK; 

Transactions in T-SQL are important as they ensure data integrity, especially in critical systems like financial applications, where partial updates could lead to inconsistencies.

You can try our Transactions and Error Handling in SQL Server career track, which covers transactions and error handling in SQL Server.

Common T-SQL Features for Data Analysis

T-SQL includes some tools that are especially useful for data professionals. Let’s look at some examples and how to apply them in data analysis.

Window functions

Window functions perform calculations across rows without grouping them into a single result. They are mostly used in ranking, running totals, and advanced analytics.

In the example below, the query assigns a unique row number to each customer based on their CustomerID.

-- Assigns row numbers to customers
SELECT 
    FirstName,
    ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum -- Assigns row numbers
FROM Customers;

In the query;

• ROW_NUMBER(): Assigns unique row numbers

• RANK(): Assigns rank with gaps for ties

• OVER(): Defines the window (ordering/partitioning)

I recommend checking out our SQL Window Functions Cheat Sheet, which you can download, where you will have a handy reference guide to the various types of window functions in SQL. Also, check out our Intermediate SQL Server course to learn more about using T-SQL in data analysis, including window functions to summarize data.

Common table expressions (CTEs)

CTEs (Common Table Expressions) create a temporary result set that you can reuse within a query. The query below creates a temporary list of USA customers and then retrieves data from it.

-- Define a CTE
WITH CustomerCTE AS (
    SELECT FirstName, Country
    FROM Customers
    WHERE Country = 'USA' -- Filter only USA customers
)

-- Query the CTE
SELECT *
FROM CustomerCTE;

On the other hand, recursive CTEs are a special type of CTE that reference themselves within their definition, allowing the query to perform repeated operations. This makes them ideal for working with hierarchical data like organizational charts.

I recommend taking our Hierarchical and Recursive Queries in SQL Server course to learn how to write advanced queries in SQL Server, including involving CTEs and recursive CTEs, with practical examples.

Temporary tables

Temporary tables store data for short-term use during a session. They are useful for staging data, simplifying multi-step transformations, or improving performance in complex queries.

To demonstrate how temporary tables work in t-SQL, consider the following three query blocks. The first one creates a temporary table, then fills it with customer data, and then queries it within the same session.

-- Create a temporary table
CREATE TABLE #TempCustomers (
    FirstName VARCHAR(50),
    Country VARCHAR(50)
);

-- Insert data into the temporary table
INSERT INTO #TempCustomers
SELECT FirstName, Country
FROM Customers;

-- Retrieve data from the temporary table
SELECT * FROM #TempCustomers;

It is important to note that temporary tables are prefixed with # and they are automatically dropped when the session ends.

T-SQL vs. Other SQL Dialects

All SQL dialects share the same foundation, but each database system adds its own syntax and features. T-SQL is designed specifically for Microsoft SQL Server, which influences how it works and where it’s best used.

T-SQL vs. PostgreSQL (PL/pgSQL)

PostgreSQL uses PL/pgSQL for procedural logic, similar to T-SQL but with different syntax and strengths. The table below summarizes these differences:

Check out our SQL for Database Administrators skill track to learn how to design relational databases and structure your queries to improve performance.

T-SQL vs. MySQL

MySQL also supports procedural SQL, but with a simpler and more limited approach. The table below compares the syntax and feature differences of these two dialects.

Real-World Use Cases of T-SQL

T-SQL is widely used beyond simple data querying. The following are examples where I found it useful as a data professional.

Reporting queries

T-SQL is commonly used to generate reports directly from the database. For example, you can aggregate sales by region or rank top-performing products using window functions.

ETL processes (Extract, Transform, Load)

T-SQL is used in data pipelines by data engineers to prepare and move data. For example, you can extract data from staging tables, transform formats, clean inconsistencies, and load into final reporting tables.

Data cleaning scripts

If your database has millions of rows with inconsistent address formats, you can use a T-SQL script using WHILE loops or CTEs to identify duplicates, fill missing values, or standardize formats.

Backend logic in applications

Many applications rely on T-SQL to handle business logic inside the database. You can use stored procedures for CRUD operations, perform validations before inserting data, or use transaction handling for critical operations.

Common Mistakes and Best Practices

Although T-SQL is useful for advanced database querying, you are likely to run into a few common issues. The following are some common mistakes I have encountered when using T- SQL:

• Overusing loops instead of set-based logic: Using loops, like WHILE, for row-by-row operations can slow down performance. SQL is designed to work with sets of data at once.

• Poor indexing awareness: Not using indexes properly can make queries slow, especially on large tables.

• Not using transactions properly: Running multiple related operations without wrapping them in a transaction can leave your data in an inconsistent state if something fails midway.

• Ignoring error handling: If you skip the TRY...CATCH blocks, it makes debugging harder and can cause scripts to fail silently or unpredictably in production.

Below are the best practices I recommend to help you write more efficient and reliable queries:

• Prefer set-based queries: Use JOIN, GROUP BY, and window functions instead of loops whenever possible. This aligns with how SQL Server is designed to process data efficiently.

• Write readable, maintainable code: Use clear naming conventions, format queries consistently, and add comments where necessary. This makes your code readable, easier to debug, and collaborate on.

• Test procedures: Always test stored procedures and queries with different inputs to ensure they work correctly and handle edge cases.

Conclusion

T-SQL extends standard SQL by adding programming capabilities, making it a flexible and powerful tool for automation, data processing, and analysis. With features like variables, stored procedures, and error handling, it allows you to build more dynamic and efficient database solutions.

As a next step, I recommend trying out our Associate Data Analyst in SQL career track to become a proficient data analyst. Our Reporting in SQL course will also help you become proficient in building complex reports. Finally, you should obtain our SQL Associate Certification to showcase your mastery in using SQL to solve business problems and stand out among other professionals.