Using large data types in SQL Server November 17, 2011Posted by fofo in Sql Server, SQL Server 2008.
Tags: large data types, nvarchar(max), varbinary(max), varchar(max)
In this post I would like to talk about the large value data types and how we can use them to store large amounts of data. We can store up to 2^31 bytes of character, binary, and Unicode data. I know there are samples of how to use large value data types in BOL,MSDN and elsewhere but I thought that it would be a good idea to give it a try with hands-on examples.
So T-SQL supports large character and binary data types.These types are varchar(max),nvarchar(max) and varbinary(max).They have no 8000 byte limitation and text,ntext and image data types are deprecated and you should not use it anymore. Actually there is no need to use them anymore.Bear in mind that you cannot use text,ntext,image data types as the data type of a local variable. If you attempt to do so you will receive an error from SQL Server ,”The text,ntext and image data types are invalid for local variables”.So that is another big limitation and you should avoid using these system data types.In some future version of SQL Server they will go away.
We will need an instance of SQL Server running in our machine.You can download and install the free SQL Server Express edition from here.I will use SQL Server 2008 R2 Express edition.
1) Launch SSMS and connect to the SQL Server instance.Start a new query window and type (copy-paste) the following
CREATE DATABASE LargeDataTypes GO USE LargeDataTypes GO DECLARE @myvar TEXT SET @myvar = 'SQL Server is the best RDMBS' SELECT @myvar
Execute the code above. This will not work.
2) In a new query window type the following
USE LargeDataTypes GO DECLARE @myvar VARCHAR(max) SET @myvar = 'SQL Server is the best RDMBS' SELECT @myvar
Execute the code above. This will work just fine.
3) In this example we will store a huge number of bytes in a variable of type varchar(max). In a new query window type
USE LargeDataTypes GO DECLARE @myvar VARCHAR(max) SET @myvar = REPLICATE(CAST('SQL Server 2012 will provide Mission Critical Confidence with greater uptime blazing-fast performance and enhanced security features for mission critical workloads' AS VARCHAR(max)),65000) SELECT @myvar SELECT LEN(@myvar)
In this little example I replicate the string in quotes (‘ ‘) 65000 times.Then I store it in a local variable of data type varchar(max) . Execute the code above and you will see that it works perfectly well.So we test beyond doubt that there is no 8000 bytes limitation.
4) In this final example I will show you how to store a large document. I have found this technical document with title “Troubleshooting Performance Problems in SQL Server 2008” very useful.Download it and save it in a folder. It is 561kbytes in size and we will store it in a variable of type varbinary(max)
In a new query window type
USE LargeDataTypes GO DECLARE @myvar VARBINARY(max) SET @myvar = (SELECT * FROM OPENROWSET(BULK 'C:\Users\fofo\Downloads\TShootPerfProbs2008.docx',SINGLE_BLOB) AS document) SELECT @myvar
Execute the code above.This code will work just fine.The results will be the binary representation of what is inside the .docx file.
5) In this final example we will store the large .docx in a table.In a new query window type
USE LargeDataTypes GO CREATE TABLE largedocs(id INT PRIMARY KEY IDENTITY, mydoc VARBINARY(max)) INSERT INTO largedocs VALUES ((SELECT * FROM OPENROWSET(BULK 'C:\Users\fofo\Downloads\TShootPerfProbs2008.docx',SINGLE_BLOB) AS document)) SELECT * FROM largedocs
Execute the code above. The .docx will be stored in the column with data type varbinary(max)
Hope it helps!!!