CODEWITHSUNDEEP
sqlsql-servert-sql
MovGP0
MovGP0

Reputation: 7765

Partitioning Table based on Record Version

I have an given financial application that has very large tables, which are—more or less—of the following form:

CREATE TABLE ProjectAccounts (
  RecordId BIGINT PRIMARY KEY, 
  AccountId GUID NOT NULL, 
  Version BIGINT, 
  -- some data
)

Because it is a business requirement that old versions of the records need to be preserved, changes in this table are done by creating a new version of the record and increment the field Version.

What I want to do is tho partition the table in such a way, that only the newest version of an record (highest version number) stays in the main partition, while obsolete records should be moved into a shadow-partition.

What would the best approach be to do so? Is there a better approach?

Alternative ideas from my colleagues:

However, I am worried about the possible performance impact of triggers (almost always bad), updates (searching and locking), and the added complexity created by searching multiple tables.

Upvotes: 1

Views: 42

Answers (1)

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

As you want to create two partitions , You can add one extra column "IsLatestVersion" use int datatype for this column.

Set "IsLatestVersion" = 0 , for most recent record and insert new record with "IsLatestVersion" = 1 , You might need to modify your code file to maintain this or you can create small trigger to do this task.

Now Table structure is ready to use. Lets create partition on this table. To do this you need to follow following steps : 1. Add two file groups to current database. You can add filegroup by following query : 

ALTER DATABASE CurrentDB ADD FILEGROUP [Filegroup_2001]  
GO  
ALTER DATABASE CurrentDB ADD FILEGROUP [Filegroup_2002]  
GO
  1. Attach files with created file groups. You can use following query to do that: 
ALTER DATABASE CurrentDB ADD FILE(NAME = N'data_2001',
FILENAME = N'C:\data_2001.ndf',
SIZE = 5000MB,
MAXSIZE = 10000MB,
FILEGROWTH = 500MB)
TO FILEGROUP [Filegroup_2001]  
GO
  1. Create partition function : 
USE [YourTableName]
GO
CREATE PARTITION FUNCTION [Newpf](int) AS RANGE RIGHT FOR VALUES (0, 1)
GO
  1. Create partition scheme
USE [YourTableName]
GO
CREATE PARTITION SCHEME [PFScheme] AS PARTITION [Newpf] TO ([PRIMARY],    [Secondary], [Secondary])
GO
  1. Now attach partition function and scheme to existing table. Following images will describe how to do that. enter image description here enter image description here enter image description here enter image description here enter image description here

You are done.

  1. Lets check number of records in each partition by following query : 
SELECT $PARTITION.Newpf(IsLatestVersion)  AS PARTITIONID,
COUNT(* ) AS ROW_COUNT
FROM     DBO.ProjectAccounts
GROUP BY $PARTITION.Newpf(IsLatestVersion) 
ORDER BY PARTITIONID

I got http://prntscr.com/5wuvu8

Upvotes: 1

Related Questions