SQL Server:当我总是要重新加入它们时，垂直分区中有任何值吗? [英] SQL Server: Any value in vertical partitioning when i'm always going to re-JOIN them?
问题描述
我面对不得不向已经有32列的表中添加64个新列的问题.例如，
i'm faced with having to add 64 new columns to table that already had 32 columns. For examples sake:
Customers
(
CustomerID int
Name varchar(50)
Address varchar(50)
City varchar(50)
Region varchar(50)
PostalCode varchar(50)
Country varchar(2)
Telephone varchar(20)
...
NewColumn1 int null
NewColumn2 uniqueidentifier null
NewColumn3 varchar(50)
NewColumn4 varchar(50)
...
NewColumn64 datetime null
...
CreatedDate datetime
LastModifiedDate datetime
LastModifiedWorkstation varchar(50)
LastModifiedUser varchar(50)
)
大多数情况下，这些新列中的大多数将包含null.
Most of the time the majority of these new columns will contain null.
假设 if 我将这64个新列垂直划分到一个新表中，那么每次我SELECT来自客户时:
It is also a given that if i vertically partition off these 64 new columns into a new table, then every time i SELECT from Customers:
SELECT ...
FROM Customers
必须转换为联接才能获得分区值(即，在不需要新列的情况下，从不可以获得性能提升):
will have to be converted to a join to get the partitioned values (i.e. there is never a performance gain to be had where i don't require the new columns):
SELECT ...
FROM Customers
INNER JOIN Customers_ExtraColumns
ON Customers.CustomerID = Customers_ExtraColumns.CustomerID
所以这是划分列的一个缺点.
So that's one con to partitioning off the columns.
另一个缺点是我必须设法同时将行插入两个表中，而不仅仅是一个.
The other con is that i have to manage inserting rows into two tables simultaneously, rather than just one.
我能想到的最后一个 con 是，SQL Server现在必须在我想访问"客户"时执行INNER JOIN.现在并永远浪费CPU和I/O来连接实际上是一个表的表-除非我决定将它们拆分.
The final con i can think of is that SQL Server now has to perform an INNER JOIN any time i want to access "Customers". There will now and forever a waste of CPU and I/O to join tables that really are one table - except that i had decided to split them up.
所以我的问题是:我为什么要把它们分开?
So my question is: why would i split them up?
在大多数情况下将64列垂直划分为单独的表时，是否有任何值? Null占用的空间很小....
Is there any value in vertically partitioning out 64 columns to a separate table when they will mostly be null? Null take up very little space....
有什么优点?
编辑:为什么我甚至考虑分区?它主要是空数据，它将表中的列数增加三倍. 当然一定很糟糕！
Why am i even considering partitioning? It's mostly null data that will triple the number of columns in the table. Surely it must be bad!
推荐答案
为简化数据模型，没有更多信息，我可能不会分区，但是您没有在这些新列中指出数据的性质(也许有些列是应该标准化的数组.
For simplicity of data model, without further information, I would probably not partition, but you haven't indicated the nature of the data in these new columns (perhaps some columns are arrays which should be normalized instead).
但是，有些要点:
如果您进行垂直分区，并且对补充表有FK约束，那么在某些情况下，这可能有助于消除联接，因为它知道只有一行会存在.显然，它将在相同的唯一键上建立索引，这将有助于消除确定是否存在交叉联接的需要，因为只能有0或1行.
If you do vertically partition, and have a FK constraint on the supplemental table, that may help eliminate the join in some scenarios, since it knows that one and only one row will exist. Obviously it will be indexed on the same unique keys, which will help to eliminate the need to determine if there is a cross-join, since there can only be 0 or 1 rows.
您可以有一个将两个表连接在一起的可更新视图，并在视图上具有一个触发器，该触发器将插入到两个连接在一起的表中以构成该视图.您还可以决定进行左联接，并且仅在需要该联接的列中的任何一个为非NULL时才创建一个补充行.
You can have a single updatable view which joins the two tables and have a trigger on the view which inserts into the two tables joined to make the view. You could also decide to do a left join and only create a supplemental row at all if any of the columns needing it are non-NULL.
您还可以使用稀疏连接的一组补充数据表.显然，这也需要连接，但是您也可以对多个补充表使用与1相似的技术.
You can also use a sparsely joined set of tables of supplemental data. Obviously this would also need joins, but you could also use similar techniques with multiple supplemental tables as you would with 1.
这篇关于SQL Server:当我总是要重新加入它们时，垂直分区中有任何值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
