I have a table that stores accounting transactions for what we call "jobs".
The table looks like this:
( REGION char(3) JobNumber char(17) SeqNum decimal(5,0) ..... ..... ..... AcctYear decimal(4,0) )
Unpartitioned, the PK and clustered index is Region, JobNumber, SeqNum.
What we want to do is partition the table by year (AcctYear column), and have each partition located on a separate filegroup. The current and previous year will reside on an SSD and previous years beyond that will reside on cheaper disk storage. Each year has approx. 40 million rows.
I have already partitioned the table, but I am questioning whether I have done it correctly. Let me explain why...
What I have done is added the AcctYear to the end of the PK/Clustered Index (i.e made the PK/CI Region, JobNumber, SeqNum, AcctYear), created the function and schema etc. I have setup 30 partitions, one for every year from 2001 till 2030.
The reason I chose this method of adding the AcctYear to the end of the PK/CI was that I don't always expect the AcctYear to be specified in queries/joins, and if I put the AcctYear at the beginning of the Clustered Index, I would have to either always specify the AcctYear in every query and join, or create another secondary index only on the existing primary key columns.
What makes me doubt what I have done is that when I query for a specific job number AND AcctYear, the Actual Execution Plan shows that it is still looking through the 31 partitions, instead of knowing which partition that data is in. The SHOW STATISTICS IO, also says 31 seeks are being done.
So, have I done this partitioning correctly based on what I want to achieve? or Is there a better way?
Thanks for any help in advance!