Scalar Functions and CASE: Strings, Date/Time, Type Conversion and Sargability

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the PostgreSQL comparisons were run on PostgreSQL 18.6.

Guess First: Same Result, Same Cost?

We ask how many orders were placed in 2025 in two different ways. There is an index on orders.ordered_at:

CREATE INDEX IX_orders_ordered_at ON dbo.orders (ordered_at);

SELECT
    (SELECT COUNT(*) FROM dbo.orders WHERE YEAR(ordered_at) = 2025) AS with_year,
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01') AS with_range;

with_year  with_range
---------  ----------
263        263

(1 rows affected)

The results are the same. The first is shorter and more readable; the second is long and makes you deal with "date range" logic. Which one should you write? Let's look at the plans:

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) FROM dbo.orders WHERE YEAR(ordered_at) = 2025;
SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= '2025-01-01' AND ordered_at < '2026-01-01';
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Scan(OBJECT:([sqlt_07].[dbo].[orders].[IX_orders_ordered_at]),  WHERE:(datepart(year,[sqlt_07].[dbo].[orders].[ordered_at])=(2025)))

  |--Index Seek(OBJECT:([sqlt_07].[dbo].[orders].[IX_orders_ordered_at]), SEEK:([sqlt_07].[dbo].[orders].[ordered_at] > [Expr1011] AND [sqlt_07].[dbo].[orders].[ordered_at] < [Expr1012]) ORDERED FORWARD)

I took the relevant lines of the plans. The first query scans the whole index and computes datepart(year, ...) for every row; the second goes straight to the start of 2025 in the index and stops at its end. On 379 orders the difference is invisible, but at the end of this post we will measure it in logical reads on a 200,000-row table. This post covers scalar functions, the functions that produce one value per row: strings, type conversion, dates and CASE. All of them are useful; but used in the wrong place, on top of a column in WHERE, they make a query non-sargable.

String Functions

Let's produce a name, a location and an email domain for the customer list:

SELECT TOP (3)
       CONCAT(first_name, N' ', last_name) AS full_name,
       CONCAT_WS(N', ', city, country_code) AS location,
       UPPER(LEFT(last_name, 3)) AS short_code,
       SUBSTRING(email, CHARINDEX('@', email) + 1, 100) AS email_domain
FROM dbo.customers
ORDER BY customer_id;

full_name     location        short_code  email_domain
------------  --------------  ----------  ------------
Ada Lovelace  Manchester, GB  LOV         example.com
Alan Turing   Berlin, DE      TUR         Example.com
Grace Hopper  Istanbul, TR    HOP         example.org

(3 rows affected)

As we saw in the previous post, CONCAT treats NULLs as empty strings, while + turns the whole result into NULL on a single NULL. CONCAT_WS (with separator) takes the separator once and skips NULL arguments; for a customer without a city it writes just "TR" instead of ", TR". SUBSTRING and CHARINDEX count from 1; the 0-based index habit from C# produces off-by-one bugs here. Alan Turing's domain came back as "Example.com": exactly as the data was stored. That is fine for comparisons because the collation is case-insensitive; but in grouping and reporting you will see two spellings.

There are two functions for length, and they measure different things:

SELECT LEN(N'Dune   ') AS len_trailing, DATALENGTH(N'Dune   ') AS datalength_trailing,
       LEN(N'   Dune') AS len_leading,
       LEN(TRIM(N'   Dune   ')) AS len_trimmed;

len_trailing  datalength_trailing  len_leading  len_trimmed
------------  -------------------  -----------  -----------
4             14                   7            4

(1 rows affected)

LEN counts characters but not trailing spaces; it does count leading ones. DATALENGTH counts bytes and includes spaces; with 2 bytes per character in NVARCHAR, 7 characters are 14 bytes. If you check a rule like "username at most 20 characters" with LEN, trailing spaces slip through. TRIM exists since SQL Server 2017; before that you wrote LTRIM(RTRIM(...)).

Let's produce a simple slug with REPLACE and LOWER:

SELECT TOP (3) title, REPLACE(LOWER(title), N' ', N'-') AS slug
FROM dbo.books
WHERE category_id = 4
ORDER BY book_id;

title                 slug
--------------------  --------------------
Nineteen Eighty-Four  nineteen-eighty-four
Brave New World       brave-new-world
Fahrenheit 451        fahrenheit-451

