복붙노트

[SQL] SQL 서버에서 레코드를 삭제 한 후 다시 설정 신원 씨

SQL

SQL 서버에서 레코드를 삭제 한 후 다시 설정 신원 씨

나는 SQL Server 데이터베이스 테이블에 레코드를 삽입했습니다. 테이블은 정의 된 기본 키를 가지고와 자동 증가 신원 씨는 "예"로 설정되어 있습니다. 이것은 SQL 애저에서, 각 테이블에 정의 된 기본 키와 정체성을 가지고있다 주로하기 때문에 이루어집니다.

나는 테이블에서 일부 레코드를 삭제해야하지만 이후, 해당 테이블에 대한 신원 씨 방해하고 (자동 생성 (1)의 증가와 함께입니다) 인덱스 컬럼은 방해를 받게됩니다.

열이 수치의 승순의 순서를 가질 수 있도록 내가 기록을 삭제 한 후 어떻게 식별 컬럼을 재설정 할 수 있습니다?

신원 열은 데이터베이스의 외래 키 어디서나 사용되지 않습니다.

해결법

  1. ==============================

    1.DBCC CHECKIDENT 관리 명령은 신원 카운터를 재설정하는 데 사용됩니다. 명령 구문은 다음과 같습니다

    DBCC CHECKIDENT 관리 명령은 신원 카운터를 재설정하는 데 사용됩니다. 명령 구문은 다음과 같습니다

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

    예:

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

    그것은 푸른 SQL 데이터베이스의 이전 버전에서 지원되지 않습니다,하지만 지금은 지원됩니다.

    그 new_reseed_value 인수는 문서에 따라 SQL Server 버전에 걸쳐 변화되어 있습니다 :

    관찰 된 행동이 적어도 SQL 서버 2012 여전히 new_reseed_value + 현재 증분 값 논리를 사용하는 것을 나타냅니다 때문에, 나는 잘못된 정보 (실제로는 그냥 일반 잘못을) 찾을 수 있습니다. 마이크로 소프트는 심지어 자신의 예 C는 같은 페이지에서 볼 그것과 모순 :

    USE AdventureWorks2012;  
