COUNT, COALESCE, ISNULL, DISTINCT

  • steve.jacobs

    SSCommitted

    Points: 1830

    Comments posted to this topic are about the item COUNT, COALESCE, ISNULL, DISTINCT

  • kapil_kk

    SSC-Insane

    Points: 21316

    Very good question 😛

    Thanks Steve 🙂

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • murray-906152

    Hall of Fame

    Points: 3888

    Steve,

    you state 'NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS )' in your answer but this is very much site specific.

    (I answered on the basis that you were assuming a case insensitive environment.)

    If the database one was playing in was defined with collation SQL_Latin1_General_CP1_CS_AS (for instance), as inherited from the model database on creation, then the results would be difference up front.

    To avoid ambiguity, you could have forced the collation on table creation with:

    CREATE TABLE TEST ( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS);

    Regards, Murray.

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    murray-906152 (11/6/2013)


    Steve,

    you state 'NOTE: You will notice that James was case insenstive (COLLATE SQL_Latin1_General_CP1_CI_AS )' in your answer but this is very much site specific.

    (I answered on the basis that you were assuming a case insensitive environment.)

    If the database one was playing in was defined with collation SQL_Latin1_General_CP1_CS_AS (for instance), as inherited from the model database on creation, then the results would be difference up front.

    To avoid ambiguity, you could have forced the collation on table creation with:

    CREATE TABLE TEST ( NAME VARCHAR(20) COLLATE SQL_Latin1_General_CP1_CI_AS);

    Regards, Murray.

    +1

    Anyway, good question. 🙂

  • This was removed by the editor as SPAM

  • Richard Warr

    SSCertifiable

    Points: 6955

    I knew the CS vs CI "default" point would be raised as soon as I read the question.

    However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.

    _____________________________________________________________________
    MCSA SQL Server 2012

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Thanks for the question!

    Considering that it states "choose 3 answers" and there was no name "JAMES", for me was pretty clear that the author assumed a case insensitive scenario.

  • sqlnaive

    SSCoach

    Points: 17435

    Richard Warr (11/7/2013)


    I knew the CS vs CI "default" point would be raised as soon as I read the question.

    However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.

    +1. Out of few SQL boxes, the box in which i tested this box was CS env, though i understood that by default Steve had CI in mind. Good question. 🙂

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice one...

  • Thomas Abraham

    SSChampion

    Points: 10761

    Good question, even with the case sensitivity issue left open. As a general rule, better to specify server settings that were in place, than to expect answerers to make the correct assumptions.

    Thanks for the question Steve!

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • TomThomson

    SSC Guru

    Points: 104763

    Good question.

    It's generally not a good idea to leave the case sensitivity issue unspecified as a large number of people override the installation default so that use Model database having a case sensitive database collation, resulting in ever database having that case sensitive database collation unless it is explicitly overridden. However in this casethe answer options didn't allow for case insensitivity, so it just meant people had to do that little bit of extra work to get it right.

    Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.

    Tom

  • Ken Wymore

    SSCoach

    Points: 16354

    sqlnaive (11/7/2013)


    Richard Warr (11/7/2013)


    I knew the CS vs CI "default" point would be raised as soon as I read the question.

    However, as the only different answer you get with a CS environment wasn't one of the options then it was pretty safe to assume we were in a case-insensitive world.

    +1. Out of few SQL boxes, the box in which i tested this box was CS env, though i understood that by default Steve had CI in mind. Good question. 🙂

    To me, the answer choices showed that it was assumed that the database was CI. I can see other's points though that case sensitivity settings should be declared in the question if the data raises that question. A good question nonetheless.

  • Revenant

    SSC-Forever

    Points: 42467

    Whew, that took a few minutes... Thanks, Steve!

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4898

    L' Eomot Inversé (11/7/2013)


    Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.

    I think @@ROWCOUNT is displaying what was inserted into the table.

  • TomThomson

    SSC Guru

    Points: 104763

    kevin.l.williams (11/7/2013)


    L' Eomot Inversé (11/7/2013)


    Point 5 in the explanation, although it is a direct quotation from BOL, is actually incorrect: the definition of @@rowcount is a bit more complicated that that, and in this case no rows are affexted but 4 rows are read and it returns 4, not 0. BOL is sometimes a bit sloppy with its definitions.

    I think @@ROWCOUNT is displaying what was inserted into the table.

    You are right; knowing that the statement in BOL (and in the explanation) was wrong I went off in a random direction with two errors:I both applied @@rowcount to the wrong statement, and forgot to count the rows output by that wrong statement. The only case where the number of rows read is used instead of the number of rows affcted is, I think, where a select statement returns rows not because there are no rows that match the selection criteria but because it is an assignment statement and the values assignedare not aggregate.

    Thoroughly stupid of me. :blush:

    Tom

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply