Transact-SQL (T-SQL) is Microsoft’s extension of the SQL language, used with SQL Server, Azure SQL products and supported SQL endpoints in other Microsoft data platforms. It adds variables, control-of-flow statements, stored procedures, structured error handling and many built-in functions to standard relational queries. Some features are platform-specific, so check the documentation for the exact database engine and compatibility level you use.

This guide explains the main function categories alongside tables, querying, transactions and common development patterns. Its examples use SQL Server-style syntax and deliberately small tables to show the underlying behaviour; production code still requires appropriate permissions, indexing and testing.

How T-SQL differs from standard SQL

SQL is primarily declarative: you describe the result or change you want, and the database engine chooses how to execute it. T-SQL includes the usual SELECT, INSERT, UPDATE and DELETE statements, while also supporting DECLARE, IF...ELSE, WHILE, BEGIN...END, TRY...CATCH and procedural database objects. Oracle PL/SQL and PostgreSQL PL/pgSQL are different language extensions, not interchangeable T-SQL dialects.

Use set-based statements for operations on many rows where practical; the availability of loops does not mean row-by-row code is always preferable. SQL Server, Azure SQL Database and Microsoft Fabric SQL surfaces differ in feature coverage and administration capabilities.

Four major categories of built-in T-SQL functions

These are useful teaching categories, not a complete inventory. Microsoft also documents mathematical, date/time, string, conversion, JSON, logical, metadata and system functions. A function’s availability and argument requirements depend on the product and version; for example, external rowset access may require provider settings or privileges.

Aggregate functions and NULL values

Aggregates are often used with GROUP BY. Except for COUNT(*), typical aggregates ignore NULL inputs. COUNT(*) counts rows; COUNT(column) counts rows with non-NULL values in that column. That distinction matters when data can be missing.

SELECT CustomerID,
       COUNT(*) AS OrdersPlaced,
       SUM(TotalAmount) AS TotalSpent,
       AVG(TotalAmount) AS AverageOrder
FROM dbo.Orders
GROUP BY CustomerID
HAVING COUNT(*) >= 2;

The query returns one row per customer meeting the group filter. WHERE filters underlying rows before aggregation; HAVING filters grouped results.

Ranking functions versus aggregates

Ranking functions use OVER and an ORDER BY inside the window. ROW_NUMBER assigns a distinct sequence, RANK leaves gaps after ties, and DENSE_RANK does not leave gaps. NTILE(4) assigns rows to approximately equal numbered groups.

SELECT OrderID, CustomerID, TotalAmount,
       ROW_NUMBER() OVER (
         PARTITION BY CustomerID
         ORDER BY TotalAmount DESC, OrderID
       ) AS OrderPosition,
       RANK() OVER (
         PARTITION BY CustomerID
         ORDER BY TotalAmount DESC
       ) AS AmountRank
FROM dbo.Orders;

In this example equal amounts share an AmountRank. The explicit unique OrderID tie-breaker in the first window makes the row-number ordering reproducible. A ranking expression with ambiguous ties need not return the same relative row order every time.

Scalar functions and safe conversions

Examples include LEN('Alice') for character count, UPPER('sql') for uppercase text, SYSUTCDATETIME() for the current UTC timestamp, and TRY_CONVERT(int, '123') for a conversion that returns 123. When a permitted conversion fails, TRY_CONVERT can return NULL rather than stopping a query; this is not permission to silently accept invalid source data.

SELECT TRY_CONVERT(int, '123') AS ValidNumber,
       TRY_CONVERT(int, 'not-a-number') AS MissingNumber,
       COALESCE(NULL, 'Unknown') AS DisplayLabel;

Be explicit about null behaviour, collation and the distinction between a server’s local time and UTC when handling dates across systems.

Rowset functions

Rowset functions can provide rows from external or semi-structured data. For instance, OPENJSON can turn a JSON array into tabular rows on compatible SQL Server database configurations, while OPENROWSET has provider and security prerequisites. They are not simple scalar expressions and should not be enabled indiscriminately to bypass controlled data-access paths.

User-defined functions: scalar and table-valued

In addition to built-ins, developers can define scalar user-defined functions that return a single value or table-valued functions (TVFs) that return rows. An inline TVF often lets the optimizer treat its query definition more flexibly than a multistatement TVF. Function restrictions differ from stored procedures: a UDF cannot generally make persistent database state changes or execute arbitrary procedures.

CREATE OR ALTER FUNCTION dbo.OrdersForCustomer
(
    @CustomerID int
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, TotalAmount
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
);

A caller can query the inline function with SELECT * FROM dbo.OrdersForCustomer(42);. Do not present every scalar UDF as guaranteed fast: expression complexity, inlining eligibility and execution plans affect performance.

Choose data types deliberately

T-SQL supports exact integers and decimals, approximate floating-point types, character strings, date/time, binary and additional specialised types. Use decimal(p,s) for money-like exact arithmetic when rounding rules matter; approximate float may not represent decimal fractions exactly. For Unicode text in common SQL Server deployments, use nvarchar where appropriate.

text, ntext and image are deprecated SQL Server data types; use appropriate varchar(max), nvarchar(max) and varbinary(max) alternatives. The historical timestamp data type is a synonym for rowversion and does not store a date or time. Use datetime2 or a suitable time-zone-aware scheme for event timestamps instead.

Build a small table with constraints

CREATE TABLE dbo.Orders (
    OrderID bigint IDENTITY(1,1) PRIMARY KEY,
    CustomerID int NOT NULL,
    TotalAmount decimal(12,2) NOT NULL,
    PlacedAt datetime2 NOT NULL
        CONSTRAINT DF_Orders_PlacedAt DEFAULT SYSUTCDATETIME(),
    Status varchar(20) NOT NULL,
    CONSTRAINT CK_Orders_Total CHECK (TotalAmount >= 0)
);

