what columns to include in index?

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)

---

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

---

None of the indexes you are thinking of creating are a bad idea. But, if you're looking for performance measures you need to look at how the table(s) are most frequently approached. Which columns will be in your WHERE clause the most; and base your indexes on those.

Also look for the best cardinality and place that at the top of your WHERE clauses. Consider partitioning as well.

链接地址: http://www.djcxy.com/p/76306.html

上一篇: 查询获取外键索引

下一篇: 索引中包含哪些列？