Have you ever needed to repeat a string multiple times in SQL Server? If so, the REPLICATE function is here to help. In today’s blog post, we will explore the REPLICATE function and discuss some interesting aspects of its behavior.

Let’s start with a simple example. Consider the following query:

SELECT 'Ha' + REPLICATE('ha', 20)

The result of this query will be a string that consists of the word “Ha” followed by the string “ha” repeated 20 times. So, the output will be “Ha” followed by “hahahahahahahahahahahahahaha”.

Now, let’s move on to a more intriguing scenario. Take a look at the following code:

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)

SET @FirstString = REPLICATE('A', 4000)
SELECT LEN(@FirstString) AS LenFirstString;

SET @SecondString = REPLICATE('B', 8000)
SELECT LEN(@SecondString) AS LenSecondString;

SET @ThirdString = REPLICATE('C', 11000)
SELECT LEN(@ThirdString) AS LenThirdString;

In this case, we are using the REPLICATE function to create three strings: @FirstString, @SecondString, and @ThirdString. We then use the LEN function to determine the length of each string.

Now, here comes the quiz:

Quiz 1: Pay attention to the last result, and you will notice that the length of @ThirdString is only 8000. Why is that?

Quiz 2: What changes should be made to the REPLICATE function so that it returns the appropriate result for @ThirdString? In our case, it should return the value of 11000 instead of 8000.

I encourage you to think about these questions and leave your answers in the comments below. I will publish the correct answers in next week’s blog post and give a shoutout to all the participants who provided valid answers.

Before we wrap up, I want to mention a fantastic tool for performance tuning called DB Optimizer XE3.5 Pro. It’s my personal favorite, and I highly recommend downloading it.

Thank you for reading, and I look forward to discussing the answers to the quiz in our next blog post!