定义索引:哪些列对性能有影响? [英] Defining indexes: Which Columns, and Performance Impact?

问题描述

我知道如何使用索引(聚类和非聚类) 但是我什么时候应该在表中使用非聚簇索引. 应该有什么情况才能使我的列成为非聚集索引. 我没有按照msdn准则进行操作，但仍然有些困惑.

I know how to use indexes(clustured and non clustured) But when should i use non clustured indexes in my table. What scenarios should be there, so as to make my column non clustured index. I have gone throught msdn guidelines but still little bit confusion.

我应该仅将唯一列作为NC还是将其他列也作为NC.

Should i make only unique columns as NC or should there any other columns also as NC.

如果我用NC索引重载了我的表，那么它也会降低我的性能吗?

If i overload my table with NC indexes then will it decrease my performance also ?

我应该在作为外键的列上使用复合非C索引.

Should I use composite non-C index on columns that are foreign keys.

我知道主键应该是Clustured，唯一键应该是NC，但是外键呢?

I know primary key should be Clustured, Unique keys should be NC but what about foreign keys.

推荐答案

每个表只能有一个聚集索引.它不必是主键，但是在大多数情况下，它会是主键.

You can only have one clustered index per table. It doesn't have to be the primary key, but in most cases it will be.

除此之外-它实际上取决于查询& 将使用哪些索引的引爆点.但是定义索引也意味着会对DML产生影响-插入，更新和更新；删除将对性能造成轻微影响.

Beyond that - it really depends on the queries & the tipping point for what indexes will be used. But defining indexes also means there will be an impact to DML - inserts, updates & deletes will take a slight performance hit.

我应该在作为外键的列上使用复合非聚集索引吗?

Should I use composite non clustered index(es) on columns that are foreign keys?

该列无关紧要，对于优化器来说，决定使用哪个索引(聚簇索引或其他索引)很重要.

Doesn't matter what the column is, it's the usage that matters for the optimizer to determine what index, clustered or otherwise, to use.

这篇关于定义索引:哪些列对性能有影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！