To handle Dead Lock on Table

Create PROCEDURE [dbo].[CreateSp_toHandleDeadLockonTable]
AS
BEGIN
   
BeginHistory:
BEGIN TRY
                       --Add your code here
END TRY

BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRAN

 IF ERROR_NUMBER() = 1205 --DEADLOCK
  BEGIN
   WAITFOR DELAY '00:00.051'
   GOTO BeginHistory
  END
 ELSE
  BEGIN

  END
 END CATCH
END

Remove Duplicate rows From SQL table

WITH common_table_expression

Create table
CREATE TABLE [dbo].[Employee](

[EID] [int] IDENTITY(1,1) NOT NULL,

[ENAME] [varchar](50) NULL,

[DEPT] [varchar](20) NULL,

[value] [int] NULL,

[type] [nchar](10) NULL
)

Add records table

INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO
  
INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO
  
INSERT INTO [dbo].[Employee]

([ENAME]

,[DEPT]

,[value]

,[type])

VALUES

('Ramu'

,'3'

,80

,'R')




GO

Check the results

select * from Employee

Remove Duplicate records

With [test2] AS

 

 

 

(

 

 

select RN=ROW_NUMBER() OVER( PARTITION BY ENAME ORDER by EID DESC),

ENAME,

 

 

EID

 

from Employee

 

 

 

)

 

Delete FROM [test2] where RN>1;

　
 select * from Employee