· 8 years ago · Aug 30, 2018, 02:16 PM
1--- preparação das tabelas de exemplo ---
2
3use AdventureWorks
4go
5
6if exists (select * from sys.objects where name like 'NewDetailsTable') drop table NewDetailsTable
7if exists (select * from sys.objects where name like 'NewDetailsTable_sortedInsert') drop table NewDetailsTable_sortedInsert
8if exists (select * from sys.objects where name like 'NewDetailsTable_noIndex') drop table NewDetailsTable_noIndex
9if exists (select * from sys.objects where name like 'stagingTable') drop table stagingTable
10go
11
12
13--- criar tabelas de teste: com Ãndice aglomerado na chave primária ---
14
15CREATE TABLE NewDetailsTable(
16 [SalesOrderID] [int] NOT NULL,
17 [SalesOrderDetailID] [int] NOT NULL,
18 [CarrierTrackingNumber] [nvarchar](25) NULL,
19 [OrderQty] [smallint] NOT NULL,
20 [ProductID] [int] NOT NULL,
21 [SpecialOfferID] [int] NOT NULL,
22 [UnitPrice] [money] NOT NULL,
23 [UnitPriceDiscount] [money] NOT NULL,
24 [LineTotal] [int] not null,
25 [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
26 [ModifiedDate] [datetime] NOT NULL,
27 CONSTRAINT [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID] PRIMARY KEY CLUSTERED
28(
29 [SalesOrderID] ASC,
30 [SalesOrderDetailID] ASC
31)
32)
33
34GO
35
36
37--- criar tabelas de teste: com Ãndice e apenas para inserção ordenada de acordo com esse Ãndice ---
38
39CREATE TABLE NewDetailsTable_sortedInsert(
40 [SalesOrderID] [int] NOT NULL,
41 [SalesOrderDetailID] [int] NOT NULL,
42 [CarrierTrackingNumber] [nvarchar](25) NULL,
43 [OrderQty] [smallint] NOT NULL,
44 [ProductID] [int] NOT NULL,
45 [SpecialOfferID] [int] NOT NULL,
46 [UnitPrice] [money] NOT NULL,
47 [UnitPriceDiscount] [money] NOT NULL,
48 [LineTotal] int,
49 [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
50 [ModifiedDate] [datetime] NOT NULL,
51 PRIMARY KEY CLUSTERED
52(
53 [SalesOrderID] ASC,
54 [SalesOrderDetailID] ASC
55)
56)
57
58GO
59
60--- criar tabelas de teste: sem Ãndice ---
61
62CREATE TABLE NewDetailsTable_noIndex(
63 [SalesOrderID] [int] NOT NULL,
64 [SalesOrderDetailID] [int] NOT NULL,
65 [CarrierTrackingNumber] [nvarchar](25) NULL,
66 [OrderQty] [smallint] NOT NULL,
67 [ProductID] [int] NOT NULL,
68 [SpecialOfferID] [int] NOT NULL,
69 [UnitPrice] [money] NOT NULL,
70 [UnitPriceDiscount] [money] NOT NULL,
71 [LineTotal] int,
72 [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
73 [ModifiedDate] [datetime] NOT NULL,
74)
75
76GO
77
78--- criar staging table ---
79
80select *
81into stagingTable
82from Sales.SalesOrderDetail
83go
84
85----------
86set IDENTITY_INSERT stagingTable ON
87
88INSERT INTO stagingTable
89 ([SalesOrderID]
90 ,[SalesOrderDetailID]
91 ,[CarrierTrackingNumber]
92 ,[OrderQty]
93 ,[ProductID]
94 ,[SpecialOfferID]
95 ,rowguid
96 ,[UnitPrice]
97 ,[UnitPriceDiscount]
98 ,LineTotal
99 ,[ModifiedDate])
100
101SELECT [SalesOrderID]
102 ,[SalesOrderDetailID] + 1000000
103 ,[CarrierTrackingNumber]
104 ,[OrderQty]
105 ,[ProductID]
106 ,[SpecialOfferID]
107 ,NEWID()
108 ,[UnitPrice]
109 ,[UnitPriceDiscount]
110 ,0.0
111 ,[ModifiedDate]
112 FROM stagingTable
113GO
114
115
116INSERT INTO stagingTable
117 ([SalesOrderID]
118 ,[SalesOrderDetailID]
119 ,[CarrierTrackingNumber]
120 ,[OrderQty]
121 ,[ProductID]
122 ,[SpecialOfferID]
123 ,rowguid
124 ,[UnitPrice]
125 ,[UnitPriceDiscount]
126 ,LineTotal
127 ,[ModifiedDate])
128
129SELECT [SalesOrderID]
130 ,[SalesOrderDetailID] + 2000000
131 ,[CarrierTrackingNumber]
132 ,[OrderQty]
133 ,[ProductID]
134 ,[SpecialOfferID]
135 ,NEWID()
136 ,[UnitPrice]
137 ,[UnitPriceDiscount]
138 ,0.0
139 ,[ModifiedDate]
140 FROM stagingTable
141GO
142
143
144INSERT INTO stagingTable
145 ([SalesOrderID]
146 ,[SalesOrderDetailID]
147 ,[CarrierTrackingNumber]
148 ,[OrderQty]
149 ,[ProductID]
150 ,[SpecialOfferID]
151 ,rowguid
152 ,[UnitPrice]
153 ,[UnitPriceDiscount]
154 ,LineTotal
155 ,[ModifiedDate])
156
157SELECT [SalesOrderID]
158 ,[SalesOrderDetailID] + 4000000
159 ,[CarrierTrackingNumber]
160 ,[OrderQty]
161 ,[ProductID]
162 ,[SpecialOfferID]
163 ,NEWID()
164 ,[UnitPrice]
165 ,[UnitPriceDiscount]
166 ,0.0
167 ,[ModifiedDate]
168 FROM stagingTable
169GO
170
171INSERT INTO stagingTable
172 ([SalesOrderID]
173 ,[SalesOrderDetailID]
174 ,[CarrierTrackingNumber]
175 ,[OrderQty]
176 ,[ProductID]
177 ,[SpecialOfferID]
178 ,rowguid
179 ,[UnitPrice]
180 ,[UnitPriceDiscount]
181 ,LineTotal
182 ,[ModifiedDate])
183
184SELECT [SalesOrderID]
185 ,[SalesOrderDetailID] + 8000000
186 ,[CarrierTrackingNumber]
187 ,[OrderQty]
188 ,[ProductID]
189 ,[SpecialOfferID]
190 ,NEWID()
191 ,[UnitPrice]
192 ,[UnitPriceDiscount]
193 ,0.0
194 ,[ModifiedDate]
195 FROM stagingTable
196GO
197
198
199INSERT INTO stagingTable
200 ([SalesOrderID]
201 ,[SalesOrderDetailID]
202 ,[CarrierTrackingNumber]
203 ,[OrderQty]
204 ,[ProductID]
205 ,[SpecialOfferID]
206 ,rowguid
207 ,[UnitPrice]
208 ,[UnitPriceDiscount]
209 ,LineTotal
210 ,[ModifiedDate])
211
212SELECT [SalesOrderID]
213 ,[SalesOrderDetailID] + 16000000
214 ,[CarrierTrackingNumber]
215 ,[OrderQty]
216 ,[ProductID]
217 ,[SpecialOfferID]
218 ,NEWID()
219 ,[UnitPrice]
220 ,[UnitPriceDiscount]
221 ,0.0
222 ,[ModifiedDate]
223 FROM stagingTable
224GO
225
226select * from stagingTable
227
228--- criar tabela de cabeçalho extra
229
230select *
231into newHeaderTable
232from Sales.salesOrderHeader
233
234
235select *
236into newHeaderTable_withIndex
237from Sales.salesOrderHeader
238
239create nonclustered index idxNonClust on newHeaderTable_withIndex (salesOrderId)
240
241select *
242into newHeaderTable_withClustIndex
243from Sales.salesOrderHeader
244
245create clustered index idxClust on newHeaderTable_withClustIndex (salesOrderId)