(3 rows affected)

A real slug also has to deal with apostrophes, Turkish characters and consecutive dashes; that kind of transformation belongs in the application, in a testable function, not in the database. The same rule applies to formatting. T-SQL's FORMAT formats numbers and dates by culture:

SELECT TOP (2) title,
       FORMAT(list_price, 'N2', 'tr-TR') AS tr_price,
       FORMAT(list_price, 'N2', 'en-US') AS us_price,
       CONVERT(VARCHAR(20), list_price) AS plain
FROM dbo.books
WHERE list_price > 1000
ORDER BY book_id;

title                     tr_price  us_price  plain
------------------------  --------  --------  -------
The Pragmatic Programmer  1.150,00  1,150.00  1150.00
Refactoring               1.150,00  1,150.00  1150.00

(2 rows affected)

The output is right, but according to Microsoft's documentation FORMAT relies on the presence of the .NET CLR (Common Language Runtime) and calls .NET's formatting machinery for every value; on large result sets it is more expensive than CONVERT. More importantly, formatting is a presentation concern: fetch the number from the database as a number and format it in the application according to the user's culture. In PostgreSQL the standard way to concatenate strings is the || operator, and it too yields NULL with NULL: 'Dune' || NULL is empty (NULL), while concat('Dune', NULL) is "Dune". SQL Server 2025 supports the || operator as well; we will look at it in post 18.

Type Conversion: CAST, CONVERT and TRY_CAST

CAST is standard; CONVERT is T-SQL specific and takes a style parameter. Style 104 is the German format, day.month.year:

SELECT CAST('2025-03-31' AS DATE) AS iso_date,
       CONVERT(VARCHAR(10), CAST('2025-03-31' AS DATE), 104) AS german_style,
       CONVERT(DATE, '31.03.2025', 104) AS parsed_104,
       TRY_CAST('31.03.2025' AS DATE) AS try_cast_result,
       TRY_CONVERT(DATE, '2025-02-30') AS invalid_date;

iso_date    german_style  parsed_104  try_cast_result  invalid_date
----------  ------------  ----------  ---------------  ------------
2025-03-31  31.03.2025    2025-03-31  NULL             NULL

(1 rows affected)

"31.03.2025" was read correctly once the style was given; TRY_CAST, which knows no style, could not read it and returned NULL instead of an error. Had you done the same conversion with CAST, the query would have stopped with Msg 241, Conversion failed when converting date and/or time from character string. The TRY_ family is useful when cleaning external data of unknown quality: you find the rows that do not convert with WHERE TRY_CAST(...) IS NULL. Build a habit with date literals too: the ISO (International Organization for Standardization) 8601 form like '2025-03-31' is read independently of the session's language and date settings for DATE and DATETIME2, unlike forms such as '31/03/2025'. For the old DATETIME, the safe form is '20250331' without dashes.

The most dangerous conversion is the one you did not write, the one SQL Server does on its own: implicit conversion. customers.email is a VARCHAR column with a unique index on it. Let's run the same lookup with and without N on the literal:

SET SHOWPLAN_TEXT ON;
GO
SELECT customer_id FROM dbo.customers WHERE email = 'ada.lovelace@example.com';
SELECT customer_id FROM dbo.customers WHERE email = N'ada.lovelace@example.com';
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Seek(OBJECT:([sqlt_07].[dbo].[customers].[UQ_customers_email]), SEEK:([sqlt_07].[dbo].[customers].[email]=[@1]) ORDERED FORWARD)

  |--Index Scan(OBJECT:([sqlt_07].[dbo].[customers].[UQ_customers_email]),  WHERE:(CONVERT_IMPLICIT(nvarchar(254),[sqlt_07].[dbo].[customers].[email],0)=[@1]))

NVARCHAR has higher type precedence than VARCHAR. When the two sides are compared, the lower-precedence side, the column, is converted to NVARCHAR, and this CONVERT_IMPLICIT done for every row prevents an index seek. With this database's collation starting with SQL_, the result is an Index Scan. This mistake is very common on the application side: in .NET, string parameters are sent as nvarchar by default. The Dapper series post that covers ANSI (American National Standards Institute) strings and TVPs (Table-Valued Parameters), "Dapper ve SQL Server: TVP, ANSI String'ler, {=literal} ve Parameter Sniffing", showed sending the parameter as DbType.AnsiString; this plan is the proof of why that setting matters.

