SQL Server에서 레코드를 삭제한 후 ID 시드 재설정

SQL Server에서 레코드를 삭제한 후 ID 시드 재설정

SQL Server 데이터베이스 테이블에 레코드를 삽입했습니다.테이블에는 프라이머리 키가 정의되어 있으며 자동 증가 ID 시드는 "Yes"로 설정되어 있습니다.이는 주로 SQL Azure에서는 각 테이블에 프라이머리 키와 ID가 정의되어 있어야 하기 때문입니다.

단, 테이블에서 일부 레코드를 삭제해야 하므로 해당 테이블의 ID 시드가 방해되고 인덱스 열(증분 1로 자동 생성됨)이 방해됩니다.

레코드를 삭제한 후 아이덴티티 컬럼을 리셋하여 컬럼에 오름차순의 숫자가 되도록 하려면 어떻게 해야 합니까?

ID 열은 데이터베이스에서 외부 키로 사용되지 않습니다.

management 명령어는 아이덴티티 카운터를 리셋하기 위해 사용합니다.명령어 구문은 다음과 같습니다.

DBCC CHECKIDENT (table_name [, { NORESEED | { RESEED [, new_reseed_value ]}}])
[ WITH NO_INFOMSGS ]

예:

DBCC CHECKIDENT ('[TestTable]', RESEED, 0);
GO

이전 버전의 Azure SQL 데이터베이스에서는 지원되지 않았지만 현재는 지원됩니다.

---

Solomon Rutzky 덕분에 명령어 문서는 수정되었습니다.

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

서 0은 '0'입니다.identity( )

대부분의 답변에서는 REESED가 0으로 되어 있습니다만, 많은 경우 다음 ID로 다시 접속할 필요가 있습니다.

declare @max int
select @max=max([Id]) from [TestTable]
if @max IS NULL   --check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

그러면 테이블이 확인되고 다음 ID로 재설정됩니다.

모든 데이터가 테이블에서 삭제되는 경우,DELETE 없음)WHEREclause하는 FK는 하지 않습니다(clause로 됩니다).a)는 「FK」, 「B」의 「FK」의 「FK」(「B」의 「FK」의 「FK」의 「FK」(「FK」의 「」)의 「FK」의 「FK」(「」의 「」의 「」의 「FK」의 「FK」의 「」의 「」의 「FK」의 「FK」를 사용하고 있습니다.TRUNCATE TABLE 효율적이기 될 것입니다.DELETE 를 리셋 합니다.IDENTITY동시에 씨를 뿌리다TRUNCATE TABLE의 MSDN 페이지에서 가져온 자세한 내용은 다음과 같습니다.

DELETE 스테이트먼트에 비해 TRUNCATE TABLE의 장점은 다음과 같습니다.

• 더 적은 트랜잭션 로그 공간이 사용됩니다.

  DELETE 문은 행을 한 번에 하나씩 삭제하고 삭제된 각 행의 엔트리를 트랜잭션로그에 기록합니다.TRUNCATE TABLE은 테이블 데이터 저장에 사용되는 데이터 페이지의 할당을 해제하여 데이터를 제거하고 페이지 할당 해제만 트랜잭션 로그에 기록합니다.

• 일반적으로 사용되는 잠금은 적습니다.

  행 잠금을 사용하여 DELETE 문을 실행하면 테이블 내의 각 행이 삭제되도록 잠깁니다.TRUNCATE TABLE은 항상 테이블(스키마(SCH-M) 잠금 포함)과 페이지를 잠그지만 각 행은 잠그지 않습니다.

• 예외 없이 테이블에는 0페이지가 남습니다.

  DELETE 문이 실행된 후에도 테이블에는 빈 페이지가 포함될 수 있습니다.예를 들어 힙의 빈 페이지는 적어도 배타적(LCK_M_X) 테이블 잠금이 없으면 할당을 해제할 수 없습니다.삭제 조작에 테이블잠금이 사용되지 않는 경우 테이블(히프)에는 빈 페이지가 다수 포함됩니다.인덱스의 경우 삭제 작업은 빈 페이지를 남길 수 있지만, 이러한 페이지는 백그라운드 정리 프로세스에 의해 빠르게 할당 해제됩니다.

테이블에 ID 열이 포함되어 있는 경우 해당 열의 카운터는 해당 열에 정의된 시드 값으로 리셋됩니다.시드가 정의되지 않은 경우 기본값1 이 사용됩니다.ID 카운터를 유지하려면 대신 DELETE를 사용합니다.

그래서 다음과 같은 것이 있습니다.

DELETE FROM [MyTable];
DBCC CHECKIDENT ('[MyTable]', RESEED, 0);

정당성:

TRUNCATE TABLE [MyTable];

해 주세요.TRUNCATE TABLE)를 참조해

는 는 i i는노노 i i i i i.@anil shahs이치노 새로운 하면, 이 은 「이행」, 「이행」이 .identity = 2대신 구문을 다음과 같이 변경했습니다.

DELETE FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED, 0)
GO

