SQL Server - 삽입된 행의 ID를 얻는 가장 좋은 방법은 무엇입니까?

질문자 :Oded

삽입된 행의 IDENTITY 를 얻는 가장 좋은 방법은 무엇입니까?

@@IDENTITY 및 IDENT_CURRENT 및 SCOPE_IDENTITY 에 대해 알고 있지만 각각에 첨부된 장단점을 이해하지 못합니다.

누가 차이점과 언제 각각을 사용해야 하는지 설명해 주시겠습니까?

• @@IDENTITY 는 모든 범위에서 현재 세션의 모든 테이블에 대해 생성된 마지막 ID 값을 반환합니다. 범위에 걸쳐 있으므로 여기에서 주의해야 합니다. 현재 명령문 대신 트리거에서 값을 가져올 수 있습니다.

• SCOPE_IDENTITY() 는 현재 세션 및 현재 범위의 모든 테이블에 대해 생성된 마지막 ID 값을 반환합니다. 일반적으로 사용하려는 것 .

• IDENT_CURRENT('tableName') 은 모든 세션 및 범위의 특정 테이블에 대해 생성된 마지막 ID 값을 반환합니다. 이렇게 하면 위의 두 테이블이 필요한 것이 아닌 경우( 매우 드문 경우 ) 값을 원하는 테이블을 지정할 수 있습니다. 또한 @ Guy Starbuck이 언급했듯이 "레코드를 삽입하지 않은 테이블의 현재 IDENTITY 값을 얻으려면 이것을 사용할 수 있습니다."

• INSERT OUTPUT 절 을 사용하면 해당 문을 통해 삽입된 모든 행에 액세스할 수 있습니다. 특정 문으로 범위가 지정되어 있기 때문에 위의 다른 함수보다 더 간단합니다. 그러나 조금 더 장황하며 (테이블 변수/임시 테이블에 삽입한 다음 쿼리해야 함) 명령문이 롤백되는 오류 시나리오에서도 결과를 제공합니다. 즉, 쿼리가 병렬 실행 계획을 사용하는 경우 이것이 ID를 가져오는 유일한 보장된 방법 입니다(병렬 처리를 해제하는 것이 아님). 그러나 트리거 전에 실행되며 트리거 생성 값을 반환하는 데 사용할 수 없습니다.

bdukes

삽입된 ID를 검색하는 가장 안전하고 정확한 방법은 출력 절을 사용하는 것이라고 생각합니다.

예를 들어(다음 MSDN 기사에서 가져옴)

 USE AdventureWorks2008R2; GO DECLARE @MyTableVar table( NewScrapReasonID smallint, Name varchar(50), ModifiedDate datetime); INSERT Production.ScrapReason OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar VALUES (N'Operator error', GETDATE()); --Display the result set of the table variable. SELECT NewScrapReasonID, Name, ModifiedDate FROM @MyTableVar; --Display the result set of the table. SELECT ScrapReasonID, Name, ModifiedDate FROM Production.ScrapReason; GO

Orry

나는 다른 사람들과 같은 것을 말하고 있으므로 모두가 옳습니다. 나는 단지 더 명확하게하려고 노력하고 있습니다.

@@IDENTITY 는 클라이언트의 데이터베이스 연결에 의해 삽입된 마지막 항목의 ID를 반환합니다.
대부분의 경우 이것은 잘 작동하지만 때로는 트리거가 이동하여 모르는 새 행을 삽입하고 원하는 행 대신 이 새 행에서 ID를 가져옵니다.

SCOPE_IDENTITY() 는 이 문제를 해결합니다. 데이터베이스에 보낸 SQL 코드에 마지막으로 삽입한 것의 ID를 반환합니다. 트리거가 추가 행을 생성하면 잘못된 값이 반환되지 않습니다. 만세

IDENT_CURRENT 는 누구나 삽입한 마지막 ID를 반환합니다. 불행한 시간에 다른 앱이 다른 행을 삽입하는 일이 발생하면 귀하의 행 대신 해당 행의 ID를 얻게 됩니다.

안전하게 플레이하려면 항상 SCOPE_IDENTITY() 사용하십시오. @@IDENTITY 를 고수하고 누군가 나중에 트리거를 추가하기로 결정하면 모든 코드가 중단됩니다.

Orion Edwards

새로 삽입된 행의 ID를 얻는 가장 좋은(읽기: 가장 안전한) 방법은 output 절을 사용하는 것입니다.

 create table TableWithIdentity ( IdentityColumnName int identity(1, 1) not null primary key, ... ) -- type of this table's column must match the type of the -- identity column of the table you'll be inserting into declare @IdentityOutput table ( ID int ) insert TableWithIdentity ( ... ) output inserted.IdentityColumnName into @IdentityOutput values ( ... ) select @IdentityValue = (select ID from @IdentityOutput)

Ian Kemp

추가하다

 SELECT CAST(scope_identity() AS int);

insert sql 문의 끝까지

 NewId = command.ExecuteScalar()

회수할 것입니다.

Jim

Entity Framework를 사용할 때 내부적으로 OUTPUT 기술을 사용하여 새로 삽입된 ID 값을 반환합니다.

 DECLARE @generated_keys table([Id] uniqueidentifier) INSERT INTO TurboEncabulators(StatorSlots) OUTPUT inserted.TurboEncabulatorID INTO @generated_keys VALUES('Malleable logarithmic casing'); SELECT t.[TurboEncabulatorID ] FROM @generated_keys AS g JOIN dbo.TurboEncabulators AS t ON g.Id = t.TurboEncabulatorID WHERE @@ROWCOUNT > 0

출력 결과는 임시 테이블 변수에 저장되고 테이블에 다시 조인되며 테이블에서 행 값을 반환합니다.

참고: EF가 임시 테이블을 실제 테이블에 다시 내부 조인하는 이유를 모르겠습니다(어떤 상황에서 두 테이블이 일치하지 않는지).

하지만 그것이 EF가 하는 일입니다.

이 기술( OUTPUT )은 SQL Server 2008 이상에서만 사용할 수 있습니다.

편집 - 가입 이유

엔티티 프레임 워크는 단순히 사용하기보다는, 원래 테이블에 다시 합류하는 이유 OUTPUT EF도 얻을이 기술을 사용하기 때문에 값은 rowversion 새로 삽입 된 행의를.

Timestamp 속성을 사용하여 엔터티 프레임워크 모델에서 낙관적 동시성을 사용할 수 있습니다.

 public class TurboEncabulator { public String StatorSlots) [Timestamp] public byte[] RowVersion { get; set; } }

이렇게 하면 Entity Framework에 새로 삽입된 행 rowversion

 DECLARE @generated_keys table([Id] uniqueidentifier) INSERT INTO TurboEncabulators(StatorSlots) OUTPUT inserted.TurboEncabulatorID INTO @generated_keys VALUES('Malleable logarithmic casing'); SELECT t.[TurboEncabulatorID], t.[RowVersion] FROM @generated_keys AS g JOIN dbo.TurboEncabulators AS t ON g.Id = t.TurboEncabulatorID WHERE @@ROWCOUNT > 0

Timetsamp 를 검색하기 위해 OUTPUT 절을 사용할 수 없습니다.

테이블에 트리거가 Timestamp 가 잘못되기 때문입니다.

• 초기 삽입. 타임스탬프: 1
• OUTPUT 절 출력 타임스탬프: 1
• 트리거는 행을 수정합니다. 타임스탬프: 2

테이블에 트리거가 있는 경우 반환된 타임스탬프는 절대 정확하지 않습니다. 따라서 별도의 SELECT 를 사용해야 합니다 .

