Index unique filtré, foreign key et performance d'update

Version imprimable

03/05/2015, 13h30
Sergejack

Index unique filtré, foreign key et performance d'update
Mon environnement :

J'ai une table T1 avec une série de champs, une clé primaire et une clé candidate filtrée.
Code:

1 2 3 4 5 6 7 8 T1 -ID [PK] -subID1 -subID2 -Col4 -Col5 UNIQUE (subID1, subID2) WHERE subID1 <> 0
Il existe (et persiste) une ligne dans T1 avecl'ID 0, cette ligne représente une "entrée non existante".

J'ai ensuite plusieurs tables T2, T3, T4 qui possèdent une référence vers la clé primaire (ID) de T1 avec une valeur par défaut de 0.
Cette référence est unique (sauf quand elle référence la ligne "0" de T1)

T2 (+/- comme T3, T4)
Code:

1 2 3 4 5 6 7 8 9 10 T2 -ID [PK] -T1ID DEFAULT 0 -Col3 -Col4 FOREIGNKEY (T1ID) REFERENCES T1 (ID) ON DELETE SET DEFAULT NONCLUSETERED UNIQUE INDEX T1ID WHERE T1ID <> 0
Donc chaque ligne de T1 peut être référencer par T2, T3, T4 au maximum une seule fois à l'exception de la "ligne 0".

À partir de là, j'ai de très mauvaise performances quand je supprime une ligne (ou ensemble de lignes) de T1.

Est-ce que ça vient de l'index filtré de T1 ?
Est-ce que ça vient des tables (T2, T3, T4) qui font références à T1?

Je ne comprends pas.
03/05/2015, 16h57
SQLpro

Ce serait plus claire avec le DDL de vos table.

Comment assurez vous cette règle : "Donc chaque ligne de T1 peut être référencer par T2, T3, T4 au maximum une seule fois à l'exception de la "ligne 0"."

Car sans un déclencheur je ne voit pas comment faire....

Or c'est peut être le code de ce dernier qui freine votre DELETE.

A +

en rajoutant "conjointement ou non" je lèverai sans doute une ambiguïté.

"Donc chaque ligne de T1 peut être référencée conjointement ou non par T2, T3, T4 au maximum une seule fois à l'exception de la "ligne 0"."