그런 다음 첫 번째 행은 ID = 1이 됩니다.

은 '이러다'를 하고 있습니다만,RESEED로로 합니다.0을 '결함'의 TRUNCATED 는 「」, 「Microsoft」를 하고 있습니다.ID

DBCC CHECKIDENT ('[TestTable]', RESEED)

다음 되고 다음 .IDMS SQL 2005로 변경.

https://msdn.microsoft.com/en-us/library/ms176057.aspx

used금금사 i를 사용했습니다.DBCC CHECKIDENT

주의사항:

• 테이블 이름 대괄호는 사용할 수 없습니다.
• DBCC CHECKIDENT('TableName',RESEED,n)n+1로 하다
  • ★★DBCC CHECKIDENT('tablename',RESEED,27)2828년부터
• 새 시작 ID를 설정하지 않아 문제가 발생할 경우 다음을 통해 문제를 해결할 수 있습니다.

    DECLARE @NewId as INT  
    SET @NewId =  (SELECT MAX('TableName')-1  AS ID FROM TableName)
    DBCC CHECKIDENT('TableName',RESEED,@MaxId)

2개의 명령어를 발행하면 효과를 얻을 수 있습니다.

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

첫 번째 ID는 0으로 리셋되고 다음 ID는 사용 가능한 다음 값인 제이콥으로 설정됩니다.

@parames(@parames)

DBCC CHECKIDENT ('[TestTable]', RESEED,0)
DBCC CHECKIDENT ('[TestTable]', RESEED)

저는 먼저 테이블에서 모든 엔트리를 지우고 삭제 후 트리거 포인트에 위의 엔트리를 추가해야 했습니다.이제 삭제할 때마다 엔트리가 거기에서 가져옵니다.

Truncate테이블은 레코드를 클리어하고 카운터를 리셋하고 디스크 영역을 회수하기 때문에 권장됩니다.

Delete ★★★★★★★★★★★★★★★★★」CheckIdent는 외부 키로 잘라낼 수 없는 경우에만 사용해야 합니다.

이 작업을 수행하려면 다음 스크립트를 사용합니다.즉 "오류는 "오류"와 "오류"가 발생하는 , "오류"가 입니다.IDENT_CURRENT는 현재 1로 설정되어 있습니다.즉, 테이블에서 첫 번째 행이1개뿐이에요

DECLARE @maxID int = (SELECT MAX(ID) FROM dbo.Tbl)
;

IF @maxID IS NULL
    IF (SELECT IDENT_CURRENT('dbo.Tbl')) > 1
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 0)
    ELSE
        DBCC CHECKIDENT ('dbo.Tbl', RESEED, 1)
    ;
ELSE
    DBCC CHECKIDENT ('dbo.Tbl', RESEED, @maxID)
;

ID 열을 새 ID로 재설정...

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

개발 중에 많은 테이블에서 이 작업을 수행하려고 노력했는데, 이것이 매력적으로 작용합니다.

DBCC CHECKIDENT('www.newsType', RESEED, 1);
DBCC CHECKIDENT('www.newsType', RESEED);

따라서 먼저 강제로 1로 설정한 다음 테이블에 있는 행의 가장 높은 인덱스로 설정합니다.idex의 나머지 부분을 빠르고 쉽게.

이건 흔한 질문이고 대답은 항상 똑같아요. 하지 마세요.아이덴티티 값은 임의의 것으로 취급해야 하며, 따라서 "올바른" 순서는 없습니다.

ID 열을 재설정하려면 이 스크립트를 실행합니다.두 가지 변경을 해야 합니다.테이블 X를 바꿉니다.갱신할 필요가 있는 테이블이 있는 YZ.또한 임시 테이블에서 ID 열의 이름을 삭제해야 합니다.이것은 35,000개의 행과 3개의 열이 있는 테이블에서 즉시 수행되었습니다.당연히 테이블을 백업하고 먼저 테스트 환경에서 이 작업을 시도합니다.

---

select * 
into #temp
From tableXYZ

set identity_insert tableXYZ ON

truncate table tableXYZ

alter table #temp drop column (nameOfIdentityColumn)

set identity_insert tableXYZ OFF

insert into tableXYZ
select * from #temp

DBCC CHECKIDENT (<TableName>, reseed, 0)

그러면 현재 ID 값이 0으로 설정됩니다.

다음 값을 삽입하면 ID 값이 1로 증가합니다.

다음 저장 프로시저를 사용합니다.

IF (object_id('[dbo].[pResetIdentityField]') IS NULL)
  BEGIN
    EXEC('CREATE PROCEDURE [dbo].[pResetIdentityField] AS SELECT 1 FROM DUMMY');
  END
GO

SET  ANSI_NULLS ON
GO
SET  QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[pResetIdentityField]
  @pSchemaName NVARCHAR(1000)
, @pTableName NVARCHAR(1000) AS
DECLARE @max   INT;
DECLARE @fullTableName   NVARCHAR(2000) = @pSchemaName + '.' + @pTableName;

DECLARE @identityColumn   NVARCHAR(1000);

SELECT @identityColumn = c.[name]
FROM sys.tables t
     INNER JOIN sys.schemas s ON t.[schema_id] = s.[schema_id]
     INNER JOIN sys.columns c ON c.[object_id] = t.[object_id]
WHERE     c.is_identity = 1
      AND t.name = @pTableName
      AND s.[name] = @pSchemaName

IF @identityColumn IS NULL
  BEGIN
    RAISERROR(
      'One of the following is true: 1. the table you specified doesn''t have an identity field, 2. you specified an invalid schema, 3. you specified an invalid table'
    , 16
    , 1);
    RETURN;
  END;

DECLARE @sqlString   NVARCHAR(MAX) = N'SELECT @maxOut = max(' + @identityColumn + ') FROM ' + @fullTableName;

EXECUTE sp_executesql @stmt = @sqlString, @params = N'@maxOut int OUTPUT', @maxOut = @max OUTPUT

IF @max IS NULL
  SET @max = 0

print(@max)

DBCC CHECKIDENT (@fullTableName, RESEED, @max)
go

--exec pResetIdentityField 'dbo', 'Table'

내 답을 다시 살펴보는 중이야.sql server 2008 r2에서 이상한 동작을 발견했습니다.

drop table test01

create table test01 (Id int identity(1,1), descr nvarchar(10))

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

delete from test01

execute pResetIdentityField 'dbo', 'test01'

insert into test01 (descr) values('Item 1')

select * from test01

번째 에서는 " " " 가 생성됩니다.0, Item 1.

것은 '아예'를 만듭니다.1, Item 1테이블 작성 직후 리셋을 실행하면 다음 값은 0이 됩니다.솔직히 마이크로소프트가 이 문제를 해결할 수 없다는 것은 놀라운 일이 아닙니다.테이블을 다시 작성한 후 실행할 때도 있고 이미 작성된 테이블이 있을 때도 있는 참조 테이블을 채우는 스크립트파일이 있기 때문에 검출되었습니다.

시드 리셋을 위해 CHECKIDENT를 사용할 수 있습니다.

