Forum Replies Created

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

  • RE: On Error () Logging by Using Event Handlers

    Just a quick question... I don't see where the column called "PrcocedureName" in the error log table is either described or used in the article. Is it just 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 27, 2009 at 9:12 pm

    #1016470

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

    Paul White (6/27/2009)

    Clearly, in this case, and with this data distribution, there is nothing to choose between the three methods.

    I'll probably stick to using the Cross-Tab code (one method or...

    --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:57 pm

    #1016468

  • RE: User-defined function not recognized

    jo stovall (6/25/2009)

    Thanks, Jeff.

    When I ran your SQL statement, I got this:

    Server: Msg 208, Level 16, State 1, Line 1

    Invalid object name 'sys.Objects'.

    Privileges, you...

    --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:37 pm

    #1016465

  • RE: printing 1 to 100 in SQL Server as a batch....

    Lynn Pettis (6/26/2009)

    Sinshith S Anand (6/26/2009)

    Hello Friends,

    I made necessary changes on the program. Now it will work smoothly..I will be careful from next time

    Okay, this is a...

    --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:29 pm

    #1016464

  • 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

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