sql server and tuning with indexes

clustered indexes ~ only one per table; for tables with PK/ID SQL Server defaults to creating a clustered index on that column; designer can change that, however, by de-selecting ‘clustered index’ checkbox. its possible for a column that’s NOT the ID/PK to have a clustered index … and then the ID/FK column becomes a unique index. the structure of the index = the physical order of data in the database.

non-clustered ~ can have up to 249 non-clustered indexes on each table; when a table already has a clustered index, when creating a new index, SQL Server defaults to creating a non-clustered index. functions similarly to the index of a book.

unique ~ columns indexed on a unique index cannot have duplicate values. not really an index, but a property. so, clustered and non-clustered indexes can be unique. by default, any column with a PK/ID key has a unique clustered index.

where to build indexes ~
on columns used in these queries, and frequently accessed by WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses; only add indexes if you know that they will be used by the queries run against the table.