July 25, 2013 at 3:25 pm
If space is not an issue, which one is preferred, adding all columns to the key column of index or adding on the searchable columns as key and rest in include columns?
July 26, 2013 at 12:42 am
There is no point adding all columns as key columns unless you have queries which use all the columns in joins/where/group by clauses.
You also have a 900 byte limit on key columns.
Also adding too many non-key columns is going to cause grief:
http://msdn.microsoft.com/en-us/library/ms190806%28v=sql.105%29.aspx
Performance Considerations
Avoid adding unnecessary columns. Adding too many index columns, key or nonkey, can have the following performance implications:
Fewer index rows will fit on a page. This could create I/O increases and reduced cache efficiency.
More disk space will be required to store the index. In particular, adding varchar(max), nvarchar(max), varbinary(max), or xml data types as nonkey index columns may significantly increase disk space requirements. This is because the column values are copied into the index leaf level. Therefore, they reside in both the index and the base table.
Index maintenance may increase the time that it takes to perform modifications, inserts, updates, or deletes, to the underlying table or indexed view.
You will have to determine whether the gains in query performance outweigh the affect to performance during data modification and in additional disk space requirements. For more information about evaluating query performance, see Query Tuning.
July 26, 2013 at 2:20 am
curious_sqldba (7/25/2013)
If space is not an issue, which one is preferred, adding all columns to the key column of index or adding on the searchable columns as key and rest in include columns?
Neither.
Design you indexes to support your queries. The queries that run against the tables will guide your index design.
http://www.sqlservercentral.com/articles/Indexing/68439/
http://www.sqlservercentral.com/articles/Indexing/68563/
http://www.sqlservercentral.com/articles/Indexing/68636/
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply