MEiT3xr8

1use YOUR_DATABASE ;
2
3go
4
5select @@VERSION ;
6
7-- Microsoft SQL Server 2016 (SP2-CU1) ...
8
9-----------------------------------------------------------------------------
10
11-- CREATE DATETIME PARTITIONED TABLE
12
13set language english ;
14
15drop table if exists dbo.test_of_filtered_idx ;
16
17drop partition scheme ps_test_of_filtered_idx ;
18
19drop partition function pf_test_of_filtered_idx ;
20
21go
22
23
24
25set language english ;
26
27go
28
29create partition function pf_test_of_filtered_idx (datetime)
30
31   as range right 
32
33   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')
34
35
36
37create partition scheme ps_test_of_filtered_idx
38
39  as partition pf_test_of_filtered_idx
40
41  ALL to ([primary])
42
43
44
45create table dbo.test_of_filtered_idx
46
47(
48
49  id int not null identity (1,1) ,
50
51  dt datetime not null ,
52
53  payload char(127) not null default(replicate('A' , 127)) ,
54
55  constraint PK__test_of_filtered_idx primary key clustered (id , dt)
56
57) on [ps_test_of_filtered_idx] (dt) ;
58
59go
60
61-----------------------------------------------------------------------------
62
63-- INSERT TEST ROW AND CREATE DATETIME FILTERED INDEX
64
65set language russian ;
66
67go
68
69insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;
70
71go
72
73set language english ;
74
75go
76
77create index IXF__test_of_filtered_idx__dt
78
79  on dbo.test_of_filtered_idx
80
81  (dt) include (id)
82
83  where dt >= '1999-12-10 00:00:00.000' 
84
85    and dt < '2000-01-20 00:00:00.000' ;
86
87go
88
89-----------------------------------------------------------------------------
90
91-- CHECK 
92
93set language english ;
94
95go
96
97insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('12/15/1999 00:00:00.000' /* native american datetime format MDY */) ;
98
99go
100
101-- GET ERROR
102
103set language russian ;
104
105go
106
107insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;
108
109-- Преобразование типа данных varchar в тип данных datetime привело к выходу значения за пределы диапазона.
110
111-- The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
112
113go
114
115-----------------------------------------------------------------------------
116
117-- DROP INDEX AND GET ERRORFREE INSERTION
118
119set language english ;
120
121go
122
123drop index IXF__test_of_filtered_idx__dt
124
125  on dbo.test_of_filtered_idx
126
127;
128
129go
130
131set language russian ;
132
133go
134
135insert into dbo.test_of_filtered_idx (dt) output inserted.* values ('15.12.1999 00:00:00.000' /* russian datetime format DMY */) ;
136
137go
138
139-----------------------------------------------------------------------------