Voici les DDL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
--		Argument.tArgument
CREATE TABLE Argument.tArgument (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgument PRIMARY KEY (ID)
 
	, typeID INT NOT NULL CONSTRAINT DF_tArgument_typeID DEFAULT (0)
	, CONSTRAINT FK_tArgument_typeID FOREIGN KEY (typeID) REFERENCES Inventory.tType(ID) ON DELETE SET DEFAULT
 
	, [name] NVARCHAR(200) NOT NULL
 
	, dataTypeID INT NOT NULL
	-- deletion of a data type should never occur => NO ACTION => error
	, CONSTRAINT FK_tArgument_dataTypeIDD FOREIGN KEY (dataTypeID) REFERENCES Argument.tDataType(ID) ON DELETE NO ACTION
 
	, accountRelated BIT NOT NULL CONSTRAINT DF_tArgument_accountRelated DEFAULT (0)
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgument_01 ON Argument.tArgument(typeID, [name]) WHERE typeID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgument_typeID ON Argument.tArgument(ID) WHERE typeID = 0
GO
ALTER INDEX IDXM_tArgument_typeID ON Argument.tArgument DISABLE
GO
 
-- Special Row(s)
SET IDENTITY_INSERT Argument.tArgument ON
GO
 
INSERT Argument.tArgument (
	ID
	, typeID
	, [name]
	, dataTypeID
)
VALUES (
	0
	, 0
	, 'NULL'
	, 0
)
GO
 
SET IDENTITY_INSERT Argument.tArgument OFF
GO
 
-- Prevent deletion of special rows
CREATE TABLE Argument.tArgument_d (
	ID INT NOT NULL 
	, CONSTRAINT PK_tArgument_d PRIMARY KEY (ID)
	, CONSTRAINT FK_tArgument_d FOREIGN KEY (ID) REFERENCES Argument.tArgument(ID) ON DELETE NO ACTION
)
GO
 
INSERT Argument.tArgument_d VALUES (0)
GO
 
-- View
CREATE VIEW Argument.vArgument
AS
SELECT
	ID
	, typeID
	, [name]
	, dataTypeID
	, accountRelated
FROM Argument.tArgument
WHERE ID > 0
AND typeID > 0
GO
 
--		Argument.tArgumentVersion
CREATE TABLE Argument.tArgumentVersion (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgumentVersion PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tArgumentVersion_argID DEFAULT (0)
	, CONSTRAINT FK_tArgumentVersion_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, [version] INT NOT NULL CONSTRAINT DF_tArgumentVersion_version DEFAULT(1)
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentVersion_argID ON Argument.tArgumentVersion(argID) WHERE argID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentVersion_argID ON Argument.tArgumentVersion(argID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentVersion_argID ON Argument.tArgumentVersion DISABLE
GO
 
-- View
CREATE VIEW Argument.vArgumentVersion
AS
SELECT
	ID
	, argID
	, [version]
FROM Argument.tArgumentVersion
WHERE argID > 0
GO
 
--		Argument.tEntryArg
CREATE TABLE Argument.tEntryArg (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tEntryArg PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tEntryArg_argID DEFAULT(0)
	, CONSTRAINT FK_tEntryArg_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, entryID INT NOT NULL CONSTRAINT DF_tEntryArg_entryID DEFAULT(0)
	, CONSTRAINT FK_tEntryArg_entryID FOREIGN KEY (entryID) REFERENCES Inventory.tEntry (ID) ON DELETE SET DEFAULT
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tEntryArg_01 ON Argument.tEntryArg(argID, entryID) WHERE argID > 0 AND entryID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tEntryArg_argID ON Argument.tEntryArg(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tEntryArg_argID ON Argument.tEntryArg DISABLE
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tEntryArg_entryID ON Argument.tEntryArg(ID) WHERE entryID = 0
GO
ALTER INDEX IDXM_tEntryArg_entryID ON Argument.tEntryArg DISABLE
GO
 
-- Special Row(s)
SET IDENTITY_INSERT Argument.tEntryArg ON
GO
 
INSERT Argument.tEntryArg (
	ID
	, argID
	, entryID
)
VALUES (
	0
	, 0
	, 0
)
GO
 
SET IDENTITY_INSERT Argument.tEntryArg OFF
GO
 
-- Prevent deletion of special rows
CREATE TABLE Argument.tEntryArg_d (
	ID INT NOT NULL 
	, CONSTRAINT PK_tEntryArg_d PRIMARY KEY (ID)
	, CONSTRAINT FK_tEntryArg_d FOREIGN KEY (ID) REFERENCES Argument.tEntryArg(ID) ON DELETE NO ACTION
)
GO
 
INSERT Argument.tEntryArg_d VALUES (0)
GO
 
 
-- View
CREATE VIEW Argument.vEntryArg
AS
SELECT
	ID
	, argID
	, entryID
FROM Argument.tEntryArg
WHERE ID > 0
AND argID > 0
AND entryID > 0
GO
 
--		Account.tAccount
CREATE TABLE Account.tAccount (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tAccount PRIMARY KEY (ID)
 
	, [name] NVARCHAR(200) NOT NULL
	, passHash BINARY(32) NOT NULL
	, salt BINARY(32) NOT NULL
	, isDeleted BIT NOT NULL CONSTRAINT DF_tAccount_isDeleted DEFAULT(0)
)
GO
 
CREATE UNIQUE INDEX UQ_tAccount_name ON Account.tAccount ([name]) WHERE isDeleted = 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tAccount_isDeleted ON Account.tAccount(ID) WHERE isDeleted = 0
GO
ALTER INDEX IDXM_tAccount_isDeleted ON Account.tAccount DISABLE
GO
 
--		Argument.tArgumentView
CREATE TABLE Argument.tArgumentView (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgumentView PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tArgumentView_argID DEFAULT (0)
	, CONSTRAINT FK_tArgumentView_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, roleID INT NOT NULL CONSTRAINT DF_tArgumentView_roleID DEFAULT (0)
	, CONSTRAINT FK_tArgumentView_roleID FOREIGN KEY (roleID) REFERENCES Account.tRole(ID) ON DELETE SET DEFAULT
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentView_01 ON Argument.tArgumentView(argID, roleID) WHERE argID > 0 ANd roleID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentView_argID ON Argument.tArgumentView(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentView_argID ON Argument.tArgumentView DISABLE
GO
 
CREATE NONCLUSTERED INDEX IDXM_tArgumentView_roleID ON Argument.tArgumentView(ID) WHERE roleID = 0
GO
ALTER INDEX IDXM_tArgumentView_roleID ON Argument.tArgumentView DISABLE
GO
 
-- View
CREATE VIEW Argument.vArgumentView
AS
SELECT
	ID
	, argID
	, roleID
FROM Argument.tArgumentView
WHERE ID > 0
AND argID > 0
ANd roleID > 0
GO
 
--		Argument.tArgumentManage
CREATE TABLE Argument.tArgumentManage (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgumentManage PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tArgumentManage_argID DEFAULT (0)
	, CONSTRAINT FK_tArgumentManage_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, roleID INT NOT NULL CONSTRAINT DF_tArgumentManage_roleID DEFAULT (0)
	, CONSTRAINT FK_tArgumentManage_roleID FOREIGN KEY (roleID) REFERENCES Account.tRole(ID) ON DELETE SET DEFAULT
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentManage_01 ON Argument.tArgumentManage(argID, roleID) WHERE argID > 0 ANd roleID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentManage_argID ON Argument.tArgumentManage(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentManage_argID ON Argument.tArgumentManage DISABLE
GO
 
CREATE NONCLUSTERED INDEX IDXM_tArgumentManage_roleID ON Argument.tArgumentManage(ID) WHERE roleID = 0
GO
ALTER INDEX IDXM_tArgumentManage_roleID ON Argument.tArgumentManage DISABLE
GO
 
-- View
CREATE VIEW Argument.vArgumentManage
AS
SELECT
	ID
	, argID
	, roleID
FROM Argument.tArgumentManage
WHERE ID > 0
AND argID > 0
ANd roleID > 0
GO
 
--		Argument.tArgumentRefType
CREATE TABLE Argument.tArgumentRefType (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgumentRefType PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tArgumentRefType_argID DEFAULT (0)
	, CONSTRAINT FK_tArgumentRefType_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, typeID INT NOT NULL CONSTRAINT DF_tArgumentRefType_typeID DEFAULT  (0)
	, CONSTRAINT FK_tArgumentRefType_typeID FOREIGN KEY (typeID) REFERENCES Inventory.tType(ID) ON DELETE SET DEFAULT
 
--	, [version] INT NOT NULL CONSTRAINT DF_tArgumentRefType_version DEFAULT  (0)
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentRefType_argID ON Argument.tArgumentRefType(argID) WHERE argID > 0 AND typeID > 0
GO
 
CREATE NONCLUSTERED INDEX IDX_tArgumentRefType_typeID ON Argument.tArgumentRefType(typeID) WHERE argID > 0 AND typeID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentRefType_argID ON Argument.tArgumentRefType(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentRefType_argID ON Argument.tArgumentRefType DISABLE
GO
 
CREATE NONCLUSTERED INDEX IDXM_tArgumentRefType_typeID ON Argument.tArgumentRefType(ID) WHERE typeID = 0
GO
ALTER INDEX IDXM_tArgumentRefType_typeID ON Argument.tArgumentRefType DISABLE
GO
 
-- View
CREATE VIEW Argument.vArgumentRefType
AS
SELECT
	ID
	, argID
	, typeID
FROM Argument.tArgumentRefType
WHERE ID > 0
AND argID > 0
AND typeID > 0
GO
 
-- 2014-09-02
--		Argument.tArgumentOption
CREATE TABLE Argument.tArgumentOption (
	ID INT NOT NULL IDENTITY
	, CONSTRAINT PK_tArgumentOption PRIMARY KEY (ID)
 
	, argID INT NOT NULL CONSTRAINT DF_tArgumentOption_argID DEFAULT (0)
	, CONSTRAINT FK_tArgumentOption_argID FOREIGN KEY (argID) REFERENCES Argument.tArgument(ID) ON DELETE SET DEFAULT
 
	, [option] NVARCHAR(200) NOT NULL
)
GO
 
CREATE UNIQUE NONCLUSTERED INDEX UQ_tArgumentOption_01 ON Argument.tArgumentOption(argID, [option]) WHERE argID > 0
GO
 
-- IDX for maintenance
CREATE NONCLUSTERED INDEX IDXM_tArgumentOption_argID ON Argument.tArgumentOption(ID) WHERE argID = 0
GO
ALTER INDEX IDXM_tArgumentOption_argID ON Argument.tArgumentOption DISABLE
GO
 
 
-- View
CREATE VIEW Argument.vArgumentOption
AS
SELECT
	ID
	, argID
	, [option]
FROM Argument.tArgumentOption
WHERE ID > 0
AND argID > 0
GO
 
CREATE NONCLUSTERED INDEX IDX_tArgumentView_roleID ON Argument.tArgumentView(roleID) WHERE argID > 0 ANd roleID > 0
GO
 
CREATE NONCLUSTERED INDEX IDX_tArgumentManage_roleID ON Argument.tArgumentManage(roleID) WHERE argID > 0 ANd roleID > 0
GO

Et voici un trigger qui est désactivé* lors du constat des mauvaises performances :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE TRIGGER Argument.trigArgument_AU
ON Argument.tArgument
AFTER UPDATE
AS
BEGIN
	IF UPDATE(typeID)
	BEGIN
		-- Argument.vArgumentRefType
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vArgumentRefType AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
 
		-- Argument.vEntryArg
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vEntryArg AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
 
		-- Argument.vArgumentView
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vArgumentView AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
 
		-- Argument.vArgumentManage
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vArgumentManage AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
 
		-- Argument.vArgumentRefType
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vArgumentRefType AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
 
		-- Argument.vArgumentOption
		UPDATE T
		SET
			T.argID = 0
		FROM Argument.vArgumentOption AS T
		INNER JOIN inserted AS I ON (
			T.argID = I.ID
		)
		WHERE I.typeID = 0
	END
END
GO

* j'ai déjà exécuté à l'avance le code tu trigger pour toutes les lignes concernée par l'opération qui prend du temps (afin de pouvoir désactiver le trigger et confirmer qu'il n'était pas l'élément causant les mauvaises performances).

Avant de conclure, voici oici une opération qui prend un temps infini (7m lignes) :

Code:

1
2
3
4
5
 
update T
set typeID = 0
from Argument.vArgument AS T
where T.ID > 100000

Et voici l'opération* en question qui prend un temps fou (5~8s) :

Code:

1
2
3
4
5
6
7
8
9
10
 
with T as (
	select top(1000)
		 T.*
	from Argument.vArgument AS T
	where T.ID > 100000
	order by id asc
)
update T
set typeID = 0

* ce n'est donc pas un vrai DELETE comme je l'avais à tort indiqué dans le premier post, mais un pseudo delete (les lignes deviennent "cachées" jusqu'au delete effectif exécuté lors des maintenances).

06/05/2015, 14h39
Sergejack

Personne n'est plus familier que ça avec une architecture en " ON CASCADE SET DEFAULT" avec des lignes représentants des suppressions ?

Je commence à pense qu'utiliser des indexes filtrées sur des colonnes qui font références à d'autres tables est une mauvaise idée..
À priori à cause du filtre, SQL Server ne se sert pas de ces indexes pour résoudre les relations dans le cas de ON DELETE/UPDATE CASCADE/SET/...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
 
-- Structure
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_Src]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_Src]
GO
 
CREATE TABLE tst20150513_Src (
	ID INT NOT NULL
	, CONSTRAINT PK_tst20150513_Src PRIMARY KEY (ID)
)
GO
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_1]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_1]
GO
 
CREATE TABLE tst20150513_1 (
	ID INT NOT NULL
	, CONSTRAINT PK_tst20150513_1 PRIMARY KEY (ID)
)
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_2_1]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_2_1]
GO
 
CREATE TABLE tst20150513_2_1 (
	ID INT NOT NULL
	, I INT NOT NULL IDENTITY PRIMARY KEY	
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tst20150513_2_1 ON tst20150513_2_1 (ID)
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_2_2]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_2_2]
GO
 
CREATE TABLE tst20150513_2_2 (
	ID INT NOT NULL
	, I INT NOT NULL IDENTITY PRIMARY KEY		
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tst20150513_2_2 ON tst20150513_2_2 (ID) WHERE ID <> 0
GO
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_2_3]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_2_3]
GO
 
CREATE TABLE tst20150513_2_3 (
	ID INT NOT NULL
	, I INT NOT NULL IDENTITY PRIMARY KEY		
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tst20150513_2_3 ON tst20150513_2_3 (ID) WHERE ID > 0
GO
 
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_2_4]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_2_4]
GO
 
CREATE TABLE tst20150513_2_4 (
	ID INT NOT NULL
	, CONSTRAINT CHK_tst20150513_2_4 CHECK (ID <> 0)
	, I INT NOT NULL IDENTITY PRIMARY KEY		
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tst20150513_2_4 ON tst20150513_2_4 (ID) WHERE ID <> 0
GO
 
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tst20150513_2_5]') AND type in (N'U'))
DROP TABLE [dbo].[tst20150513_2_5]
GO
 
CREATE TABLE tst20150513_2_5 (
	ID INT NOT NULL
	, CONSTRAINT CHK_tst20150513_2_5 CHECK (ID > 0)
	, I INT NOT NULL IDENTITY PRIMARY KEY		
)
GO
 
CREATE NONCLUSTERED INDEX IDX_tst20150513_2_5 ON tst20150513_2_5 (ID) WHERE ID > 0
GO
 
-- Populate
; WITH CTE AS (
	SELECT
		ROW_NUMBER() OVER (ORDER BY [type],	[number],[name]) AS n
	FROM master.dbo.spt_values AS T
)
INSERT tst20150513_Src ( ID )
SELECT
	n
FROM CTE WHERE n <= 1000
 
INSERT tst20150513_2_1 (ID)
SELECT ID FROM tst20150513_Src
 
INSERT tst20150513_2_2 (ID)
SELECT ID FROM tst20150513_Src
 
INSERT tst20150513_2_3 (ID)
SELECT ID FROM tst20150513_Src
 
INSERT tst20150513_2_4 (ID)
SELECT ID FROM tst20150513_Src
 
INSERT tst20150513_2_5 (ID)
SELECT ID FROM tst20150513_Src
GO
 
-- Tests
PRINT '
tst20150513_2_1
---------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_1 ADD
	CONSTRAINT FK_tst20150513_2_1 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_1]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_1]'))
