Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Query Optimizations With Dates

From Wiki

Jump to: navigation, search

When querying for dates do not use convert. The following code will cause a table scan

  1. WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'

Let's take a look what we should do. First create this table

  1. CREATE TABLE #temp (SomeDate DATETIME)
  2. INSERT #temp VALUES ('2008-01-08 10:19:40.703')
  3. INSERT #temp VALUES ('2008-01-07 13:19:40.703')
  4. INSERT #temp VALUES ('2008-01-07 12:19:40.703')
  5. INSERT #temp VALUES ('2008-01-07 11:19:40.703')
  6. INSERT #temp VALUES ('2008-01-08 11:19:40.703')
  7. INSERT #temp VALUES ('2008-01-06 15:19:40.703')
  8. INSERT #temp VALUES ('2008-01-06 16:19:40.703')
  9. INSERT #temp VALUES ('2008-01-06 17:19:40.703')
  10. INSERT #temp VALUES ('2008-01-06 18:19:40.703')
  11. INSERT #temp VALUES ('2008-01-06 19:19:40.703')
  12. INSERT #temp VALUES ('2008-01-06 20:19:40.703')
  13. INSERT #temp VALUES ('2008-01-08 21:19:40.703')
  14. INSERT #temp VALUES ('2008-01-07 22:19:40.703')
  15. INSERT #temp VALUES ('2008-01-08 23:19:40.703')

Add an index

  1. CREATE INDEX ix_TempDate ON #temp(SomeDate)

Hit CTRL + K and run these two queries

  1. SELECT *FROM #temp WHERE CONVERT(VARCHAR(8),SomeDate,112) = '20080107'  
  2. SELECT *FROM #temp WHERE SomeDate >= '20080107'AND SomeDate < '20080108'

Look at the plan, see the big difference?


If you prefer to see the plan in text you can run the SET SHOWPLAN_TEXT command


An here is the output if you would run the same two queries

  1. |Index Scan(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
  2. WHERE:(Convert([#temp].[SomeDate])='20080107'))

  1. |Index Seek(OBJECT:([tempdb].[dbo].[#temp00000000A324].[ix_TempDate]),
  2. SEEK:([#temp].[SomeDate] >= 'Jan 7 2008 12:00AM'
  3. AND [#temp].[SomeDate] < 'Jan 8 2008 12:00AM') ORDERED FORWARD)

make sure you turn it off again


So why does this happen? Well when you have a function on a column in the WHERE clause the optimizer can not use an index seek because it does not know how many rows will be affected. It has to do an expensive index scan and scan the whole index instead

Contributed by: --SQLDenis 15:24, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks - 100+ List

Section Query Optimization

111 Rating: 2.6/5 (112 votes cast)