2

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.

  • use VARCHAR(MAX) which will allow you to stored up to 2GB data as column value. – aasim.abdullah Nov 17 '14 at 9:38
  • Thanx aasim.I tried with VARCHAR(MAX) but the result is still same. – Mohan Seth Nov 17 '14 at 9:45
  • Error description itself suggesting you to use MAX: Use of MAX data types when over >8000 characters. (error exceeds the maximum allowed for any data type (8000), can you please share you create table script ? – aasim.abdullah Nov 17 '14 at 9:48
  • I am not facing error as such.My data is getting truncated. – Mohan Seth Nov 17 '14 at 9:52
  • 1
    Nope, data is not being truncated, but it could be the SSMS which is showing you less data. Result to Grid will show you all the data, while for Result to Text, SSMS can show you maximum 8192 characters per column. In SSMS Click on Tools-->Options-->Query Result-->SQL Server-->Results to Text – aasim.abdullah Nov 17 '14 at 9:56
6

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 varchar(max):

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;

See char and varchar and nchar and nvarchar in Books Online.

Not the answer you're looking for? Browse other questions tagged or ask your own question.