ALTER TABLE [dbo].[tst20150513_2_1] DROP CONSTRAINT [FK_tst20150513_2_1]
GO
 
PRINT '
tst20150513_2_2
---------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_2 ADD
	CONSTRAINT FK_tst20150513_2_2 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_2]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_2]'))
ALTER TABLE [dbo].[tst20150513_2_2] DROP CONSTRAINT [FK_tst20150513_2_2]
GO
 
PRINT '
tst20150513_2_2 bis
-------------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
INSERT tst20150513_2_2 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_2 ADD
	CONSTRAINT FK_tst20150513_2_2 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID <> 0
AND ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_2]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_2]'))
ALTER TABLE [dbo].[tst20150513_2_2] DROP CONSTRAINT [FK_tst20150513_2_2]
GO
 
PRINT '
tst20150513_2_3
---------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_3 ADD
	CONSTRAINT FK_tst20150513_2_3 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_3]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_3]'))
ALTER TABLE [dbo].[tst20150513_2_3] DROP CONSTRAINT [FK_tst20150513_2_3]
GO
 
PRINT '
tst20150513_2_3 bis
------------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
INSERT tst20150513_2_3 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_3 ADD
	CONSTRAINT FK_tst20150513_2_3 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID > 0
AND ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_3]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_3]'))
ALTER TABLE [dbo].[tst20150513_2_3] DROP CONSTRAINT [FK_tst20150513_2_3]
GO
 
