Forum Replies Created

Viewing 15 posts - 16,846 through 16,860 (of 59,101 total)

  • RE: % of total count

    Alan.B (9/15/2016)

    Gazareth (9/15/2016)

    Also SUM with OVER()

    DECLARE @table TABLE (stateName varchar(20), Count1 int, TotalChargeOffAmount int);

    INSERT @table

    VALUES

    ('Alabama',5,1000),

    ('Arizona',2,4000),

    ('Arkansas',3,5000);

    SELECT

    stateName,

    Count1,

    TotalChargeOffAmount,

    100.0*Count1 / SUM(t1.[Count1])...

    --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)

    September 16, 2016 at 7:18 am

    #1901334

  • RE: Want to learn SQL

    chatwithgeorgelucas (9/15/2016)

    Dear Friends,

    Please suggest me some sites to learn SQL online. Thanks in advance.

    Lookup "SQL Tutorial" on Yabingooglehoo.

    --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)

    September 16, 2016 at 12:13 am

    #1901270

  • RE: Down Tools Week 2016

    Steve Jones - SSC Editor (9/15/2016)

    Jeff Moden (9/14/2016)

    From the Article:

    An organization wouldn't need to cater food every night.

    Night? Free brainstorming for the company and it's at night?

    I've heard of "Down...

    --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)

    September 15, 2016 at 11:49 pm

    #1901268

  • RE: Add unique valules to a heap

    durga.palepu (9/1/2016)

    Yes, its always a best practice to have a primary key on table to avoid the heap. It can be either simple identity column or any business key.

    Also...

    --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)

    September 15, 2016 at 11:37 pm

    #1901266

  • RE: Searching set of words by ignoring spaces

    Alan.B (8/25/2016)

    ScottPletcher (8/25/2016)

    Rather than literal strings of spaces, I recommend using SPACE(), just for readability:

    /*CREATE FUNCTION ...*/

    SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Text)),

    SPACE(33), ' '),

    SPACE(17), ' '),

    ...

    --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)

    September 15, 2016 at 11:29 pm

    #1901264

  • RE: Shrinking a large DB

    MiguelSQL (9/15/2016)

    We would need to do this several times a year (bad idea... I know)

    I'm not sure why anyone would think that's a bad idea unless there were some PII...

    --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)

    September 15, 2016 at 11:20 pm

    #1901263

  • RE: Shrinking a large DB

    TheSQLGuru (9/15/2016)

    I don't think you need to shrink it to share it with a bunch of developers efficiently. Go get a copy of Redgate's SQL Clone.

    http://www.red-gate.com/products/dba/sql-clone/

    https://www.simple-talk.com/sql/sql-tools/clone-sweet-clone-database-provisioning-made-easy/

    Tell Richard I sent...

    --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)

    September 15, 2016 at 11:16 pm

    #1901262

  • RE: The Danger of xp_cmdshell

    Heh... I think I'm being prodded to publish. 😀

    --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)

    September 15, 2016 at 11:08 pm

    #1901261

  • RE: Index guidance needed

    MickyD (9/15/2016)

    Thanks everyone for the prompt replies.

    I have been given a selection of queries from the users.

    As an example a very basic select returns (669484 row(s) affected) in around 5...

    --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)

    September 15, 2016 at 4:46 pm

    #1901245

  • RE: Index guidance needed

    MickyD (9/15/2016)

    As an example a very basic select returns (669484 row(s) affected) in around 5 mins.

    Hold the phone a minute... WHERE are those rows being returned to? What does...

    --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)

    September 15, 2016 at 4:41 pm

    #1901244

  • RE: Index guidance needed

    MickyD (9/15/2016)

    Hi everyone.

    I recently came across a dev database in our organization that had no clustered indexes and circa 100 million row.....:crazy:

    In order to rectify I need some advice on...

    --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)

    September 15, 2016 at 4:03 pm

    #1901241

  • RE: A Case FOR Cursors...

    Took a quick look, Hugo. Looks great and I don't actually have to figure out how to write a Cursor (I think I've done it only 2 or 3...

    --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)

    September 15, 2016 at 9:45 am

    #1901160

  • RE: A Case FOR Cursors...

    Hugo Kornelis (9/15/2016)

    Jeff Moden (9/14/2016)

    Hugo Kornelis (9/12/2016)

    What I should have written was "a WHILE loop, no matter how well written it is, will never ever beat a (well written) cursor".

    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)

    September 15, 2016 at 9:31 am

    #1901157

  • RE: Add million of records

    sqlenthu 89358 (9/15/2016)

    Jeff Moden (9/14/2016)

    sqlenthu 89358 (9/14/2016)

    Hi all, I have to transfer around 50 million records from one table to another. What should be the best approach. I know it...

    --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)

    September 15, 2016 at 9:22 am

    #1901151

  • RE: Convert Columns to Rows

    tripleAxe (9/15/2016)

    Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with 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)

    September 15, 2016 at 9:11 am

    #1901147

Viewing 15 posts - 16,846 through 16,860 (of 59,101 total)