SQL Server 2000 Table Hints

When and Why to Override SQL Server’s Locking Strategy

For most day‑to‑day Transact‑SQL work, the optimizer is careful enough to pick a lock type that balances concurrency and consistency. The engine’s default behavior - using shared locks for SELECT, update locks for UPDATE, and exclusive locks for INSERT and DELETE - suits the majority of queries. Yet there are moments when the automatic choice misaligns with the business need. Whether you’re pushing a massive bulk load, orchestrating a real‑time reporting pipeline, or fine‑tuning a complex join, a hand‑crafted lock hint can shift performance in your favor.

Consider a nightly data warehouse job that writes 500,000 rows into a staging table every 12 hours. The process stalls when user queries lock the target table; the optimizer, oblivious to the upcoming write burst, keeps issuing row‑level locks for read‑only queries. Adding a TABLOCK hint to the bulk insert forces the engine to acquire a table lock that releases only at statement completion, allowing the writer to run quickly. In the meantime, the optimizer may still honor the hint on the SELECT side of the pipeline if you pair it with READUNCOMMITTED - this lets readers see uncommitted rows without stepping on the writer’s toes.

Another common scenario involves an out‑of‑date index that the optimizer favors over a newer, more selective one. If you’ve rebuilt the statistics for the newer index but the optimizer still chooses a scan, an INDEX hint can nudge it toward the correct path. Hints are also valuable when you need a deterministic execution plan across versions or environments - by locking the index choice, you eliminate the “plan drift” that can creep in after a software upgrade.

Because hints are explicit overrides, they should be used sparingly. The optimizer’s cost model is built on a wealth of data; it knows when a lock level will likely lead to deadlocks or high blocking. Only when you have empirical evidence that the automatic choice is hurting throughput, or when you have a business requirement that demands a specific locking strategy, should you write a hint. Even then, it is prudent to compare the query plan before and after the hint to confirm that the change actually delivers the expected benefit.

Remember that hints are not immune to being ignored. If the optimizer decides not to use the target table at all - perhaps because an indexed view is a better fit - your NOLOCK or TABLOCK won’t take effect. To force the optimizer to include the table, you can use OPTION (EXPAND VIEWS), but this forces view expansion regardless of the cost. Likewise, hints placed on a table that contains computed columns referencing other tables may be dropped because the optimizer must lock those other tables instead. These nuances mean that a hint is only a tool, not a guarantee.

SQL Server also limits how many hints can coexist on a single table. You can’t mix ROWLOCK with TABLOCK, and DML statements reject hints that would conflict with the transaction’s semantics - such as READPAST on an UPDATE target. These constraints prevent accidental lock escalation or misuse that could harm concurrency.

Decoding the Table Hint Syntax and Grouping

The grammar for a table hint lives inside the FROM clause. A typical construct looks like this:

The WITH keyword is optional, but Microsoft recommends including it because future releases may make it mandatory. Commas separate multiple hints, allowing you to stack granularity hints (ROWLOCK, PAGLOCK, TABLOCK, TABLOCKX) with isolation hints (NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE) or with INDEX and FASTFIRSTROW directives.

There are fifteen recognized hints in total. Thirteen of them directly influence locking: HOLDLOCK, NOLOCK, PAGLOCK, READCOMMITTED, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, and XLOCK. The remaining two, INDEX and FASTFIRSTROW, instruct the optimizer about index usage and row retrieval strategy, not about locking per se.

Hints are grouped into functional families. Granularity hints - ROWLOCK, PAGLOCK, TABLOCK, and TABLOCKX - define the lock scope. Isolation hints - NOLOCK, READCOMMITTED, REPEATABLEREAD, SERIALIZABLE, READPAST, and READUNCOMMITTED - adjust the isolation level for the specific table. Update‑specific hints - UPDLOCK and XLOCK - request update or exclusive locks that survive the remainder of the transaction. Finally, HOLDLOCK is an alias for SERIALIZABLE, useful for legacy code that still refers to the older keyword.

Because the optimizer picks the best lock type for a query, you cannot simultaneously request conflicting granularities. For example, a ROWLOCK and a TABLOCK hint on the same table are mutually exclusive; the optimizer will discard one of them. The same rule applies to isolation hints: you cannot combine NOLOCK and READCOMMITTED on a single table. When you need to enforce a particular lock level, choose the most appropriate hint from each family and apply it once.

Hints are also evaluated for compatibility with the statement type. The READPAST hint, for instance, is valid only on SELECT statements. If you try to attach it to an UPDATE or DELETE target, the engine rejects the statement outright. Likewise, hints that request exclusive locks - TABLOCKX, XLOCK, and UPDLOCK - are prohibited on tables that are read‑only or accessed only through an indexed view. These restrictions exist to preserve consistency and prevent deadlocks.

