This is expected. It happens under these circumstances:

From: http://support.microsoft.com/kb/815008

This behavior may occur when a row in a table is deleted between the time SQL Server reads the location of...

These have different predicates in the index scan on TurningPointSP3.dbo.CustomerInvoice

View: InvoiceCode, Type, and BillToCode against an implicit conversion between varchar() and nvarchar()

This restricts the actual # of rows here to...

First item of business... head into tools-options in your SSMS, and go to query results node, then SQL Server, then change the following:

Results to Grid: Non XML Data: 65535

Results to...

mar311 (3/14/2011)

---

Unfortunately Wayne that did not work. If you look at the query in my original post, how could we structure the syntax in a way to get your suggestion...

At a guess, your delete is properly using only row locking, but your insert is trying to get its hands on an escalated table lock. Look at the insert...

mymail.default (3/14/2011)

---

I did, and that's why I put the statistics for IO from the exec plans.

My question is why is it that the exec plan for a view is better...

As Paul mentioned check the execution plans. If you could post the view and proc definition that would help as well.

As was brought to my attention (thanks WayneS!) I've neglected to include an @Anchor column in the above script, which helps protect you from wonky index usage. I personally...

jason-772533 (3/14/2011)

---

Thanks Jeff . . . it was puzzling me a bit, but makes sense now - the technique isn't using MAX in its classic sense.

However, I still don't think...

The above will work if it's in the command of the jobstep. If it's buried in SSIS, you're SoL. You can try a trace to track when it's...

Heh, Steve, don't go too nuts. The copy I sent you is all out of order as a...

Thanks Chris, but I agree with Phil, I'm trying to avoid using system level functions, as the framework functions aren't promised to be the same (afaik) between .NET versions and...

I believe you're looking for the ROLLUP operator.

http://msdn.microsoft.com/en-us/library/ms189305(v=sql.90).aspx

Jeff Moden (3/13/2011)

---

Craig Farrell (3/13/2011)

---

Anyone around willing to take a readability review of an article (though will probably be split in two) I'm in the middle of writing? I...

Anyone around willing to take a readability review of an article (though will probably be split in two) I'm in the middle of writing? I need a second set...