1

I need some help figuring out how to best partition a large transaction table in SQL Server 2014. The transaction table has a CLUSTERED INDEX on the date, a primary key to identify each record, and an institution ID to identify which institution the transaction belongs to that is not part of any index. This table houses data for 3 small institutions, but the combined data over 6 years have made the table too bloated for efficient querying.

Would it make sense to first alter the table to include the institution ID in the CLUSTERED INDEX and partition by year/institution ID like this:

FG1 -> 2008 Institution 1
FG2 -> 2008 Institution 2
FG3 -> 2008 Institution 3
FG4 -> 2009 Institution 1
FG5 -> 2009 Institution 2
FG6 -> 2009 Institution 3

....

OR will partitioning by year regardless of institution be just fine (no need to alter the CLUSTERED INDEX) like this:

FG1 -> 2008
FG2 -> 2009
FG3 -> 2010

...

Keith Harris
  • 1,118
  • 3
  • 13
  • 25
  • `too bloated for efficient querying` partitioning does not help with that. Rather, define good indexes. Using partitioning as a crude, coarse index is usually worse. Are you sure you understand *why* partitioning is beneficial and in what cases? – usr Jan 05 '16 at 22:37
  • Great article on how to implement clustered index "partitioning". The only limitation to this method is that your date needs to be part of the primary key :( https://sqlsunday.com/2014/08/31/partitioned-views/ If you have the professional version of SQL Server you can use the actual PARTITION function which doesn't have this problem. – RobPio Jan 04 '18 at 16:21

0 Answers0