Using Comparison operator with dateTime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Using Comparison operator with dateTime

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-05-19 : 06:40:20

Hi

I am trying to understand how using a comparison operator with a dateTime field affects the logical reads and scan vs seek.In the following DT is dateTime field and the table has around 10 million rows with a non-clustered index on DT:

1. SELECT TOP 10 DT FROM TABLE1 WHERE DT LIKE '2000%' - logical reads 20713 and took 4 seconds to execute
2. SELECT TOP 10 DT FROM TABLE1 WHERE YEAR(DT)='2000' - logical reads 20713 and took 2 seconds to execute
3. SELECT TOP 10 DT FROM TABLE1 WHERE DT >= '2000' AND DT <'2001' - logical reads only 3 and query finished in less than a sec.

I need to understand how the comparison operators (as used in query 3) reduced the logical reads drastically and does '2000' in query 3 means '2000-01-01'? Does this mean I can omit the MMDD part when using > or < operators with dates?

Thanks

--------------------
Rock n Roll with SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 07:08:15

Your examples and my opinions:

1. LIKE on a datetime isn't possible so DT will be implicit converted to character and that means the index can't be used.

2. YEAR() on the indexed column means the index can't be used.

3. '2000' gets implicit converted to datetime = 2000-01-01 00:00:00.000 same to '2001' = 2001-01-01 00:00:00.000 and the index can be used and will be used.

No, you're never too old to Yak'n'Roll if you're too young to die.

rocknpop
Posting Yak Master

201 Posts

Posted - 2010-05-19 : 07:16:51

Hi webfred, Thanks for the explanation. I got it now.

--------------------
Rock n Roll with SQL

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-19 : 07:19:02

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-19 : 09:44:50

But the effecient method would be

SELECT TOP 10 DT FROM TABLE1 WHERE DT >= '2000-01-01' and DT < '2001-01-01'

Madhivanan

Failing to plan is Planning to fail

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-19 : 11:35:23

quote:
Originally posted by madhivanan

But the effecient method would be

SELECT TOP 10 DT FROM TABLE1 WHERE DT >= '2000-01-01' and DT < '2001-01-01'

Madhivanan

Failing to plan is Planning to fail

If we assume a COMPUTED COLUMN on the DT column like this:
MyYear AS YEAR(DT)

Then create a nonclustered index like this:
CREATE NONCLUSTERED INDEX IX_0 ON table (MyYear) INCLUDE (covering other needed columns...);

Is this approch efficient or not?

______________________

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-05-19 : 14:19:30

That takes up extra disk space by storing a superfluous column (assumsing it is persisted) and index data. Plus the overhead of maintaining all of that. So, it's be less efficient. However, it should perform well. So, you might consider it efficient from that perspective.

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-19 : 16:05:10

Thank you,
I did not notice the extra disk space and overhead of modifications.

______________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources