T-SQL Habits Worth Unlearning

Some T-SQL habits survive because they worked once and became the default response. Revisit them when they hide the query’s purpose, weaken correctness, or make performance harder to explain.

Make the Result Contract Visible

SELECT star is convenient while exploring a table. In application code, it leaves the result shape tied to future schema changes. It can also retrieve columns the caller never needed.

WITH Customers AS
(
    SELECT * FROM (VALUES (1, N'North'), (2, N'South'))
         AS v(CustomerId, Region)
)
SELECT CustomerId, Region
FROM Customers
ORDER BY CustomerId;

The explicit projection tells the next reader what the query promises. It also makes a review of network payload and index coverage easier. That does not mean every exploratory query needs a formal column contract.

Avoid turning the rule into folklore about COUNT(*). That expression counts rows and does not request every column as output. Similar punctuation can have a different meaning.

Stop Using NOLOCK as a Universal Repair

NOLOCK permits read-uncommitted behavior for data access. It can expose changes that later roll back and can produce inconsistent results. A report finishing quickly does not make those results dependable.

It also does not mean that the statement acquires no locks. Schema stability locks still matter during compilation and execution. A blocked query therefore needs an investigation, not an automatic hint.

SELECT session_id, wait_type, wait_time,
       blocking_session_id, command
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;
SELECT name, is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE database_id = DB_ID();

Investigate transaction length, access paths, and the actual blocking chain. Row-versioning isolation may help some workloads, but it changes behavior and resource use. Evaluate it deliberately instead of exchanging one unexplained default for another.

Leave Searchable Columns in a Useful Form

A function applied to a filtered column can make a direct index search harder. For date ranges, expressing the required interval is often clearer. It also avoids relying on a particular time precision.

DECLARE @Start date = CONVERT(date, '20260101', 112);
WITH Events AS
(
    SELECT EventTime FROM (VALUES
      (CONVERT(datetime2, '2026-01-01T12:00:00', 126)),
      (CONVERT(datetime2, '2026-02-01T00:00:00', 126))
    ) AS v(EventTime)
)
SELECT EventTime
FROM Events
WHERE EventTime >= @Start
  AND EventTime < DATEADD(month, 1, @Start);

The lower boundary is included and the next month’s boundary is excluded. Use the business time zone consistently when constructing those boundaries. Replacing a date expression without checking its meaning can create a faster wrong answer.

Not every function forces a scan, and the optimizer supports some useful transformations. Read the actual plan rather than treating a style rule as a measurement. Matching parameter and column types remains part of the same review.

Question Scalar Function Assumptions

A scalar function can hide repeated work behind a tidy expression. That becomes important when the calling query touches many rows. Read the function body before assuming it is cheap.

SELECT SCHEMA_NAME(o.schema_id) AS schema_name,
       o.name, m.is_inlineable, m.inline_type
FROM sys.objects AS o
JOIN sys.sql_modules AS m ON m.object_id = o.object_id
WHERE o.type = 'FN';

Modern SQL Server can inline eligible T-SQL scalar functions under the required settings and conditions. Eligibility does not guarantee that a particular call is inlined. Inspect the calling plan and check the documented restrictions.

Do not replace every function simply because an old tuning checklist says so. Compare the real implementation and its maintainability. Keep the business calculation visible enough to test.

Try a Set Operation Before a Row Loop

A cursor is a tool, not a moral failure. Some administrative tasks genuinely need separate commands per object. Trouble starts when every data change is written as repeated single-row work.

DECLARE @Tasks table
(
    TaskId int PRIMARY KEY,
    IsComplete bit NOT NULL,
    CompletedAt datetime2 NULL
);
INSERT @Tasks VALUES (1, 1, NULL), (2, 0, NULL);
UPDATE @Tasks
SET CompletedAt = SYSUTCDATETIME()
WHERE IsComplete = 1 AND CompletedAt IS NULL;
SELECT TaskId, IsComplete, CompletedAt FROM @Tasks;

This expresses one rule over the eligible set. For large persistent tables, also consider transaction size, logging, and batching. Set-based syntax does not remove the need to manage operational impact.

Replace Reflexes With Small Explanations

During review, ask why each hint, loop, and extra column exists. Keep the choices that have a clear reason. Change one behavior at a time so correctness and performance can be compared.

A useful habit survives questions and changing versions. An unhelpful habit survives because nobody asks anymore. Leave the next reader enough context to tell the difference.

A coding habit is not a performance guarantee, it is a choice worth revisiting.

This post was rewritten from scratch in September 2026. The original, published on 2011-11-14, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.