I have a column in one of the tables in my SQL Server database that can have more than 8000 characters. As the maximum allowed character length for a column in SQL Server is 8000, how can I store the data which is having more than 8000 characters data?
closed as unclear what you're asking by Remus Rusanu, Mark Storey-Smith, Paul White♦, Max Vernon, Aaron Bertrand♦ Nov 17 '14 at 15:07
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
There are two things to consider if you want to store values > 8000 bytes in a single column in SQL Server.
First, the column must be capable of holding values of this length. For strings, this is typically done by giving the column a type of
varchar(max) (for single-byte characters) or
nvarchar(max) (for Unicode). As an example, the following table variable allows > 8000-byte strings in its column:
DECLARE @Example AS table ( LongColumn varchar(max) NULL );
Second, you must ensure that any value you attempt to store in this column is also typed as e.g.
varchar(max). For example, the following results in truncation, because the constructed string is implicitly typed as varchar (not varchar(max)) and therefore limited to 8000 bytes:
DECLARE @Example AS table ( LongColumn varchar(max) NULL ); INSERT @Example (LongColumn) VALUES (REPLICATE('x', 9000)); -- 'x' is implicitly varchar SELECT E.LongColumn, DATALENGTH(E.LongColumn) FROM @Example AS E;
The following code works correctly because it constructs a string of type
DECLARE @Example AS table ( LongColumn varchar(max) NULL ); INSERT @Example (LongColumn) VALUES (REPLICATE(CONVERT(varchar(max), 'x'), 9000)); SELECT E.LongColumn, DATALENGTH(E.LongColumn) FROM @Example AS E;