Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Type lengths Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, September 09, 2008 9:26 AM
 SSCrazy Group: General Forum Members Last Login: Wednesday, December 04, 2013 5:04 PM Points: 2,952, Visits: 1,206
 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 09, 2008 11:54 AM
 SSC Eights! Group: General Forum Members Last Login: Friday, February 04, 2011 7:20 AM Points: 977, Visits: 1,499
 It happens every time. I hate being caught with a tricky one.Good QD. Tom GarthVertical 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 Group: General Forum Members Last Login: Thursday, November 07, 2013 7:19 AM Points: 5,023, Visits: 1,363
 Good Question...
Post #567514
 Posted Monday, September 15, 2008 5:29 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 06, 2013 5:59 AM Points: 2,817, Visits: 3,899
 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));goSELECT 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 Group: General Forum Members Last Login: Friday, December 06, 2013 5:59 AM Points: 2,817, Visits: 3,899
 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));goSELECT 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 Group: General Forum Members Last Login: Friday, June 21, 2013 1:53 PM Points: 21,376, Visits: 9,590
 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 Group: General Forum Members Last Login: Friday, December 06, 2013 5:59 AM Points: 2,817, Visits: 3,899
 hi,if you see table creation syntax ,i m using A varchar(40) instead of A varcharbelow queries resluts same but WHY????CREATETABLE #TEST(A varchar(40),B varchar(30)); INSERTINTO #TEST(A) VALUES('A'); INSERTINTO #TEST(B) VALUES('Some text'); INSERTINTO #TEST(A) VALUES('B'); INSERTINTO #TEST(B) VALUES('Some more text'); SELECTCOUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A)); goSELECTCOUNT(*) 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 Group: General Forum Members Last Login: Today @ 11:57 PM Points: 40,103, Visits: 32,827
 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 ShawMicrosoft Certified Master: SQL Server 2008, MVPSQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enterWe stand on the bridge and no one may pass
Post #570018
 Posted Tuesday, September 16, 2008 3:05 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 06, 2013 5:59 AM Points: 2,817, Visits: 3,899
 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 Group: General Forum Members Last Login: Friday, November 22, 2013 10:54 AM Points: 236, Visits: 238
 Questions like these should be worth more than 1 point!Good 'trick' question!
Post #570223

 Permissions