在SQL Server创建索引中，我得到了两个术语，一个有'Include'，另一个有'with。两者之间有什么区别？

Question

CREATE NONCLUSTERED INDEX IX_AgentRegistration_Indx2 
ON [AgentRegistration] ([AgencyType], [IsFIT], [IsActive]) 
INCLUDE ([AgencyCode], [AgencyName])
    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
          IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
          ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [SECONDARY_NEW]

第二个

CREATE NONCLUSTERED INDEX IX_AgentRegistration_Indx3 
ON [AgentRegistration] ([ExchAgentCode])
   WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, 
         IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, 
         ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [SECONDARY_NEW]

Answer 1

如果您正在寻找 INCLUDE 关键字在索引创建中的含义，这里有一个简短的解释：

• 如果创建复合索引，则索引将同时包含两者索引 B 树所有级别的值;这意味着，查询分析器在制作时也有机会使用这两个值决策，这可以支持在a WHERE 子句

• 如果在一列上创建索引并且仅包含其他列，然后，索引将仅包含所有级别的索引列值索引 B 树，并且仅在叶子级别，"最后一个"级别，还将有包含列的值。包括的列值不能用于选择数据 - 它们只是存在于索引叶级别以进行查找。

希望有帮助。

拉詹