9

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!

Improve this question
4
  • 2
    If you do not use that data for query purposes, why do you store it? To be more precise, for what purposes do you use that data? Does it really necessary to store in the DB (can you store it in files and store only the path to the files?) Commented Nov 14, 2014 at 9:38
  • Good question, I am using Database for our product not for my own project..The product will be installed by only customers. So customers always asks us store data only in database and don't create any file so that they can maintain database history easily. Commented Nov 14, 2014 at 9:42
  • I won't use only in SQL Where clause, but I will use it Select clause whenever it needs Commented Nov 14, 2014 at 9:44
  • 4
    If this is textual data you're storing - please don't store it as Varbinary - this is just too cumbersome. If storing it as varbinary seems to reduce storage space by 50%, I'm assuming this is non-Unicode text that would easily fit into varchar(max). nvarchar datatype is a Unicode string - each character uses 2 bytes of storage. If you use varchar instead, each character uses only 1 byte of storage (but you won't be able to store Arabic, Hebrew, Chinese, Japanese, Korean etc. texts in such a column) Commented Nov 14, 2014 at 10:05

1 Answer 1

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.