Using JOIN, NOLOCK, and Table (Index) Hints Together

[Update - 2013-12-23: As Sven Treffinger has pointed out in the comments, this post should include a warning about using NOLOCK. Here are a couple examples of the many articles on-line about the evils of NOLOCK:

• The Side Effect of NOLOCK
• SQL Server 2005 Row Versioning-Based Transaction Isolation

So, proceed only after you understand the risks of NOLOCK.]

Using JOIN, NOLOCK, or table hints by themselves is straightforward, but what if you need to use them all at once? What's the order?

I tried and failed with a number of different permutations. I finally figured out that the NOLOCK has an implied WITH*. That brought me to the solution.

SELECT ...
FROM xx AS x WITH (NOLOCK)
JOIN yy AS y WITH (NOLOCK, INDEX (yIdx))
ON x.col = y.col

* I always write NOLOCKs using the the WITH and "()", but I didn't write this query originally.

Here is an example that you can run:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
CREATE TABLE #t1 (ColID int IDENTITY, Col2 int)
CREATE TABLE #t2 (ColID int IDENTITY, Col2 int)
GO
CREATE INDEX t1Col2 ON #t2 (Col2)
GO
INSERT INTO #t1 (Col2) VALUES (1)
INSERT INTO #t1 (Col2) VALUES (2)
INSERT INTO #t2 (Col2) VALUES (3)
INSERT INTO #t2 (Col2) VALUES (4)
GO
SELECT t1.Col2, t2.Col2
FROM #t1 AS t1 WITH (NOLOCK)
JOIN #t2 AS t2 WITH (NOLOCK, INDEX (t1Col2))
ON t1.ColID = t2.ColID

---

Col2 Col2
---- ----
   1    3
   2    4