Correct Way to Get Date Between Dates In SQL Server

Answer 1

Nothing you can do here except having the proper index. Well, get rid of the "*" in the select - enter the fields, and do not ask for all fields (limits data transfer).

That basically is it. If you have an index on the Date_Printed, as you say, then that is it for that query. More performance = better machine, mostly faster discs, more RAM for caching.

Answer 2

I'd recommend that you use:

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2010-01-01'
SET @EndDate = '2010-06-19'

SELECT [someColumn],... FROM table WHERE Date_Printed >= @StartDate AND Date_Printed < @EndDate

Don't know what could hit in that last second of the day!

Answer 3

Ideas to optimize this query:

1. Make the index on Date_Printed a clustered index. However this may slow other queries and DML down.
2. Explicitly list the columns needed if you don't need all the columns of the table. (And even if you do, it's better form to list the columns.) This will decrease the amount of traffic traveling back to the caller of the query.
3. If you don' create the index on Date_Printed as a clustered index, and you are able to limit the columns needed then create a covering index for this query. A covering index is one where all the columns needed by the query are included in the index. An alternative on SQL Server 2008 is to use the include option to include columns in the index without indexing those columns. In other words index on just date_printed, but include the other columns being returned.

Some of the above ideas may involve trade-offs of speed of other DML and increased storage space.

Answer 4

If your using Microsoft SQL Server, a good way to test how efficient the query is to run SQL Server Profiler (From Tools). Then as you run the query you should see your query being called with information on how efficient it is. You can also export your log from profiler as an xml file into the Database Engine Tuning Advisor which might help you with ideas for creating indexes on your table.

Another technique is to include SET STATISTICS IO ON in your query, then in messages you will get a list of tables (1 in your case) and the number of reads etc performed by the query

Another is to include the execution plan in the results by clicking on the 'Include Actual Execution Plan' button in SQL Server Management Studio This can be a little complicated, but should tell you if your index is being used or not.