USE tempdb
GO

CREATE TABLE largeValues (
  id INT IDENTITY, 
  long_document VARCHAR(MAX),
  long_unicode_document NVARCHAR(MAX),
  large_picture VARBINARY(MAX)
)
go

CREATE FUNCTION dovmax(@in VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
-- supports concatenation
RETURN @in + '12345'
END
go

DECLARE @x VARCHAR(MAX)
SET @x = REPLICATE('a', 9000) -- first operand is only VARCHAR
PRINT DATALENGTH(@x)  -- prints 8000

SET @x = REPLICATE(CAST('a' as VARCHAR(MAX)), 9000)
PRINT DATALENGTH(@x)  -- prints 9000


-- WRITE method
DECLARE @x NVARCHAR(MAX)
DECLARE @y NVARCHAR(MAX)

SET @y = REPLICATE(cast('b' as NVARCHAR(MAX)), 500)
-- SET @x.WRITE(@y, 0, 500)     -- error, can't write to NULL instance

SET @x = REPLICATE(cast('a' as NVARCHAR(MAX)), 5000)
PRINT LEN(@x)                -- prints 5000

SET @x.WRITE(@y, 5000, 500)
PRINT LEN(@x)                -- prints 5500

SET @x.WRITE(@y, 5000, 995)  -- not an error, replaces 500
PRINT LEN(@x)                -- prints 5500

SET @x.WRITE(@y, 5200, 995)  -- not an error, replaces 300, appends 200
PRINT LEN(@x)                -- prints 5700

SET @x.WRITE(@y, 5800, 500) -- error, offset larger than last character
GO

-- 8060 byte limit for fixed but not variable length
CREATE TABLE largeValues2 (
 COLUMN1 VARCHAR(MAX), COLUMN2 VARCHAR(MAX)    -- OK
) 
GO

CREATE TABLE largeFixedLengthValues (
 COLUMN1 CHAR(80),   COLUMN2 CHAR(8000) )       -- error
GO
