[Yukon] About large value data types

The maximum capacity for Varchar / Varbinary in SQL Server 7 and 2000 are 8,000 bytes. Similarly for nvarchar it is 4,000 bytes. For any content which is more than 8000 bytes we would go for "Text, NText, or Image" data types.

In SQL Server 2005 it has changed greatly with the introduction of the "Max" specifier. The Max specifier allows storage of up to 2^31 bytes of data. For Unicode content it allows storage of up to 2^30 bytes.

When you use the Varchar(Max) or NVarchar(Max) data type, the data is stored as character strings, whereas for Varbinary(Max) it is stored as bytes.

Basic example showing the usage of this new specifier.

Create table PatientDetails
(
PatientNumber int Identity,
FirstName varchar(max),
LastName varchar(max),
Memo varchar(max)
)

In earlier version of SQL Server we cannot use Text, ntext or image data types as variables / parameters in stored procedure or user defined funtions. But that is perfectly valid in Yukon.

Passing character data as parameter to a user defined proceedure and doing string concatenation. This would work perfectly fine in Sql Server 7 and later.

Create proc uspStrConcatenation
(
@strInputValue varchar(10)
)
as
begin
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'
end

exec uspStrConcatenation 'vel'

Passing Text data as parameter to a user defined proceedure.

This would fail in Sql Server 7 and 2000 as Text / ntext / image etc., data types cannot be used as variables in procedures / functions.

Create proc uspTextConcatenation
(
@strInputValue Text)
as
begin
Select 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue)) as 'Concatenated Value'
end

In SQL Server 2005 we can now pass any size string as parameter.

Create function fnStrConcatenation
(
@strInputValue varchar(max)
)
returns varchar(max)
as
Begin
return 'The string passed as parameter is ' + Rtrim(Ltrim(@strInputValue))
End