Multiple Indexes vs Multi-Column Indexes

by

I agree with Cade Roux.

This article should get you on the right track:

One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column.
Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.

Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

  • you sometimes search by state only.
  • you sometimes search by state and county.
  • you frequently search by state, county, zip.

Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by ‘state’ it is efficient but yet not as efficient as Single-Column index on ‘state’

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by’s.

The article will help a lot. 🙂

More Related Contents:

  1. What columns generally make good indexes?
  2. Updating image in VB and SQL Server not working [closed]
  3. What do Clustered and Non-Clustered index actually mean?
  4. Storing a file in a database as opposed to the file system?
  5. List of all index & index columns in SQL Server DB
  6. Preferred Method of Storing Passwords In Database
  7. Using a sort order column in a database table
  8. Why use the INCLUDE clause when creating an index?
  9. Create a view with ORDER BY clause
  10. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  11. SQL Server: Index columns used in like?
  12. What are the differences between a clustered and a non-clustered index?
  13. Getting execute permission to xp_cmdshell
  14. Should every User Table have a Clustered Index?
  15. TSQL: Create a view that accesses multiple databases
  16. How to migrate a PostgreSQL database into a SQLServer one?
  17. What is the point of “Initial Catalog” in a SQL Server connection string?
  18. “Cannot open user default database. Login failed.” after installing SQL Server Management Studio Express
  19. How to create a yes/no boolean field in SQL server?
  20. What datatype should be used for storing phone numbers in SQL Server 2005?
  21. Best way to store time (hh:mm) in a database
  22. Executing set of SQL queries using batch file?
  23. Database design: Calculating the Account Balance
  24. Date range overlapping check constraint
  25. What are the advantages of VistaDB
  26. Generate table relationship diagram from existing schema (SQL Server) [closed]
  27. Calculate Time Difference Between Two Rows
  28. What are the limitations to SQL Server Compact? (Or – how does one choose a database to use on MS platforms?)
  29. How do you deal with blank spaces in column names in SQL Server?
  30. Tool for Scripting Table Data

Leave a Comment