The primary key identifies each row. NOT NULL requires a value, DEFAULT supplies a value if omitted, and CHECK enforces the stated condition. Foreign keys can enforce relationships with other tables, provided compatible keys are defined. Constraints protect integrity more reliably than a UI that can be bypassed.

INSERT INTO dbo.Orders
    (CustomerID, TotalAmount, Status)
VALUES
    (42, 125.50, 'Placed'),
    (42, 19.95, 'Placed'),
    (73, 80.00, 'Pending');

List insert columns explicitly. A bare INSERT ... VALUES relying on column order can fail when the schema changes and is harder for readers to maintain.

Query, filter, join and order records

SELECT TOP (5)
       OrderID, CustomerID, TotalAmount
FROM dbo.Orders
WHERE TotalAmount >= 50
ORDER BY TotalAmount DESC, OrderID;

TOP without ORDER BY does not define which rows are first. Include a meaningful sort, and where stable pagination matters, add a unique tie-breaker. A LEFT JOIN keeps rows from the left table even when there is no matching row on the right; an INNER JOIN returns matches from both. Choose join type based on the business question, not a blanket statement that joins are always faster than subqueries.

Common table expressions (WITH ... AS (...)) can organise a complex query and can be recursive for hierarchical relationships. A CTE is a query expression, not automatically a physically materialised intermediate table. Subqueries, derived tables, CTEs and views have different maintainability tradeoffs; their performance depends on the resulting plan and database configuration.

Transactions with correct error handling

BEGIN TRANSACTION, COMMIT and ROLLBACK delimit a transaction. An operation that should be all-or-nothing needs reliable error handling. Checking only @@ERROR after one statement is insufficient for a multistatement workflow because later statements can overwrite that status.

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Orders
    SET Status = 'Confirmed'
    WHERE OrderID = 1
      AND Status = 'Placed';

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

TRY...CATCH handles supported runtime errors, XACT_STATE() helps determine whether a transaction remains active and committable, and THROW preserves an error for the caller. XACT_ABORT ON is appropriate in many data-modification transactions but its behaviour and compatibility should be tested for your actual workflow. Transactions do not themselves protect against every application concurrency or business-rule error.

Stored procedures, views and triggers are different objects

Use the appropriate object for the task. Do not implement routine auditing or validation in a trigger by default when a constraint, controlled application layer, change-data-capture feature or structured audit mechanism would be clearer.

Performance, indexes and execution plans

Indexes can reduce the work required for some searches, joins and sort operations, but they add storage and can slow writes. A clustered index defines the table’s clustered storage organisation; a nonclustered index offers another access path. Neither makes every query automatically fast. Filtered or covering indexes may suit particular workloads, subject to engine support.

Use actual or estimated execution plans, representative parameters, elapsed time, logical reads and Query Store (where available) to investigate slow SQL. Statistics influence cardinality estimates and query planning. SQL Server’s Extended Events are preferable for many modern troubleshooting scenarios to relying exclusively on the legacy SQL Server Profiler.

A leading-wildcard predicate such as LIKE '%son' may not be able to seek a conventional prefix index. Changing it to LIKE 'Son%' changes the question’s meaning and is not an equivalent optimisation. Likewise, always replacing a subquery with a join is not a reliable performance rule; measure the plan for the same intended result.

Dynamic SQL and secure input handling

Dynamic SQL is useful when query structure changes at runtime, but concatenating untrusted values into executable SQL exposes applications to injection. Prefer sp_executesql with typed parameters for values and validate or allow-list SQL identifiers when identifiers must be dynamic. Parameterisation does not automatically protect concatenated table names or entire SQL fragments.

DECLARE @statement nvarchar(max) =
    N'SELECT OrderID, TotalAmount
      FROM dbo.Orders
      WHERE CustomerID = @customer';

EXEC sys.sp_executesql
    @statement,
    N'@customer int',
    @customer = 42;

Grant least-privilege permissions with roles and object-level controls. Encryption at rest, Always Encrypted, row-level security and auditing are separate features with product-specific requirements; SQL syntax alone is not a complete security strategy.

Working with JSON and analytic queries

In compatible SQL Server products, ISJSON, JSON_VALUE, JSON_QUERY, JSON_MODIFY and OPENJSON help work with JSON values. Native JSON data-type and indexing capabilities depend on version and service. SQL analytics endpoints in Fabric and other Microsoft products support subsets of T-SQL, not necessarily every stored procedure, trigger or server-level administration operation.

Window aggregates such as SUM(TotalAmount) OVER (PARTITION BY CustomerID) calculate a customer total while retaining the detail rows. Unlike a grouped aggregate, the window expression doesn’t collapse each customer’s orders into one row. This is useful in running totals, cohort comparisons and ranked reports.

A reliable T-SQL learning progression

1. Learn SELECT, filters, joins, grouping, sorting and clear NULL handling.
2. Create small tables with sensible types, primary keys, check constraints and explicit inserts.
3. Practise scalar, aggregate, ranking and rowset functions using real test data.
4. Learn window expressions and write a small inline table-valued function.
5. Build a safe transaction with TRY...CATCH, THROW and rollback.
6. Compare procedure, view, CTE and trigger use cases, focusing on their limitations.
7. Read execution plans, investigate indexing and learn secure parameterised dynamic SQL.

T-SQL is powerful because it lets declarative SQL work alongside database-specific programming and management features. Good code uses the right function or object for the job, handles errors and NULLs deliberately, and measures real behaviour rather than following unconditional performance shortcuts.