Date and Time Functions

The most frequently used ones together:

SELECT SYSUTCDATETIME() AS utc_now_type_check,
       DATEADD(DAY, 14, CAST('2026-06-20' AS DATE)) AS due_date,
       EOMONTH('2026-02-10') AS end_of_feb,
       DATEFROMPARTS(2026, 6, 1) AS first_of_june,
       DATEADD(MONTH, 1, CAST('2026-01-31' AS DATE)) AS jan31_plus_month;

utc_now_type_check           due_date    end_of_feb  first_of_june  jan31_plus_month
---------------------------  ----------  ----------  -------------  ----------------
2026-11-08 12:10:13.4864959  2026-07-04  2026-02-28  2026-06-01     2026-02-28

(1 rows affected)

SYSUTCDATETIME() returns the UTC (Coordinated Universal Time) time with DATETIME2(7) precision; GETDATE() returns the server's local time as the old DATETIME type. Since Bookstore stores UTC, the first is the right one. Note the last column: adding a month to January 31 cannot give February 31, so it lands on the last day of the month, February 28. You need to know this behaviour for a calculation such as "a subscription renewed on the same day every month".

DATEDIFF is the most counter-intuitive function. It does not return the time between two dates but the number of boundaries crossed:

SELECT DATEDIFF(YEAR, '2025-12-31', '2026-01-01') AS years_1_day,
       DATEDIFF(YEAR, '2025-01-01', '2025-12-31') AS years_364_days,
       DATEDIFF(DAY, '2025-03-31 23:59', '2025-04-01 00:01') AS days_2_minutes,
       DATEDIFF(MONTH, '2025-01-31', '2025-02-01') AS months_1_day;

years_1_day  years_364_days  days_2_minutes  months_1_day
-----------  --------------  --------------  ------------
1            0               1               1

(1 rows affected)

A one-day difference is "1 year", a 364-day difference is "0 years". Two minutes are "1 day". That is why computing a customer's age or a subscription length with DATEDIFF(YEAR, ...) gives wrong results. Let's see the difference when measuring shipping time:

SELECT TOP (5) order_id, ordered_at, shipped_at,
       DATEDIFF(HOUR, ordered_at, shipped_at) / 24.0 AS days_exact,
       DATEDIFF(DAY, ordered_at, shipped_at) AS day_boundaries
FROM dbo.orders
WHERE shipped_at IS NOT NULL
ORDER BY order_id;

order_id  ordered_at           shipped_at           days_exact  day_boundaries
--------  -------------------  -------------------  ----------  --------------
1         2025-01-02 09:19:25  2025-01-03 11:19:25  1.083333    1
2         2025-01-02 22:36:16  2025-01-05 04:36:16  2.250000    3
3         2025-01-10 10:19:19  2025-01-13 13:19:19  3.125000    3
4         2025-01-11 19:07:55  2025-01-13 02:07:55  1.291666    2
5         2025-01-12 09:03:38  2025-01-15 17:03:38  3.333333    3

(5 rows affected)

Order 2 shipped in 2.25 days, but because it was placed at night it crossed three day boundaries. The business definition of "how many days until shipping" (elapsed time or calendar days) must be settled before the query. If you need the local calendar day, convert the time zone first. With AT TIME ZONE, which we saw in post 3, two orders placed on December 31 by UTC fall on New Year's Day in Istanbul:

SELECT order_id, ordered_at,
       CAST(ordered_at AT TIME ZONE 'UTC' AT TIME ZONE 'Turkey Standard Time' AS DATE) AS istanbul_date
FROM dbo.orders
WHERE order_id IN (262, 263, 264);

order_id  ordered_at           istanbul_date
--------  -------------------  -------------
262       2025-12-31 22:30:22  2026-01-01
263       2025-12-31 23:06:27  2026-01-01
264       2026-01-01 13:26:26  2026-01-01

(3 rows affected)

In PostgreSQL most of these functions have different names: interval arithmetic instead of DATEADD, subtraction and date_part instead of DATEDIFF, date_trunc('month', ...) for the start of a month. Date functions are the least portable area across dialects; we will see them in the comparison table in post 16.

CASE Expressions

