Writing T-SQL That Scales
T-SQL best practices exist because SQL Server's query optimizer, storage engine, and locking model behave very differently depending on how a query is phrased. Following a consistent set of conventions -- sargable predicates, explicit transaction boundaries, disciplined error handling, and idempotent DDL -- turns a query that works fine on a small development database into one that still performs well under production load, concurrent users, and years of schema drift.
Cricket analogy: Like a bowler who adjusts line and length depending on the pitch at Chepauk versus the WACA, a T-SQL developer adjusts query patterns depending on data volume and concurrency rather than reusing one template everywhere.
Sargable Predicates and Set-Based Thinking
Avoid Wrapping Indexed Columns in Functions
A predicate is sargable (Search ARGument ABLE) when SQL Server can use an index seek to evaluate it directly. Wrapping an indexed column in a function, such as WHERE YEAR(OrderDate) = 2025, forces the optimizer to compute the function for every row before it can compare, which usually causes an index scan instead of a seek. Rewriting the predicate as a range, WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01', lets the same index be used efficiently. The same principle applies to set-based thinking: operating on whole result sets with joins and aggregates is almost always faster than row-by-row cursor loops (sometimes called RBAR, 'row by agonizing row'), because set-based operations let the storage engine and optimizer batch work instead of paying per-row overhead.
Cricket analogy: A commentator who has to recompute a batter's strike rate from scratch for every ball bowled instead of keeping a running tally slows the broadcast down, just like recomputing YEAR(OrderDate) per row slows a query.
-- Non-sargable: forces a scan even if OrderDate is indexed
SELECT OrderId, CustomerId, TotalAmount
FROM Sales.Orders
WHERE YEAR(OrderDate) = 2025;
-- Sargable: lets SQL Server seek the index on OrderDate
SELECT OrderId, CustomerId, TotalAmount
FROM Sales.Orders
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01';
-- Set-based update instead of a cursor loop
UPDATE o
SET o.Status = 'Archived'
FROM Sales.Orders AS o
WHERE o.OrderDate < DATEADD(YEAR, -2, SYSDATETIME());Transactions and Error Handling
Every write that must succeed or fail as a unit belongs inside an explicit BEGIN TRANSACTION / COMMIT block wrapped in TRY/CATCH, with SET XACT_ABORT ON at the top of the batch so any run-time error automatically rolls back the transaction instead of leaving it open. Inside the CATCH block, capture ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), and ERROR_SEVERITY() before re-throwing with THROW, since those functions only return valid values while still inside the CATCH scope. Keeping transactions as short as possible -- doing all validation before acquiring locks, not calling out to slow external operations mid-transaction -- minimizes the time other sessions spend blocked.
Cricket analogy: A third umpire reviewing a run-out doesn't confirm the decision until every replay angle is checked, and only then commits to 'out' or 'not out' -- like a transaction only committing after every check passes.
Never leave a BEGIN TRANSACTION without a matching COMMIT or ROLLBACK reachable on every code path, including error paths. Without SET XACT_ABORT ON, some run-time errors (like a conversion failure) do not automatically abort the batch, leaving an open transaction that holds locks indefinitely and can block the entire application.
Naming, Formatting, and Idempotent Scripts
Schema-qualify every object reference (dbo.Orders, not just Orders) so SQL Server can resolve the object and its execution plan deterministically instead of probing the default schema first; this also improves plan cache hit rates because two callers referencing the same object identically reuse the cached plan. Avoid SELECT * in application code and views, since it silently pulls in new columns after a schema change and prevents the optimizer from using narrower covering indexes. Deployment scripts should be idempotent -- using IF NOT EXISTS / IF EXISTS checks around CREATE TABLE, CREATE INDEX, and ALTER statements -- so the same script can be re-run safely during a failed deployment retry without erroring out or duplicating objects.
Cricket analogy: A scorer who always records 'V Kohli, RCB' rather than just 'Virat' avoids ambiguity when multiple players share a first name -- like schema-qualifying dbo.Orders instead of just Orders.
Add SET NOCOUNT ON at the start of stored procedures to suppress the 'N rows affected' message SQL Server sends after each statement. This reduces network chatter between the server and client, and some client libraries can misinterpret the extra result set as actual data.
- Write sargable predicates: avoid wrapping indexed columns in functions, prefer range comparisons.
- Favor set-based operations over cursors and row-by-row (RBAR) loops.
- Wrap multi-statement writes in explicit transactions with TRY/CATCH and SET XACT_ABORT ON.
- Capture ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_LINE() inside CATCH before using THROW.
- Schema-qualify object names and avoid SELECT * to improve plan cache reuse and stability.
- Make DDL and deployment scripts idempotent with IF EXISTS / IF NOT EXISTS guards.
- Use SET NOCOUNT ON in stored procedures to reduce unnecessary network round-trips.
Practice what you learned
1. Why does WHERE YEAR(OrderDate) = 2025 typically prevent an index seek on OrderDate?
2. What does SET XACT_ABORT ON primarily guarantee?
3. Why is SELECT * discouraged in application code and views?
4. What makes a deployment script 'idempotent'?
5. Which functions must be called inside the CATCH block to return valid error details?
Was this page helpful?
You May Also Like
SQL Server vs Other Databases
How SQL Server compares to PostgreSQL, MySQL, and Oracle across architecture, tooling, licensing, and everyday development experience.
Building a Reporting Query
A step-by-step walkthrough of designing a real-world T-SQL reporting query using joins, aggregation, window functions, and pivoting.
T-SQL Quick Reference
A fast-lookup reference covering core T-SQL syntax: data types, joins, window functions, string/date functions, and common statements.
SQL Server Interview Questions
Commonly asked SQL Server and T-SQL interview questions with the reasoning behind strong answers, from indexing to isolation levels.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
Programmingdbt (Data Build Tool) Study Notes
Programming · 30 topics