SPACE function will return maximum of 8000 spaces only

• We can use SPACE function to replicate spaces for a string. Consider the following statement 

 SQL Query: select 'A'+space(10)+'B'
The result is:  A          B

• As you see space function adds 10 spaces between the string A and B. Note that the space function though accepts any positive number, it will return maximum of 8000 spaces only which you can confirm from the following example  

SQL Query: select datalength(space(10000))

• The above returns 8000. So you should be aware of this. The alternate solution to have more spaces is to use replicate function as shown below

SQL Query: select datalength(replicate(cast(' ' as varchar(max)),10000))  

• Which returns 10000. Also note that replicate returns maximum 8000 characters so we need to convert the string to varchar(max) datatype to have more chanracters. 

• Varchar(max) datatype and Replicate function

  SQL Query: declare @v varchar(max)
             set @v=replicate('a',50000)
             select len(@v),datalength(@v)
  • Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype

  To get a correct result, you need to convert the expression to the type of varchar(max)

  SQL Query:declare @v varchar(max)

            set @v=replicate(convert(varchar(max),'a'),50000)

            select len(@v),datalength(@v)

  • Now the result is 50000 as expected.

  So you need to be aware of this implicit convertion when using Replicate function to assingn value to the column of varchar(max) datat