CASE is standard SQL's conditional expression and comes in two forms. A searched CASE evaluates a separate condition in each WHEN; the first one that is TRUE wins. Let's put books into price bands:

SELECT book_id, title, list_price,
       CASE
           WHEN list_price < 300 THEN 'budget'
           WHEN list_price < 700 THEN 'standard'
           WHEN list_price < 1000 THEN 'premium'
           ELSE 'collector'
       END AS price_band
FROM dbo.books
WHERE book_id IN (39, 26, 58, 60, 73)
ORDER BY list_price;

book_id  title            list_price  price_band
-------  ---------------  ----------  ----------
39       The Hobbit       230.90      budget
26       Dune             390.50      standard
73       Meditations      670.50      standard
58       Clean Code       890.00      premium
60       Design Patterns  1450.00     collector

(5 rows affected)

The order of the conditions matters: a 230.90 TRY (Turkish lira) book also satisfies < 700 and < 1000, but the first matching WHEN wins. A simple CASE compares one expression with values and can also be used to define a custom sort order in ORDER BY:

SELECT status,
       CASE status
           WHEN 'Pending' THEN N'Awaiting payment'
           WHEN 'Paid' THEN N'Preparing'
           WHEN 'Shipped' THEN N'On the way'
           WHEN 'Delivered' THEN N'Delivered'
       END AS status_text,
       COUNT(*) AS order_count
FROM dbo.orders
GROUP BY status
ORDER BY CASE status WHEN 'Pending' THEN 1 WHEN 'Paid' THEN 2 WHEN 'Shipped' THEN 3 WHEN 'Delivered' THEN 4 ELSE 5 END;

status     status_text       order_count
---------  ----------------  -----------
Pending    Awaiting payment  1
Paid       Preparing         1
Shipped    On the way        8
Delivered  Delivered         345
Cancelled  NULL              24

(5 rows affected)

We forgot to write a WHEN for Cancelled and there is no ELSE; when no branch matches, CASE returns NULL without an error. Write a deliberate ELSE in every CASE. T-SQL's IIF(condition, a, b) is shorthand for a two-branch CASE; IIF(is_active = 1, 'on sale', 'discontinued') returns "discontinued" for The Colour of Magic and Code Complete. One last rule: a CASE has a single result type, and the type with the highest precedence among the branches is chosen. Trying to return text from one branch and a number from another leads to this:

SELECT CASE WHEN list_price > 1000 THEN 'expensive' ELSE list_price END AS label
FROM dbo.books;

...
470.00
460.90
950.00
Msg 8114, Level 16, State 5, Server b8be943222fa, Line 1
Error converting data type varchar to numeric.

The result type chosen was DECIMAL, and the query returned 56 rows up to the first book above 1000 TRY, then stopped because it could not turn "expensive" into a number. I shortened the first lines of the output; the real lesson is that the error blows up in the middle of the query depending on the data. Bring the branches to the same type yourself: ELSE CAST(list_price AS VARCHAR(20)).

Sargability: Do Not Wrap the Column in a Function

If a predicate is sargable (search argument-able), the optimizer can turn it into a range search on an index. When you put the column inside a function, as in YEAR(ordered_at), the optimizer is left not with the value the index is sorted by but with another value that must be computed for every row. To measure the difference, let's build a 200,000-row order history table that imitates a future in which Bookstore has grown:

CREATE TABLE dbo.order_history (
    history_id  INT          NOT NULL PRIMARY KEY,
    ordered_at  DATETIME2(0) NOT NULL,
    customer_id INT          NOT NULL
);
INSERT INTO dbo.order_history (history_id, ordered_at, customer_id)
SELECT value, DATEADD(MINUTE, value * 5, CAST('2024-01-01' AS DATETIME2(0))), value % 40 + 1
FROM GENERATE_SERIES(1, 200000);
CREATE INDEX IX_order_history_ordered_at ON dbo.order_history (ordered_at);

GENERATE_SERIES arrived with SQL Server 2022 and generates numbers from 1 to 200,000; one order every 5 minutes, from the start of 2024 to November 2025. Let's count March 2025 in two ways:

SET STATISTICS IO ON;
SELECT COUNT(*) AS march_2025 FROM dbo.order_history WHERE YEAR(ordered_at) = 2025 AND MONTH(ordered_at) = 3;
SELECT COUNT(*) AS march_2025 FROM dbo.order_history WHERE ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01';
SET STATISTICS IO OFF;

