Type lengths

  • anders-731262

    Ten Centuries

    Points: 1256

    Comments posted to this topic are about the item Type lengths

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Good Question!

    The information in the MSDN link is hard to grasp in my opinion (it is a long way from reading "must be implicitly convertible" to understanding that the conversion may result in loss of data), but the example provided makes it very clear.

    Thanks for this QOTD!

    Best Regards,

    Chris Büttner

  • SuperDBA-207096

    SSCrazy Eights

    Points: 8176

    Nice one!

    Coalesce is a pretty useful function - it comes in handy when dealing with nulls....

  • dunnjoe

    SSCrazy

    Points: 2180

    Good question! I had to do some investigation of coalesce which looks like a very useful function.

  • douglascfast

    Right there with Babe

    Points: 776

    Did I miss something? If two columns are 1 in lengh, and the others are 9 "some text" and 14 "Some more text", then would not the count(*) give you 2 (1 length) 1(9 lenght) and 1(14 lenght).

    I did this on my database and i got

    Query1

    -----------

    2

    1

    1

    Query2

    -----------

    4

    Query3

    -----------

    2

    1

    1

    Confused - - - as normal

    Doug

  • rajankjohn

    SSCertifiable

    Points: 5061

    douglascfast (9/9/2008)


    Did I miss something? If two columns are 1 in lengh, and the others are 9 "some text" and 14 "Some more text", then would not the count(*) give you 2 (1 length) 1(9 lenght) and 1(14 lenght).

    I did this on my database and i got

    Query1

    -----------

    2

    1

    1

    Query2

    -----------

    4

    Query3

    -----------

    2

    1

    1

    Confused - - - as normal

    Doug

    I also got the same results. Anything missing?

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    Yes, that's 3 rows, 1 row, 3 rows...

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Hi Doug,

    thats exactly the purpose of the question.

    If you execute SELECT ISNULL(A,B) FROM #Testyou will get four 1-char strings because of the implicit conversion.

    A

    S

    B

    S

    Since the length of these is 1, the group by returns only one row.

    Hope this helps.

    Best Regards,

    Chris Büttner

  • StarNamer

    SSCrazy Eights

    Points: 8633

    I knew there was a reason I'd switched to preferring COALESCE over ISNULL... 🙂

    Derek

  • arehman626

    Grasshopper

    Points: 19

    Let's go step by step.

    CREATE TABLE #TEST(A varchar,B varchar(30)); ---> Field named A is of size 1 with type Varchar.

    ISNULL(A,B) ---> means:evaluate the field A, (the first parameter), if has something other than a NULL then return it to me otherwise return me the value converted to Varchar size 1 (As A is Varchar (1)) from varchar size 30 in field B for current record.

    So if you run

    SELECT A, B, ISNULL(A,B),LEN(ISNULL(A,B)) FROM #Test

    A B

    ---- ------------------------------ ---- -----------

    A NULL A 1

    NULL Some text S 1

    B NULL B 1

    NULL Some more text S 1

    so if you now run

    SELECT COUNT(*),LEN(ISNULL(A,B))

    FROM #TEST

    GROUP BY LEN(ISNULL(A,B));

    you'll see

    ----------- -----------

    4 1

    And

    SELECT A, B, ISNULL(B,A),LEN(ISNULL(B,A)) FROM #Test

    A B

    ---- ------------------------------ ------------------------------ -----------

    A NULL A 1

    NULL Some text Some text 9

    B NULL B 1

    NULL Some more text Some more text 14

    and run

    SELECT COUNT(*),LEN(ISNULL(B,A))

    FROM #TEST

    GROUP BY LEN(ISNULL(B,A));

    ----------- -----------

    2 1

    1 9

    1 14

    Now let's talk about COALESCE(A,B)

    COALESCE returns the first non-null expression among its arguments,

    If you run

    SELECT LEN(COALESCE(A,B)),COALESCE(A,B)

    FROM #TEST

    then

    ----------- ------------------------------

    1 A

    9 Some text

    1 B

    14 Some more text

    so

    SELECT COUNT(*),LEN(COALESCE(A,B))

    FROM #TEST

    GROUP BY LEN(COALESCE(A,B));

    ----------- -----------

    2 1

    1 9

    1 14

    .

    I hope this has been useful.

    Abdul Rehman

  • kevin.l.williams

    SSCarpal Tunnel

    Points: 4916

    Thank you very much for this QOTD! I have been using COALESCE only when I was working with more than two values and was totally unaware of this behavior of ISNULL.

  • Tom Garth

    SSCertifiable

    Points: 6173

    It happens every time. I hate being caught with a tricky one.

    Good QD.

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Anipaul

    SSC-Insane

    Points: 24681

    Good Question...

  • Bhuvnesh

    SSC Guru

    Points: 59344

    CREATE TABLE #TEST(A varchar,B varchar(30));

    INSERT INTO #TEST(A) VALUES('A');

    INSERT INTO #TEST(B) VALUES('Some text');

    INSERT INTO #TEST(A) VALUES('B');

    INSERT INTO #TEST(B) VALUES('Some more text');

    SELECT COUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A));

    go

    SELECT COUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));

    Why above queries are giving differentt results?????????

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Bhuvnesh

    SSC Guru

    Points: 59344

    CREATE TABLE #TEST(A varchar,B varchar(30));

    INSERT INTO #TEST(A) VALUES('A');

    INSERT INTO #TEST(B) VALUES('Some text');

    INSERT INTO #TEST(A) VALUES('B');

    INSERT INTO #TEST(B) VALUES('Some more text');

    SELECT COUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A));

    go

    SELECT COUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));

    Why above queries are giving differentt results?????????

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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