SQL Server 2012 Table Partitioning - Have I done it correctly? 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!
Unfortunately, you are correct the suspect that the partitioning you created can get you in trouble.

First of all, partition elimination only happens if the partitioning key is in the query you execute. In other words, any query that does NOT contain AcctYear will do (at least) 31 seeks. SQL Server has no way to know which partition to search if you don't specify it.

Second, there are several issue with the optimiser and partitioning that prevents elimination even when it is theoretically possible to achieve it (like in your  example). In SQL Server, you can think of a partition as a hidden index column. In your case, if you were to index in such a way that job number was leading the index, your query with both AcctYear and job number might do only a single seek.

If you add the most common queries you expect to your question, it would be possible to advise you on the best partitioning strategy that achieves your goal.