Home Forums SQL Server 2008 T-SQL (SS2K8) column value balancing (copy previous value <> NULL) RE: column value balancing (copy previous value <> NULL)

  • November 17, 2010 at 3:25 pm

    #1252188

    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

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2