Dynamics GP SQL Server Composite Primary Key Trivia

By Steve Endow

Coincidentally, after working on a really interesting Dynamics GP SQL Server performance issue earlier this week, I will be having a call tomorrow with another GP partner and GP customer to discuss a different Dynamics GP SQL performance issue.

This time, it involves an eCommerce web site that is trying to stay synchronized with Dynamics GP.  I don't have much information on the integration at the moment, but I do have a list of requests from the web developers.  They are asking that several indexes be added to Dynamics GP.

When I reviewed their request for new indexes, I looked at the GP tables to see what indexes already existed on the tables.

One example is that they requested an index for CUSTNMBR on the SOP10107 table.  The SOP10107 table has a composite primary key (a key consisting of multiple fields), but does not have any additional indexes (at least not on my GP 2010 TWO database).

So the developers are apparently asking for an index on CUSTNMBR, the first field in SOP10107.

But CUSTNMBR is one of the fields in the primary key.  Doesn't SQL Server use the primary as an index?  I didn't actually know and had to do some research.

The answer is one of my favorites:  It depends.

Apparently, if you query the first field in a composite primary key, or all fields in the composite primary key, the primary key can be used as an index.  But if you query the second or subsequent fields in the key, then the primary key cannot be used as an index.

I need to track down the MSDN or TechNet documentation to back this up, but I initially found it explained in two threads on StackOverflow:

http://stackoverflow.com/questions/4381921/composite-primary-key-and-additional-indexes

http://stackoverflow.com/questions/3613103/indexing-individual-fields-of-sql-server-composite-keys


I haven't researched the specific mechanics of the primary key in SQL Server to understand exactly why this limitation exists, but I have speculated about the reason and can understand why it might be the case.

So there you go.  Now you can talk fancy about SQL Server composite primary keys at your next social gathering!  You will be the life of the party.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com