Retrieving data from an audit table.
Retrieving information from a history table for a given point in time is a common data task. For example, using a table that contains all historical prices, what where the prices of all products as they were 30 days ago? There are a number of ways to different methods to obtain this information from the audit table. In this article, I compare the performance of five such methods for varying amounts of historical data.
Data
Our sample set consists of two tables: a Products table that holds information about products and a PriceHistories table that holds all historical prices for those products.
Below are the scripts to create and populate the two tables.
–Products table
create table dbo.Products(
ProductID int not null,
Name nvarchar(50) not null,
Description nvarchar(2000) null,
constraint PK_Products
primary key(ProductID)
)
go
create unique index AK_Products_1 on Products(Name)
go
–PriceHistories table
create table dbo.PriceHistories(
PriceHistoryID int not null identity(1,1),
ProductID int not null,
Price decimal(8, 2) not null,
DateEntered datetime not null,
constraint PK_PriceHistories
primary key(PriceHistoryID),
constraint FK_PriceHistories_ProductID
foreign key (ProductID)
references dbo.Products
)
go
create unique index AK_PriceHistories_1 on dbo.PriceHistories(ProductID, DateEntered) include (Price)
go
create index IX_PriceHistories_1 ON dbo.PriceHistories (DateEntered) include (ProductID,Price)
— Populate the products table
begin tran
declare @x int
select @x = 0
while @x < 100
begin
select @x = @x + 1
insert into Products(ProductID, Name)
select @x, ‘Product ‘ + cast(@x as nvarchar)
end
commit
go
— Populate the PriceHistories table
begin tran
declare @x int
select @x = 0
while @x < 1000
begin
select @x = @x + 1
begin try
insert into PriceHistories(ProductID, Price, DateEntered)
select rand()*100 + 1, rand(),dateadd(dd, 0 – rand() * 1000, getdate())
end try
— Retry in case the record is not unique
begin catch
while @@rowcount = 0
begin
insert into PriceHistories(ProductID, Price, DateEntered)
select rand()*100 + 1, rand(),dateadd(dd, 0 – rand() * 1000, getdate())
end
end catch
end
commit
go