Reset IDENTITY column whitout deleting all rows

Question

I have the following table

CREATE TABLE [dbo].[MyTable](
    [Name] NVARCHAR(200) NOT NULL,
    [Surname] NVARCHAR(200) NOT NULL,
    [Permanent] [bit] NULL,
    [Idx] [bigint] IDENTITY(1,1) NOT NULL
 CONSTRAINT [MyTable] PRIMARY KEY CLUSTERED 
(
    [Idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

that contains 1000 rows where [Permanent]=1 and 500.000 rows with [Permanent] NULL

In addition, I have a stored procedure (called MySP) that do the following steps:

1. DELETE FROM [MyTable] WHERE [Permanent] IS NULL
2. (lots of calculations)
3. INSERT results of point 2 into [MyTable]

MySP runs every day so the number of [Idx] increase very quickly as every day 500.000 rows are deleted and inserted. My target is, during the execution of the SP, to reset the value of column [Idx] to mantain the max number of [Idx] lower possible.

1st try

I have tried to update with the following query at the end of MySP but the system gives me a (correct) error.

UPDATE A
SET A.[Idx]=B.[Idx_new]
FROM [dbo].[MyTable] A
INNER JOIN (
    SELECT [Idx],ROW_NUMBER() OVER (ORDER BY [Idx]) AS [Idx_new]
    FROM [dbo].[MyTable]
) B
ON A.[Idx]=B.[Idx]

2nd try After reading the following two questions/answers

• Reset identity seed after deleting records in SQL Server
• How to update Identity Column in SQL Server?

I have add the following at the end of MySP

DBCC CHECKIDENT ('MyTable', RESEED, 1); 

but also this doesn't not work as in [MyTable], differently from the situation of both quesions, remain some rows, so the is a concrete risk that [Idx] is not unique and that's not good as [Idx] is my primary key.

How could I reset the identity column value and also the rows that still remains into [MyTable]?

Answer 1

Using @Killer Queen suggest, I have solved using this snippet of code, which find the MAX([Idx]) of MyTable after the DELETE and the reeseed the identity before the new INSERT.

It works because the rows with [Permanent]=1 are the first rows inserted in the table so their [Idx] values start from 1 and are very low.

DELETE 
FROM [MyTable]
WHERE [Permanent]!=1 OR [Permanent] IS NULL

DECLARE @MaxIdx As bigint
SET @MaxIdx = (SELECT ISNULL(MAX([Idx]),0) FROM [MyTable])

DBCC CHECKIDENT ('MyTable', RESEED, @MaxIdx);