Type lengths

Question

Type lengths

anders-731262

Ten Centuries

Points: 1256
More actions
September 8, 2008 at 9:39 pm

#382569

Comments posted to this topic are about the item Type lengths

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

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 1

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 More actions · Answer 2

Nice one!

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

dunnjoe SSCrazy Points: 2180 More actions · Answer 3

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

douglascfast Right there with Babe Points: 776 More actions · Answer 4

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

Query2

-----------

4

Query3

-----------

2

1

Confused - - - as normal

Doug

rajankjohn SSCertifiable Points: 5061 More actions · Answer 5

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 More actions · Answer 6

Ninja's_RGR'us

SSC Guru

Points: 294069

September 9, 2008 at 6:47 am

#869011

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 7

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: 8744 More actions · Answer 8

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

Derek

arehman626 Grasshopper Points: 19 More actions · Answer 9

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 SSCarpal Tunnel Points: 4962 More actions · Answer 10

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: 6193 More actions · Answer 11

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 More actions · Answer 12

Anipaul

SSC-Insane

Points: 24681

September 11, 2008 at 1:16 am

#870141

Good Question...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 13

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: 59351 More actions · Answer 14

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