問題描述

使用此代碼(*)，在 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.

這篇關于如何在 SQL Server 2016 中創建寬表?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！