Indexing is the fast way to be faster

The problem is, though … how do you choose the right indexes? How do you choose the right index keys?

It’s very easy to choose the wrong indexes, in this case bad performance.

FILLFACTOR and PAD_INDEX

FILLFACTOR and PAD_INDEX are only used when the index is being built or rebuilt. FILLFACTOR = 0, it is the same of FILLFACTOR = 100 There is no formula to calculate FILLFACTOR A good start of FILLFACTOR is 70 when you know nothing about it.

Rebuilding a clustered index

Rebuilding a clustered index does NOT rebuild another indexes ( after SQL2000)

Nonclustered index

nonclustered indexes should be created when they are selective enough and they are most efficient choice.

Fragmentation

You must always be concerned about fragmentation. It’s not just the fragmentation that is a problem. It’s the cause of the fragmentation – the page splits – generating a lot of transaction log.

GUID key

GUIDs are random and so cause index fragmentation. GUIDs are very large, 16 bytes long, and it is included in each nonclustered indexes. You can use NEWSEQUENTIALID, it is NOT random, but it still 16 bytes long. GUIDs affect non-clustered indexes when it is used as primary key. Best choice still be an INT or BITINT.