Storing more than 8000 bytes in a column

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?

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.

StackExchange Database Administrators Q#82818, answer score: 10