在SQL中查询数据字典的最佳方法 [英] Best way to query a data dictionary in sql
问题描述
对于我要设置的项目，我需要一个非常动态的数据结构。我将存储未定义的记录。一条记录可能包含5列，另一条记录可能是10列。
For a project I'm setting up, I need a very dynamic data structure. I'm going to store records for which I don't have a defintion. One record could consist of 5 columns, another might be 10.
为了支持这一点，我想使用包含以下列的表：
To support this, I want to use a table with the following columns:
ResultId
RecordId
RecordTypeId
键
值
ResultId RecordId RecordTypeId Key Value
这支持所有可能的方案，但是我有几个问题。
This supports all possible schemes, but I have do a few questions.
- 这是最好的方法还是我错过了更好的选择？
- 这是什么查询此问题并获取记录的好方法吗？我将定义存储在另一个表中，该表告诉我每个记录类型存储哪些列。我看过需要动态buildp的PIVOT语句，因为您需要以数组样式提供字段（尽管我想可以由存储过程来提供字段）
- 性能如何保留大量记录？
- 使用EF / Linq这样的工具方便吗？
- Is this the best approach or are there better options I've missed?
- What's a good way to query this and get my records out the way they got in? I have definitions stored in a different table which tell me which columns are stored per recordtype. I've seen the PIVOT statement which requires a dynamic buildp since you need to provide the fields in an array style (though that could be fed by a stored procedure I guess)
- How will performance hold up for large numbers of records?
- Is it handy to use things like EF / Linq?
这将被编码为.NET 4，带有SQL2008R2 Express后端的C＃。我正在寻找一些最佳做法。
This will be coded in .NET 4, C# with a SQL2008R2 Express back-end. I'm looking for some best practices.
更新！为了进一步说明：我需要存储来自不同，未知查询的数据结果集。我不知道会有多少个查询定义或它们的外观。该数据库中的数据仅用于处理。用户甚至都不知道这些表的存在，不会有输入，也不会以任何方式在屏幕上显示任何报告或数据。结果集用于通过代码与其他结果集进行比较。我需要的是一种高度动态的方式来存储这些记录，当涉及大量记录时，该方法将继续执行。
Update! To clarify a bit more: I need to store resultsets of data from different, unknown, queries. I don't know how many query definitions there will be or how they will look like. The data in this database is only used for processing. Users won't even know these tables exist, there will be no input, there won't be any reports or any data hitting the screen in any way. The resultsets are used for comparison against other resultsets, all through code. What I need is a highly dynamic way to store these records which keeps performing when there's a great number of records involved.
我想到的另一个选择是：
Another option I thought of is:
ResultId
RecordId
field1（sql_variant）
field2（sql_variant）
field3（sql_variant）
field4（sql_variant）
...
ResultId RecordId field1 (sql_variant) field2 (sql_variant) field3 (sql_variant) field4 (sql_variant) ...
这是一个更好的主意吗？
Is this a better idea?
推荐答案
我知道这不能回答问题，但我无法避免发帖（尽管我很可能会被否决）。它说明了这一点。直接来自《每日WTF》：
I know this does not answer the question but I could not refrain from posting (although I will most likely get down voted). It illustrates the point. Straight from The Daily WTF:
这是我的第二次采访，并且开始了很好的对话。
It was my second interview and it opened pretty conversationally.
我们正在考虑将表名和列存储在数据库中，
接受采访时说，这是一个想法。
"We're thinking of storing table names and columns in the database," the interview said, "it's an idea floating around."
我回答说：不是创建表并存储数据，而是取决于
的结构？
I replied, "Instead of creating tables and storing the data depending on how it's structured?"
是的！他的眼睛闪闪发光，那么我们就不必定义结构或
任何其他混乱！
"Yeah!" his eyes lit up, "then we don't have to define structures or any other mess!"
我想知道这是一种考验。 您将如何举报？
会不会很尴尬？
I wondered it it was some sort of test. "How would you do reports? Would it not be awkward?"
我们可以从前端开始。
"We could do that from the front end."
也许吧，我说，但它不需要很多代码吗？它的运行速度会非常慢。
"Maybe," I said, "but wouldn't it require a lot of code? And it'd be incredibly slow."
如果我们使用哈希表，那就不行！
"Not if we used hashtables!"
好吧...那么数据完整性，外键如何呢？
"Okay... what about data integrity, foreign keys, that sort of thing?"
他的防御能力越来越强，全都来自前端。
He was getting more defensive, "it'd be all from the front end."
我想，但这全都需要大量的代码
以及各种复杂的SQL查询。如果您要更改
数据库，也很不错-
"I suppose, but this would all require an exorbitant amount of code with all sorts of complex SQL queries. It's not that bad to change a database if you—"
不，他打断道，我们将使用数据访问层将
抽象到规范化的数据结构。
"No," he interrupted, "we would use a data access layer to abstract it to normalized data structures."
您的意思是。 ....就像桌子吗？
"You mean... like tables?"
两天后，我的招聘人员告诉我，他们拒绝我成为
的候选人，因为我的技术水平不够。尽管我确实怀疑
是否实现了元数据库实现，但我很高兴
我永远不会知道。
Two days later, my recruiter told me that they declined me as a candidate because I wasn't technical enough. Though I do wonder if they ever implemented their meta-database implementation, I'm glad I'll never know.
这篇关于在SQL中查询数据字典的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
