Monday, June 29, 2009

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:


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

Monday, June 22, 2009

SSMS 2008 Uncomment Bug

In SQL Server Management Studio 2008, there is a bug in the way it uncomments lines of code.

If you start with this code:
--Code line 1
--Code line 2
--Code line 3
--Code line 4

Code line 5
and want to uncomment lines 1, 2, and 3, you would select the first three lines and click [ctl]K/U.

The problem is that in SSMS 2008, if the line immediately after the line or block you are trying to uncomment is also commented, that line will be uncommented.

I know that's hard to follow, but after selecting lines 1, 2, and 3 to uncomment, you will end up with lines 1, 2, 3, and 4 uncommented.

Instead of this:
Code line 1
Code line 2
Code line 3
--Code line 4
Code line 5
You'll see this:
Code line 1
Code line 2
Code line 3
Code line 4 -- whoops
Code line 5
I entered this issue at Microsoft Connect in February. They logged it as bug ID 418596.

Today, they replied and wrote that they would fix it (someday). This is great news.
Hello rgarrison,

Thank you for reporting this problem. We understand the usability issue wrt uncommenting and will fix this issue in a future release.

Thanks,
-Karthik

Monday, June 15, 2009

Speaking at PASS Summit 2009

I received word over the weekend (in the middle of moving) that one of my abstracts had been accepted for PASS.
  • Accepted: Working in Management Will Rot Your Brain - How to Stay Technical and Advance Your Career
  • Alternate: Exploring Filtered Indexes: How They Work, When They are Used
I am very excited to be able to speak there. Now I just have to do all the work to get that talk ready.

PASS Speaker