Forum Replies Created

Viewing 15 posts - 16 through 30 (of 4,080 total)

  • Reply To: Transaction log and updates on large tables

    Paul is certainly a worthwhile authority.   I will study his article later.   I did notice this quote, which was emphasized:

    A clustered table will always produce full logging and dirty buffer...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 16, 2021 at 7:10 pm

    #3872146

  • Reply To: Transaction log and updates on large tables

    Same result.    Identical timings and statistics.    Potential update to a unique clustered index column does cause additional I/O, but it's because the query plan has to include a...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 16, 2021 at 6:42 pm

    #3872136

  • Reply To: Transaction log and updates on large tables

    Scott, could you please cite some authority to that effect?     I couldn't find it with some quick googling, so I ran an experiment.    The code below is showing...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 16, 2021 at 4:58 pm

    #3872092

  • Reply To: Problem using While to loop from record

    ZZartin is right.    It's not only easier to code, it's faster when executing.

    INSERT INTO TargetTable (ColX, ColY, ColZ)
    SELECT ColA, ColB, ColC
    FROM SourceTable
    WHERE ...

    Plenty of examples are...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 14, 2021 at 4:10 pm

    #3871128

  • Reply To: Top 1 makes very fast query into very slow query

    "I guess it takes the HASH directive into account for the whole query, not just the one JOIN where it is specified."

    When you force the HASH join on one table,...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 14, 2021 at 3:59 pm

    #3871119

  • Reply To: Help with a query

    I would not get into the habit of using OR to test variable values when trying to add flexibility to queries like this.   For one thing, you get a single...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 14, 2021 at 3:34 pm

    #3871108

  • Reply To: Top 1 makes very fast query into very slow query

    Yes.    Eirikur's version of DelimitedSplit8k is fast AND is an inline table valued function.   Everyone who can't use String_Split should get a copy.   Also, use of an inline table-valued...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 13, 2021 at 9:00 pm

    #3870705

  • Reply To: Top 1 makes very fast query into very slow query

    Looking at the two plans, it seems that the TOP 1 version tries to avoid the sort.   (With disastrous results.)    Apparently it can read the correct sequence from the...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 13, 2021 at 7:22 pm

    #3870673

  • Reply To: RunningTotal column not working

    You are absolutely correct, Jeff.

    select conta, descritivo, edeb
    ,RunningTotal = SUM(edeb) OVER(PARTITION BY conta ORDER BY dinome, NrLanc ROWS UNBOUNDED PRECEDING)
    from  #tmpTable
    order by conta, dinome, NrLanc

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 13, 2021 at 6:50 pm

    #3870659

  • Reply To: Help with a query

    I would do this with dynamic SQL.   Build the basic query string and then add the WHERE clause only when both date parameters are null.    Using sp_ExecuteSQL allows you...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 13, 2021 at 6:39 pm

    #3870656

  • Reply To: Help with a query

    What if one of the date parameters is populated but the other isn't?

     

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 13, 2021 at 6:18 pm

    #3870639

  • Reply To: RunningTotal column not working

    Here is a quick example of using SUM() with an OVER clause to create a running total without having to UPDATE.  It uses the data from your #tmpTable.

    __________________________________________________
    

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller 
    
Stop, children, what's that sound?   Everybody look what's going down. -- Stephen Stills

    April 12, 2021 at 3:39 pm

    #3870171

  • Reply To: How do I JOIN these records?

    Good catch, Scott.   I wasn't thinking about his use of a LEFT Join.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 3, 2021 at 8:43 pm

    #3865131

  • Reply To: How do I JOIN these records?

    You could also do it with a cross applied subquery.     Whether or not it this a "better" way usually means which runs faster.     Performance will depend on the...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 2, 2021 at 3:12 pm

    #3864797

  • Reply To: insert column data from one table, parse and insert into other table

    You already have a splitter function available in STRING_SPLIT().   It will parse the CSV string into a set of rows with a single column heading of [Value].

    SELECT...

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

    April 1, 2021 at 7:06 pm

    #3864544

    • Viewing 15 posts - 16 through 30 (of 4,080 total)