January 10, 2007 at 6:51 am
1) Most of our PK's are non-clustered. In this case when should we go for a clustered index.
2) Apart from Primary Keys which are indexed by default, I am going for single column indexes for foreign keys & other
columns which are involved in WHERE, GROUP BY, HAVING & ORDER BY.
3) When is a covered index good to have i.e. a composite index on entire select list.
4) When should we go for a index on HERE, GROUP BY, HAVING or ORDER BY columns followed by select list columns.
5) Are functional indexes allowed in SQL Server i.e. when column is in function like ABS/MAX etc. If so, what is the DDL?
6) When data has been encrypted and we decrypt while selecting, how to make sure that index gets picked.
Thanks in advance.
January 10, 2007 at 8:00 am
There are a lot of factors to consider with indexes.
1. When you are considering a candidate for a clustered index, consider the selectivity of the index. A clustered index should be highly selective (unique). Consider how the data will usually be accessed. A clustered key defines the physical order of the data on the disk. Consider this scenario; you have a table of 10,000,000 rows. Would you rather do 10000 reads to access 10000 rows, or 500 reads to access 10000 rows. Also, understand how the different physical join operators work. If, for example, you have an inovice and InvoiceDetail table--and they are almost always joined when quering--consider clustering InvoiceID (PK) on the invoice table PK and the InvoiceID (FK) on the InvoiceDetail Table. This will allow the possibility for a Merge Join, as well as ensure that invoiceDetail data is contigous. When you are doing this, however, also understand fill factor and index fragmentation, when that will occur and how to deal with it.
2. Always index your foreign keys. Otherwise SQL server will be forced to use a less-than-optimal join operator. The Having clause is used for filtering aggregated fields, so it would be difficult to index the SUM(VALUE) of a particuar group. I also have to question the value of using an index on the ORDER BY column (unless it is clustered) Generally, I have seen that SQL server does a sort operation last and does not use an index. SQL Server also makes decisions on whether or not to use an index based on a large number of factors, so an index on a grouped column may or may not be used. The best way to learn all of this is to try different options and see what is the fastest.
3. When you have a covering index, you are optimizing for reads over writes. Covering indexes are great in some situations because you can retrieve ordered data without the cost of a bookmark lookup. Remember, though, with any composite index, order matters!
4. See 2, but when the indexes are going to be useful and be used. Look at how the data is going to be accessed.
5. You cannot index the value of a function in a SELECT statement, you can index a computed column. Check out Computed Columns in BOL.
6. If you indexes are good, then they will be used. If you are asking these questions, then I suggest you continue to read about indexes and don't second guess the query optimizer for now. It really depends on how you implement the encryption. Beware of using a cleartext value in your where clause on an encrypted column. If you do that, your index will NEVER get used. SQL Server will have to decrypt every row to effect the where clause. How are you implementing the data encryption.
It is important not to place too many indexes on a table because the optimizer can time out. Ensure that every index will be used. By understanding how SQL Server works, you can take away the mystery of why your indexes aren't being used. My advice: Read the articles on indexing here, and read the comments in the 'Your Opinion' section, read sql-server-performance.com, read craig freedman's blog on MSDN, read the books online, ask specific questions and request an explanation. Learn how to interpret a query plan and the I/O stats. Try out different options and see what works best. Make sure you are doing your performance testing on production-like data (i.e. if you have 10,000,000 rows in your tables in production, don't use a test envrionment with only 1000 rows)
Good luck!
SQL guy and Houston Magician
January 11, 2007 at 1:11 pm
The clustered vs non clustered decision is not an easy one.
My personal preference is that every table have a clustered index unless I have a good reason not to. Cluster keys should be narrow and non changing. Preferably unique. Also unless you want lots of fragmentation, try for ones that always increase, like date added or an identity column.
Fewer wider indexes are better than lots of narrow ones. The best way to really get good indexes is to test all your queries, trying different variations and see what works best, unless you know very well how indexes work and how the query optimiser works
the more insert/updates/deletes to the table, the fewer indexes you should have. It's a tradeoff.
Other very good place to look for info is sqlskills.com Lots of good info there on indexing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply