Is it possible to have multiple sets of key columns in a table?
- by Peter Larsson
Filtered indexes is one of my new favorite things with SQL Server 2008.
I am currently working on designing a new datawarehouse. There are two restrictions doing this
It has to be fed from the old legacy system with both historical data and new data
It has to be fed from the new business system with new data
When we incorporate the new business system, we are going to do that for one market only. It means the old legacy business system still will produce new data for other markets (together with historical data for all markets) and the new business system produce new data to that one market only. Sounds interesting this far?
To accomplish this I did a thorough research about the business requirements about the business intelligence needs.
Then I went on to design the sucker.
How does this relate to filtered indexes you ask? I'll give one example, the Stock transaction table.
Well, the key columns for the old legacy system are different from the key columns from the new business system.
The old legacy system has a key of 5 columns
Movement date
Movement time
Product code
Order number
Sequence number within shipment
And to all thing, I found out that the Movement Time column is not really a time. It starts out like a time HH:MM:SS but seconds are added for each delivery within the shipment, so a Movement Time can look like "12:11:68". The sequence number is ordered over the distributors for shipment. As I said, it is a legacy system.
The new business system has one key column, the Movement DateTime (accuracy down to 100th of nanosecond).
So how to deal with this?
On thing would be to have two stock transaction tables, one for legacy system and one for the new business system.
But that would lead to a maintenance overhead and using partitioned views for getting data out of the warehouse.
Filtered index will be of a great use here.
MovementDate DATETIME2(7)
MovementTime CHAR(8) NULL
ProductCode VARCHAR(15) NOT NULL
OrderNumber VARCHAR(30) NULL
SequenceNumber INT NULL
The sequence number is not even used in the new system, so I created a clustered index for a new IDENTITY column to make a new identity column which can be shared by both systems.
Then I created one unique filtered index for old system like this
CREATE UNIQUE NONCLUSTERED INDEX IX_Legacy (MovementDate, MovementTime, ProductCode, SequenceNumber) INCLUDE (OrderNumber, Col5, Col6, ... ) WHERE SequenceNumber IS NOT NULL
And then I created a new unique filtered index for the new business system like this
CREATE UNIQUE NONCLUSTERED INDEX IX_Business (MovementDate) INCLUDE (ProductCode, OrderNumber, Col12, ... ) WHERE SequenceNumber IS NULL
This way I can have multiple sets of key columns on same base table which is shared by both systems.
