Why can't I both index and include the same column in my index?
indexing, sql-server, sql-server-2008
Solution
The reason is `ObjectId` is already included into the index as a key column and you're trying to include it as a non-key one too, which is unnecessary.
CREATE NONCLUSTERED INDEX Ix_Foo
ON Foo (ForeignKeyID, ObjectID)
This should be enough for your purposes.
You'd normally need to include non-key columns so that (quoting MSDN):
They can be data types not allowed as index key columns.
They are not considered by the Database Engine when calculating the number of index key columns or index key size.
Problem
Why can't I do something like the following? I want filters on this "common" column to be both fast and to also be able to return it without requiring a table scan. ``` CREATE NONCLUSTERED INDEX Ix_Foo ON Foo (ForeignKeyID, ObjectID) INCLUDE (ObjectID) ``` When I do this, I get the error: Cannot use duplicate column names in index. Column name 'ObjectID' listed more than once. I'd like this for queries such as this where I both want to return ObjectID as well as filter by it. The subquery here is an example of what I mean: ``` SELECT something FROM Bar WHERE Bar.FooID IN (SELECT ObjectID FROM Foo WHERE ForeignKeyID=13 AND ObjectID IN (12, 13, 14, 15)) ``` What am I conceptually missing?