Len, the following will prevent an index from being used if one is available...

where convert( varchar(10), DateField,112 ) between @T1 and @T2

Papillon,

Would you post the query that makes the output, please... there may be a trick or two we can pull off on this one...

Do you have an example?

And, be aware that because the code uses a global temp table, you should do something to guarantee that only one instance of the code will ever be executed at...

Robert,

Shifting gears a bit, how's the performance of inserts of new records?  How about for a returning customer with a new order?  Your clusterd index is VERY contrary to the...

Nope... other than moving the table as Serqiy suggested (and, is actually the correct way to do it!), the only other way is by trigger...

I'm assuming that the proc you made from my code example creates a temp table within it... that won't work because of the scope of temp tables... you have to...

Gail,

Sorry I don't have time to modify this code snippet I borrowed from some of the production code I wrote for work... lemme give you a couple of hints...

1. "NPA"...

Really?  Easier in the App?  Could I see an example, please?  And don't forget to report what the performance is... thanks.

One test is worth a thousand words... run the following... do note that the AUTHOR table does NOT exist in PUBS...

USE PUBS

GO

     IF OBJECT_ID('dbo.Author') IS NOT NULL --No...

Ummm.... why don't you just store it in a variable and execute it using EXEC or sp_ExecuteSQL ???

You're missing the implied carriage returns both captured in single quotes and by the return of each line... to emphasize...

SELECT 'Hey

'+'Go'

FROM MASTER..SYSDATABASES WITH(NOLOCK)

WHERE DBID>5

Spot on, John...

It's important that it print right on the screen?  Ok.. I have to ask... what are you actually doing with this?  Hopefully you're not doing a cut and paste from...

Ok... One picture is worth a thousand words...

--===== Conditionally drop the temp table

     IF OBJECT_ID('TEMPDB..#Test') IS NOT NULL

        DROP TABLE #Test

DECLARE @Rows INT

 CREATE TABLE...