--------------------------------------------------------------------------------
--建立uniqindex , 並指定ignore_dup_key --------------------------------------------------------------------------------
--1. 建立target table,儲存無重複的資料 if exists (select name from sys.tables t where t.name = 'category') drop table category go --2. 刪除既有的index create table category(cat_desc varchar(50), cat_code varchar(10)) if exists(select name from sys.indexes i where i.name = 'idx_cat') drop index idx_cat on cayrgory go
--3. 建立unique index,並指定ignore_dup_key create unique index idx_cat on category(cat_desc) with ignore_dup_key go
--4. 建立1,000測試資料,其中5,000筆重複 declare @tb1 table(c1 varchar(50), c2 varchar(10)) declare @i int declare @loop_start_date datetime, @loop_end_date datetime declare @uniq_start_date datetime, @uniq_end_date datetime
set @i = 1 set @loop_start_date = GETDATE() --設定迴圈起始時間
while @i <= 10000 begin if @i % 2 = 0 insert @tb1 values('test' + cast((@i-1) as varchar(50)), cast((@i-1) as varchar(10))) else insert @tb1 values('test' +cast(@i as varchar(50)), cast(@i as varchar(10)))
set @i = @i + 1 end set @loop_end_date = GETDATE() --設定迴圈結束時間
set @uniq_start_date = GETDATE() --設定insert 至target table的起始時間 insert category(cat_desc, cat_code) select * from @tb1 set @uniq_end_date = GETDATE() --設定insert 至target table的結束時間
select 'inserting loop takes...' select DATEDIFF(SECOND, @loop_start_date, @loop_end_date) select 'inserting into uniq index with ignore_dup_key takes...' select DATEDIFF(SECOND, @uniq_start_date, @uniq_end_date)
select * from @tb1 order by c1 select * from category order by cat_desc go
|