GO  
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);  
GO

    그러나이 모든 잎 새로운 SQL Server 버전에 다른 동작에 대한 옵션을 선택합니다. 나는 마이크로 소프트가 자신의 문서에 물건을 지울 때까지, 확신 할 수있는 유일한 방법을 생각, 사용하기 전에 실제 테스트를하는 것입니다.

  2. ==============================

    2.

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

    정체성 시작 값은 어디 0

  3. ==============================

    3.모든 데이터가 삭제 (즉 아니오 WHERE 절)를 통해 테이블로부터 제거되는 경우, 긴 a) 권한이 허용 한 바와 같이 b) (표를 참조하여 더 FKS 없다는 것을 유의해야한다 나타날 그것이 더 효율적 DELETE 수행하고 동시에 IDENTITY 시드를 재설정 같은 경우 여기를)로, TRUNCATE 테이블을 사용하는 것이 바람직 할 것이다. 다음 세부 사항은 TRUNCATE 테이블에 대한 MSDN 페이지에서 가져옵니다 :

    모든 데이터가 삭제 (즉 아니오 WHERE 절)를 통해 테이블로부터 제거되는 경우, 긴 a) 권한이 허용 한 바와 같이 b) (표를 참조하여 더 FKS 없다는 것을 유의해야한다 나타날 그것이 더 효율적 DELETE 수행하고 동시에 IDENTITY 시드를 재설정 같은 경우 여기를)로, TRUNCATE 테이블을 사용하는 것이 바람직 할 것이다. 다음 세부 사항은 TRUNCATE 테이블에 대한 MSDN 페이지에서 가져옵니다 :

    다음 그래서 :

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

    단지가된다 :

    TRUNCATE TABLE [MyTable];

    제한 사항에 대한 자세한 내용은, 등 (위 링크)를 TRUNCATE 표 설명서를 참조하시기 바랍니다

  4. ==============================

    4.대부분의 답변이 0 RESEED, 그러나 많은 시간을 제안하고 있지만 우리는 바로 옆 가능한 ID로 다시 시드 할 필요가

    대부분의 답변이 0 RESEED, 그러나 많은 시간을 제안하고 있지만 우리는 바로 옆 가능한 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로 재설정됩니다.

  5. ==============================

    5.shahs 대답하고 정체성을 다시 @anil 나는 시도했다. 새 행이는 정체성을 가지고 삽입 때 = 2. 그래서 그 대신 내가 구문을하는 변경 :

    shahs 대답하고 정체성을 다시 @anil 나는 시도했다. 새 행이는 정체성을 가지고 삽입 때 = 2. 그래서 그 대신 내가 구문을하는 변경 :

    DELETE FROM [TestTable]

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

    그런 다음 첫 번째 행은 정체성 = 1을 얻을 것이다.

  6. ==============================

    6.대부분의 답변이 0 RESEED을 제안하고 있으며, 일부 잘린 테이블의 결함으로이 볼 동안 있지만, 마이크로 소프트는 제외 ID가 그 해결책을 가지고

    대부분의 답변이 0 RESEED을 제안하고 있으며, 일부 잘린 테이블의 결함으로이 볼 동안 있지만, 마이크로 소프트는 제외 ID가 그 해결책을 가지고

    DBCC CHECKIDENT ('[TestTable]', RESEED)

    이 테이블을 확인하고 다음 ID로 재설정됩니다. 이 전류에 MS SQL 2005부터 사용할 수있다.

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

  7. ==============================

    7.이 명령을 실행하면 트릭을 할 수 있습니다

    이 명령을 실행하면 트릭을 할 수 있습니다

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

    먼저 제로로 신원을 재설정하고 다음 사용 가능한 다음 값으로 설정됩니다  - 야곱

  8. ==============================

    8.이 카운터를 재설정, 기록을 지우고 디스크 공간을 회수하기 때문에 자르기 표 바람직하다.

    이 카운터를 재설정, 기록을 지우고 디스크 공간을 회수하기 때문에 자르기 표 바람직하다.

    외래 키는 절단하지 못할 경우에만 삭제 CHECKIDENT를 사용해야합니다.

  9. ==============================

    9.@jacob

    @jacob

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

    나를 위해 일했다, 난 그냥 테이블에서 처음으로 모든 항목을 삭제했다, 다음 삭제 후 트리거 포인트에서 위를했다. 지금 때마다 내가 삭제 항목이에서 가져옵니다.

  10. ==============================

    10.새로운 ID로 재설정 ID 열 ...

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

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

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

    11.이것은 일반적인 질문과 대답은 항상 동일합니다 :하지 않습니다. 식별 값과 같은 어떤 "올바른"순서가없는 임의적으로 취급되어야한다.

    이것은 일반적인 질문과 대답은 항상 동일합니다 :하지 않습니다. 식별 값과 같은 어떤 "올바른"순서가없는 임의적으로 취급되어야한다.

  12. ==============================

    12.ID 열을 재설정이 스크립트를 실행합니다. 두 변경해야합니다. 당신이 업데이트해야 할 어떤 테이블과 tableXYZ를 교체합니다. 또한, 식별 컬럼의 이름은 임시 테이블에서 삭제해야합니다. 이것은 35,000 행 및 3 열이있는 테이블에 순간이었다. 물론, 첫 번째 백업 테이블 및 테스트 환경에서 이것을 시도.

    ID 열을 재설정이 스크립트를 실행합니다. 두 변경해야합니다. 당신이 업데이트해야 할 어떤 테이블과 tableXYZ를 교체합니다. 또한, 식별 컬럼의 이름은 임시 테이블에서 삭제해야합니다. 이것은 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
  13. ==============================

    13.

    DBCC CHECKIDENT (<TableName>, reseed, 0)

    이것은 0으로 현재 ID 값을 설정합니다.

    다음 값을 삽입에 ID 값은 1로 증가 얻을.

  14. ==============================

    14.이 저장 프로 시저를 사용합니다 :

    이 저장 프로 시저를 사용합니다 :

    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, 항목 1 생산하고 있습니다.

    테이블이 다음 값을 만든 후 리셋 권리를 실행하면 두 번째는 솔직히 0 1, 항목 1. 생산, 나는 Microsoft는이 물건의 권리를 얻을 수 놀라게하고 있지 않다. 내가 채워집니다 내가 가끔 후 실행하는 것이 테이블을 참조하는 스크립트 파일이 있기 때문에 나는 테이블이 이미 만들어 질 때 가끔 표를 다시 작성하고 그것을 발견했다.

  15. ==============================

    15.나는이 작업을 수행하기 위해 다음 스크립트를 사용합니다. 이 당신이 테이블에서 모든 행을 삭제 한 경우 인 "오류"를 생산할 예정하는 하나의 시나리오, 그리고 IDENT_CURRENT는 현재로 시작하는 테이블의 한 행이 있었다 즉, 1로 설정됩니다.

    나는이 작업을 수행하기 위해 다음 스크립트를 사용합니다. 이 당신이 테이블에서 모든 행을 삭제 한 경우 인 "오류"를 생산할 예정하는 하나의 시나리오, 그리고 IDENT_CURRENT는 현재로 시작하는 테이블의 한 행이 있었다 즉, 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)
