.Net Compression and SQL Server 2005 NVARCHAR(MAX)

Question

Can I safely store a .Net compressed memory stream (System.IO.Compression) in an SQLServer 2005 NVARCHAR(MAX) field? SQLServer 2008 is not an alternative.

Answer 1

Use VARBINARY(MAX) for binary data - VARCHAR(MAX) and NVARCHAR(MAX) are for character-data (strings).

Answer 2

You'd be better off using varbinary(max)

Answer 3

I would think varbinary(max) is more suitable. Remember that there is a max size of 2GB.

Answer 4

A Stream is just a pointer to data, so you cannot store the stream into SQL Server, you can store the data that this stream points to. As you mention the System.IO.Compression namespace I suppose you mean either DeflateStream or GZipStream which both contain binary data. The appropriate type to store binary data in SQL is VARBINARY(MAX).