• Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
  • Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
  • #News
  • #APPS
  • #Apple WWDC
  • #Google I/O
  • #Microsoft Ignite
  • #Let’s Talk
  • #Advertise

MyCodeTips mycodetips-newlogocopy1

  • Home
  • Basics
  • DSA
  • MAD
  • Concept
  • Practice
  • Misc
    • Tips
    • QA’s
    • Misc
  • Course
Programming

SQL SERVER – Solution to Puzzle – REPLICATE over 8000 Characters

Just quick to summarize the puzzle. Here is the quick recap of the same.

Now let us run following script.

DECLARE @FirstString VARCHAR(MAX)
DECLARE @SecondString VARCHAR(MAX)
DECLARE @ThirdString VARCHAR(MAX)
SET @FirstString = REPLICATE('A',4000)
SELECT LEN(@FirstString) LenFirstString;
SET @SecondString = REPLICATE('B',8000)
SELECT LEN(@SecondString) LenSecondString;
SET @ThirdString = REPLICATE('C',11000)
SELECT LEN(@ThirdString) LenThirdString;

The script above will return following result:

f53adb9a82 replicatepuz2

Quiz 1:

Pay attention to the last resultant and you will notice that the length of the @ThirdString is only 8000. WHY?

Answer 1:

The reason for the same is that if the first parameter of the REPLICATE function is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, the first parameter must be explicitly cast to the appropriate varchar(max) or nvarchar(max).

Quiz 2:

What changes I should do in the REPLICATE function so it will give the appropriate result in the case of @ThirdString. In our case, it should return the value of 11000 and not 8000.

Answer 2:

To return the result as 11000, one has to just CAST or CONVERT the first parameters to VARCHAR(MAX) or NVARCHAR(MAX). Here is the example of the same.

DECLARE @ThirdString VARCHAR(MAX)
SET @ThirdString = REPLICATE(CONVERT(VARCHAR(MAX),'C'),11000)
SELECT LEN(@ThirdString) LenThirdString;

Now let us see the result set.

dadba4d791 replicate8000

DB Optimizer

One last thing: Do not forget to download DB Optimizer XE3.5 Pro. It is my favorite tool for performance tuning.

If you notice that this was not very difficult puzzle but it was interesting for sure. There are so many valid answers that it will be not possible to name every single person. I strongly encourage all of you to go over the original blog post and read all the comments. Though all the comments are very similar there are so many new information there that I will say wealth of information just right there in the comments area.

  • Click to share on Reddit (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • More
  • Click to share on Pocket (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
Written by Ranjan - May 25, 2013 - 1339 Views
Tags | SQL
AUTHOR
Ranjan

This website is basically about of what we learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures and its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

mycodetips-newlogo2

How to prevent SQL Injection in iOS apps?

October 7, 2013
mycodetips-newlogo2

How to update specific column in entire database using SQL

June 11, 2013
mycodetips-newlogo2

Tips to protect your site from hackers

November 8, 2013
Next Post

Support us

Subscribe for updates

Join 8,278 other subscribers

Latest Posts

  • Exploring Single Point Failure
    Exploring Single Point Failures: Causes and Impacts
  • primitive-datatypes-new
    Exploring the Pros and Cons of Primitive Data Types
  • best practice clean code
    Essential Coding Standards and Best Practices for Clean Code
  • YT-Featured-Templates--lld
    What Business Problems Solves in Low Level Design (LLD)
  • SRP-SingleResopnsibility
    SRP : Single Responsibility Principle in Swift and Objective-C
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

do2day

Do2Day

  • #about
  • #myapps
  • #contact
  • #privacy
  • #Advertise
  • #myQuestions

Android Database Interview IOS IOSQuestions Javascript Objective-c Programming Swift Tips&Tricks Web Wordpress

  • Exploring Single Point Failures: Causes and Impacts
  • Exploring the Pros and Cons of Primitive Data Types
  • Essential Coding Standards and Best Practices for Clean Code
MyCodeTips

©mycodetips.com

Insert/edit link

Enter the destination URL

Or link to existing content

    No search term specified. Showing recent items. Search or use up and down arrow keys to select an item.