How can I insert over 8000 bytes into SQL using C#?

Question

How can I insert over 8000 bytes into SQL using C#? I have a large amount of text I want to store in a varchar, 8000 bytes seems to be the limit, can I increase this?

Thanks

Answer 1

which version of SQL are you using? SQL 2008 supports the varchar(MAX) variable which allows a much larger amount of text.

Update: Sorry, that's Microsoft SQL 2005 and greater ... varchar(MAX) can hold 2GB of data .... you didn't specify if you're using MS-SQL so I made an assumption.

Answer 2

Use nvarchar(max) or varchar(max), as nText and Text are deprecated.

Answer 3

From SQL2005 onwards, you can define VARCHAR(MAX) or NVARCHAR(MAX) which does give you all the benefits of a VARCHAR without the downsides of TEXT.

Answer 4

You can use nText for anything in unicode over 8000 characters. The size of the nText field is 1,073,741,823 characters. If you're not using unicode, and need even more space, you can use Text, which has a capacity of 2,147,483,647 characters.

However, nText and Text have been deprecated in favor of using VARCHAR(MAX), which can hold up to 2 GB of data. The VARCHAR(MAX) data type uses an overflow page for anything over 8 KB in a row, so performance may suffer if this gets too out of control.

Answer 5

FYI : exec function has limit of 8000 bytes as well example:

EXEC 'Insert into bla(col) values(''data'')'  

this will fail if the over all string sent to exec function is over 8000 bytes

to mitigate this issue use

EXEC sp_executesql @tmpUpdQuery,
               N'
               @PtmpColValue varchar(max),
               @Pident varchar(max)' ,                                          
               @tmpColValue,
               @tmpId;