By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Smalldatetime comparisons with non-clustered index

P: n/a
This looks like a bug - hopefully somebody can explain what is actually
happening. Using SQL Server 2000 SP4.

Here's a repro script with comments:

/* repro table */
CREATE TABLE dbo.T (
ID int NOT NULL,
Time datetime NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (ID, Time)
)
GO

/* the problem does not happen without this index */
CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
GO

/*
sample row - note that
CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
*/
INSERT INTO dbo.T (ID, Time)
VALUES (1, '2006-04-08 13:14:58.870')
GO

/*
This does not return any rows - why?
The comparison should evaluate to TRUE.
*/
SELECT *
FROM dbo.T
WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
GO

/*
This does return the row.
*/
SELECT *
FROM dbo.T
WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
'2006-04-08 13:15:00'
GO

DROP TABLE dbo.T
GO

The difference between the two SELECT statements is that the first one uses
a non-clustered index seek, whereas the second one uses a scan of the same
index.

--
(remove a 9 to reply by email)
Apr 12 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Dimitri Furman wrote:
This looks like a bug - hopefully somebody can explain what is actually
happening. Using SQL Server 2000 SP4.

Here's a repro script with comments:

/* repro table */
CREATE TABLE dbo.T (
ID int NOT NULL,
Time datetime NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (ID, Time)
)
GO

/* the problem does not happen without this index */
CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
GO

/*
sample row - note that
CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
*/
INSERT INTO dbo.T (ID, Time)
VALUES (1, '2006-04-08 13:14:58.870')
GO

/*
This does not return any rows - why?
The comparison should evaluate to TRUE.
*/
SELECT *
FROM dbo.T
WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
GO
'2006-04-08 13:14:58.870' is not greater than '2006-04-08 13:15:00' - I
would not expect any results here.
/*
This does return the row.
*/
SELECT *
FROM dbo.T
WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
'2006-04-08 13:15:00'
GO
Maybe casting makes it larger / rounds it? Note that smalldatetime has
minute as precision.
DROP TABLE dbo.T
GO

The difference between the two SELECT statements is that the first one uses
a non-clustered index seek, whereas the second one uses a scan of the same
index.


Kind regards

robert

Apr 12 '06 #2

P: n/a
It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/micros...a?dmode=source

I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

HTH,
Gert-Jan
Dimitri Furman wrote:

This looks like a bug - hopefully somebody can explain what is actually
happening. Using SQL Server 2000 SP4.

Here's a repro script with comments:

/* repro table */
CREATE TABLE dbo.T (
ID int NOT NULL,
Time datetime NOT NULL,
CONSTRAINT PK_T PRIMARY KEY (ID, Time)
)
GO

/* the problem does not happen without this index */
CREATE NONCLUSTERED INDEX IX_T ON dbo.T (Time)
GO

/*
sample row - note that
CAST('2006-04-08 13:14:58.870' AS smalldatetime) = '2006-04-08 13:15:00'
*/
INSERT INTO dbo.T (ID, Time)
VALUES (1, '2006-04-08 13:14:58.870')
GO

/*
This does not return any rows - why?
The comparison should evaluate to TRUE.
*/
SELECT *
FROM dbo.T
WHERE CAST(Time as smalldatetime) >= '2006-04-08 13:15:00'
GO

/*
This does return the row.
*/
SELECT *
FROM dbo.T
WHERE CAST(DATEADD(millisecond, 0, Time) as smalldatetime) >=
'2006-04-08 13:15:00'
GO

DROP TABLE dbo.T
GO

The difference between the two SELECT statements is that the first one uses
a non-clustered index seek, whereas the second one uses a scan of the same
index.

--
(remove a 9 to reply by email)

Apr 12 '06 #3

P: n/a
Gert-Jan Strik (so***@toomuchspamalready.nl) writes:
It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/micros...amming/msg/fe4
f6ec635260e5a?dmode=source
I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
right now.) Do you if has been reported on
http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 12 '06 #4

P: n/a
On Apr 12 2006, 06:27 pm, Erland Sommarskog <es****@sommarskog.se> wrote
in news:Xn*********************@127.0.0.1:
Gert-Jan Strik (so***@toomuchspamalready.nl) writes:
It is a bug. For an example of an earlier discussion, see

http://groups.google.nl/group/micros...programming/ms
g/fe4 f6ec635260e5a?dmode=source

I don't know if there is a knowledge base article about it, or a
proposed fix. The thread does show workarounds.

The bug is also in SQL 2005 RTM. (I don't have the CTP of SP1 running
right now.) Do you if has been reported on
http://lab.msdn.microsoft.com/ProductFeedback/ for SQL 2005?


I haven't found anything, so I went ahead and opened a bug:
http://lab.msdn.microsoft.com/Produc...px?feedbackId=
FDBK48623

Feel free to add anything that may be important.

--
(remove a 9 to reply by email)
Apr 13 '06 #5

P: n/a
Dimitri Furman (df*****@cloud99.net) writes:
I haven't found anything, so I went ahead and opened a bug:
http://lab.msdn.microsoft.com/Produc...px?feedbackId=
FDBK48623

Feel free to add anything that may be important.


Good!

I tested it on the CTP of SP1, and the bug appears there as well.

I final blow to anyone who is in doubt over whether this is a bug is
that if I add WITH (INDEX = 1), that is index hint force the clustered
index to be used, the query returns a non-empty result set.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 13 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.