Grant Fritchey (4/30/2009)

---

Hm. Interesting. I didn't know that. Too late to recall all the books though. SP:Recompile does work and will capture the statement level recompiles. It even shows...

Consider makling the INSERT and EXISTS test the same statement:

INSERT dbo.Table (...columns..)

SELECT @primary_key_value, ...other columns

WHERE NOT EXISTS

(

SELECT 1 FROM dbo.Table

WHERE dbo.Table.primary_key_column = @primary_key_value

)

Make sure you have a primary key on...

Also, the behaviour is the same when executed alternately from different connections, even with different SET options, and different user ids- the allocated pages stored with the plan are re-used...

GilaMonster (4/30/2009)

---

Offhand, no, but I suspect it has something to do with Statement level recompile and/or temp table caching....

Tim,

You should use the event SQL:StmtRecompile instead of SP:Recompile in 2005 and later.

See http://msdn.microsoft.com/en-us/library/ms179294.aspx

The SP:Recompile event is there to allow tracing of SQL2K instances.

Anyway, on to the answer:

SQL2K5 caches...

John,

Good point - I feel as though I was harsh now.

However, I suspect that expecting behaviour to change simply by deleting a comment is the same in any language 😉

Paul

Lynn Pettis (4/30/2009)

---

I'm glad someone else took the time to read through your unformatted code, as I really didn't have the time. A good suggestion, especially...

At least that gives you freedom to roam about while on the forum? :laugh:

Ok...

You could have a SQL Agent job that goes around creating the appropriate linked servers for you, I have seen that approach taken before.

I tend to agree with Flo though...

RBarryYoung (4/30/2009)

---

Dude, what is going on with your code-window font sizes lately? They are showing up in my Firefox browser larger than PowerPoint letters. Makes them really hard...

RBarryYoung (4/30/2009)

---

Also, there is very probably a whole lot of other columns that Steve hasn't mentioned because they are unnecessary, unless you are recreating the whole table.

Still, it's always bothered...

TheSQLGuru (4/30/2009)

---

Reread my post Paul - I said using a function on a COLUMN. Your example is using a function on a VARIABLE. Just a slight difference there....

:laugh:

Saw that yesterday and figured Steve had sorted it. He's a braver man than I.

Seems I underestimated the OP.

Ho hum.

Paul

Hmm. That blunted the intended impact somewhat :doze:

Fixed now - I must not put underline tags inside the url tags it seems!

Thanks everyone for letting me know.

Paul

I used:

Insert Into dbo.Users (UserName, LastLoggedIn)

SELECT TOP 40000 CHAR(65+FLOOR(RAND(a.column_id*801020 + a.column_id*705020)*12)) + CHAR(65+FLOOR(RAND(a.column_id*6010 + b.object_id)*5)), DATEADD(dd,RAND(a.column_id*600 + b.object_id/1.5)*500,'2008/01/01')

FROM master.sys.columns a CROSS JOIN master.sys.columns b

from a previous post...was that yours? ...