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 indexes should be created when they are selective enough and they are most efficient choice.
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.
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.