Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Type lengths Expand / Collapse
Author
Message
Posted Tuesday, September 9, 2008 9:26 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 3:37 PM
Points: 3,123, Visits: 1,256
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.


Post #566264
Posted Tuesday, September 9, 2008 11:54 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
It happens every time. I hate being caught with a tricky one.

Good QD.


Tom Garth
Vertical Solutions

"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
Post #566365
Posted Thursday, September 11, 2008 1:16 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
Good Question...


Post #567514
Posted Monday, September 15, 2008 5:29 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #569350
Posted Monday, September 15, 2008 5:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #569354
Posted Monday, September 15, 2008 7:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 12:52 AM
Points: 21,385, Visits: 9,601
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...
Post #569409
Posted Monday, September 15, 2008 10:00 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
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
Post #569916
Posted Tuesday, September 16, 2008 2:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 42,450, Visits: 35,505
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 2008, MVP
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

Post #570018
Posted Tuesday, September 16, 2008 3:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 14, 2014 4:48 AM
Points: 2,834, Visits: 3,950
Thanks GILA...i got the point ....

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #570026
Posted Tuesday, September 16, 2008 7:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
Questions like these should be worth more than 1 point!
Good 'trick' question!
Post #570223
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse