Timeline for Hard and Fast rule for include columns in index
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 7, 2017 at 6:32 | comment | added | gbn | @ViswanathanIyer it won't be added twice though to the actual on-disk storage: SQL Server detects this. So it is not needed but it makes things clearer. However, we don't know of any clustered indexes in the question so it's safer to assume none. | |
| Aug 6, 2017 at 6:01 | comment | added | Vishwanath Dalvi | @gbn Do we really required EmployeeID in include list? As If we've a clustered index on EmployeeID Column and on top of this if we create a nonclustered index on DeptId column thus NonClustered index already has reference to clustering key which is included in NonClustered Index structure, including clustering key in INCLUDE list doesn't add any benefits. | |
| Feb 13, 2012 at 22:04 | history | migrated | from stackoverflow.com (revisions) | ||
| May 31, 2011 at 13:28 | vote | accept | CommunityBot | ||
| May 31, 2011 at 13:27 | comment | added | JNK | @Rocky - NonKeyColList order doesn't matter. KeyColList order should be in order of frequency you expect them to be used in queries. See my notes on my answer below, but it's like Last Name, First Name, Middile Initial in a phone book. You need the first field in order to find the second field. | |
| May 31, 2011 at 13:18 | comment | added | Rocky Singh | Great! just one thing more what will decide the order of KeyColList and NonKeyColList. Can you just explain with my example? Suppose now my query is SELECT EmployeeID, DepartmentID, LastName FROM EmployeeWHERE DepartmentID = 5, StateID=4 How hsould be the index now? | |
| May 31, 2011 at 13:10 | comment | added | JNK | +1 - I agree (see my ans) that the sample indexes in OP are worthless for the query! | |
| May 31, 2011 at 13:08 | history | answered | gbn | CC BY-SA 3.0 |