SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Creation Guidelines


Index Creation Guidelines

Author
Message
bill_twomey
bill_twomey
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 Visits: 611

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





John Deupree
John Deupree
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1003 Visits: 374

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.



John Deupree
Dean Cochrane
Dean Cochrane
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 641
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.
LP-181697
LP-181697
SSC-Enthusiastic
SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)SSC-Enthusiastic (110 reputation)

Group: General Forum Members
Points: 110 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


bill_twomey
bill_twomey
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1935 Visits: 611

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.





Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28739 Visits: 9671
wetware??
Stuart McDougal
Stuart McDougal
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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!
Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
In most cases when you searching a name application iis searching with like criteria. And if it is '%company%' index will not be used.



John Deupree
John Deupree
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1003 Visits: 374
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.

John Deupree
Leo Peysakhovich
Leo Peysakhovich
Old Hand
Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)Old Hand (324 reputation)

Group: General Forum Members
Points: 324 Visits: 350
Varchar is always slower, but char data type for free form columns will creates much more problems. So, stay with varchar.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search