Datetime conversion fails when filtered index is placed on partition column
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.
The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.
Does anyone have any ideas on why this happens?
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
-----------------------------------------------------------------------------
sql-server t-sql partitioning filtered-index
New contributor
add a comment |
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.
The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.
Does anyone have any ideas on why this happens?
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
-----------------------------------------------------------------------------
sql-server t-sql partitioning filtered-index
New contributor
1
Try changing your filtered index specification to use an ISO 8601 date time format, likeWHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.
– Dan Guzman
15 hours ago
add a comment |
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.
The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.
Does anyone have any ideas on why this happens?
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
-----------------------------------------------------------------------------
sql-server t-sql partitioning filtered-index
New contributor
I've got a mysterious error of char data conversion at the moment of insertion a new row into a table. Troubleshooting reveals that a filtered index is the source of the problem.
The index's filter uses a DateTime column, which also is a partitioning column of the table. Some clients connect with Russian by default and get the error.
I've managed to reproduce it on a test table.
Does anyone have any ideas on why this happens?
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
-----------------------------------------------------------------------------
sql-server t-sql partitioning filtered-index
sql-server t-sql partitioning filtered-index
New contributor
New contributor
New contributor
asked 16 hours ago
V. KiselV. Kisel
283
283
New contributor
New contributor
1
Try changing your filtered index specification to use an ISO 8601 date time format, likeWHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.
– Dan Guzman
15 hours ago
add a comment |
1
Try changing your filtered index specification to use an ISO 8601 date time format, likeWHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.
– Dan Guzman
15 hours ago
1
1
Try changing your filtered index specification to use an ISO 8601 date time format, like
WHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.– Dan Guzman
15 hours ago
Try changing your filtered index specification to use an ISO 8601 date time format, like
WHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.– Dan Guzman
15 hours ago
add a comment |
2 Answers
2
active
oldest
votes
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.
Martin Smith I upvoted your answer but can you please explain why addingshowplan_xml on; go
before the problem insert leads to no error at all?
– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
Indeed. Thank you again
– sepupic
13 hours ago
add a comment |
In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.
That's why you get error when the command below is implicitly run:
set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');
To avoid this sort of errors explicitly use the datetime
type in your filter expression or use an ISO 8601 date time format for your constants:
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
and dt < CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);
ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?
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
Because after conversion from varchar to datetime:
'1999-01-06' = 1st JUN
'1999-02-06' = 2nd JUN
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
V. Kisel is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e) {
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom)) {
StackExchange.using('gps', function() { StackExchange.gps.track('embedded_signup_form.view', { location: 'question_page' }); });
$window.unbind('scroll', onScroll);
}
};
$window.on('scroll', onScroll);
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230035%2fdatetime-conversion-fails-when-filtered-index-is-placed-on-partition-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
Martin Smith I upvoted your answer but can you please explain why addingshowplan_xml on; go
before the problem insert leads to no error at all?
– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
Indeed. Thank you again
– sepupic
13 hours ago
add a comment |
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.
Martin Smith I upvoted your answer but can you please explain why addingshowplan_xml on; go
before the problem insert leads to no error at all?
– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
Indeed. Thank you again
– sepupic
13 hours ago
add a comment |
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.
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.
edited 8 hours ago
answered 15 hours ago
Martin SmithMartin Smith
63.3k10170254
63.3k10170254
Martin Smith I upvoted your answer but can you please explain why addingshowplan_xml on; go
before the problem insert leads to no error at all?
– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
Indeed. Thank you again
– sepupic
13 hours ago
add a comment |
Martin Smith I upvoted your answer but can you please explain why addingshowplan_xml on; go
before the problem insert leads to no error at all?
– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
Indeed. Thank you again
– sepupic
13 hours ago
Martin Smith I upvoted your answer but can you please explain why adding
showplan_xml on; go
before the problem insert leads to no error at all?– sepupic
14 hours ago
Martin Smith I upvoted your answer but can you please explain why adding
showplan_xml on; go
before the problem insert leads to no error at all?– sepupic
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
@sepupic - because then the statement isn't executed. It just generates the estimated plan but the problem operator is never executed. It happens at runtime inside a compute scalar.
– Martin Smith
14 hours ago
1
1
Indeed. Thank you again
– sepupic
13 hours ago
Indeed. Thank you again
– sepupic
13 hours ago
add a comment |
In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.
That's why you get error when the command below is implicitly run:
set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');
To avoid this sort of errors explicitly use the datetime
type in your filter expression or use an ISO 8601 date time format for your constants:
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
and dt < CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);
ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?
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
Because after conversion from varchar to datetime:
'1999-01-06' = 1st JUN
'1999-02-06' = 2nd JUN
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
add a comment |
In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.
That's why you get error when the command below is implicitly run:
set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');
To avoid this sort of errors explicitly use the datetime
type in your filter expression or use an ISO 8601 date time format for your constants:
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
and dt < CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);
ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?
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
Because after conversion from varchar to datetime:
'1999-01-06' = 1st JUN
'1999-02-06' = 2nd JUN
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
add a comment |
In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.
That's why you get error when the command below is implicitly run:
set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');
To avoid this sort of errors explicitly use the datetime
type in your filter expression or use an ISO 8601 date time format for your constants:
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
and dt < CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);
ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?
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
Because after conversion from varchar to datetime:
'1999-01-06' = 1st JUN
'1999-02-06' = 2nd JUN
In your filter condition you have varchar values so before working with datetime values they must be converted to datetime (Data type precedence). And this conversion depends on language settings.
That's why you get error when the command below is implicitly run:
set language russian ;
SELECT CONVERT(DATETIME, '2000-01-20 00:00:00.000');
To avoid this sort of errors explicitly use the datetime
type in your filter expression or use an ISO 8601 date time format for your constants:
create index IXF__test_of_filtered_idx__dt
on dbo.test_of_filtered_idx
(dt) include (id)
where dt >= CONVERT(DATETIME, '1999-12-10 00:00:00.000', 120)
and dt < CONVERT(DATETIME, '2000-01-20 00:00:00.000', 120);
ADD:
Why isn't presented one value in filtered index from @MartinSmith's answer?
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
Because after conversion from varchar to datetime:
'1999-01-06' = 1st JUN
'1999-02-06' = 2nd JUN
edited 12 hours ago
answered 14 hours ago
Denis RubashkinDenis Rubashkin
5487
5487
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
add a comment |
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.– Martin Smith
14 hours ago
set language russian ;select CONVERT(datetime, '15.12.1999 00:00:00.000')
works fine and does not produce an error. Demo code is provided in the question that more or less just needs to be executed to see if the filtered index is responsible or not. The OP will get an error inserting with russian language with any date however generated with that filtered index definition present.– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Example dbfiddle.uk/…
– Martin Smith
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Thanks, @MartinSmith, I've already seen and try to sort out.
– Denis Rubashkin
14 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
Martin's example operates 1 jan 1999 only. You can use DATEPART to check it. The difference is that a select with a WHERE clause uses the filtered index while a select without any filter doesn't.
– V. Kisel
10 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
@V.Kisel, yes, select with a WHERE clause uses the filtered index, but the index includes only one row whereas the table has two. I can't check it now but I guess, If you rebuild the index, the query which uses the filter will return two rows or zero depending on current language setting.
– Denis Rubashkin
5 hours ago
add a comment |
V. Kisel is a new contributor. Be nice, and check out our Code of Conduct.
V. Kisel is a new contributor. Be nice, and check out our Code of Conduct.
V. Kisel is a new contributor. Be nice, and check out our Code of Conduct.
V. Kisel is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e) {
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom)) {
StackExchange.using('gps', function() { StackExchange.gps.track('embedded_signup_form.view', { location: 'question_page' }); });
$window.unbind('scroll', onScroll);
}
};
$window.on('scroll', onScroll);
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230035%2fdatetime-conversion-fails-when-filtered-index-is-placed-on-partition-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e) {
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom)) {
StackExchange.using('gps', function() { StackExchange.gps.track('embedded_signup_form.view', { location: 'question_page' }); });
$window.unbind('scroll', onScroll);
}
};
$window.on('scroll', onScroll);
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e) {
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom)) {
StackExchange.using('gps', function() { StackExchange.gps.track('embedded_signup_form.view', { location: 'question_page' }); });
$window.unbind('scroll', onScroll);
}
};
$window.on('scroll', onScroll);
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
var $window = $(window),
onScroll = function(e) {
var $elem = $('.new-login-left'),
docViewTop = $window.scrollTop(),
docViewBottom = docViewTop + $window.height(),
elemTop = $elem.offset().top,
elemBottom = elemTop + $elem.height();
if ((docViewTop elemBottom)) {
StackExchange.using('gps', function() { StackExchange.gps.track('embedded_signup_form.view', { location: 'question_page' }); });
$window.unbind('scroll', onScroll);
}
};
$window.on('scroll', onScroll);
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Try changing your filtered index specification to use an ISO 8601 date time format, like
WHERE dt >= '1999-12-10T00:00:00.000' AND dt < '2000-01-20T00:00:00.000'
.– Dan Guzman
15 hours ago