The following example illustrates just a few of the ways that standard operators can now be used with large-value data types.
String concatenation can now be used with large data types. The thing to note with this example is the use of the REPLICATE function. The REPLCIATE function returns a character expression of the same type as the supplied character expression. So if the supplied expressions is not CAST as a large data type the MAXIMUM length of the expression returned would be 8000 characters.
INSERT INTO dbo.LargeDataType(LargeColumn)
SELECT ‘There is lots of data in this row ‘ +
REPLICATE(CAST(‘x’ AS VARCHAR(MAX)), 100000)
Updates can be made directly to large data types without the need to use the UPDATETEXT operator:
UPDATE dbo.LargeDataType
SET LargeColumn = REPLACE(LargeColumn, ‘lots’, ‘lots and lots’)
Standard string operators such as SUBSTRING can now be used with large data types
SELECT SUBSTRING(LargeColumn, 10, 4)
FROM dbo.LargeDataType
The support for large data types is a valuable new addition to SQL Server. So if you are designing a new application that needs to store vales that are greater than 8000 bytes you should use the new large-value data types. As not only will the use of large-value data types assist by providing a unified programming model, it will also ensure that your application can take advantage of additional storage in future versions of SQL Server, allowing you to really Super Size your columns.
]]>