PRINT '
tst20150513_2_4
---------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_4 ADD
	CONSTRAINT FK_tst20150513_2_4 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_4]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_4]'))
ALTER TABLE [dbo].[tst20150513_2_4] DROP CONSTRAINT [FK_tst20150513_2_4]
GO
 
PRINT '
tst20150513_2_5
---------------
'
 
TRUNCATE TABLE tst20150513_1
GO
 
INSERT tst20150513_1 (ID)
SELECT ID FROM tst20150513_Src
GO
 
ALTER TABLE tst20150513_2_5 ADD
	CONSTRAINT FK_tst20150513_2_5 FOREIGN KEY (ID) REFERENCES tst20150513_1 (ID) ON DELETE CASCADE
GO
 
SET STATISTICS IO ON
GO
 
DELETE FROM tst20150513_1
WHERE ID < 50
GO
 
SET STATISTICS IO OFF
GO
 
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tst20150513_2_5]') AND parent_object_id = OBJECT_ID(N'[dbo].[tst20150513_2_5]'))
ALTER TABLE [dbo].[tst20150513_2_5] DROP CONSTRAINT [FK_tst20150513_2_5]
GO

C'est la première fois que je m'investit dans ce pattern, découvert grâce à sqlpro (postposer les suppressions + ne jamais utiliser "ON DELETE SET NULL") et je me demande si je fais tout bien...

13/05/2015, 10h29
Sergejack

Ok, quand plutôt que d'avoir des références vers 0 dans le cas de suppression, j'ai des références vers NULL alors les indexes peuvent être filtrés "IS NOT NULL" et là, SQL Server fait un bon taff.

Moralité :

Si vous avez une référence qui doit être unique vers une autre table, n'utilisez rien d'autre que NULL comme exception à l'unicité.
Vous aurez des orphelins dans votre DB, et certaines personnes vous diront que c'est mal !
Ne vous laisser alors pas trop impressionner par ces éventuels grands théoriciens.