有没有一种方法可以使查询看起来像SQL Server 2008中的序列化二进制对象? [英] Is there a way to make a query that looks in serialized binary object in SQL Server 2008?

问题描述

我有一个名为Data的对象，序列化为varbinary(MAX).数据对象包含名为Source的属性.有没有办法做类似的事情:

I have 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'

我知道使用XML序列化(XQuery)时是可能的.但是在这种情况下，无法更改序列化类型.

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

推荐答案

如果您使用过 BinaryFormatter ，那么，不是，不是真的-至少在不对整个对象模型进行反序列化的情况下，通常这是不可行的在数据库上不可能.这是一种未记录的格式，很少用于即席查询.

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.

注意:对于与项目存储有关的任何事情， BinaryFormatter 都不是(IMO)的好选择；我完全希望 会在某个时候咬住您(即无法可靠地反序列化存储的数据).痛点:

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:

• 与类型名称紧密联系；在您移动代码时可能会中断
• 与字段名称紧密关联；在重构类时可能会中断(即使仅创建为自动实现的属性也是一项重大更改)
• 容易包含比您预期更大的图形，尤其是通过事件

当然，它也是特定于平台的，并且可能是潜在特定于框架的.

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.

但是，我真的怀疑这样的工作能否在 WHERE 子句等中达到效率的任何工作；您将需要SQL/CLR方法等.IMO，一种更好的方法是将所需的 filter 列作为数据存储在其他列中，从而允许您添加索引等.使用了 xml 类型，这与我完成的那里相同(但要注意的一点是，您可以使用底层的提升的" compute + stored + indexed列xml数据，在这里是不可能的-额外的列必须是明确的).

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).

这篇关于有没有一种方法可以使查询看起来像SQL Server 2008中的序列化二进制对象?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！