Indexing datetime field for selecting dates ranges

Hi,
I have a table with a smalldatetime field. Some of the queries in my
application are using range searches over that smalldatetime field, such as
selecting all the records within a date range. None of these queries are
selecting records from a specific date/time. They all work on ranges (e.g.
using BETWEEN or operators like >=).
Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
Regards,
Amir.Yes, indexes on those columns can be beneficial, just as indexes on any colu
mn. You need to make
sure that your query is written in a way so that those indexes can be used
(http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And whether
the indexes then *will*
be used is dependent on a lot of factors (the query, the data, selectivity e
tc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amir" <agamy@.actcom.co.il> wrote in message news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...[
color=darkred]
> Hi,
> I have a table with a smalldatetime field. Some of the queries in my appli
cation are using range
> searches over that smalldatetime field, such as selecting all the records
within a date range.
> None of these queries are selecting records from a specific date/time. The
y all work on ranges
> (e.g. using BETWEEN or operators like >=).
> Is there any reason for indexing the smalldatetime field? Could that make
the queries run faster?
> Regards,
> Amir.
>[/color]|||Thanks for the explanation!
Kind Regards,
Amir.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uOlH7mDGGHA.1424@.TK2MSFTNGP12.phx.gbl...
> Yes, indexes on those columns can be beneficial, just as indexes on any
> column. You need to make sure that your query is written in a way so that
> those indexes can be used
> (http://www.karaszi.com/SQLServer/info_datetime.asp), of course. And
> whether the indexes then *will* be used is dependent on a lot of factors
> (the query, the data, selectivity etc).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Amir" <agamy@.actcom.co.il> wrote in message
> news:OCbLcq9FGHA.1032@.TK2MSFTNGP15.phx.gbl...
>

indexes question

Assume we have a sales table, which contains a OrderID(char(8)) and a OrderDate(smalldatetime) field. Each day, hundreds of thousands of records needs to be inserted into this table. We need a daily sales report, and the OrderID is not necessarilly sequentially entered (every data operator has a range of OrderID, so for each data operator, it is sequential, but globally, it is not).

So, I would like to create a clustered index on OrderDate, and the Primary Key is on OrderID. I think it is good for generating the daily sales report. However, when an order is entered, we need to quickly check if the OrderID already exits, because the unique index on OrderID is built on the clustered index (on OrderDate), which means the Database Engine will search all the records on that day. This could be a slow process.

Is it possible to create indexes so that I can generate daily report and search for the OrderID quickly at the same time?

Thanks.

Create the clustered index on the orderDate and a second, unique non clustered index on orderId. When you check that the order id already exists, if you do a "if exists (select orderId from sales where orderId = @.orderId)", then this will not need to do a clustered index lookup as the index is covered i.e. : there is no need to go back to the base table, as the only column you are asking for (orderId) is already in the non clustered index.

The daily sales report would continue to work with the clustered index on the orderDate.

Hope this helps.