Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding columns to a Index As key Vs Non-key columns Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 3:25 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 3:44 PM
Points: 1,266, Visits: 2,912

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?
Post #1477763
Posted Friday, July 26, 2013 12:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:40 PM
Points: 1,077, Visits: 1,496
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.
Post #1477872
Posted Friday, July 26, 2013 2:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 41,514, Visits: 34,429
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1477902
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse