(I’ll admit that this post isn’t really really geeky, but it is prerequisite detail for a geeky storage topic next time, so it’s really just for geeks.)
The new large object datatype capabilities require that you start thinking about storage in new ways. In my next post I’ll show you my favorite metadata queries for detecting how many pages a table or database is using to store these new large object datatypes, but today, I’ll tell you a trick about how you can generate large data values.
SQL Server provides a function called replicate, which takes 2 arguments: a character string and an integer (N) and returns an output string consisting of the input string repeated N times.
For example, execute this SELECT:
SELECT replicate ('ab', 4)
And the result is:
So I can use the replicate functions to generate strings of 1000’s of characters in length. A LOB (large object) value needs to be more than 8000 bytes in length, so we can try using replicate. Here I’ll create a table with a text column, and insert two rows into it, and then check the length of the text column:
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'test')
DROP TABLE test;
CREATE TABLE test
INSERT INTO test VALUES (1,replicate('a',5000));
INSERT INTO test VALUES (1,replicate('a',15000));
SELECT id, datalength(data) AS string_length FROM test;
I get these results:
The replicate function is not returning more than 8000 bytes, because it returns a value of the same datatype as the input value. And the default datatype of a constant string is a normal varchar, with an 8000 byte maximum. If we want to generate a string longer than 8000 bytes, we have to give it a datatype which can hold more than 8000 bytes, e.g. varchar(max). We have to cast the constant as the desired datatype inside the replicate function, as shown. (Note that we cannot use a value of type text as input to the replicate function.)
INSERT INTO test
VALUES (1,replicate(cast('a' as varchar(max)),15000))
Now check the data lengths using the same query as above, and you should see we now have a data column holding more than 8000 bytes.