Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Index Creation Guidelines Expand / Collapse
Author
Message
Posted Friday, October 21, 2005 7:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 1,895, Visits: 395

You can use indexes on columns like OrderStatus that have a small number of values.  Hopefully 99% of your orders are completed, but if you need to check the sub-1% of orders that have status of HOLD, ERROR, INQUEUE, PROCESSING, you want to use an index. 

You can have an index with highly selective values and low-selectivity values.  Don't generalize, know your selectivity!

I had a client that needed to work on orders that were status=QUEUE.  The order status index worked great even though it would have hindered a search on status=COMPLETED.  The searches that would return less than 1% of the rows used the index. 

Bill Twomey

Microsoft SQL DBA




Post #231141
Posted Friday, October 21, 2005 8:10 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:02 PM
Points: 973, Visits: 293

Good article.  I have a question about the comment not to index free form text fields, though.  Does this mean not to index a name field at all (a concept I don't understand if a search is to be done on a name) or the name field should be a char type and not a varchar type?

I also would be interested in more information on indexing datetime fields, as I do that quite often.




Post #231166
Posted Friday, October 21, 2005 9:01 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, March 10, 2014 7:03 AM
Points: 85, Visits: 624
I didn't agree with that part of the article, but I think that it is the way that the article is worded. It says:

"If the search will use a datetime function in ‘where’ clause or some conversion mechanism, most likely the index will not be used by the Query Optimizer but performance will suffer."

This, I think, is actually the point, that DATETIME columns are often misused in queries. If you have an index on a DATETIME column, and you join or query on that column using a function like DATEPART or something like that, then SQL Server cannot use the index.

However, that is not a good reason not to index DATETIME columns. If your queries are using functions, the answer is to index the column if it is otherwise warranted, and then rewrite the offending queries so that they don't use functions.

Personally, I have found that indexes on DATETIME columns are very useful when querying on date ranges. In fact, in databases with poor indexes where I don't have rights to change anything, I will often pull DATETIME and PK columns into a temporary table and index them, then use that in the join. On a large table, the overhead of creating the temp table and index is more than made up for by time saved in querying the main table.
Post #231198
Posted Friday, October 21, 2005 1:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, January 30, 2012 5:12 AM
Points: 40, Visits: 143

It is related  to the dates where portion for time is not used. For example: '10/21/2005 00:00:00:000' and many records have the same date. Otherwise index is fine

Post #231366
Posted Friday, October 21, 2005 2:11 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 1,895, Visits: 395

An index on datetime which only has a calendar date might be more useful than you think.  1 day out of 365 is 0.27%.  0.27% is selective.  I expect the optimizer to use an index in this case.  If you have a few years of data, the index would be used for a weeks worth of data. 

The only problem with indexes on datetime, is sometimes they don't get used.  Coders defeat the index by using datepart on the indexed column.  But that's a problem with wetware, not the data type, the index or the optimizer. 

 

 




Post #231377
Posted Friday, October 21, 2005 2:20 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, January 16, 2014 12:03 PM
Points: 21,376, Visits: 9,594
wetware??
Post #231386
Posted Friday, October 21, 2005 2:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 2:36 PM
Points: 1, Visits: 3
Nice work Leo, good article on indexes, but should also include a general definition of what a clustered vs non-clustered, but this may be in the book. Also, indexes will change over time since how data is acessed will change, queries change as the database and apps over the db update as business domain rules change. Bottom line is to use indexes when necesary to minimixe table scans, and remove them when no longer needed.

Simple/general definition of cluster and non_cluster.

cluster: like the table of contents of a book in some common grouping.
non cluster: like the index in the back ordered in some general order.

Cheers!
Post #231390
Posted Friday, October 21, 2005 3:13 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 138, Visits: 260
In most cases when you searching a name application iis searching with like criteria. And if it is '%company%' index will not be used.


Post #231399
Posted Friday, October 21, 2005 4:09 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 3:02 PM
Points: 973, Visits: 293
We look for names of people (last first).  We never use the leading %, only the trailing %.  So an index is very useful.  I just wonder if using a varchar instead of a char field has an effect on performance.


Post #231415
Posted Friday, October 21, 2005 8:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 138, Visits: 260
Varchar is always slower, but char data type for free form columns will creates much more problems. So, stay with varchar. 


Post #231433
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse