New Datatypes
The Sql Server 2005 added a new data type ,’xml’, for storing and manipulating the xml data. Like other BLOBs it can store up to 2GB of data. Even though, you can store data of other data types, it is not advisable to store data other than xml, because these will create problem while dealing with XQuery / xml DML.
DECLARE @xmlTest TABLE (xmlString XML)
INSERT INTO @xmlTest SELECT '<xml><name>test</name></xml>'
SELECT * FROM @xmlTest
Other than the ‘xml’ datatype, SQL server 2005 also extends the maximum capacity of varchar, nvarchar and varbinary data types. Now you can store values upto 2GB on these by specifying a ‘Max’ at the length portion, like , varchar(max), nvarchar(max) etc. I doubt this could be the initial step to completely eliminate the ‘text/ntext’ data types in future versions. The main advantage of these over the text/ntext datatype, is now you can use an UPDATE statement for updating a particular row using a ‘..WRITE (expression, @Offset, @Length)’ method. The other pros over its text/Ntext are
- unlike text/nTex, we can declare these types inside any query
- can FETCH these inside a cursor
- can use all the functions that can used with Varchar/nVarchar
the ‘ .Write’ work similar to STUFF. The ‘.WRITE’ clause replaces a section of the value in a column starting at @Offset for @Length units with the value expression.
In order to append a string, the @offset value must be NULL, for prepending, both @OffSet and @length must be 0’s.
DECLARE @textUpdateTest TABLE(i INT IDENTITY, SampleText VARCHAR(MAX) )
INSERT INTO @textUpdateTest SELECT 'Hello'
INSERT INTO @textUpdateTest SELECT 'World'
SELECT * FROM @textUpdateTest
--- append a value
UPDATE @textUpdateTest
SET SampleText .WRITE(' world',NULL,NULL)
WHERE i = 1
SELECT * FROM @textUpdateTest
--prepend a string
UPDATE @textUpdateTest
SET SampleText .WRITE('This ',0,0)
WHERE i = 2
SELECT * FROM @textUpdateTest