When to Use Each Hint: Real‑World Scenarios

At a large telecommunications company, a nightly job loads new records into a call‑detail record table every three days. The load can take up to 16 hours, and the table sits behind 10,000 simultaneous user queries. To keep the load from stalling the business, the team forces the bulk insert to acquire a table lock:

The TABLOCK ensures that the insert runs as a single, uncontested operation. The accompanying READUNCOMMITTED allows readers to skip the uncommitted rows and avoid blocking. Because the data is only appended and never updated, a dirty read is acceptable during the load window.

When statistics are stale, the optimizer may prefer a full table scan even if an index exists. Suppose you have a date index on the CDR table that is usually the most selective. If statistics lag, the optimizer might still issue a scan. By adding an INDEX (ix_CDR_Date) hint, you force it to use the index, reducing scan time dramatically. The syntax is straightforward:

Read‑past is handy in work‑queue systems where you want to avoid reprocessing rows that are locked by another worker. For example:

Each worker receives distinct rows and bypasses those that another worker has already claimed. This technique eliminates waiting and improves throughput in high‑concurrency environments.

Fastest first row is useful when you want to provide instant feedback to a user while the rest of the query continues in the background. A report that aggregates millions of rows can return a total and a quick count with FASTFIRSTROW, giving the user something to look at right away.

For financial and compliance workloads that demand absolute consistency, UPDLOCK or HOLDLOCK can prevent phantom reads. An audit query that reads a set of transaction rows and then updates a summary table must lock the transaction rows until the summary update completes. Applying UPDLOCK ensures that no other session can modify the rows during the transaction:

Finally, exclusive locks - TABLOCKX and XLOCK - are reserved for rare cases such as schema changes or large table rewrites where you need absolute control. They lock the entire table (or page, if paired with PAGLOCK) and prevent any concurrent reads or writes until the transaction ends.

In every scenario, the key is to test the plan. Run SET STATISTICS IO ON and SET STATISTICS TIME ON to verify that the hint actually improves performance and does not introduce deadlocks or excessive blocking. Once you confirm a benefit, commit the hint; if the results are neutral or negative, drop it and let the optimizer decide.

Common Pitfalls and How to Troubleshoot Them

Even seasoned developers stumble over hints that produce subtle bugs or errors. Below is a curated list of known issues along with practical fixes.

• NOLOCK / READUNCOMMITTED generates error 605 – If a SELECT statement with NOLOCK references a view that contains a computed column, the engine may raise error 605. The workaround is to either remove the hint or rewrite the view so that it does not use computed columns referencing other tables.
• READPAST returns an incorrect number of rows – When using READPAST on a table with a composite index that includes a non‑clustered key, the hint can skip rows that should have been returned. The fix is to enforce an index hint that guarantees the desired key order.
• Optimizer ignores INDEX hint for UPDATE if one or more non‑clustered indexes exist – If you issue an UPDATE with INDEX on a table that also has other non‑clustered indexes, SQL Server may ignore the hint for the UPDATE portion. Ensure that you reference the index name correctly and that the index is viable for the update predicate.
• NOLOCK optimizer hint may cause transient corruption errors in the error log – Under heavy load, dirty reads can expose internal inconsistencies. Avoid NOLOCK on tables that are part of a recovery group or that store financial data. Use READPAST or READCOMMITTED instead.
• Bulk insert with TABLOCK hint may result in errors 8929 and 8965 when you run CHECKDB – CHECKDB inspects the internal structures of a table. A bulk insert that holds a table lock can lock CHECKDB out of access, leading to these errors. Schedule CHECKDB after the bulk load completes or use TABLOCKX only if the table is not part of the CHECKDB schedule.
• Dynamic cursor with NOLOCK hint, DELETE activity, causes assertions in error log – When a cursor iterates over a table that is being deleted from elsewhere, the combination of NOLOCK and DELETE can trigger internal assertions. Replace NOLOCK with READCOMMITTED or split the cursor operation into smaller batches.

  When you encounter an error, start by inspecting the execution plan. A missing or unexpected hint can change the plan dramatically. Use DBCC SHOWPLAN_XML or the graphical plan in SSMS to verify that the hint is in effect. If the hint is ignored, check for one of the following causes: the target table is not referenced, the hint conflicts with another hint, or the hint is applied to a view that expands to multiple tables.

  Finally, remember that hints are a double‑edged sword. They grant precise control, but they also bypass the optimizer’s heuristics. The most common source of performance regressions comes from using a hint that locks the table too aggressively, turning a high‑concurrency workload into a bottleneck. Always pair a hint with thorough monitoring: keep an eye on blocking sessions, wait stats, and resource usage before committing the change to production.