Best way to store Large String in SQL Server database?

Question

I am using SQL Server 2005 and 2008 R2 databases, and I need to store large string which may contains over 50 thousands characters. Currently I am using the data type nvarchar(max) to store larger string.

My problem with nvarchar(max) is it takes more storage space in database. I have tested with 100000 records and it takes around 10 GB memory to store 100000 records.

Here, one good point is, I don't use this column for SQL Where query purpose, so that I have decided to store the data as BLOB data type - nvarbinary(max). In this way the storage memory decreased 50%, it means, it takes around 5 GB memory to store 100000 records.

So my question, since I am not going to use this string data for SQL Where query purpose, I would like to store it in any other better way, so that the database size will be reduced below 5 GB.

Hope, someone will give better idea!

Answer 1

In SQL Server 2008 you have the option to use FILESTREAM to store large binary data. In this case, the data will be available in queries, but physically stored on the filesystem.

Microsoft published a SQL Server Technical Article which contains usefull information about how and when to use FILESTREAM. On the 8th page, the comparsion chart shows that the FILESTREAM has advantage over BLOBs stored in database if the stored data is larger than 1MB.

NOTE FILESTREAM is not available in SQL Server 2005! (And SQL Server 2005 is no longer supported by Microsoft - except the extended support)

Some more articles to read

You can see some comparsion charts on SQLSkills blog about its performance.

Also Microsoft Research published a Technical Article: To BLOB or Not To BLOB about filestreams and BLOBS.

You can give it a try, but as always, you have to run some environment specific test to be sure that this solution works or not. If this is a product on the market, it is a good idea to implement the FILESTREAM support as an opt-in or opt-out feature.

Just a side-note

NVARCHAR is twice as large than VARBINARY because SQL Server stores each character on 2 bytes in the unicode (NCHAR, NVARCHAR, etc) columns.