LutzM (11/17/2010)
@Dick: VERY IMPORTANT!!!Based on your previous note "4. No clustered index yet " Do NOT run the code until you added a clustered index that supports the order the data need to be updated!!
I'd probably go with
CREATE UNIQUE CLUSTERED INDEX CX_testtable_ImportantForQuirkyUpdate
ON testtable (Category,Class,Location,RowNo)
I knew there was something I was going to add to my post - THANKS LUTZ!
Since you said that the PK was these same four columns - I just make the PK clustered. Since you have a multi-million row table, you will have to consider whether this is what you want to do or not.
Regarding performance:
I duplicated your data to build a Million-Row table (increasing Category value, actually 1.4Mill rows). Waynes code run in 3 seconds (excluding the final SELECT * which took another 17 sec). My system: Home PC with WinXP and SQL2K5.
Range as expected 😀
It took 3 whole seconds on your system? What'd you do, kick off a full system virus scan at the same time? :w00t:
Seriously, that's about the expected time for this to run.
Sorry I forgot to mention the clustered index - and again, thank you Lutz for picking up on that. Dick, that clustered index is supremely important for this to work correctly - otherwise, you'll end up with garbage.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes