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 ...* I always write NOLOCKs using the the WITH and "()", but I didn't write this query originally.
FROM xx AS x WITH (NOLOCK)
JOIN yy AS y WITH (NOLOCK, INDEX (yIdx))
ON x.col = y.col
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
