February 19, 2009 at 3:16 am
Is there any way to find out the max number of characters the datatypes varchar(max), nvarchar(max) can occupy?
I had a variable of varchar(max) and another of nvarchar(max) and both seemed to occupy max of 65535 characters.
Which datatype occupies the largest number of characters?
February 19, 2009 at 3:52 am
varchar(max), nvarchar(max), varbinary(max), text, image are large value data types. They can store upto 2^31-1 bytes of data (i.e. 2,147,483,647 characters) and with the unicode version you can store upto 2^30-1 (i.e. 1,073,741,823 characters)
--Ramesh
February 19, 2009 at 4:22 am
The maximum size for a VARCHAR(MAX) is 2^31 - 1 bytes / characters.
When i run the below query and check
create table #tmp_xml (xmlrow varchar(max))
insert into #tmp_xml (xmlrow)
select replicate(cast('*' as varchar(max)), 20000000)
select len(xmlrow) from #tmp_xml
The length is correctly output as 20000000.
But the select displays a max of 65535 characters followed by '...'
xmlrow
----------------
*************...
So I guess its not displaying all the characters via select statement.
Is there any way that i can retrieve all the characters?
February 19, 2009 at 4:36 am
Check the attached screenshot.
Max
February 19, 2009 at 4:59 am
Excellent..Thanks!! 🙂
So, I can't change the value of Non-xml data here? I tried but it didn't. Is there a way?
But it gave me an idea. I typecast my result to xml datatype and it works fine!! Thanks!!
February 19, 2009 at 5:23 am
No, sorry, should have mentioned that the screenshot depicts the non-xml length per column as at maximum already.
Max
February 19, 2009 at 6:29 am
If Non-xml datatypes returns max of 65535 characters, then how can we get all the characters that varchar(max) is storing. There must be some way to retrieve the data..
February 19, 2009 at 6:43 am
...Till now, I haven't found an easy way to get all in the pane, but there is a work-around, that needs to split the text in multiple columns, i.e.
DECLARE @LongText VARCHAR(MAX)
SELECT@LongText = replicate(cast('*' as varchar(max)), 70000)
SELECTSUBSTRING( @LongText, 1, 60000 ) AS LongText1,
SUBSTRING( @LongText, 60001, 120000 ) AS LongText2
--Ramesh
February 19, 2009 at 6:57 am
Well, this solved my need today. I casted it to xml datatype.
create table #tmp_xml (xmlrow xml)
insert into #tmp_xml (xmlrow)
select cast(replicate(cast('*' as varchar(max)), 20000000) as xml)
select * from #tmp_xml
February 19, 2009 at 7:05 am
Looks like Ramesh has this sussed, but what do you get for this, as a matter of interest (I don't have 2k5 to play with right now)...
DECLARE @MyEnormousBelly VARCHAR(max)
SET @MyEnormousBelly = replicate(cast('*' AS varchar(max)), 20000000)
CREATE TABLE #tmp_xml (xmlrow varchar(max))
INSERT INTO #tmp_xml (xmlrow)
SELECT @MyEnormousBelly
SELECT @MyEnormousBelly = xmlrow
FROM #tmp_xml
SELECT LEN(@MyEnormousBelly), DATALENGTH(@MyEnormousBelly)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 19, 2009 at 7:15 am
Chris Morris (2/19/2009)
Looks like Ramesh has this sussed, but what do you get for this, as a matter of interest (I don't have 2k5 to play with right now)...
DECLARE @MyEnormousBelly VARCHAR(max)SET @MyEnormousBelly = replicate(cast('*' AS varchar(max)), 20000000)
CREATE TABLE #tmp_xml (xmlrow varchar(max))
INSERT INTO #tmp_xml (xmlrow)
SELECT @MyEnormousBelly
SELECT @MyEnormousBelly = xmlrow
FROM #tmp_xml
SELECT LEN(@MyEnormousBelly), DATALENGTH(@MyEnormousBelly)
....A long break, a cup of coffee and a resultset with values 2000000020000000:w00t:
--Ramesh
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply