Delete duplicate records and create a unique index

During work, duplicate data exists due to the design problem of the original data table, and some duplicate records with the same column data exist in the table. The following record solves the actual solution: delete duplicate records (retain one record ), use these columns to create a unique index to avoid duplicate data insertion in the database.

/*
Procedure:
1. query the total number of records, number of Repeated Records, and number of records after deduplication to obtain the total number of records after deduplication.
2. Save all records after deduplication to the temporary table
3. Delete the original table
4. Insert temporary table data to the original data table
5. delete a temporary table
6. Create a unique index
*/

-Step 1
-Total number of records
Select count (*)
From [Training]. [yfss_learn_user_course]
Go
-Repeated Records
Select count (*) from (select * from [Training]. [yfss_learn_user_course] as
Where (select count (*) from [Training]. [yfss_learn_user_course]
Where a. course_id = course_id and A. user_id = user_id and A. courseware_id = courseware_id
And a. termyear = termyear)> 1) as tab1
Go
-After filtering these duplicate records
Select count (*) from (select distinct user_id, course_id, courseware_id, termyear from [Training]. [yfss_learn_user_course] as
Where (select count (*) from [Training]. [yfss_learn_user_course]
Where a. course_id = course_id and A. user_id = user_id and A. courseware_id = courseware_id
And a. termyear = termyear)> 1) as tab2
Go

-Step 2
-Add the result to the temporary table and number it.
Select Identity (INT, 1, 1) as autoid, * into # TMP from [Training]. [yfss_learn_user_course]
-Remove duplicate records and save autoid
Select min (autoid) as autoid into # tmp2 from # TMP group by user_id, course_id, courseware_id, termyear-filter the data with the same content in the four columns
Go
-Remove the number of results after repeated records
Select COUNT (*) from # Tmp where autoID in (select autoID from # tmp2)
GO

-Step 3: delete the original table
-Truncate table TRAINING. YFSS_LEARN_USER_COURSE
Drop table training. YFSS_LEARN_USER_COURSE
GO
-Step 4-insert the de-duplicated result from the temporary table to the original table
Select * into TRAINING. YFSS_LEARN_USER_COURSE
From (
Select [ID]
, [USER_ID], [COURSE_ID], [STUDYSTATUS], [TIME]
, [HOURS], [FIRSTTIME], [LASTTIME], [COMPLETETIME]
, [ENSURETIME], [STATUS], [DURATION], [DELFLAG], [COURSEWARE_ID]
, [TERMYEAR]
From # Tmp where autoID in (select autoID from # tmp2)
) As tab3
GO
-Step 5: delete a temporary table
Drop table # Tmp
Drop table # tmp2
GO
-Step 6: create a unique index and its related primary and Foreign keys and field default values.
Create unique nonclustered index IX_YFSS_LEARN_USER_COURSE
On [TRAINING]. [YFSS_LEARN_USER_COURSE] (USER_ID, COURSE_ID, COURSEWARE_ID, TERMYEAR)
GO

Alter table [TRAINING]. [YFSS_LEARN_USER_COURSE] WITH CHECK ADD
CONSTRAINT [PK_YFSS_LEARN_USER_COURSE] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Alter table [TRAINING]. [YFSS_LEARN_USER_COURSE] with check add constraint [FK_CU_REF_COURSE] foreign key ([COURSE_ID])
REFERENCES [TRAINING]. [YFSS_RES_COURSE] ([ID])
GO

Alter table [Training]. [yfss_learn_user_course] Check constraint [fk_cu_ref_course]
Go

Alter table [Training]. [yfss_learn_user_course] with check add constraint [fk_cu_ref_user] foreign key ([user_id])
References [Training]. [yfss_sys_user] ([ID])
Go

Alter table [Training]. [yfss_learn_user_course] Check constraint [fk_cu_ref_user]
Go

Alter table [Training]. [yfss_learn_user_course] add constraint [df__yfss_lear1_study1_4bac3f29] default ('01') for [studystatus]
Go

Alter table [Training]. [yfss_learn_user_course] add constraint [df__yfss_learn1_time1_4ca06133] default (0) for [time]
Go

Alter table [Training]. [yfss_learn_user_course] add constraint [df__yfss_lear1_hours1_4d94879b] default (0) for [hours]
Go

Alter table [TRAINING]. [YFSS_LEARN_USER_COURSE] add constraint [df__yfss_lear1_statu1_4e88abd4] DEFAULT ('00') FOR [STATUS]
GO

Alter table [TRAINING]. [YFSS_LEARN_USER_COURSE] add constraint [DF_YFSS_LEARN_USER_COURSE_DELFLAG] DEFAULT ('02') FOR [DELFLAG]
GO

Alter table [TRAINING]. [YFSS_LEARN_USER_COURSE] add constraint [df__yfss_lear1_termy1_664b26cc] DEFAULT ('20140901') FOR [TERMYEAR]
GO