SQL Server 2005 表有多少列太多了? [英] How many columns are too many for a SQL Server 2005 table?

问题描述

我有一个请求，允许一个动态表有 1000 列(由我的最终用户随机选择).这对我来说似乎是个坏主意.这是一个可自定义的表，因此它将混合 varchar(200) 和 float 列(float 最匹配应用程序 c++ double 类型).该数据库主要是遗留应用程序的索引，并用作报告存储库.这不是记录系统.该应用程序有数千个数据点，其中很少有可以归一化的.

I have a request to allow a dynamic table to have 1000 columns(randomly selected by my end users). This seems like a bad idea to me. It's a customizable table so it will have a mixture of varchar(200) and float columns(float best matches the applications c++ double type). This database is mostly an index for a legacy application and serves as a reporting repository. It's not the system of record. The application has thousands of data points very few of which could be normalized out.

关于这对性能有什么影响的任何想法?或者一个理想的表大小也可以将其分区?

Any ideas as to what the performance implications of this are? Or an ideal table size to partition this down too?

由于我不知道最终用户会选择 2 万个选项中的哪些字段进行规范化表格是不可行的.我可以将这些数据分离到几个我必须动态管理的表中(可以添加或下垂字段.然后删除行并重新解析记录系统以填充表.)我的偏好是推回并标准化所有 20k 位数据.但我没有看到这种情况发生.

Since I don't know what fields out of 20k worth of choices the end users will pick normalizing the tables is not feasible. I can separate this data out to several tables That I would have to dynamically manage (fields can be added or drooped. The rows are then deleted and the system of record is re parsed to fill the table.) My preference is to push back and normalize all 20k bits of data. But I don't see that happening.

推荐答案

这让我觉得设计很糟糕.

This smells like a bad design to me.

需要考虑的事项:

这些列中的大多数会包含 NULL 值吗?

Will most of those columns be contain NULL values?

是否会有很多命名为 Property001、Property002、Property003 等...?

Will many be named Property001, Property002, Property003, etc...?

如果是这样，我建议您重新考虑您的数据规范化.

If so, I recommend you rethink your data normalization.

这篇关于SQL Server 2005 表有多少列太多了?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！