True, if you do *everything* in dynamic SQL the batch won't matter, since every statement will be run in its own batch. Big overhead for what's being done, but...

I would think you would run into the same issue even with dynamic SQL. SQL will not "know" the other table exists in the column at the time he...

Given the limited info, it's likely because of an improper query plan, typically due to "parameter sniffing" issues.

Try EXEC the stored proc WITH RECOMPILE and see if it runs fast...

Hmm, don't think SQL will recognize the new column within the same batch. It's already "compiled" the batch, so the new column name won't have been there at compile...

Sorry, don't think so. It must be a constant or certain system...

IF NOT EXISTS ( SELECT * FROM syscolumns WHERE name='new_col' AND id=OBJECT_ID('old_table') )

BEGIN

ALTER TABLE dbo.old_table

ADD new_col int

END

-- a GO here should work (??)

GO

Then wouldn't you want:

order by (case...

Sure, you can never guarantee what the optimizer is going to do.

But that's a far cry from a blanket statement of "SQL does not short-circuit IFs".

Is there a reason to...

Maybe SQL doesn't do it on certain IF statements?? That would be unfortunate, since the same principle should apply. Hopefully, if needed, they will add that in the...

Of course just because SQL could short-circuit does not mean that it actually will.

But at least you give it a chance.

http://technet.microsoft.com/en-us/cc678236.aspx

Hi, I'm Nigel Ellis. I'm the development manager for the...

Never??

That's a terrible thought. Why does it waste so many resources on unneeded processing?

I can't believe that. I really hope...

OP didn't actually state only modifications counted as actitity ...

Are SELECTs counted as "activity"?

If no UPDATEs/INSERTs/DELETEs are done on a table for 3 hours, but a SELECT is done every...

Check each query separately for EXISTS, and put the most likely to be true first.

This should allow you to get rid of the UNION (which should be UNION...

I don't object to a calendar table per se, and a specific calendar table does have certain advantages over a tally table.

But I'm not sure that big of a performance...

You have a couple of other methods to reduce down time for a rebuild, although they are more effort.

Is this table involved in Foreign Key relationships?