September 15, 2008 at 10:05 pm
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');
------------------------------------------------------------
CREATE
TABLE #TEST2(A varchar,B varchar(30));
INSERT
INTO #TEST2(A) VALUES('A');
INSERT
INTO #TEST2(B) VALUES('Some text');
INSERT
INTO #TEST2(A) VALUES('B');
INSERT
INTO #TEST2(B) VALUES('Some more text');
Q1:
SELECT
COUNT(*)FROM #TEST GROUP BY LEN(ISNULL(B,A));
go
Q2:
SELECT
COUNT(*) from #TEST GROUP BY LEN(ISNULL(A,B));
Q3:
SELECT
COUNT(*)FROM #TEST2 GROUP BY LEN(ISNULL(B,A));
go
Q4:
SELECT
COUNT(*) from #TEST2 GROUP BY LEN(ISNULL(A,B));
if you see results of above query the functionality of LEN(ISNULL(B,A)) changes
just because ini #test2 i m declaring A as varchar instead of varchar(40)
Can any body explain WHY ???
i m apolgise that i m re-posting this Question:):)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 16, 2008 at 2:45 am
Already asked in the appropriate Question of the Day thread
http://qa.sqlservercentral.com/Forums/Topic565885-1370-1.aspx
Please don't post multiple threads with the same question. It wastes people's time, it breaks replies up and results in people answering already answered questions.
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
March 7, 2011 at 6:07 am
Its because of ISNULL() function. ISNULL(exp1, expr2) always returns expr1 if expr1 is not null. Else expr2 typecasted to expr1. As column 'A' is of varchar with no length.. so default 1 character in length. When evaluating ISNULL(), when column A is null, it type casted value of columnB to 1 char i.e., trimmed rest of characters.. As column 'A' contained values "A" and "B".. so len(ISNULL(A,B)) returned 1 each. When ColumnA is NULL and columnB contained values 'Some text' and 'some more text'... it has taken first character because of expr1 in ISNULL(). So len(ISNULL(A,B)) again returned 1 each. Alltogether there are 4 1's. So when GROUP BY is applied, COUNT(*) gave you 4. 🙂
March 7, 2011 at 11:55 am
Please note: 3 year old thread.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply