Success!

DrJogalog (4/2/2010)

---

Duplicate and missing records on queries without NO LOCK hints too?! Thats a very simple but clear explanation and seems it could be common occurence than with the NO...

Or locking...that's something else that is often forgotten 🙂

Table variable changes are still logged, they have to be (all...

carl.anderson-1037280 (4/1/2010)

---

when I run your example code, I get a separate resulting row for every character I'm trying to exclude. I don't see the same behavior in Jeff's code....

Right.

I cannot make any modifications to the databases...

matt stockham (4/1/2010)

---

As Paul said, you really need to understand parameter sniffing to resolve this correctly. The performance impact *may* be due to recompiling every single time this proc...

Toby White (4/1/2010)

---

One possibility is to use the option (optimize for (@var = value)) query hint. This might work if the distribution/frequency of the parameter you are using is in...

But then of course you have to explicitly set the SNAPSHOT...

Daniel C (4/1/2010)

---

The problem should be easily solved with a MERGE statement inside your trigger. I had a similar task to solve and it's been taken care of very elegantly...

Using a synonym would be my default choice here, since they were pretty much invented just for this type of scenario.

Actually, my advice is to read everything Craig F has ever written!

Well...I know what you mean, but it's not literally true:

-- For the demo

USE tempdb;

-- Truncate the log

CHECKPOINT;

-- Show log records

-- (just LOP_BEGIN_CKPT...

...and think again, again:

http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx

...rows can be missed or double-counted at the default READ COMMITTED level too. It might surprise you that this can still happen at REPEATABLE...

Grant Fritchey (4/1/2010)

---

Oh that was flipping sad.

I hope the schmuck finds your post every day for the rest...

Alice