|
|
Posted by Gert-Jan Strik on 04/12/06 21:15
It is a bug. For an example of an earlier discussion, see
http://groups.google.nl/group/microsoft.public.sqlserver.programming/msg/fe4f6ec635260e5a?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)
Navigation:
[Reply to this message]
|