How do I insert more than 8k in an SQL Server text column using REPLICATE()?

Question

I've got to write a test that requires large amounts of data to be stored in a text column.

When I try this (insert 2 billion X characters):

INSERT INTO table VALUES ( REPLICATE('X', 2000000000) )

This is what I get:

SELECT *, DATALENGTH(textCol) FROM table

XXXXXXXXXXXXX.... 8000

I was hoping for more than 8000. Any ideas where I'm going wrong? My google-fu is failing me.

Caveat: Yes, text columns are deprecated. I'm sure there are lots of very valid and sensible reasons why it's a bad idea to want to use them as bulk data stores. Assume I'm dealing with a legacy system that happens to have text columns storing large amounts of bulk data and I have to write tests to figure out how my bit of third party code can deal with that.

Answer 1

The REPLICATE function returns data in the same type as was passed in. Because you're passing in a normal quoted string, that creates a varchar (not to be confused with a varchar(MAX)), which has a maximum capacity of 8000 characters.

Try this:

INSERT INTO table VALUES ( REPLICATE(convert(varchar(MAX), 'X'), 2000000000) )

I can't let you go without the obligatory recommendation that you use varchar(MAX) (or nvarchar(MAX)) columns instead of text, but this should do the trick. I'm not in front of SSMS right now, but you may have to convert(text, REPLICATE(convert(varchar(MAX), 'X'), 2000000000)), but I don't think so. Either way, this should do the trick.

Answer 2

Just cast inline to a clob datatype (replicate returns same as passed), like this:

INSERT INTO table VALUES ( REPLICATE(cast('X' as varchar(max)), 2000000000) )