none
Generating Id/Key Column for Aggregated Data Table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    We have the following table populated by a client daily base.

    CREATE TABLE [Sales].[Orders] (
	OrderDate			DATE		 NOT NULL,
	OrderId				VARCHAR(50)  NOT NULL,
	ProductCategory		VARCHAR(100) NULL,
	ProductSubCategory	VARCHAR(100) NULL,
	ProductClass		VARCHAR(100) NULL,
	ProductSubClass		VARCHAR(100) NULL,
	ProductName			VARCHAR(100) NULL,
	SalesUnit			VARCHAR(100) NULL,
	SalesSubUnit		VARCHAR(100) NULL,
	Currency			VARCHAR(3)   NULL,
	NumberOfItems		INT			 NULL,
	ProcessDate			DATE		 NULL,
	TotalPrice			DECIMAL		 NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(	OrderDate ASC,
	OrderId   ASC
)
) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NonClusteredIndex_OrderId] ON [Sales].[Orders]
(
	OrderId ASC
)
GO

    Due to the large number of records coming in per day, they decided to send us aggregated data only. This is the simplified version of the new aggregated table.

    CREATE TABLE [Sales].[AggregatedOrders] (
	OrderDate			DATE			NOT NULL,
	AggregatedOrderId	VARCHAR(2000)	NOT NULL,
	AggregatedPrice		DECIMAL				NULL
)

    Basically, they generate AggregatedOrderId column data by concatenating most of varchar columns.

    Considering performance impact and the maximum key length for a nonclustered index limitation (1700 bytes), do you see a better solution?

    Thanks,

    Kuzey

    Wednesday, July 10, 2019 5:15 PM
    |

Answers