march_2025
----------
8928

(1 rows affected)
Table 'order_history'. Scan count 1, logical reads 402, ...
march_2025
----------
8928

(1 rows affected)
Table 'order_history'. Scan count 1, logical reads 21, ...

The same 8,928 orders; the first query read 402 pages, the second 21. The ratio is not fixed; it widens as the table grows: the function version always reads the whole index, the range version only the pages of that month. The rule is general: move the computation to the constant's side, not the column's. In a condition like "orders to be shipped within the last 7 days" the difference is plain in the plan:

SET SHOWPLAN_TEXT ON;
GO
SELECT order_id FROM dbo.orders WHERE DATEADD(DAY, 7, ordered_at) > '2026-06-25';
SELECT order_id FROM dbo.orders WHERE ordered_at > DATEADD(DAY, -7, CAST('2026-06-25' AS DATETIME2(0)));
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Scan(OBJECT:([sqlt_07].[dbo].[orders].[IX_orders_ordered_at]),  WHERE:(dateadd(day,(7),[sqlt_07].[dbo].[orders].[ordered_at])>'2026-06-25 00:00:00.0000000'))

  |--Index Seek(OBJECT:([sqlt_07].[dbo].[orders].[IX_orders_ordered_at]), SEEK:([sqlt_07].[dbo].[orders].[ordered_at] > '2026-06-18 00:00:00') ORDERED FORWARD)

In the second form DATEADD is applied to the constant, computed once (2026-06-18 00:00:00) and becomes a seek. The same logic applies to writing WHERE list_price > 1000 / 1.10 instead of WHERE list_price * 1.10 > 1000, WHERE title LIKE N'Dune%' instead of WHERE LEFT(title, 4) = N'Dune', and WHERE city IS NULL OR city = N'' instead of WHERE ISNULL(city, N'') = N''. In some cases SQL Server helps on its own: it specifically recognises a conversion such as CAST(ordered_at AS DATE) = '2025-03-31' and can turn it into a range search. But instead of relying on that, writing the predicate sargable from the start tells both the reader and the optimizer clearly what you want. How to read plans and which functions are exceptions is the topic of the "SQL Server Derinlemesine" (SQL Server in Depth) series.

In Production

Look for implicit conversions in the plan cache. Every query where nvarchar parameters from the application are compared with varchar columns is invisible on small test data and shows up as an Index Scan on the large production table. Entity Framework Core (EF Core) knows the column type from the model and sends the right parameter type; in hand-written Dapper code you set it yourself. If you filter often on a computed value (such as order year), instead of repeating the function in every query you can consider a computed column with an index on it; that is an index design decision, covered in the "SQL Server Derinlemesine" series. Finally, write the time zone explicitly in date calculations: for an operations team in Istanbul, "today's orders" start at midnight Istanbul time, not at midnight UTC.

Common Pitfalls

• Applying a function to the column, as in WHERE YEAR(ordered_at) = 2025; a scan instead of an index seek.
• Comparing an nvarchar parameter from .NET with a varchar column and losing the index to CONVERT_IMPLICIT.
• Computing age or duration with DATEDIFF(YEAR, ...); DATEDIFF counts boundaries, not durations.
• Writing date literals in session-dependent formats like '31/03/2025'; use ISO 8601.
• Forgetting that LEN does not count trailing spaces.
• Forgetting ELSE in a CASE and getting silent NULLs; returning different types from branches so the query blows up midway depending on the data.
• Formatting (FORMAT) in the database; carry numbers as numbers and format them in the presentation layer.

A Small Experiment

★☆☆ Split the customers' email addresses into two columns, user name (before @) and domain. Lowercase the domains and count how many distinct domains there are.

★★☆ Taking the data's "today" as 2026-06-30, write the "orders placed last month" query so that it is sargable, using EOMONTH and DATEADD. Compare its plan with the MONTH(ordered_at) = 5 form.

★★★ On order_history, write "number of orders per customer in 2025" first with YEAR(), then with a range, and compare them with SET STATISTICS IO. Then add an index on (customer_id, ordered_at) and measure both again. For which form did the index help?

So far we have always read from a single table. In the next post we combine tables: INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, how writing a join condition in ON or in WHERE changes the result, and why Venn diagrams explain joins wrongly.