Is there a way to make a query that looks in serialized binary object in SQL Server 2008?

I h开发者_Python百科ave an object called Data serialized as varbinary(MAX). Data object contains property named Source. Is there a way to make something similar:

select * from content_table where Data.Source == 'Feed'

I know that it is possible when XML serialization is used (XQuery). But serialization type cannot be changed in this case.

If you have used BinaryFormatter, then no, not really - at least, not without deserilizing the entire object model, which is usually not possible at the database. It is an undocumented format, with very little provision for ad-hoc query.

Note: BinaryFormatter is not (IMO) a good choice for anything that relates to storage of items; I fully expect that this will bite you at some point (i.e. unable to reliably deserialize the data you have stored). Pain points:

• tightly tied to type names; can break as you move code around
• tightly tied to field names; can break as you refactor your classes (even just creating to an automatically implemented property is a breaking change)
• prone to including a bigger graph than you expect, in particular via events

It is of course also platform-specific, and potentially framework-specific.

In all seriousness, I've lost count of the number of "I can't deserialize my data" questions I've fielded over the years...

There are alternative binary serializers that do allow some (limited) ability to inspect the data via a reader (without requiring full deserialization), and which do not become tied to the type metadata (instead, being contract-based, allowing deserialization into any suitable type model - not just that specific type/version.

However, I genuinely doubt that such work work with anything approaching efficiency in a WHERE clause etc; you would need a SQL/CLR method etc. IMO, a better approach here is simply to store the required filter columns as data in other columns, allowing you to add indexing etc. On the rare occasions that I have used the xml type, this is the same as I have done there (with the small caveat that you can use "lifted" computed+stored+indexed columns from the underlying xml data, which wouldn't be possible here - the extra columns would have to be explicit).

You could deserialise the data, using a SQL CLR function. But I suspect it wont be fast. It all depends on how the serialisation was done. If the library is available, then a simple CLR function, shuld be able to query the data quite easily.