;
  16. ==============================

    16.신원 수를 재설정 완전한 DELETE 행의 경우와,이 (SQL 서버 2008 R2)를 사용

    신원 수를 재설정 완전한 DELETE 행의 경우와,이 (SQL 서버 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
  17. ==============================

    17.당신은 전체 테이블을 정리하지 않으면 0으로 시드 매우 실용적되지 않습니다.

    당신은 전체 테이블을 정리하지 않으면 0으로 시드 매우 실용적되지 않습니다.

    다른 하나는 현명한 안토니 레이몬드에 의해 주어진 대답은 완벽합니다. 다음 최대로 씨앗, 첫째 ID 열의 최대를 가져옵니다.

  18. ==============================

    18.그것의 항상 더 나은 사용에 TRUNCATE 가능한 경우 대신 그것은 또한 로그 공간을 사용하지 않는 모든 레코드를 삭제.

    그것의 항상 더 나은 사용에 TRUNCATE 가능한 경우 대신 그것은 또한 로그 공간을 사용하지 않는 모든 레코드를 삭제.

    경우 (다시 시드 'tablenem', 0) 우리는 삭제할 필요와 요구가 항상 씨앗을 다시 테이블에 채워하지 않은 경우 기억하는 당신은 DBCC CHECKIDENT를 사용 다음 첫 번째 레코드의 정체성을 얻을 것이다 = 0 MSDN 문서에 명시된

  19. ==============================

    19.첫째 : 신원 사양 그냥 "아니오">> 저장 데이터베이스 프로젝트를 실행

    첫째 : 신원 사양 그냥 "아니오">> 저장 데이터베이스 프로젝트를 실행

    다음 후 : 신원 사양 그냥 "예">> 저장 데이터베이스 프로젝트를 실행

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

    20. from https://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] IN 값에 대한 반환 기본 결과에 관계없이  (0) 2020.03.18
[SQL] 에서 VARCHAR 날짜 시간으로 변환하는 방법  (0) 2020.03.18
[SQL] 변환 UTF-8 SQL 데이터베이스에 문자열 클래식 ASP  (0) 2020.03.18
[SQL] SQL 분할 쉼표로 구분 행 [중복]  (0) 2020.03.18
[SQL] 동일한 사용자 이름의 다른 테이블에서 업데이트 테이블 값  (0) 2020.03.18
,

티스토리툴바