{"id":9711,"date":"2025-06-16T10:07:37","date_gmt":"2025-06-16T10:07:37","guid":{"rendered":"https:\/\/www.examsnap.com\/certification\/?p=9711"},"modified":"2026-10-27T14:15:57","modified_gmt":"2026-10-27T14:15:57","slug":"what-is-transact-sql-t-sql-and-the-different-types-of-its-functions","status":"publish","type":"post","link":"https:\/\/www.examsnap.com\/certification\/what-is-transact-sql-t-sql-and-the-different-types-of-its-functions\/","title":{"rendered":"What Is Transact-SQL (T-SQL) and the Different Types of Its Functions"},"content":{"rendered":"
\n

Transact-SQL (T-SQL<\/strong>) 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.<\/p>\n

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.<\/p>\n

How T-SQL differs from standard SQL<\/h2>\n

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<\/code>, INSERT<\/code>, UPDATE<\/code> and DELETE<\/code> statements, while also supporting DECLARE<\/code>, IF...ELSE<\/code>, WHILE<\/code>, BEGIN...END<\/code>, TRY...CATCH<\/code> and procedural database objects. Oracle PL\/SQL and PostgreSQL PL\/pgSQL are different language extensions, not interchangeable T-SQL dialects.<\/p>\n

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.<\/p>\n

Four major categories of built-in T-SQL functions<\/h2>\n\n\n\n\n\n\n\n\n
Function category<\/th>\nCommon examples<\/th>\nTypical purpose<\/th>\n<\/tr>\n<\/thead>\n
Aggregate<\/th>\nCOUNT<\/code>, SUM<\/code>, AVG<\/code>, MIN<\/code>, MAX<\/code>, STRING_AGG<\/code><\/td>\nSummarize many rows as one result or one result per group<\/td>\n<\/tr>\n
Ranking<\/th>\nROW_NUMBER<\/code>, RANK<\/code>, DENSE_RANK<\/code>, NTILE<\/code><\/td>\nAssign order or ranking inside a window<\/td>\n<\/tr>\n
Scalar<\/th>\nLEN<\/code>, SUBSTRING<\/code>, GETDATE<\/code>, COALESCE<\/code>, TRY_CONVERT<\/code><\/td>\nProduce a value from expressions, strings, dates or inputs<\/td>\n<\/tr>\n
Rowset<\/th>\nOPENROWSET<\/code>, OPENQUERY<\/code>, OPENJSON<\/code> where supported<\/td>\nExpose data as tabular rows for further querying<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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.<\/p>\n

Aggregate functions and NULL values<\/h3>\n

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

SELECT CustomerID,\n       COUNT(*) AS OrdersPlaced,\n       SUM(TotalAmount) AS TotalSpent,\n       AVG(TotalAmount) AS AverageOrder\nFROM dbo.Orders\nGROUP BY CustomerID\nHAVING COUNT(*) >= 2;<\/code><\/pre>\n
The query returns one row per customer meeting the group filter. WHERE<\/code> filters underlying rows before aggregation; HAVING<\/code> filters grouped results.<\/p>\n
Ranking functions versus aggregates<\/h3>\n
Ranking functions use OVER<\/code> and an ORDER BY<\/code> inside the window. ROW_NUMBER<\/code> assigns a distinct sequence, RANK<\/code> leaves gaps after ties, and DENSE_RANK<\/code> does not leave gaps. NTILE(4)<\/code> assigns rows to approximately equal numbered groups.<\/p>\n
SELECT OrderID, CustomerID, TotalAmount,\n       ROW_NUMBER() OVER (\n         PARTITION BY CustomerID\n         ORDER BY TotalAmount DESC, OrderID\n       ) AS OrderPosition,\n       RANK() OVER (\n         PARTITION BY CustomerID\n         ORDER BY TotalAmount DESC\n       ) AS AmountRank\nFROM dbo.Orders;<\/code><\/pre>\n
In this example equal amounts share an AmountRank<\/code>. The explicit unique OrderID<\/code> 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.<\/p>\n
Scalar functions and safe conversions<\/h3>\n
Examples include LEN('Alice')<\/code> for character count, UPPER('sql')<\/code> for uppercase text, SYSUTCDATETIME()<\/code> for the current UTC timestamp, and TRY_CONVERT(int, '123')<\/code> for a conversion that returns 123. When a permitted conversion fails, TRY_CONVERT<\/code> can return NULL rather than stopping a query; this is not permission to silently accept invalid source data.<\/p>\n
SELECT TRY_CONVERT(int, '123') AS ValidNumber,\n       TRY_CONVERT(int, 'not-a-number') AS MissingNumber,\n       COALESCE(NULL, 'Unknown') AS DisplayLabel;<\/code><\/pre>\n
Be explicit about null behaviour, collation and the distinction between a server’s local time and UTC when handling dates across systems.<\/p>\n
Rowset functions<\/h3>\n
Rowset functions can provide rows from external or semi-structured data. For instance, OPENJSON<\/code> can turn a JSON array into tabular rows on compatible SQL Server database configurations, while OPENROWSET<\/code> has provider and security prerequisites. They are not simple scalar expressions and should not be enabled indiscriminately to bypass controlled data-access paths.<\/p>\n
User-defined functions: scalar and table-valued<\/h2>\n
In addition to built-ins, developers can define scalar user-defined functions<\/strong> that return a single value or table-valued functions (TVFs)<\/strong> 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.<\/p>\n
CREATE OR ALTER FUNCTION dbo.OrdersForCustomer\n(\n    @CustomerID int\n)\nRETURNS TABLE\nAS\nRETURN\n(\n    SELECT OrderID, TotalAmount\n    FROM dbo.Orders\n    WHERE CustomerID = @CustomerID\n);<\/code><\/pre>\n
A caller can query the inline function with SELECT * FROM dbo.OrdersForCustomer(42);<\/code>. Do not present every scalar UDF as guaranteed fast: expression complexity, inlining eligibility and execution plans affect performance.<\/p>\n
Choose data types deliberately<\/h2>\n
T-SQL supports exact integers and decimals, approximate floating-point types, character strings, date\/time, binary and additional specialised types. Use decimal(p,s)<\/code> for money-like exact arithmetic when rounding rules matter; approximate float<\/code> may not represent decimal fractions exactly. For Unicode text in common SQL Server deployments, use nvarchar<\/code> where appropriate.<\/p>\n
text<\/code>, ntext<\/code> and image<\/code> are deprecated<\/strong> SQL Server data types; use appropriate varchar(max)<\/code>, nvarchar(max)<\/code> and varbinary(max)<\/code> alternatives. The historical timestamp<\/code> data type is a synonym for rowversion<\/code> and does not store a date or time<\/strong>. Use datetime2<\/code> or a suitable time-zone-aware scheme for event timestamps instead.<\/p>\n
Build a small table with constraints<\/h2>\n
CREATE TABLE dbo.Orders (\n    OrderID bigint IDENTITY(1,1) PRIMARY KEY,\n    CustomerID int NOT NULL,\n    TotalAmount decimal(12,2) NOT NULL,\n    PlacedAt datetime2 NOT NULL\n        CONSTRAINT DF_Orders_PlacedAt DEFAULT SYSUTCDATETIME(),\n    Status varchar(20) NOT NULL,\n    CONSTRAINT CK_Orders_Total CHECK (TotalAmount >= 0)\n);<\/code><\/pre>\n
The primary key identifies each row. NOT NULL<\/code> requires a value, DEFAULT<\/code> supplies a value if omitted, and CHECK<\/code> 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.<\/p>\n
INSERT INTO dbo.Orders\n    (CustomerID, TotalAmount, Status)\nVALUES\n    (42, 125.50, 'Placed'),\n    (42, 19.95, 'Placed'),\n    (73, 80.00, 'Pending');<\/code><\/pre>\n
List insert columns explicitly. A bare INSERT ... VALUES<\/code> relying on column order can fail when the schema changes and is harder for readers to maintain.<\/p>\n
Query, filter, join and order records<\/h2>\n
SELECT TOP (5)\n       OrderID, CustomerID, TotalAmount\nFROM dbo.Orders\nWHERE TotalAmount >= 50\nORDER BY TotalAmount DESC, OrderID;<\/code><\/pre>\n
TOP<\/code> without ORDER BY<\/code> does not define which rows are first.<\/strong> Include a meaningful sort, and where stable pagination matters, add a unique tie-breaker. A LEFT JOIN<\/code> keeps rows from the left table even when there is no matching row on the right; an INNER JOIN<\/code> returns matches from both. Choose join type based on the business question, not a blanket statement that joins are always faster than subqueries.<\/p>\n
Common table expressions (WITH ... AS (...)<\/code>) 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.<\/p>\n
Transactions with correct error handling<\/h2>\n
BEGIN TRANSACTION<\/code>, COMMIT<\/code> and ROLLBACK<\/code> delimit a transaction. An operation that should be all-or-nothing needs reliable error handling. Checking only @@ERROR<\/code> after one statement is insufficient for a multistatement workflow because later statements can overwrite that status.<\/p>\n
SET XACT_ABORT ON;\n\nBEGIN TRY\n    BEGIN TRANSACTION;\n\n    UPDATE dbo.Orders\n    SET Status = 'Confirmed'\n    WHERE OrderID = 1\n      AND Status = 'Placed';\n\n    COMMIT TRANSACTION;\nEND TRY\nBEGIN CATCH\n    IF XACT_STATE() <> 0\n        ROLLBACK TRANSACTION;\n\n    THROW;\nEND CATCH;<\/code><\/pre>\n
TRY...CATCH<\/code> handles supported runtime errors, XACT_STATE()<\/code> helps determine whether a transaction remains active and committable, and THROW<\/code> preserves an error for the caller. XACT_ABORT ON<\/code> 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.<\/p>\n
Stored procedures, views and triggers are different objects<\/h2>\n\n\n\n\n\n\n\n\n
Object<\/th>\nPurpose<\/th>\nImportant limitation<\/th>\n<\/tr>\n<\/thead>\n
Stored procedure<\/th>\nExecute reusable statements with parameters, updates and error handling<\/td>\nPermissions and transaction design still need review<\/td>\n<\/tr>\n
View<\/th>\nPresent a reusable query as a virtual table<\/td>\nA normal view is not a copy of data; some updates through views are restricted<\/td>\n<\/tr>\n
Trigger<\/th>\nRespond automatically to defined DML or DDL events<\/td>\nCan have hidden side effects and performance costs; design for multiple rows<\/td>\n<\/tr>\n
User-defined function<\/th>\nReturn an expression value or table of rows<\/td>\nHas restrictions on side effects and operations<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
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.<\/p>\n
Performance, indexes and execution plans<\/h2>\n
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.<\/p>\n
Use actual or estimated execution plans<\/strong>, representative parameters, elapsed time, logical reads and Query Store<\/strong> (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.<\/p>\n
A leading-wildcard predicate such as LIKE '%son'<\/code> may not be able to seek a conventional prefix index. Changing it to LIKE 'Son%'<\/code> changes the question’s meaning and is not<\/em> 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.<\/p>\n
Dynamic SQL and secure input handling<\/h2>\n
Dynamic SQL is useful when query structure changes at runtime, but concatenating untrusted values into executable SQL exposes applications to injection. Prefer sp_executesql<\/code> 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.<\/p>\n
DECLARE @statement nvarchar(max) =\n    N'SELECT OrderID, TotalAmount\n      FROM dbo.Orders\n      WHERE CustomerID = @customer';\n\nEXEC sys.sp_executesql\n    @statement,\n    N'@customer int',\n    @customer = 42;<\/code><\/pre>\n
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.<\/p>\n
Working with JSON and analytic queries<\/h2>\n
In compatible SQL Server products, ISJSON<\/code>, JSON_VALUE<\/code>, JSON_QUERY<\/code>, JSON_MODIFY<\/code> and OPENJSON<\/code> 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.<\/p>\n
Window aggregates such as SUM(TotalAmount) OVER (PARTITION BY CustomerID)<\/code> 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.<\/p>\n
A reliable T-SQL learning progression<\/h2>\n
    \n
  1. Learn SELECT<\/code>, filters, joins, grouping, sorting and clear NULL handling.<\/li>\n
  2. Create small tables with sensible types, primary keys, check constraints and explicit inserts.<\/li>\n
  3. Practise scalar, aggregate, ranking and rowset functions using real test data.<\/li>\n
  4. Learn window expressions and write a small inline table-valued function.<\/li>\n
  5. Build a safe transaction with TRY...CATCH<\/code>, THROW<\/code> and rollback.<\/li>\n
  6. Compare procedure, view, CTE and trigger use cases, focusing on their limitations.<\/li>\n
  7. Read execution plans, investigate indexing and learn secure parameterised dynamic SQL.<\/li>\n<\/ol>\n
    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.<\/p>\n<\/article>\n","protected":false},"excerpt":{"rendered":"
    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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[708],"tags":[],"class_list":["post-9711","post","type-post","status-publish","format-standard","hentry","category-data"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t