Increase performance (a little bit?) and reduce index sizes - Keep index tree optimized

On our radar
On Service Studio it would be nice to be able to specify which entity fields should be "included" in the index.

As of today, if we have the query :

select a.name from a where a.typeid = 5

whe should,(assuming it has a lot of requests and it's the only query on this table, have an index on typeid, name (including only typeid would make SQL go look into the page table).

Still, if the name attribute is never used for searching purposes, then in SQL Server we can just build the index this way

CREATE INDEX idx_type ON a(typeid) 
INCLUDE (name)

This way SQL will neither search and compare on a.name, and the size of the index is smaller, since the include fields are kept at the leaf level.

Reducing the index size, by keeping it lean, will also mean less time on the update, insert and delete operations, since as bigger an index is, more resources it requires.

Check out this posts for a little more insight : http://stackoverflow.com/questions/2436923/why-use-include-in-a-sql-index
Created on 22 May 2012
Comments (0)