mikesigsworth (12/1/2010)
Should I just put all of the columns in the included columns?
No, absolutely not. By doing that you will be duplicating the table. Think of the impact on database size, backup size, backup duration, reindex time, etc.
There might be some specialised cases where this is a good idea, but they're edge cases (and probably data warehouse type systems)
Edit: Maybe this would be better stated as pure SQL. I'll be honest when I say that I don't understand indexes as thoroughly as I'd like to. But after re-reading my post I think it could be stated simply as, "What's the difference between the following queries (with respect to performance and the above index)"
-- Index seems tuned for this query
SELECT FirstName, LastName
FROM Customer
WHERE EmailAddress = ?
-- L2S will actually do something like this
SELECT Id, StatusId, CustomerNumber, FirstName, LastName, EmailAddress
FROM Customer
WHERE EmailAddress = ?
For the first the index is covering, for the second it's not. Depending how many rows will be returned, SQL may or may not use the index. If email address is unique (or nearly so), the index will still be used.
As for indexes...
http://www.sqlservercentral.com/articles/Indexing/68439/ (3 parts)
http://www.sqlservercentral.com/articles/Indexing/68439/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability