mssql-index

inf : http://blog.sqlauthority.com/2007/05/08/sql-server-index-optimization-checklist/


mssql-Create- index

CREATE INDEX index_name ON table_name (column_name)

CREATE UNIQUE INDEX index_name ON table_name (column_name)

CREATE INDEX PIndex ON Persons (LastName)
CREATE INDEX PIndex ON Persons (LastName, FirstName)


mssql-Reindex table-reindex
ALTER INDEX ALL ON <TABLE_NAME > REBUILD
refer : http://technet.microsoft.com/en-us/library/ms188388.aspx


ReIndexing Database Tables and Update Statistics on Tables



mssql-index-policy


Avoid Full table scan

Index optimization is always interesting subject to me.

Most of the time the Query Speed is optimized just following basic rules mentioned below. Once following checklist applied interesting optimization part begins which only experiment and experience can resolve.

Create Index on frequently used columns in T-SQL Code. Columns used in WHERE, ORDER BY and GROUP BY are good candidate for Indexes. Create Index on column which are used in JOIN Condition.


Remove any un-necessary Indexes.
As Index occupies hard drive space as well as it decreases performance of all the insert, updates, deletes to the table.

Smaller Index
Key gives better performance than Index key which covers large data or many columns

Index on Integer Columns performs better than varchar columns.

Clustered Index must exist before creating Non-Clustered Index.

Clustered Index must be created on Single Column which is not changing and narrow in size. Best candidate is primary key.

Non-clustered Indexes increases performance of the query that returns fewer rows and rows has wide selectivity spectrum.

Each table must have one Clustered Index.

If column have low selectivity avoid creating Index on that column as it slow down the rows modification and system will not get benefit from Index.

Multiple Columns Index or Covered Index should be ordered as Most Selective column on left and gradually decreasing selectivity as they go right.

Use SORT_IN_TEMPDB option when table is created if tempdb is on different disk. This will increase the performance to create Index.

Rebuild Index frequently using ALTER INDEX and De-fragment Index to keep performance optimal for Indexes.