TOP 1 WITH TIES

You are no longer limited to that 8000 charcter limit for VARCHAR variables. SQL Server 2005 enhances the VARCHAR, NVARCHAR and VARBINARY datatypes by raising the capacity to Approximately 2GB. You can declare the variables with the MAX specifier. Lets quickly try some VARCHAR(MAX) operations.


DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE('AA', 5000)
SELECT LEN(@vchTest) as Length

Length
--------
8000

Ofcourse, I expected to see 10000 back as result. I refer the REPLICATE command in BOL which clearly says

"If character_expression 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, character_expression must be explicitly cast to the appropriate large-value data type. "

So I tried


DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE(CAST('AA' AS VARCHAR(MAX)), 5000)
SELECT LEN(@vchTest) as Length


Length
--------
10000

Then I tried Concatenating many VARCHAR(n) values and assign it to VARCHAR(MAX) .

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = REPLICATE('AA',4000)+ REPLICATE('AA', 4000)
SELECT LEN(@vchTest) as Length

Length
--------
8000


This time I expected to see 16000 as the result. But I still get 8000 back!

I got help from Christian Wade's blog Which says,

<QUOTE>
The problem is that if you concatenate two varchar(8000) variables, you will be left with a varchar(8000) variable. This is the same issue as populating a float with the division of two integers.
The reason is that varchar(n) variables are physically stored in the same way - irrespective of length. Whereas an varchar(MAX) is effectively a text under the covers and needs to worry about managing pointers to the relevant data page (although this is abstracted away from the developer).
<QUOTE/>

To ensure I just tried

DECLARE @S VARCHAR(9000)

which returned the error.

Msg 131, Level 15, State 3, Line 1
The size (9000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).


The workaround Christian Wade suggested iscasting the two expressions individually to the large-valued type and then do the assignment.

However, I find it more easy to do an Initialisation and Concantenation with the actual variable, like

DECLARE @vchTest VARCHAR(MAX)
SET @vchTest = 'Start'
SET @vchTest = @vchTest + REPLICATE('AbC2',2000) + 'Test'+ REPLICATE('AbC2',2000) + 'End'
SELECT LEN(@vchTest) as Length

Length
---------
16012

Then I tried all String functions on the variable

SELECT LEN(LEFT(@vchTest,9000)) --OK
SELECT RIGHT(@vchTest,3)
SET @vchTest= REPLACE(@vchTest,'End','XXX')
SELECT RIGHT(@vchTest,3)
SET @vchTest = REVERSE(@vchTest)
SELECT RIGHT(@vchTest,3)
SET @vchTest = REVERSE(@vchTest)
SET @vchTest = SPACE(9000) + @vchTest
SELECT LEN(@vchTest)
SELECT CHARINDEX('Test',@vchTest) --Correct
SET @vchTest = STUFF(@vchTest,24001,4,'SQL')
SELECT CHARINDEX('SQL',@vchTest) --Correct
SELECT SUBSTRING(@vchTest,32000,3)
SET @vchTest = LOWER(UPPER(@vchTest))
SELECT LEN(@vchTest)
SET @vchTest = LTRIM(@vchTest)
SELECT LEN(@vchTest)


All seems to work fine.

The text, ntext and image data types will be deprecated. The varchar(max), nvarchar(max) and varbinary(max) data types are far superior than these old datatypes, because you dont have to use special statements like WRITETEXT and UPDATE text. You can use the standard INSERT and UPDATE statements. . For more information, see the topic Using Large Value Data Types in Books Online.