Does varchar(8000) really store 8000 characters?

Question

Post reply

Does varchar(8000) really store 8000 characters?

adonetok

SSCrazy Eights

Points: 8395
More actions
May 24, 2012 at 1:19 pm

#258631

I set one column as varchar(8000) but user still complain that some input was cut off.
Does varchar(8000) really store 8000 characters?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

apparently not, i get about 7999 characters into a varchar(8000), have you considered using varchar(max)?

***The first step is always the hardest *******

Paul White SSC Guru Points: 150468 More actions · Answer 2

adonetok (5/24/2012)
I set one column as varchar(8000) but user still complain that some input was cut off.
Does varchar(8000) really store 8000 characters?

Yes.

DECLARE @String varchar(8000);

SET @String = REPLICATE('ABCDEFGH', 1000);

SELECT DATALENGTH(@String);

If you have a longer string to store, consider varchar(max).

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

David Wiseman SSCommitted Points: 1713 More actions · Answer 3

adonetok (5/24/2012)
I set one column as varchar(8000) but user still complain that some input was cut off.
Does varchar(8000) really store 8000 characters?

VARCHAR(8000) can store 8000 characters. You can prove this by running the following code:

DECLARE @t VARCHAR(8000)

SELECT @t = REPLICATE('T',7999) + 'X'

SELECT LEN(@t),RIGHT(@t,1)

This stores the value "T" 7999 times plus the character "X" as the 8000th character. The select statement checks the length is 8000 characters and returns the last character "X".

If there is a situation you've found where this isn't the case it might be worth posting some details - it could be interesting.

It might not just be a case of changing the column to 8000 chars. The application might input via a stored procedure that declares the variable as VARCHAR(500) etc. Or the truncation might even occur within the application itself.

Hope this helps,

David

DBA Dash - Free, open source monitoring for SQL Server

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

chances are the cut off is happening at the front end where the application textbox char length is set to a lower value.

At what length is it cutting off at ? ur sure its not nvarchar ?

Jayanth Kurup[/url]

adonetok SSCrazy Eights Points: 8395 More actions · Answer 5

I think that the problem is in my code below:

.Parameters.Add(New SqlParameter("@Comments", SqlDbType.VarChar, 255)).Value = _comments

I set @comments as 255 but in SQL table is 8000

Paul White SSC Guru Points: 150468 More actions · Answer 6

adonetok (5/24/2012)
I think that the problem is in my code below:
.Parameters.Add(New SqlParameter("@Comments", SqlDbType.VarChar, 255)).Value = _comments
I set @comments as 255 but in SQL table is 8000

That would be it, yes 🙂

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi