SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Peter Kierstead / VarChar(max)?!? / Latest Posts

RE: VarChar(max)?!?

SQLRNNR — Fri, 10 Jul 2009 13:04:13 GMT

nice to have a clear explanation.Good question

RE: VarChar(max)?!?

Bhuvnesh — Mon, 15 Sep 2008 06:17:53 GMT

--Step 1 Declare @Str VarChar(max);Set @Str=Replicate(cast('*'as varchar(MAX)) ,10000) Print Len(@Str);--Step 2 Declare @Str VarChar(max);Set @Str=Replicate('*' ,10000) Print Len(@Str);----i m hardly got the difference between step 1 and step 2 ..please explain both ????

RE: VarChar(max)?!?

Linson.Daniel — Mon, 18 Aug 2008 00:36:14 GMT

A real good explanation by Simon....Im quiet clear on this one..Thanks Simon:)

RE: VarChar(max)?!?

GSquared — Wed, 13 Aug 2008 08:19:02 GMT

[quote][b]vlad (8/12/2008)[/b][hr][quote]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[/quote]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![/quote]Exactly.

RE: VarChar(max)?!?

bhusank_neuronimbus — Wed, 13 Aug 2008 07:46:20 GMT

Hi Simon,Thank for the explanation of the question.

RE: VarChar(max)?!?

vlad-548036 — Tue, 12 Aug 2008 10:51:12 GMT

[quote]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[/quote]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!

RE: VarChar(max)?!?

GSquared — Tue, 12 Aug 2008 08:48:50 GMT

[quote][b]vlad (8/11/2008)[/b][hr]this makes varchar(max) a [b]special[/b] type ... necessitating special handling aka "workaround" for a bug, maybe ??[/quote]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.

RE: VarChar(max)?!?

Philip Barry — Tue, 12 Aug 2008 02:42:21 GMT

Glad to see a clearer explanation from Simon, had a better understanding after that.

RE: VarChar(max)?!?

vlad-548036 — Mon, 11 Aug 2008 16:07:27 GMT

this makes varchar(max) a [b]special[/b] type ... necessitating special handling aka "workaround" for a bug, maybe ??

RE: VarChar(max)?!?

Tom Garth — Mon, 11 Aug 2008 14:40:52 GMT

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.

RE: VarChar(max)?!?

TDuffy — Mon, 11 Aug 2008 11:40:16 GMT

Nice question. This hasn't burned my yet and this type of question may mean it never does! Thanks.

RE: VarChar(max)?!?

Cliff Jones — Mon, 11 Aug 2008 08:50:39 GMT

Simon, thanks for the explanation. Implicit conversions involving varchar (max) have bitten me a couple of times already. Nice question.

RE: VarChar(max)?!?

GSquared — Mon, 11 Aug 2008 07:11:48 GMT

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.

RE: VarChar(max)?!?

Atif-ullah Sheikh — Mon, 11 Aug 2008 03:54:02 GMT

Nice... Atif Sheikh

RE: VarChar(max)?!?

Anipaul — Mon, 11 Aug 2008 03:09:21 GMT

Nice question.

RE: VarChar(max)?!?

ChiragNS — Mon, 11 Aug 2008 00:58:44 GMT

Yup... i got the same results..

RE: VarChar(max)?!?

Simon Facer — Sun, 10 Aug 2008 20:58:08 GMT

I thought the explanation was really lacking something ... like explainingThe 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 [b]before[/b] assigning the value to the @str field, hence the results is 8000, not 16000 as you might expect.To try and make that clearer ...[code]DECLARE @Str VARCHAR(MAX);-- The Example from the QOTDSET @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);[/code]Gives the results:[code]80002000016000[/code]

VarChar(max)?!?

Peter E. Kierstead — Sat, 09 Aug 2008 23:38:19 GMT

Comments posted to this topic are about the item [B]VarChar(max)?!?[/B]