January 29, 2010 at 12:09 am
What is the maximum limit (in terms of characters) of varchar(max) in SQL server 2005? I mean if I am passing a string to a SP with parameter type as varchar(max) what can be the maximum length of that string?
January 29, 2010 at 12:23 am
8000 characters
Yes, the Maximum length of the string that can be stored in VARCHAR(MAX) variable in SQL Server 2005 is 8000 characters
January 29, 2010 at 12:25 am
I tried it in SQL Server 2008.
I just created a sample stored procedure to find out. It takes an input parameter of varchar(max). And the stored procedure is returning the length of the string being passed. I executed the stored procedure by passing it a string 25935 in length and the result was 25939. Why only 25939 as I didnt want to copy and paste anymore. I was just testing to make sure it can handle more than 8000 characters.
create procedure sample_proc
@sample varchar(max)
as
select LEN(@sample)
go
declare @s-2 varchar(max)
select @s-2 = ''
exec sample_proc @s-2
go
I didnt want to paste the long string here, but you should be able to easily generate one or create it in notepad. It took about 5 seconds.
January 29, 2010 at 12:31 am
Bhavesh, are you sure, you can only store a max of 8000 characters in varchar(max)? Please try and confirm. I dont think that is right. If it can only store 8000 characters why even have varchar(max).
This is from MS site.Here is the link as well. Char and Varchar in SQL Server 2005
char [ ( n ) ]
Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes. The SQL-2003 synonym for char is character.
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying
January 29, 2010 at 12:40 am
Sorry I was Wrong ...
It is 2,147,483,645 characters
The VARCHAR(MAX) variable in SQL Server 2005 can store string with maximum length of 2,147,483,645 characters
January 29, 2010 at 3:34 am
Instead of relying on forum information you could use BOL (BooksOnLine, the SQL Server help system) to get the information you're looking for:
varchar [ ( n | max ) ]
Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying.
8000 is correct as the max number you can specify.
e.g. varchar(8000) will work but varchar(8001)
will return an error:
declare @t1 varchar(8001)
Msg 131, Level 15, State 3, Line 3
The size (8001) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).
If you use max instead of a number, it will hold up to the storage size defined above.
Another option to get an answer to this question would be: open SSMS and give it a try... 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply