|
16.11.2018, 00:59
#39733838
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: Donetsk->Emerald City
Сообщения: 345
Рейтинг:
0
/ 0
|
|
|
|
Для совсем ленивого решения можно пересоздать UNIQUE с указанием:
Быстродействие, возможно, будет еще то и структурно использование данного флага мне не нравится, но для разовой операции может и сгодиться.
Впрочем, на Azure SQL Database (ну нету обычного под рукой, извините) быстродействие не отличается:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
SET STATISTICS IO ON
SET STATISTICS TIME ON
CREATE TABLE u(ai int)
CREATE TABLE nu(ai int)
GO
CREATE UNIQUE CLUSTERED INDEX idx1 ON u(ai) WITH (IGNORE_DUP_KEY = ON)
CREATE CLUSTERED INDEX idx1 ON nu(ai)
GO
INSERT INTO u select object_id from sys.objects
INSERT INTO nu select object_id from sys.objects
GO
INSERT INTO u select object_id from sys.objects
INSERT INTO nu select object_id from sys.objects
GO
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.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 7 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 8 ms.
Table 'u'. Scan count 0, logical reads 215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(107 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 9 ms.
Table 'nu'. Scan count 0, logical reads 215, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(107 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'u'. Scan count 0, logical reads 214, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(0 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 10 ms.
Duplicate key was ignored.
Table 'nu'. Scan count 0, logical reads 214, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysschobjs'. Scan count 1, logical reads 67, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(107 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 15 ms, elapsed time = 8 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
И план одинаковый. |
|
|