그리고 잘못된 rowversion을 기꺼이 감수하더라도 별도의 SELECT rowversion 을 테이블 변수에 출력할 수 없기 때문입니다.

 DECLARE @generated_keys table([Id] uniqueidentifier, [Rowversion] timestamp) INSERT INTO TurboEncabulators(StatorSlots) OUTPUT inserted.TurboEncabulatorID, inserted.Rowversion INTO @generated_keys VALUES('Malleable logarithmic casing');

세 번째 이유는 대칭 때문입니다. 트리거가 있는 테이블에 UPDATE 를 수행할 때 OUTPUT 절을 사용할 수 없습니다. OUTPUT UPDATE 를 시도하는 것은 지원되지 않으며 오류가 발생합니다.

• 트리거가 테이블에 있을 때 OUTPUT 절과 함께 UPDATE를 사용할 수 없습니다.

이를 수행하는 유일한 방법은 후속 SELECT 문을 사용하는 것입니다.

 UPDATE TurboEncabulators SET StatorSlots = 'Lotus-O deltoid type' WHERE ((TurboEncabulatorID = 1) AND (RowVersion = 792)) SELECT RowVersion FROM TurboEncabulators WHERE @@ROWCOUNT > 0 AND TurboEncabulatorID = 1

Ian Boyd

MSDN에서

@@IDENTITY, SCOPE_IDENTITY 및 IDENT_CURRENT는 테이블의 IDENTITY 열에 삽입된 마지막 값을 반환한다는 점에서 유사한 함수입니다.

@@IDENTITY 및 SCOPE_IDENTITY는 현재 세션의 모든 테이블에서 생성된 마지막 ID 값을 반환합니다. 그러나 SCOPE_IDENTITY는 현재 범위 내에서만 값을 반환합니다. @@IDENTITY는 특정 범위로 제한되지 않습니다.

IDENT_CURRENT는 범위 및 세션에 의해 제한되지 않습니다. 지정된 테이블로 제한됩니다. IDENT_CURRENT는 모든 세션 및 범위의 특정 테이블에 대해 생성된 ID 값을 반환합니다. 자세한 내용은 IDENT_CURRENT를 참조하세요.

• IDENT_CURRENT는 테이블을 인수로 사용하는 함수입니다.
• @@IDENTITY는 테이블에 트리거가 있는 경우 혼란스러운 결과를 반환할 수 있습니다.
• SCOPE_IDENTITY는 대부분의 경우 영웅입니다.

Jakub Šturc

@@IDENTITY 는 현재 SQL 연결을 사용하여 삽입된 마지막 ID입니다. 이것은 새 레코드에 대해 삽입된 ID만 필요하고 나중에 더 많은 행이 추가되더라도 상관하지 않는 삽입 저장 프로시저에서 반환하기에 좋은 값입니다.

SCOPE_IDENTITY 는 현재 SQL 연결을 사용하여 삽입된 마지막 ID이며 현재 범위에서 -- 즉, 삽입 후 트리거를 기반으로 삽입된 두 번째 IDENTITY가 있는 경우 SCOPE_IDENTITY에는 반영되지 않고 수행한 삽입만 수행됩니다. . 솔직히 저는 이것을 사용할 이유가 없었습니다.

IDENT_CURRENT(tablename) 은 연결 또는 범위에 관계없이 삽입된 마지막 ID입니다. 레코드를 삽입하지 않은 테이블의 현재 IDENTITY 값을 가져오려는 경우 이를 사용할 수 있습니다.

Guy Starbuck

다른 버전의 SQL Server에 대해 말할 수는 없지만 2012년에는 직접 출력이 잘 작동합니다. 임시 테이블로 귀찮게 할 필요가 없습니다.

 INSERT INTO MyTable OUTPUT INSERTED.ID VALUES (...)

그건 그렇고, 이 기술은 여러 행을 삽입할 때도 작동합니다.

 INSERT INTO MyTable OUTPUT INSERTED.ID VALUES (...), (...), (...)

산출

 ID 2 3 4

MarredCheese

항상 scope_identity()를 사용하세요. 다른 것은 절대 필요하지 않습니다.

erikkallen

삽입하는 행의 ID를 보장하는 또 다른 방법은 ID 값을 지정하고 SET IDENTITY_INSERT ON 을 사용한 다음 OFF 입니다. 이렇게 하면 ID 값이 무엇인지 정확히 알 수 있습니다! 값이 사용되지 않는 한 이러한 값을 ID 열에 삽입할 수 있습니다.

 CREATE TABLE #foo ( fooid INT IDENTITY NOT NULL, fooname VARCHAR(20) ) SELECT @@Identity AS [@@Identity], Scope_identity() AS [SCOPE_IDENTITY()], Ident_current('#Foo') AS [IDENT_CURRENT] SET IDENTITY_INSERT #foo ON INSERT INTO #foo (fooid, fooname) VALUES (1, 'one'), (2, 'Two') SET IDENTITY_INSERT #foo OFF SELECT @@Identity AS [@@Identity], Scope_identity() AS [SCOPE_IDENTITY()], Ident_current('#Foo') AS [IDENT_CURRENT] INSERT INTO #foo (fooname) VALUES ('Three') SELECT @@Identity AS [@@Identity], Scope_identity() AS [SCOPE_IDENTITY()], Ident_current('#Foo') AS [IDENT_CURRENT] -- YOU CAN INSERT SET IDENTITY_INSERT #foo ON INSERT INTO #foo (fooid, fooname) VALUES (10, 'Ten'), (11, 'Eleven') SET IDENTITY_INSERT #foo OFF SELECT @@Identity AS [@@Identity], Scope_identity() AS [SCOPE_IDENTITY()], Ident_current('#Foo') AS [IDENT_CURRENT] SELECT * FROM #foo

이것은 다른 소스에서 데이터를 로드하거나 두 데이터베이스 등에서 데이터를 병합하는 경우 매우 유용한 기술이 될 수 있습니다.

Andy Robertson

uuid 만들고 열에 삽입합니다. 그런 다음 uuid로 행을 쉽게 식별할 수 있습니다. 그것이 당신이 구현할 수 있는 유일한 100% 작동 솔루션입니다. 다른 모든 솔루션은 너무 복잡하거나 동일한 경우에 작동하지 않습니다. 예:

1) 행 생성

 INSERT INTO table (uuid, name, street, zip) VALUES ('2f802845-447b-4caa-8783-2086a0a8d437', 'Peter', 'Mainstreet 7', '88888');

2) 생성된 행 가져오기

 SELECT * FROM table WHERE uuid='2f802845-447b-4caa-8783-2086a0a8d437';

Frank Roth

이것은 이전 스레드이지만 서버 재부팅 후 ID 값의 간격과 같은 이전 버전의 SQL Server에서 IDENTITY 열의 일부 함정을 피하는 새로운 방법이 있습니다. 시퀀스는 SQL Server 2016 이상에서 사용할 수 있으며 TSQL을 사용하여 SEQUENCE 개체를 만드는 새로운 방법입니다. 이를 통해 SQL Server에서 고유한 숫자 시퀀스 개체를 만들고 증가 방법을 제어할 수 있습니다.

다음은 예입니다.

 CREATE SEQUENCE CountBy1 START WITH 1 INCREMENT BY 1 ; GO

그런 다음 TSQL에서 다음 시퀀스 ID를 얻기 위해 다음을 수행합니다.

 SELECT NEXT VALUE FOR CountBy1 AS SequenceID GO

다음은 CREATE SEQUENCE 및 NEXT VALUE FOR에 대한 링크입니다.

StevenJe

삽입문 뒤에 이것을 추가해야 합니다. 그리고 데이터가 삽입되는 테이블 이름을 확인하십시오. insert 문에 의해 영향을 받는 행이 없는 현재 행을 가져옵니다.

 IDENT_CURRENT('tableName')

Khan Ataur Rahman

출처 : http:www.stackoverflow.com/questions/42648/sql-server-best-way-to-get-identity-of-inserted-row