GilaMonster (7/24/2013)

---

Rob-350472 (7/24/2013)

---

I've added the OPTIMIZE hint to the end of the procedure - I can't quite believe this, results sets from the proc and query are IDENTICAL as a...

Added the no-lock hints back, thankfully this keeps the results the same, literally comment out or don't:

And the proc results set changes from ~90...

Hi Erland,

The results of @@Version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition...

Both were run against a live production server, they're running against tables which log our communications, diferences in row counts (not that significant, a few but not hundreds!) would be...

Those queries both returned the same value, 1 in each case, for both the proc and the code (with ' around ansi_nulls).

The consolidated table only exists once on the audit...

I've sussed it out, the code has this column:

CASE WHEN a.User_ID IN (select c.tgtUserID from Audit..tblConsolidated c) THEN 1 ELSE 0 END as UserConsolidated

If I comment out this part of...

I wondered if someone would pick up on those 😮

I'm aware of their caveats, they're not used in every proc we write but in some they are.

I did wonder how someone would find those useful to be honest, but I'm no plan guru by any means. See attached...

I emphasise that the only change from scripting the...

I've uploaded the two execution plans (I had no idea SSMS tools allowed me to save it as a jpg, nifty!).

There's a couple of maxes but no tops in the...

Hmm, to an extent yes, however, without the objects and data behind it I'm not sure it'll be that useful - basically on the same DB - 'script procedure... Alter...

As with all our Sprocs really,

Then straight into 'Alter Proc...' or 'Create Proc...'

Then the TSQL. Nothing fancy.

There are very subtle differencs in the Exceution plans between the SP and the code, however, they are different.

Glad it's still helping people, on a slight side note, I can't recommend SSMS Tool Pack enough for this kind of thing, a colleague fell foul of a rogue restart...

J. McCarthy (5/10/2013)

---

Further, for maintenance purposes, I find using Access to do quick data edits in a table or view easier than in SSMS. Of course, this is...

The more I've played with this (and made some interesting discoveries along the way, thanks for hte pointers guys!) the more I realise that trying to build a homegrown solution...