Top 10 Advanced T-SQL Commands for SQL Server Development

This article is a list of commands and techniques for working with SQL Server, which are routine for advanced T-SQL developers. If you are interested in SQL basics, I recommend the SQL tutorials overview.

STRING_SPLIT with the “ordinal” parameter for parsing text

The newer version of the STRING_SPLIT function, available from SQL Server 2022, extends the basic functionality with an optional ordinal parameter that provides information about the original order of elements. This allows for more precise data processing and reconstruction of input strings in ETL or transformation processes.

SELECT [value], [ordinal]
FROM STRING_SPLIT('a,b,c,d', ',', 1);

IIF/CHOOSE as a clearer alternative to CASE

The IIF and CHOOSE functions are similar to CASE but more concise. They offer simple syntax for conditional expressions and selection from a set of predefined values, reducing the verbosity of CASE expressions and improving query readability.

SELECT IIF([Price] > 100, 'Expensive', 'Cheap');
SELECT CHOOSE(2, 'A', 'B', 'C');

CROSS APPLY and OUTER APPLY

The CROSS APPLY and OUTER APPLY operators allow applying subqueries or table-valued functions to each row of the left-hand input table. CROSS APPLY returns only matching results, making it a recommended and high-performance solution for such data scenarios.

SELECT [c].[Name], [x].[TopOrder]
FROM [Customers] [c]
CROSS APPLY (
    SELECT TOP 1 [OrderID]
    FROM [Orders] [o]
    WHERE [o].[CustomerID] = [c].[ID]
    ORDER BY [o].[Date] DESC
) x;

LAG, LEAD, and ROW_NUMBER – Window functions

Window functions such as LAG, LEAD, and ROW_NUMBER allow working with adjacent values (e.g., reading the previous or next row relative to the current row), generating rankings, or comparing state between rows within a logical window.

SELECT
    [SaleDate],
    [Amount],
    LAG([Amount]) OVER (ORDER BY [SaleDate]) AS [PreviousAmount],
    ROW_NUMBER() OVER (ORDER BY [Amount] DESC) AS [RN]
FROM [Sales];

TRY_CONVERT/TRY_PARSE for safe data type conversion

The TRY_CONVERT and TRY_PARSE functions return NULL instead of an error for invalid input. This allows safe processing of inconsistent data sources, particularly in ETL scenarios where we convert values into the required data model format without failing the entire data load process.

SELECT TRY_CONVERT(INT, 'abc');

FOR JSON clause – generating JSON format

The FOR JSON clause generates JSON documents directly from table data. This is an efficient way to produce output for scenarios requiring JSON consumption, such as APIs.

SELECT * 
FROM [Orders] 
FOR JSON AUTO;

JSON_VALUE/OPENJSON for working with JSON structures

While the previous function generates JSON, JSON_VALUE and OPENJSON allow extracting values from JSON documents or converting them into tabular form.

SELECT JSON_VALUE([JSONColumn], '$.customer.name');

COALESCE as a multi-level fallback expression

COALESCE allows defining sequential fallback values (substitute values if the previous value is NULL) and is a more versatile alternative to ISNULL, which only allows a single fallback value. Differences between ISNULL and COALESCE are explained in the article SQL ISNULL and COALESCE functions – differences and usage.

SELECT COALESCE(NULL, NULL, 'Default');

Recommendations for using table variables

Table variables do not maintain statistics, which can lead to inaccurate cardinality estimates and less optimal execution plans. They are suitable for lightweight queries, while temporary tables are recommended for larger data volumes.

DECLARE @t TABLE (ID INT);

OUTPUT clause for tracking changes in INSERT, UPDATE, and DELETE

The OUTPUT clause allows capturing newly inserted, updated, or deleted values directly within DML statements, which is useful for auditing and logging. Example of capturing deleted records during a DELETE operation:

DELETE FROM [Customers]
OUTPUT [deleted].*
WHERE [IsInactive] = 1;