Basic Functions of SQL Server

Conversion functions

CAST and CONVERT do the same job. Use CONVERT when you need a style code, most often for dates:

SELECT CAST(19.99 AS int)                     AS Truncated,   -- 19
       CAST(19.99 AS decimal(4,1))            AS Rounded,     -- 20.0
       CONVERT(varchar(10), GETDATE(), 23)    AS IsoDate,     -- 2026-10-08
       CONVERT(date, '20261008', 112)         AS FromIso;     -- 2026-10-08

Three things to remember:

• Converting to an integer truncates, converting to a decimal rounds. CAST(19.99 AS int) is 19, not 20.
• varchar without a length is 30 characters in CAST and CONVERT (and 1 character in a DECLARE). Longer values are cut off silently. Always write the length.
• A failed conversion aborts the whole statement. One bad row is enough:

SELECT CAST(Rating AS decimal(3,1)) FROM dbo.Products;

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

TRY_CONVERT returns NULL for the bad row instead, which also makes it the reliable way to find dirty data:

SELECT ProductID, Rating
FROM dbo.Products
WHERE Rating IS NOT NULL
  AND TRY_CONVERT(decimal(3,1), Rating) IS NULL;   -- returns the 'n/a' row

Avoid ISNUMERIC for this test. It returns 1 for strings such as '$' and '1e4' that will not convert to int or decimal. Note also that TRY_CONVERT still raises an error when the conversion is not permitted at all (for example int to xml); it only swallows failures caused by the value.

Implicit conversion

When two types meet in one expression, SQL Server converts the one with lower data type precedence to the higher one. Numbers outrank strings, so '5' + 1 is 6, and 'n/a' + 1 fails with error 245 (Conversion failed when converting the varchar value 'n/a' to data type int). The same rule explains most surprises with COALESCE, IIF and CASE below.

NULL handling

Any arithmetic or comparison with NULL yields NULL. That is why the Webcam row has no NetPrice above, and why WHERE Discount = NULL never returns rows. Use IS NULL and IS NOT NULL.

ISNULL and COALESCE both substitute a value for NULL, but they are not interchangeable:

The return-type difference causes silent truncation:

DECLARE @code varchar(3) = NULL;

SELECT ISNULL(@code, 'unknown')   AS WithIsNull,     -- unk
       COALESCE(@code, 'unknown') AS WithCoalesce;   -- unknown

It also causes errors in the other direction: COALESCE(Rating, 0) mixes varchar and int, so the result type is int and text values such as 'n/a' fail to convert. Write COALESCE(Rating, '0').

COALESCE is expanded to a CASE expression, so a subquery used as an argument can be evaluated more than once. Use ISNULL around subqueries.

NULLIF(a, b) goes the other way: it returns NULL when the two values are equal. Its main use is preventing divide-by-zero (error 8134):

SELECT Name, 1000 / NULLIF(Stock, 0) AS BudgetPerUnit
FROM dbo.Products;          -- Mouse returns NULL instead of an error

To compare two nullable values and treat two NULLs as equal, SQL Server 2022 and later support a IS NOT DISTINCT FROM b.

Logical functions

IIF(condition, true_value, false_value) is shorthand for a two-branch CASE. A condition that evaluates to unknown takes the false branch:

SELECT Name, IIF(Discount >= 0.20, 'Big', 'Small') AS DiscountBand
FROM dbo.Products;          -- NULL discounts are reported as 'Small'

If that is not what you want, write a CASE with an explicit WHEN Discount IS NULL branch. IIF can be nested, but beyond two levels a CASE expression is easier to read.

CHOOSE(index, v1, v2, ...) returns the item at a 1-based position, and NULL when the index is out of range:

SELECT CHOOSE(3, 'Low', 'Medium', 'High')  AS Third,      -- High
       CHOOSE(0, 'Low', 'Medium', 'High')  AS OutOfRange; -- NULL

GREATEST and LEAST (SQL Server 2022 and later, and Azure SQL) compare values across columns in one row. They ignore NULL arguments unless every argument is NULL:

SELECT Name, GREATEST(Price, 25.00) AS PriceWithFloor
FROM dbo.Products;          -- Mouse becomes 25.00; Webcam also returns 25.00

On older versions use CASE WHEN Price > 25 THEN Price ELSE 25 END.

Both branches of IIF, and every value in CHOOSE, must be convertible to one common type. IIF(Stock > 0, Stock, 'none') compiles, then fails with error 245 on the first row where it has to convert 'none' to int.

Math functions

The traps are all about types:

• Integer division. Stock / 2 discards the remainder. Multiply by 1.0 or cast one side to decimal first.
• ROUND keeps the input type. ROUND(748.58, -3) fails with an arithmetic overflow because the result, 1000.00, does not fit the decimal(5,2) of the literal. Cast to a wider type before rounding up.
• POWER returns the type of its first argument. POWER(2, -1) is 0 because the result is an int; POWER(2.0, -1) is 0.5. POWER(2, 31) overflows int; use POWER(CAST(2 AS bigint), 31).
• SQRT of a negative number raises error 3623 (An invalid floating point operation occurred).
• float is approximate. Functions that return float (SQRT, SQUARE, EXP, LOG) are fine for measurements, not for money. Keep currency in decimal.

Performance: keep functions off indexed columns

These functions are cheap in a SELECT list. In a WHERE or JOIN clause, wrapping a column in a function usually stops SQL Server from seeking an index on that column:

-- scans: the function must run for every row
SELECT Name FROM dbo.Products WHERE ISNULL(Price, 0) > 100;

-- can seek an index on Price, and returns the same rows
SELECT Name FROM dbo.Products WHERE Price > 100;

The same applies to implicit conversions. Comparing a varchar column with an nvarchar or int value can force a conversion of the column on every row. Match the parameter type to the column type.

Common mistakes

• Using = NULL instead of IS NULL.
• Using ISNULL with a short first argument and losing the end of the replacement value.
• Mixing strings and numbers in COALESCE, IIF or CASE and getting conversion errors on some rows only.
• Expecting CAST(x AS int) to round.
• Trusting ISNUMERIC to validate input; use TRY_CONVERT to the exact target type.
• Writing CAST(x AS varchar) without a length.

Version notes

TRY_CAST, TRY_CONVERT, TRY_PARSE, IIF and CHOOSE exist in every supported version of SQL Server. GREATEST, LEAST and IS [NOT] DISTINCT FROM need SQL Server 2022 or later, or Azure SQL.

Further reading

• CAST and CONVERT (Transact-SQL)
• COALESCE (Transact-SQL)
• Mathematical functions (Transact-SQL)
• Data type precedence (Transact-SQL)

Should I use WITH (NOLOCK) to make SQL Server queries faster?

NOLOCK doesn't just risk dirty reads. It can skip rows, count rows twice and fail with error 601. Read Committed Snapshot Isolation removes the blocking without the wrong answers.

#concurrency#locking#transactions