如何在 SQL Server 2016 中创建宽表?

2021-09-10 00:00:00 sql tsql sql-server

使用此代码(*)，在 SQL 中创建宽表让我不断发送:

With this code(*), the creation of a wide table in SQL keeps me sending this:

Msg 1702, Level 16, State 1, Line 11
CREATE TABLE failed because column '2010/12/01' in table 'PriceToBookFinalI' exceeds the maximum of 1024 columns.

使用[样式]去

CREATE TABLE [dbo].[PriceToBookFinalI]
    (DocID int PRIMARY KEY,
    [2006/12/29][Money],
    [2007/01/01][Money],
    ...
    SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS);  

GO

(2614 columns)

寻找好的提示！

这里是我想导入到宽表的背景数据集

Here is the background set of data I want to import to my wide table

推荐答案

对此的解决方案是规范化您的设计.即使您可以将其放入 1024 限制，您的设计也不是一个好主意.例如，如果您想知道 DocID 每月更改的平均金额怎么办.在这个模型中编写这将是一场噩梦.

The solution for this is to normalize your design. Even if you could fit it into the 1024 limit, your design is not a good idea. For example, what if you wanted to know the average amount a DocID changed per each month. That would be a nightmare to write in this model.

试试这个.

CREATE TABLE dbo.PriceToBookFinalI (
                                     DocID INT PRIMARY KEY,
                                     SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
                                   );


CREATE TABLE dbo.PriceToBookFinalMoney (
                                         DocID INT,
                                         DocDate DATE,
                                         DocAmount MONEY,
                                         CONSTRAINT PK_PriceToBookFinalMoney
                                           PRIMARY KEY CLUSTERED
                                           (
                                             DocID,
                                             DocDate
                                           )
                                       );

您可以轻松地将带有 SpecialPurposeColumns 的表连接到带有每个 DocID 的日期和金额的表.如果需要，您仍然可以将日期转换为上面提供的格式.将日期作为列中的值可让您更灵活地使用数据、提高性能并自然地处理更多日期.

You can easily join the table with the SpecialPurposeColumns to the table with the dates and amounts for each DocID. You can still pivot the dates if desired into the format you provided above. Having the date as a value in a column gives you much more flexibility how you use the data, better performance, and naturally handles more dates.

相关文章