David McFarland (10/22/2007)I'll make a change that will blow your covering index away, and while my change will take 10 seconds, your's will require a changed index, changed code, and...

David McFarland (10/22/2007)The two queries in the second test don't return the same results, as SQL Server happily keeps all of the customer rows together in a group when using...

And for clean test you need to try to reverse order of tests.

With no index in place Server supposes to create statistics on those columns during 1st query and use...

Is password missing in you bcp call or you just removed it befor posting?

bcp message shows you forgot to pass sa password to the Server.

BTW,

can you please post those identical execution plans?

😉

David,

Another false statement from you:

[Code]

--===== Add an index to the CustomerID/TotalDue columns just for grins

--===== Removed the TotalDue from the index, since it quite frankly wouldn't be there in...

Did you include LOG file in those 700 MB?

It's always good to read whole related topics in BOL and try to understand WHY.

Truncating a table is faster than deleting the records in a table because it does uses...

Matt,

you did not answer the question:

How do you think OVER gets data?

It's not about optimisation of queries.

It's about number of queries you execute.

"OVER" option executes 2 subqueries, "INNER" option just...

Matt, do you believe in miracles?

How do you think OVER gets data?

Can you post execution plans for both options of the query?

May be this will help you believe less in...

Of course I messed with whole number divisions and remainders.

Here is correct version (I believe ;))

[Code]

SELECT CreateDate + @NotificationDays/5*7 + @NotificationDays%5 +

...

Greg Snidow (10/21/2007)

---

Kenneth, I am using it for setting the delivery date for email notification in an email staging table. I...

Joe Clifford (10/21/2007)

---

I've got to chime in here, the locks question is entirely dependent on what type of activity is causing the locking. Though the poster didn't tell us...

Jeff Moden (10/22/2007)

---

Heh... I'm sick too! Sick of working on weekends because of other people's poor planning 😛 Just now finishing a gotta-have-it-now project that's due in 5 1/2...

Jeff Moden (10/21/2007)

---

BTW... Sergiy, do you have convenient access to a 2k5 box that you could run these tests on? Would be real good if we had a 3rd...