• Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
  • #News
  • #APPS
  • #Events
    • #WWDC
    • #I/O
    • #Ignite
  • #Let’s Talk

MyCodeTips mycodetips-newlogocopy1

  • Home
  • MAD
    • IOS Series
    • Android Series
    • Flutter Series
    • Xamarin Series
  • Concept Series
    • Software Design
    • Software Arch
    • GIT & Github
    • System Design
    • Cloud
    • Database Integration
    • Push Notification
    • API Integration
    • Cocoa PODS
  • DSA
  • Interview
  • Tips&Tricks
  • YT
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.

Liked it? Take a second to support Ranjan on Patreon!
become a patron button
  • 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 - 1195 Views
Tags | SQL
AUTHOR
Ranjan

Namaste, My name is Ranjan, I am a graduate from NIT Rourkela. This website is basically about of what i learnt from my years of experience as a software engineer on software development specifically on mobile application development, design patterns/architectures, its changing scenarios, security, troubleshooting, tools, tips&tricks and many more.

You Might Also Like

mycodetips-newlogo2

RESEED Identity Column in Database Table – Rest Table Identity Value – SQL in Sixty Seconds

May 25, 2013
mycodetips-newlogo2

Interesting Observation of CONCAT_NULL_YIELDS_NULL and CONCAT in SQL Server 2012 onwards

May 25, 2013
mycodetips-newlogo2

How to puzzle SET ANSI_NULLS and resultset in sqlserver

June 11, 2013
Next Post

Support us

mycodetips
mycodetips

Follow us @ LinkedIn 2850+

Subscribe for updates

Join 8,213 other subscribers

Latest Posts

  • YT-Featured-solidprinciples
    SOLID Principles of Software Design
  • IOS 16 Features
    Latest features in IOS 16
  • r-language
    How can R language be used for data analysis?
  • wordpress-coding-blog
    Guide To WordPress Coding Standards
  • YT-Featured-Algorithm
    What is Algorithm?
  • Frameworks of IOS
    Frameworks of IOS – Part ( I )
  • NSFileManager or NSPathUtilities
    NSFileManager or NSPathUtilities in Objective-C
  • Passing data between view controllers in Objective-C
    Passing data between view controllers in Objective-C
  • structures-classes-enum
    Structures and Classes in swift !
  • control-system-swift
    Control Flow in Swift
whiteboard

Whiteboard(PRO)

whiteboard

Whiteboard(lite)

alphabets

Kids Alphabet

techlynk

Techlynk

techbyte

Do2Day

techbyte

Techbyte

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

Android Android Studio API APP Programming Apps blogging CSS DATABASE dsa Features HTML HTML5 installation Interview Questions IOS iPhone javascript Mac objective-c OS Programming quicktips SDK SEO SQL swift Tips & Tricks Tools UI Web Wordpress Xcode

  • SOLID Principles of Software Design
  • Latest features in IOS 16
  • How can R language be used for data analysis?
  • Guide To WordPress Coding Standards
  • What is Algorithm?

©mycodetips.com