Datetime conversion fails when filtered index is placed on partition column
The error comes from converting the string 2000-01-20 00:00:00.000
(in the filtered index definition)
Changing the filtered index to use unambigous datetime format works.
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= '19991210 00:00:00.000'
and dt < '20000120 00:00:00.000' ;
It definitely seems buggy to me that the filtered index range is interpreted according to the settings of the client and so the same date can be either included or not included in the filtered index dependant on the settings of the client at insert time (as in below demo)
CREATE TABLE T
(
dt DATETIME
)
CREATE INDEX IXF__test_of_filtered_idx__dt
ON T (dt)
WHERE dt >= '1999-01-06' AND dt < '1999-02-06';
SET LANGUAGE ENGLISH
GO
INSERT INTO T
VALUES ( DATEADD(DAY,5,'1999-01-01')); --6th Jan
GO
SET LANGUAGE RUSSIAN
INSERT INTO T
VALUES ( DATEADD(DAY,5,'1999-01-01')); --Still 6th Jan
GO
SET LANGUAGE ENGLISH
GO
SELECT *
FROM T
Returns two rows - both with the same date
+-------------------------+
| dt |
+-------------------------+
| 1999-01-06 00:00:00.000 |
| 1999-01-06 00:00:00.000 |
+-------------------------+
But
SET LANGUAGE ENGLISH
GO
SELECT *
FROM T
WHERE dt >= '1999-01-06'
AND dt < '1999-02-06';
Uses the filtered index and just returns one of them
+-------------------------+
| dt |
+-------------------------+
| 1999-01-06 00:00:00.000 |
+-------------------------+
Running DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS
at this point then fails for either language.
Msg 8951, Level 16, State 1, Line 4
Table error: table 'T' (ID 1045578763). Data row does not have a matching index row in the index 'IXF__test_of_filtered_idx__dt' (ID 2). Possible missing or invalid keys for the index row matching:
Msg 8955, Level 16, State 1, Line 4
Data row (4:24:1) identified by (HEAP RID = (4:24:1)) with index values 'dt = '1999-01-06 00:00:00.000' and HEAP RID = (4:24:1)'.
DBCC results for 'T'.
There are 2 rows in 1 pages for object "T".
CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'T' (object ID 1045578763).
repair_rebuild is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.T).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.