Forum Replies Created

Viewing 15 posts - 43,321 through 43,335 (of 59,098 total)

  • RE: Are the posted questions getting worse?

    Heh... Ok... Remember... you asked.:-)

    Here's why... the original code looks like this (for example)....

    [font="Courier New"]--===== Create and populate the Tally table on the fly

     SELECT TOP 1000000 --equates to more than 30 years of dates

            IDENTITY(INT,1,1) AS N

       INTO dbo.Tally

       FROM Master.dbo.SysColumns sc1,

            Master.dbo.SysColumns sc2

    --===== Add a Primary Key to maximize performance

      ALTER TABLE dbo.Tally

        ADD CONSTRAINT PK_Tally_N 

            PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

    --===== Allow the general public to use it

      GRANT SELECT ON dbo.Tally TO PUBLIC[/font]

    If I throw that code into a code window in the presence of IE,...

    --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 20, 2009 at 11:13 pm

    #1012961

  • RE: converting lowercase to uppercase

    smsam1 (6/20/2009)

    i want to store only T or F in the flag field . for example if i enter 't' it should be stored as 'T' and vice versa.

    Why? ...

    --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 20, 2009 at 10:41 pm

    #1012958

  • RE: CLR Triggers

    haroonrashed786 (6/20/2009)

    but..i have to use CLR triggers only

    As previous suggested, you really need to tell us why you are constrained to using only CLR triggers. Even CLR experts like...

    --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 20, 2009 at 10:39 pm

    #1012957

  • RE: Common questions asked in SQL Server DBA Interview

    akash.suryan (6/18/2009)

    Thanks Grant.....

    I will love to see those questions please let me know.....

    You missed the point. After the first two or three questions, the questions are mostly not planned....

    --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 20, 2009 at 10:33 pm

    #1012956

  • RE: Any Utility available to convert Sql Server Stored Procedures into user defined functions

    It's probably not the answer you want to hear but, there's a large amount of functionality in many stored procedures that simply cannot be migrated to a function. Just...

    --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 20, 2009 at 10:13 pm

    #1012949

  • RE: Going Native

    Heh... like everything else, "It Depends". 😛 I'll do it 3 different ways depending on the situation and the database... use the Wizard to setup a plan, use 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 20, 2009 at 10:05 pm

    #1012946

  • RE: need help transforming a table's column to rows

    ktlady (6/20/2009)

    Jeff, thanks for the pointer. Sorry that I missed it the first time. It sure is a great article! There is so much to learn for SQL server!

    It's ok......

    --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 20, 2009 at 9:53 pm

    #1012940

  • RE: need help transforming a table's column to rows

    As a side bar, for those interested in a Tally table solution that will work in virtually any release of SQL Server...

    [font="Courier New"]--===== Build the test table as the data source

     CREATE TABLE dbo.TableA (Column1 VARCHAR(5), Column2 VARCHAR(30))

     INSERT INTO dbo.TableA

            (Column1, Column2)

     SELECT 'a1', '1:3:5:6' UNION ALL

     SELECT 'a2', '2:4:5'

    --===== Solution for virtually any version of SQL Server

     INSERT INTO dbo.TableB

            (Column1, Column2)

     SELECT a.Column1,

            SUBSTRING(a.Column2, t.N+1, CHARINDEX(':', a.Column2, N+1) - N-1) AS Column2

       FROM dbo.Tally t

      CROSS JOIN 

            (SELECT Column1, ':'+Column2+':' AS Column2 FROM dbo.TableA) a

       WHERE N < LEN(a.Column2)

         AND SUBSTRING(a.Column2, N, 1) = ':'

    [/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 20, 2009 at 9:41 pm

    #1012937

  • RE: need help transforming a table's column to rows

    Florian Reischl (6/20/2009)

    If you don't know a Table or how to work with please search this site. You will find a really good article published by Jeff Moden which explains...

    --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 20, 2009 at 9:18 pm

    #1012934

  • RE: Scalar UDF Performance Issue

    Nice article and great explanation in the article... too bad they didn't actually test it for performance... 😉 Both of the following UDF's render identical execution plans and they...

    --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 20, 2009 at 9:12 pm

    #1012932

  • RE: Week numbers and week start dates

    balars_2000 (6/14/2009)

    Thanks Jeff. Really appreciate your help mate.

    Sorry for the late feedback on my part. Thank you for your's, Balars... it's the only "payment" we get for doing stuff...

    --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 20, 2009 at 7:20 pm

    #1012917

  • RE: HOW TO AVOID FUNCTIONS ON MERGE STATEMENTS.

    Sorry for the delay. I agree that if you have any leading spaces, you'll need to do one of two things... so far as I'm concerned, the best thing...

    --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 20, 2009 at 6:47 pm

    #1012914

  • RE: High volume of transactions definition/measure?

    Steve Jones - Editor (6/15/2009)

    I think the classification is just to have a frame of reference. It does always change, but it helps to get an idea of scale for...

    --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 20, 2009 at 6:29 pm

    #1012913

  • RE: Grouping problem

    jcrawf02 (6/18/2009)

    . . . and then there are the unfortunate few who keep seeing you lob out these answers, but are still anxiously awaiting 2005 servers. Luckily everyone seems to...

    --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 20, 2009 at 6:25 pm

    #1012912

  • RE: Crosstab type report -- Horizontal Summray

    Joseph Henry (5/14/2009)

    Jeff:

    First and foremost, thank you very much for the help you have been providing me. It is helping me to both learn more and have a stronger...

    --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 20, 2009 at 6:17 pm

    #1012911

Viewing 15 posts - 43,321 through 43,335 (of 59,098 total)