asked
Which Index to Create without change in table structure
I have a table column Id(int), ExecDate(Datetime) and there are two queries who are accessing the data from this table. Id Date 1 2009-01-08 10:42:15.613 2 2011-02-03 11:42:15.613 3 2012-04-05 16:42:15.613 4 2007-08-11 12:42:15.613 5 2013-10-02 13:42:15.613 One query is accessing data based on Column ExecDate E.g. SELECT ID FROM Table Where ExecDate='2009-08-17 10:72:11.613' And other query is only accessing the date part of ExecDate SELECT ID FROM Table Where convert(varchar,ExecDate,111)='14/01/25' --'yy/mm/dd' Now I want to create such index that should cover both queries. I know I can create index on Execdate(datetime), but that will cover only 1st query, not 2nd. Is there any way I can create one/two index that will help the performance of both queries. Or Creating Index on ExecDate(datetime) is the only solution. Note: I cannot do any change in table structure(i.e. cannot add any column)
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
There is no way to create an index that will work through a function like that.
you should change your 2nd query to below. And the index on ExecDate will cover that SELECT ID FROM Table Where ExecDate >= '2014-01-25' and ExecDate
16 People are following this question.
