THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Geek City: Generating Long Strings

(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:


USE tempdb;


IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'test')

                   DROP TABLE test;



(id int

,data text);


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:


id          string_length

----------- --------------------

1           5000

1           8000


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.)


   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.


Have fun!


-- Kalen



Published Saturday, December 9, 2006 4:00 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement