SQL Server Indexing best practice (SQL Server 2008)
indexing, sql-server, sql-server-2008
Solution
The clustered index is the index that (a) defines the storage layout of your table (the table data is physically sorted by the clustering key), and (b) is used as the "row locator" in every single nonclustered index on that table.
Therefore, the clustered index should be
- narrow (4 byte is ideal, 8 byte OK - anything else is too much)
- unique (if you don't use a unique clustered index, SQL Server will add a 4 byte uniqueifier to your table)
- static (shouldn't change)
- optimally it should be ever-increasing
- fixed with - e.g. don't use large `Varchar(x)` columns in your clustered index
Out of these requirements, the `INT IDENTITY` seems to be the most logical, most obvious choice. Don't use variable length columns, don't use multiple columns (if ever possible), don't use GUID (that's a horribly bad choice because of it's size and randomness)
For more background info on clustering keys and clustered indexes - read everything that Kimberly Tripp ever publishes! She's the Queen of Indexing in SQL Server - she knows her stuff extremely well!
See e.g. these blog posts:
- GUIDs as PRIMARY KEYs and/or the clustering key
- The Clustered Index Debate Continues...
- Ever-increasing clustering key - the Clustered Index Debate..........again!
- Disk space is cheap - that's not the point!
In general: don't overindex! too many indices is often worse than none!
For non-clustered indexes: I would typically index foreign key columns - those indexes help with JOINs and other operations and make things faster.
Other than that: don't put too many indexes in your database ! Every index must be maintained on every CRUD operation on your table! This is overhead - don't excessively index!
An index with all columns of a table is an especially bad idea since it really cannot be used for much - but carries a lot of administrative overhead.
Run your app, profile it - see which operations are slow, try to optimize those by adding a few selective indexes to your table.
Problem
I have some doubts on choosing the right index and have some questions: Clustered index What is the best candidate? Usually is the primary key but if the primary key is not used in the search by eg `CustomerNo` is used to search on customers should the clustered index put on `CustomerNo`? Views with SchemaBinding If have a view with indexes I read that these are not used but those on tables are. Pointless no? Or am I missing the point? Will it make a difference using "NOExpand" to force to read the index from the view rather than the table? Nonclustered indexes Is it good practice when adding a nonclustered index to include every possible column till you reach the limit? Many thanks for your time. I am reading massive database and speed is a must