What serialization format should we use to store serializaed objects in a SQL Server database

Question

We are developing a customized caching solution that will use a SQL Server database to store cached objects. The hosting environment of the application does not provide an "in-memory" cache such as memcached or app fabric so we must use a SQL Server database.

While most of the cached objects will be simple types (int, string, dates, etc) we will need to also store more complex types such as DataSets, DataTables, generic collections and custom classes.

I have very little experience with the .NET's native serialization and deserialization but I figure we will have to serialize the objects into some form (binary, xml, JSON, etc) to store it in the database and then deserialize it when we pull it out of the database. I would like to have some expert opinions on what the the "some form" should be.

We are using JSON.NET to serialize data into JSON for various AJAX requests. My initial thought was to serialize the cached data into JSON to store it in the database. However, I wanted to get a few opinions as to what would be best for performance and data integrity.

Answer 1

All three of the serialization options you mentioned (binary, json or XML) are valid choices for a serialization format. There are many other serialization formats but the three you mentioned are the most common. As to choosing between the three, here are some of the considerations:

1. If you store your data in a binary format in the database, it is not human readable if if you ever want to look at it via using Sql Server Management Studio or via a text editor. You would have to write some sort of deserialization tool if you wanted to manually peruse the data.

2. Binary format will likely result in serialize objects have the smallest size, followed by json, with XML being the largest. As far as the actual size differences, that will vary with your data structures.

3. As far as performance, binary serialization may be faster than json or XML. However, you would have to benchmark this with your data to see what the differences are.

4. I think there are excellent .net libraries and BCL support for all three of the format types, so any choice should be doable.

So your choice would depend upon which factors are most important to you: CPU utilization, disk storage space, human readability, and/or personal preference.

We have used json extensively for serialization of our objects for storage in a database , using JSON.Net and we like it a lot. It is handy sometimes to manually view the data via SSMS, and json is significantly more compact for our data than XML.