August 9, 2008 at 11:38 pm
Comments posted to this topic are about the item VarChar(max)?!?
August 10, 2008 at 8:58 pm
I thought the explanation was really lacking something ... like explaining
The REPLICATE function only returns 8000 characters (defaults to VARCHAR(8000)) if the character value to be replicated isn't explicitly CAST as VARCHAR(MAX). As both REPLICATE statements are internally CAST as VARCHAR(8000) fields, the concatenation of the two VARCHAR(8000) fields yields another VARCHAR(8000) field before assigning the value to the @STR field, hence the results is 8000, not 16000 as you might expect.
To try and make that clearer ...
DECLARE @STR VARCHAR(MAX);
-- The Example from the QOTD
SET @STR=REPLICATE('*',10000) + REPLICATE('*',10000);
PRINT LEN(@Str);
-- Explicitly CAST the Replicated character as type VARCHAR(MAX)
SET @STR=REPLICATE(CAST('*' AS VARCHAR(MAX)),10000) + REPLICATE(CAST('*' AS VARCHAR(MAX)),10000);
PRINT LEN(@Str);
-- Explicitly CAST the results from REPLICATE statements as VARCHAR(MAX), without casting the Replicated character as VARCHAR(MAX)
SET @STR=CAST(REPLICATE('*',10000) AS VARCHAR(MAX)) + CAST(REPLICATE('*',10000) AS VARCHAR(MAX));
PRINT LEN(@Str);
Gives the results:
8000
20000
16000
August 11, 2008 at 12:58 am
Yup... i got the same results..
"Keep Trying"
August 11, 2008 at 3:09 am
Nice question.
August 11, 2008 at 3:54 am
August 11, 2008 at 7:11 am
I spotted the implicit conversion and was about to go into the details on it, but then realized Simon had already covered it.
Good question. Understanding implicit conversions is a critical skill for anyone dealing with code.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 11, 2008 at 8:50 am
Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.
August 11, 2008 at 11:40 am
Nice question. This hasn't burned my yet and this type of question may mean it never does! Thanks.
August 11, 2008 at 2:40 pm
Even though the answer was obvious, and even though I had QA open at the time, I went ahead and "Took the Bait". What's 1 lousy point.
Good QotD. Learnt sumpin.
August 11, 2008 at 4:07 pm
this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
August 12, 2008 at 2:42 am
Glad to see a clearer explanation from Simon, had a better understanding after that.
Regards,
Phil
August 12, 2008 at 8:48 am
vlad (8/11/2008)
this makes varchar(max) a special type ... necessitating special handling aka "workaround" for a bug, maybe ??
Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 12, 2008 at 10:51 am
Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.
- GSquared
Despite the fact that implicit conversion maybe good or bad, varchar(max) is a not the same type as varchar(N), 0<=N<=8000 .. something to keep in mind!
August 13, 2008 at 7:46 am
Hi Simon,
Thank for the explanation of the question.
August 13, 2008 at 8:19 am
vlad (8/12/2008)
Nope. Implicit conversion can be a problem on just about any data type. Elimination of implicit conversion would be the only way to handle that, and implicit conversion has too many good uses to get rid of it because of people who don't learn how to handle it correctly.
- GSquared
Despite the fact that implicit conversion maybe good or bad, varchar(max) is a not the same type as varchar(N), 0<=N<=8000 .. something to keep in mind!
Exactly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply