Type lengths

Question

Type lengths

Viewing 8 posts - 16 through 22 (of 22 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Reread the question's explaination.

The first parameter in the isnull function determines what data type will be returned. Since one of the column is varchar(1), then when that column is in the parameter 1 spot, isnull returns a varchar(1)... truncating whatever is in column b... but it does not throw a truncation error nor warning for this.

That's why it's called a gotcha...

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

hi,

if you see table creation syntax ,i m using A varchar(40) instead of A varchar

below queries resluts same but WHY????

CREATE

TABLE #TEST(A varchar(40),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));

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Because none of the strings you have there are reaching the 30/40 character limit for the varchar. The ISNULL returns for the first query a data type of VARCHAR(30) and for the second query a VARCHAR(40)

The 'trick' to the original question was than in one case the ISNULL, because of the order of the parameters passed to it, returned a VARCHAR(1). LEN of a VARCHAR(1) can only be 1 or 0

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Thanks GILA...i got the point ....

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

Preet_S SSCrazy Points: 2184 More actions · Answer 5

Questions like these should be worth more than 1 point!

Good 'trick' question!

Preet_S SSCrazy Points: 2184 More actions · Answer 6

Rajan John (9/9/2008)
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?

As Ninja's_RGR'us points out 3,1,3. i.e. Question asks for total number of rows not the Counts returned by those rows.

JamieX Hall of Fame Points: 3763 More actions · Answer 7

I almost went 3,3,3 then took a look at the data types again before submitting .. tricky 🙂

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 8

I got wrong.

But the question is good.

Malleswarareddy
I.T.Analyst
MCITP(70-451)