Using large data types in SQL Server

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!!!