SQL Server Performance Forum – Threads Archive

index on a date column

Hello,
I have a create_date column which populates system date into it (hh,mi,ss include). One of our reporting system sends query like this: where convert(datetime,CONVERT(char(10), "TableName"."create_date", 101))
between {ts ‘2005-08-01 00:00:00.000’} and {ts ‘2005-08-31 00:00:00.000’} I have an index on this column, but because of the conversion, looks like it is not using the index. Any suggestion ?

---

Not sure why it is not using an index. I have written numerous queries like that but never faced any issue. Is it possible that you convert the RHS to datetime format and then try it. I see that you are converting LHS from datetime into char(10). Is this where clause (currently used) fetching you any data ?

---

1st, why are you doing a string comparison, instead of a data comparison, ie, where "TableName"."create_date" between {ts ‘2005-08-01 00:00:00.000’} and {ts ‘2005-08-31 00:00:00.000’} 2nd, if you want to know why it is not using the index, compare the execution plans between queries: A.
SELECT create_date
FROM TableName
WHERE create_date between {ts ‘2005-08-01 00:00:00.000’} and {ts ‘2005-08-31 00:00:00.000’} B. SELECT xx (whatever you are selecting in your query
FROM TableName
WHERE create_date between {ts ‘2005-08-01 00:00:00.000’} and {ts ‘2005-08-31 00:00:00.000’} C. SELECT xx (whatever you are selecting in your query
FROM TableName WITH(INDEX(index name))
WHERE create_date between {ts ‘2005-08-01 00:00:00.000’} and {ts ‘2005-08-31 00:00:00.000’} the difference in cost explains why it may or may not use the date index

---

See if this provides additional help:http://www.sql-server-performance.com/fk_datetime.asp —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

If you use Convert function Index cannt be used. Dont use Convert functions in the query. Use Format function of Reports to format it Madhivanan Failing to plan is Planning to fail

---

1. I’d like to elaborate on Joe’s suggestion to try different queries to see why it isn’t using an index. What I’ve read, and have seen from various experiments in my database is that if a particular index will return more than a certain percentage of the rows based on the value, the optimizer skips the index step and simply does a table scan or uses the clustered index if there is one, and does a where instead. Thus if you just select the data field you should see it use the index, but if you select other fields that will force a bookmark lookup you may or may not see it use the index. It could also be ignoring the index because of the convert, and once removed it may use the index. 2. I’m obviously new to the forum, but as the thread is titled "index on a date column" I wanted to carry the same train of thought of "indexing a date column" and combine it with previous threads and great advice in Jeff’s article on fill factors, as I have some mildly large tables with indexes also on date columns. The database I’m maintaining was constructed by a third party and it has a couple of indexes on date columns with a fill factor of only 90. Providing I know for a fact that the data is inserted in order of time and there is no way to go back and insert a previous date/time value into the table, it would be a good idea to go ahead and use a fill factor of 100. Wouldn’t it? Thanks,
Dalton

---

it is not the percentage of rows, but rather the number of rows relative to the number of pages (dpages column in sysindexes). figure the optimizer assumes 1 bookmark lookup row costs approx 3.5 more than 1 page of a table scan, depending on a number of things

---

Yes, that’s correct.

---

]]>