SQL Server indexes - what columns to include in index?
sql-server
Solution
Q1. Yes. (You can always remove them later if you find they are not being used regularly).
Q2. Would I create an Index for Table2 just with Table1ID? Yes. Assuming the primary intended use of the index is to help the optimiser with joins between these 2 tables. (Rather than satisfy some other query that is part of your overall query workload).
Q3. Yes. Each column with a foreign key should have a separate index to its its parent key table.
(All assuming you have an OLTP database rather than an OLAP database)
This post provides TSQL that will generate a script of all the missing Foreign key indexes in a database: TSQL: Generate Missing Foreign Key Indexes
Problem
Q1: Is it worthwhile to create indexes for foreign key columns in a SQL Server database? Q2: If I have two related tables in a SQL Server database and I want to create an index on the foreign key column to improve performance, which columns do I need to include in the index and what type of index is best suited? For example... `Table1` Table1ID int (Primary Key) `Table2` Table2ID int (Primary key) Table1ID int (Foreign key) ..Would I create an Index for Table2 just with Table1ID or do I need to include the primary key (Table2ID) as well. Q3: If I extend the example to include a third table which is related to both Table1 and Table2, do I create one index for each column or one index with both columns? `Table3` Table3ID int (Primary key) Table1ID int (Foreign key) Table2ID int (Foreign key)