Forum Replies Created

Viewing 15 posts - 43,216 through 43,230 (of 59,098 total)

  • RE: Formatting data from two rows

    NewBie (6/23/2009)

    Jeff Moden (6/23/2009)

    Will there ever be 3 rows?

    No, only 2 rows. But why do you ask ?

    Just to be sure. Problems like this usually fail sometime in the...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 27, 2009 at 8:23 pm

    #1016462

  • RE: Converting non-clustered index to clustered index

    Eswin (6/24/2009)

    Hi jeff,

    The story is i have non-clustered PK .

    I want to make this non-clustered PK to clustered PK in sql server 2000 because it will improve performance i guess.

    When...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 27, 2009 at 8:12 pm

    #1016461

  • RE: converting lowercase to uppercase

    sqlguru (6/24/2009)

    The database is not suppose to format data on the fly, that's not it's job. RDMS are very weak at computational stuff like formatting. This should be handled in...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 27, 2009 at 7:35 pm

    #1016458

  • RE: Are the posted questions getting worse?

    Florian Reischl (6/27/2009)

    I read your really good article and did my own tests. If multi-aggregated results are required the cross-tab solution is much faster than pivot. But in case of...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 27, 2009 at 7:16 pm

    #1016455

  • RE: pivot problem

    Thanks for the feedback, Vick. If you want that to fly, convert it to a bit of pre-aggregation. Like this...

    [font="Courier New"];WITH

    ctePreAgg AS

    (

     SELECT BatchID, ParamName, MIN(ParamValue) AS MinParamValue

       FROM #Foo

      GROUP BY BatchID, ParamName

    )

     SELECT BatchID,

            MIN(CASE WHEN ParamName = 'outfolder' THEN ParamValue ELSE NULL END) AS OutFileLocation

            MIN(CASE WHEN ParamName = 'outfile'   THEN ParamValue ELSE NULL END) AS OutFileName

       FROM ctePreAgg

      GROUP BY BatchID[/font]

    Put an index on BatchID, ParamName with an...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 10:22 pm

    #1016350

  • RE: Are the posted questions getting worse?

    Roy Ernest (6/26/2009)

    GilaMonster (6/26/2009)

    Grant Fritchey (6/26/2009)

    And if things get REALLY bad, we'll just toss Gail into the middle of them. Although, that might be considered a cruel thing to do...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 8:17 pm

    #1016330

  • RE: Are the posted questions getting worse?

    Florian Reischl (6/26/2009)

    lmu92 (6/24/2009)

    Hi folks,

    would someone with execution plan background mind to take a look at this post and verify if I'm on the right track or misguiding the OP?

    The...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 7:57 pm

    #1016327

  • RE: How to transpose from Row to Columns with out using Pivot

    Heh... I almost forgot... you can get a tiny bit more speed out of it if you move the pre-aggregation from the derived table to a CTE like this...

    [font="Courier New"];WITH 

    ctePreAgg AS

    (--==== Pre-aggregate the data.  This will obviously work much better with the correct index

     SELECT Acct_Debtor, Occurance, MAX(LandLine_Contact_No) AS Max_LandLine_Contact_No

       FROM dbo.Post_File082_Landline_No

      GROUP BY Acct_Debtor, Occurance

    )

     SELECT preagg.Acct_Debtor, 

            MAX(CASE WHEN preagg.Occurance=1 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber1,

            MAX(CASE WHEN preagg.Occurance=2 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber2,

            MAX(CASE WHEN preagg.Occurance=3 THEN preagg.Max_LandLine_Contact_No ELSE NULL END) AS LandLineNumber3

       FROM ctePreAgg AS preagg

      GROUP BY preagg.Acct_Debtor[/font]

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 7:56 pm

    #1016326

  • RE: How to transpose from Row to Columns with out using Pivot

    Paul White (6/25/2009)

    Cool stuff Chris. Of course it leaves me wondered about the hash aggregate versus stream aggregate thing but hey.

    So PIVOT can be slightly more efficient - if...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 7:43 pm

    #1016325

  • RE: How to transpose from Row to Columns with out using Pivot

    lmu92 (6/22/2009)

    You don't need the subselect:

    SELECT

    ACCT_DEBTOR,

    MAX(Case WHEN OCCURRENCE=1 THEN LANDLINE_CONTACT_NO ELSE null END) AS LandLineNumber1,

    MAX(Case WHEN OCCURRENCE=2 THEN LANDLINE_CONTACT_NO ELSE null END) AS LandLineNumber2,

    MAX(Case WHEN OCCURRENCE=3 THEN LANDLINE_CONTACT_NO ELSE...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 7:34 pm

    #1016324

  • RE: select count(*) in a partitioned table takes 100%cpu

    In that case, if the CPU spikes for more than an hundred milliseconds or so, I'd have to say something is wrong.

    Can't help without a bit more information:

    1. How...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 6:58 pm

    #1016321

  • RE: 911 - Cursor killing my trigger

    Scott Coleman (6/26/2009)

    Sorry, I got here through "Active Threads" and didn't notice that it was 2000.

    You can use a self-join solution, although depending on your rowcounts and indexes this may...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 3:29 pm

    #1016299

  • RE: Good SQL Server article

    Florian Reischl (6/26/2009)

    Sure, it's a task on my list, too. 😉 But it's really neat that he provides a complete test setup.

    I agree... it just like some of the good...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 3:25 pm

    #1016297

  • RE: Good SQL Server article

    Florian Reischl (6/26/2009)

    Jeff Moden (6/26/2009)

    jaclynmcatanzaro (6/26/2009)

    Good SQL Server article

    http://www.informationflash.com/%5B/url%5D

    Which one?

    Can't answer your question but the new article by Adam Machanic is really good in my opinion:

    The Hidden Costs of INSERT...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 3:19 pm

    #1016295

  • RE: What has been your experience of agile development

    Unless you're very careful, it becomes nothing more than an excuse for releasing bad or slow product with no documentation. Contrary to what many believe, it does take some...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

    June 26, 2009 at 3:08 pm

    #1016292

Viewing 15 posts - 43,216 through 43,230 (of 59,098 total)