將篩選索引放在分區列上時日期時間轉換失敗

在將新行插入表時，我遇到了一個神秘的 char 數據轉換錯誤。故障排除表明過濾索引是問題的根源。

索引的過濾器使用一個 DateTime 列，它也是表的一個分區列。某些客戶端預設使用俄語連接並收到錯誤消息。我設法在測試台上重現了它。

有沒有人知道為什麼會發生這種情況？

use YOUR_DATABASE ;

go

select @@VERSION ;

-- Microsoft SQL Server 2016 (SP2-CU1) ...

-----------------------------------------------------------------------------

-- CREATE DATETIME PARTITIONED TABLE

set language english ;

drop table if exists dbo.test_of_filtered_idx ;

drop partition scheme ps_test_of_filtered_idx ;

drop partition function pf_test_of_filtered_idx ;

go



set language english ;

go

create partition function pf_test_of_filtered_idx (datetime)

  as range right 

  for values ('1999-11-01 00:00:00.000' , '1999-12-01 00:00:00.000' , '2000-01-01 00:00:00.000' , '2000-02-01 00:00:00.000')



create partition scheme ps_test_of_filtered_idx

 as partition pf_test_of_filtered_idx

 ALL to ([primary])



create table dbo.test_of_filtered_idx

(

 id int not null identity (1,1) ,

 dt datetime not null ,

 payload char(127) not null default(replicate('A' , 127)) ,

 constraint PK__test_of_filtered_idx primary key clustered (id , dt)

) on [ps_test_of_filtered_idx] (dt) ;

go

-----------------------------------------------------------------------------

-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

set language english ;

go

create index IXF__test_of_filtered_idx__dt

 on dbo.test_of_filtered_idx

 (dt) include (id)

 where dt >= '1999-12-10 00:00:00.000' 

   and dt < '2000-01-20 00:00:00.000' ;

go

-----------------------------------------------------------------------------

-- CHECK 

set language english ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;

go

-- GET ERROR

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.

-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

go

-----------------------------------------------------------------------------

-- DROP INDEX AND GET ERRORFREE INSERTION

set language english ;

go

drop index IXF__test_of_filtered_idx__dt

 on dbo.test_of_filtered_idx

;

go

set language russian ;

go

insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;

go

-----------------------------------------------------------------------------

錯誤來自轉換字元串2000-01-20 00:00:00.000（在過濾的索引定義中）

更改過濾索引以使用明確的日期時間格式有效。

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' ;

在我看來，過濾後的索引範圍是根據客戶端的設置進行解釋的，因此根據插入時客戶端的設置，可以在過濾索引中包含或不包含相同的日期（如下所示）展示）

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

返回兩行 - 都具有相同的日期

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
| 1999-01-06 00:00:00.000 |
+-------------------------+

但

SET LANGUAGE ENGLISH

GO

SELECT *
FROM   T
WHERE  dt >= '1999-01-06'
      AND dt < '1999-02-06';

使用過濾後的索引並只返回其中之一

+-------------------------+
|           dt            |
+-------------------------+
| 1999-01-06 00:00:00.000 |
+-------------------------+

此時執行DBCC CHECKTABLE (T) WITH EXTENDED_LOGICAL_CHECKS任何一種語言都會失敗。

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.

引用自：https://dba.stackexchange.com/questions/230035