CODEWITHSUNDEEP
sqlsql-serverindexing
Paulus Viljoen
Paulus Viljoen

Reputation: 91

Best practice for indexing in SQL Server

I have a transaction table and a inventory table that I would like to 'JOIN' together. The tables need to 'JOIN' on three primary keys.

My question is: should I create a unique key (concatenation of the three fields) and create a 'INDEX' on the unique key or would I just create a non-clustered 'INDEX' on all three fields?

I'm currently using SQL Server 2014

Upvotes: 0

Views: 557

Answers (1)

john McTighe
john McTighe

Reputation: 1181

I'm guessing the Transaction table is the biggest and the Inventory is the smaller. A lot depends on what proportion of the data would you expect to be returned by your join - If its most then a table scan will probably occur so an index wont help much. If your going to try and get a small subset of date then create an index on the 3 columns on both tables and create a foreign key from Trans to Inventory on the 3 cols. (SQL Server needs an index as well as a FK) Pick the most granular column as the first in your index as this will encourage SQL servers Optimiser to use the index.

Upvotes: 1

Related Questions