DBCC CHECKIDENT
 (
    table_name
        [ , { NORESEED | { RESEED [ , new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]

예

 DBCC CHECKIDENT ('TAble', reseed,0)

-- 쿼리 예시

다음 코드로 기본 데이터를 삽입할 수 있습니다.

테이블에 데이터를 삽입한 후 먼저 하나의 테이블을 만듭니다.

코드 이해를 위해 "상세보기"를 위한 "데이터 표시" 및 "데이터 삭제" 단계

결과 코드 : ID가 있는 dmv sys.identity_columns를 사용하여 rest seed ID를 가진 테이블을 만듭니다.

--Create Table 
DROP TABLE IF EXISTS  ExampleTable
create table ExampleTable (Id Bigint identity(1,1), Name nvarchar(10))

--Insert to ExampleTable and Delete and Show identity 
insert into ExampleTable (Name) 
select 'Test1' as NAme union all select 'Test2' as NAme

select * from ExampleTable

| Id       | Name |
| -------- | -----|
| 1        |Test1 |
| 2        |Test2 |

delete from ExampleTable

insert into ExampleTable (Name) select 'Test3' as NAme

select * from ExampleTable

| Id       | Name |
| -------- | -----|
| 3        |Test3 |

delete from ExampleTable

먼저 테이블에 데이터가 없는 경우 시드 테이블을 사용하여 데이터를 확인합니다.

테이블에 데이터가 있는 경우 최대 ID 사용

CHECKIDENT로 시드 변경 후

--Find seedTable
declare @reseed int=0

if(not exists( select top 1 * from ExampleTable))
begin

    
     SELECT 
        @reseed=cast( seed_value as int)
    FROM sys.tables tables 
        JOIN sys.identity_columns identity_columns 
    ON tables.object_id=identity_columns.object_id
    where 
        tables.name='ExampleTable' 
    and OBJECT_SCHEMA_NAME(tables.object_id, db_id())='dbo'
 
      set @reseed=@reseed -1

 end
 else
 begin
   --if Table Has Data and use Max id For  seed
    set @reseed=(select top 1 id from ExampleTable order by id desc)

 end


  DBCC CHECKIDENT ('ExampleTable', reseed,@reseed)


insert into ExampleTable
(Name)
select 'Test4' as NAme


select * from ExampleTable


| Id       | Name |
| -------- | -----|
| 1        |Test4 |
 

GO

완전한 DELETE 행과 ID 의 카운트를 리셋 하려면 , 이것을 사용합니다(SQL Server 2008 R2).

USE mydb

-- ##################################################################################################################
-- DANGEROUS!!!! USE WITH CARE
-- ##################################################################################################################

DECLARE
  db_cursor CURSOR FOR
    SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
     WHERE TABLE_TYPE = 'BASE TABLE'
       AND TABLE_CATALOG = 'mydb'

DECLARE @tblname VARCHAR(50)
SET @tblname = ''

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @tblname

WHILE @@FETCH_STATUS = 0
BEGIN
  IF CHARINDEX('mycommonwordforalltablesIwanttodothisto', @tblname) > 0
    BEGIN
      EXEC('DELETE FROM ' + @tblname)
      DBCC CHECKIDENT (@tblname, RESEED, 0)
    END

  FETCH NEXT FROM db_cursor INTO @tblname
END

CLOSE db_cursor
DEALLOCATE db_cursor
GO

테이블을 전체적으로 청소하지 않는 한 0으로 다시 시딩하는 것은 그다지 실용적이지 않습니다.

그렇지 않으면 앤서니 레이먼드가 준 답은 완벽하다.먼저 최대 ID 열을 가져온 다음 최대값으로 시드하십시오.

대부분의 응답에서는 테이블이 비어 있어 Identity 값을 리셋할 필요가 있다고 가정하고 있는 것 같습니다.그러나 질문을 어떻게 읽었냐면 @xorpower에는 1, 2, 3, 5, 6, 7, 12, 13, 14 등의 레코드가 있는 테이블이 있습니다.연속된 목록으로 되돌리는 방법을 원합니다(1, 2, 3, 4, 5, 6, 7, 8, 9 등).

cfBut since I have to delete some records from the table,서 ''는someIMHO의

AFAIK는 MSQL에는 이러한 기능이 없습니다.최악의 경우 기존 레코드 테이블을 새 테이블에 덤프하고 거기에서 이동할 수 있습니다.내가 묻고 싶은 것은, 「왜 그렇게 하고 싶은가」입니다.ID 컬럼이 최선의 접근법일까요?

을 갖는 내의 입니다.TRUNCATE기존 테이블을 다시 배치하고 모든 것을 다시 복사합니다.물론 효과가 있겠지만 데이터가 많으면 작업이 상당히 어렵습니다.개인적으로는 동일한 테이블을 생성하여 새로운 테이블에 데이터를 복사하고(배치할 수도 있음) 마지막으로 하고 싶습니다.SWITCH데이터를 원래 테이블에 입력하고 새로 생성된 테이블을 다시 삭제합니다. 하면 될 것 CHECKIDENT 한 테이블로 를 한 .이렇게 하면 한 테이블에서 다른 테이블로 데이터를 한 번만 이동할 수 있습니다.공간을 절약하기 위해DELETE배치가 복사된 후 원본 테이블에서 관련 레코드를 가져옵니다.

PS:질문인 의 선두에 : 、 래 、 래 、 래 、 래 、 mentioned 、 mentioned 、 mentioned 、 mentioned 、 mentioned 、 mentioned 、 ps ps ps ps ps ps ps 。누구도 언급하지 않았기 때문입니다.SWITCH추가할 가치가 있을 것 같았어

안녕하세요 이 저장 프로시저에서 테이블의 DELETE ALL ROWS를 실행하여 테이블에 ID 열이 있는지 확인합니다.이 저장 프로시저는 다시 설치됩니다.

*** 주의: 모든 행이 삭제됩니다.****

Create PROCEDURE ClearTableData 
    @TableName varchar(100)
AS
    EXEC ('ALTER TABLE '+@TableName+'  NOCHECK CONSTRAINT ALL ') 
    BEGIN TRY  
        EXEC ('Truncate Table'+@TableName )
        PRINT @TableName +' rows have trancated '
    END TRY  
    BEGIN CATCH  
        EXEC ('Delete From '+@TableName )
        PRINT @TableName +' rows have deleted '
    END CATCH  
    
    IF (OBJECTPROPERTY(OBJECT_ID(@TableName), 'TableHasIdentity'))  = 1 
            DBCC CHECKIDENT (@TableName, RESEED, 0) 

    EXEC ('ALTER TABLE '+@TableName+'  CHECK CONSTRAINT ALL ') 

GO

모든 테이블에 이 SP를 사용합니다.

재설치된 '접수 가능'

레코드를 삭제한 후(트리거 p.e.

ALTER PROCEDURE [dbo].[RESEED](@Tabla nvarchar(100))

as

-- ********************* CREAR ESTA FUNCIÓN PRIMERO *******************************
/*
create function  dbo.FN_EsIdentidad(@Tabla nvarchar(100), @Campo nvarchar(100))
    returns int
    as
    begin
        return columnproperty ( object_id ( @Tabla  ), @Campo , 'IsIdentity')
    end 
GO
************************************************************************************
*/


declare @CampoIdentidad nvarchar(100)

    SELECT @CampoIdentidad  = Column_Name
        --,dbo.FN_EsIdentidad(Table_Name, Column_name) as EsIdentidad,
        --table_name, column_name 
        FROM information_schema.columns
        where dbo.FN_EsIdentidad(Table_Name, Column_name) = 1 
        and Table_Name=@Tabla


declare @Sql nvarchar(max)
declare @OutPutNum int
set @Sql = 'Select MAX(' + @CampoIdentidad + ') From ' + @Tabla
--select @CampoIdentidad


set @sql = 'Select @ValorOut=max(' + @CampoIdentidad + ') From ' + @Tabla 
declare @ParamDefinition nvarchar(max)
SET @ParamDefinition = '@ValorOut int OUTPUT'

EXECUTE sp_executesql  
    @SQL 
    ,@ParamDefinition  
    ,@ValorOut = @OutPutNum OUTPUT;  

--select @OutPutNum
set @OutPutNum  = coalesce(@OutPutNum ,0)
DBCC CHECKIDENT (@Tabla, RESEED, @OutputNum)

TRUNCATE는 로그 공간도 사용하지 않으므로 가능한 경우 모든 레코드를 삭제하는 대신 항상 TRUNCATE를 사용하는 것이 좋습니다.

한 경우 및 되지 않고 인 경우 .DBCC CHECKIDENT('tablenem',RESEED,0)그 후 첫 번째 레코드는 msdn 문서에 기재된 ID =0 을 취득합니다.

이 경우 인덱스를 재구축하기만 하면 됩니다.일반적인 시나리오이므로 일련의 ID가 손실될 염려가 없습니다.

첫 번째 : ID 지정 : "No" >> 데이터베이스 실행 프로젝트 저장

그 후 : ID 지정 : "YES" >> 데이터베이스 실행 프로젝트 저장

데이터베이스 ID, PK 시작 1 >>

언급URL : https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server