创建索引 SQL Server 2008
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15652587/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATING INDEX SQL Server 2008
提问by Billa
Recently I was put into database fine tuning. I have some ideas about SQL Server and decided to create some index.
最近我被投入到数据库微调中。我对 SQL Server 有一些想法并决定创建一些索引。
Referred this http://sqlserverplanet.com/ddl/create-index
引用此http://sqlserverplanet.com/ddl/create-index
But i don't understand how other types of Index like INCLUDE, WITHoptions will help. I tried google to but failed to see a simple description when to use those.
但我不明白其他类型的索引如何INCLUDE，WITH选项会有所帮助。我试过谷歌，但没有看到何时使用这些的简单描述。
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
INCLUDE (President,YearsInOffice,RatingPoints)
WHERE ElectoralVotes IS NOT NULL
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
WITH ( DATA_COMPRESSION = ROW )
CREATE NONCLUSTERED INDEX IX_NC_PresidentNumber
ON dbo.Presidents (PresidentNumber)
WITH ( DATA_COMPRESSION = PAGE )
What scenario I should use the above? Will they increase performance?
我应该使用上面的什么场景？它们会提高性能吗？
回答by Billa
Data compression will help your query performance too, since after compression, when you run a query, less page/extent will be loaded, since I/O is reduced, reducing I/O is always a good choice.
数据压缩也将有助于您的查询性能，因为压缩后，当您运行查询时，将加载更少的页面/范围，因为减少了 I/O，减少 I/O 始终是一个不错的选择。
回答by BlackICE
I can't speak to the with datacompression option, but the Include option can definitely improve performance. If you select only the PresidentNumber and one or more of President, YearsInOffice, or RatingPoints columns, and the ElectoralVotes is not null, then your query will get values from the index itself and not have to touch the underlying table. If your table has additional columns and you include one of those in your query then it will have to retrieve values from the table as well as the index.
我不能说 with datacompression 选项，但 Include 选项绝对可以提高性能。如果您只选择 PresidentNumber 和一个或多个 President、YearsInOffice 或 RatingPoints 列，并且 ElectoralVotes 不为空，那么您的查询将从索引本身获取值，而不必涉及基础表。如果您的表有其他列并且您在查询中包含其中之一，那么它将必须从表和索引中检索值。
Select top 20 PresidentNumber, President, YearsInOffice, RatingPoints
From Presidents
where ElectoralVotes IS NOT NULL
The above query will only read from IX_NC_PresidentNumber and not have to pull data from the Presidents table because all columns from the query are included in the index
上面的查询只会从 IX_NC_PresidentNumber 读取，而不必从 Presidents 表中提取数据，因为查询中的所有列都包含在索引中
Select top 20 PresidentNumber, President, YearsInOffice, PoliticalParty
From Presidents
where ElectoralVotes IS NOT NULL
This query will use the index IX_NC_PresidentNumber and the Presidents table as well because the PoliticalParty column in the query is not included in the index.
此查询将使用索引 IX_NC_PresidentNumber 和 Presidents 表，因为查询中的党列未包含在索引中。
Select PresidentNumber, President, YearsInOffice, RatingPoints
From Presidents
Where RatingPoints > 50
This query will most likely end up doing a table scan because the where clause in the query versus the where clause used in the index don't match, and there no limit on the rowcount.
此查询很可能最终会执行表扫描，因为查询中的 where 子句与索引中使用的 where 子句不匹配，并且行数没有限制。
