Лучший способ узнать идентичность вставленной строки?

Из MSDN

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

• IDENT_CURRENT - это функция, которая принимает в качестве аргумента таблицу.
• @@ IDENTITY может вернуть непонятный результат, если у вас есть триггер в таблице.
• SCOPE_IDENTITY большую часть времени ваш герой.

• @@IDENTITY возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе, во всех областях. Здесь нужно быть осторожным, так как это вне границ. Вы можете получить значение из триггера вместо текущего оператора.

• SCOPE_IDENTITY() возвращает последнее значение идентификатора, созданное для любой таблицы в текущем сеансе и текущей области. Вообще то, что вы хотите использовать.

• IDENT_CURRENT('tableName') возвращает последнее значение идентификатора, созданное для конкретной таблицы в любом сеансе и любой области. Это позволяет вам указать, из какой таблицы вы хотите получить значение, на случай, если две приведенные выше не совсем то, что вам нужно (очень редкий). Кроме того, как упоминалось в @ Гай Старбак: «Вы можете использовать это, если хотите получить текущее значение IDENTITY для таблицы, в которую вы не вставляли запись».

• Пункт OUTPUT оператора INSERT позволит вам получить доступ к каждой строке, которая была вставлена ​​с помощью этого оператора. Поскольку он привязан к конкретному оператору, он более простой, чем другие функции, указанные выше. Однако это небольшой более подробный (вам нужно вставить в таблицу переменную / временную таблицу, а затем запросить ее), и он дает результаты даже в сценарии ошибки, когда оператор откатывается. Тем не менее, если ваш запрос использует план параллельного выполнения, это только гарантированный метод для получения идентификатора (за исключением отключения параллелизма). Однако он выполняет триггеры перед и не может использоваться для возврата значений, созданных триггером.

@@ЛИЧНОСТЬ - это последний идентификатор, вставленный с использованием текущего соединения SQL. Это хорошее значение для возврата из хранимой процедуры вставки, где вам просто нужно, чтобы идентификатор был вставлен для вашей новой записи, и не заботиться о том, были ли добавлены другие строки после этого.

SCOPE_IDENTITY - это последний идентификатор, вставленный с использованием текущего соединения SQL и в текущей области - то есть, если после вашей вставки был вставлен второй идентификатор IDENTITY на основе триггера, он не будет отражен в SCOPE_IDENTITY, только выполненная вами вставка . Честно говоря, у меня никогда не было причин использовать это.

IDENT_CURRENT (имя таблицы) - это последний идентификатор, вставленный независимо от соединения или области действия. Вы можете использовать это, если хотите получить текущее значение IDENTITY для таблицы, в которую вы не вставляли запись.

Я говорю то же, что и другие парни, так что все правы, я просто пытаюсь прояснить это.

@@IDENTITY возвращает идентификатор последнего элемента, который был вставлен клиентом при подключении к базе данных. В большинстве случаев это работает нормально, но иногда срабатывает триггер и вставляет новую строку, о которой вы не знаете, и вы получите идентификатор из этой новой строки вместо того, который вам нужен.

SCOPE_IDENTITY() решает эту проблему. Он возвращает в базу данных идентификатор последнего элемента ты вставил в SQL-коде ты отправил. Если триггеры создают дополнительные строки, они не вернут неправильное значение. Ура

IDENT_CURRENT возвращает последний ID, который был вставлен кем-либо. Если какое-то другое приложение вставит еще одну строку в непредвиденное время, вы получите идентификатор этой строки вместо своего.

Если вы хотите перестраховаться, всегда используйте SCOPE_IDENTITY(). Если вы будете придерживаться @@IDENTITY, и кто-то решит добавить триггер позже, весь ваш код сломается.

ВСЕГДА используйте scope_identity (), НИКОГДА не нужно ничего другого.

Я считаю, что наиболее безопасным и точным методом получения вставленного идентификатора было бы использование предложения output.

например (взято из следующей статьи 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

Лучший (читай: самый безопасный) способ получить идентификацию вновь вставленной строки - использовать предложение 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)

Добавлять

SELECT CAST(scope_identity() AS int);

до конца вашей инструкции insert sql, затем

NewId = command.ExecuteScalar()

получит его.

Когда вы используете Entity Framework, он внутренне использует технику OUTPUT для возврата вновь вставленного значения идентификатора.

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 или новее.

Редактировать - Причина присоединения

Причина, по которой Entity Framework присоединяется к исходной таблице, а не просто использует значения 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.

И даже если вы были готовы столкнуться с неправильной версией строки, другой причиной для выполнения отдельного 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. Попытка сделать UPDATE с OUTPUT не поддерживается и выдаст ошибку:

• Невозможно использовать UPDATE с предложением OUTPUT, когда триггер находится в таблице

Единственный способ сделать это - выполнить следующий запрос 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

После вашего заявления о вставке вам необходимо добавить это. И убедитесь в названии таблицы, в которую вставляются данные. Вы не получите текущую строку там, где строка, затронутая только что оператором вставки.

IDENT_CURRENT('tableName')

Я не могу говорить с другими версиями SQL Server, но в 2012 году прямой вывод работает нормально. Вам не нужно возиться с временной таблицей.

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

Кстати, этот прием работает и при вставке нескольких строк.

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

Выход

ID
2
3
4

Создайте 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';

Еще один способ гарантировать идентичность вставляемых строк - указать значения идентичности и использовать SET IDENTITY_INSERT ON, а затем OFF. Это гарантирует, что вы точно знаете, каковы значения идентичности! Пока значения не используются, вы можете вставить эти значения в столбец идентификаторов.

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 

Это может быть очень полезным методом, если вы загружаете данные из другого источника или объединяете данные из двух баз данных и т. д.

Несмотря на то, что это более старый поток, есть новый способ сделать это, который позволяет избежать некоторых ошибок столбца IDENTITY в старых версиях SQL Server, вроде пробелов в значениях идентичности после перезагрузки сервера. Последовательности доступны в SQL Server 2016 и далее, что является более новым способом создания объекта SEQUENCE с использованием TSQL. Это позволяет вам создать собственный объект числовой последовательности в SQL Server и контролировать его приращение.

Вот пример:

CREATE SEQUENCE CountBy1  
    START WITH 1  
    INCREMENT BY 1 ;  
GO  

Затем в TSQL вы должны сделать следующее, чтобы получить следующий идентификатор последовательности:

SELECT NEXT VALUE FOR CountBy1 AS SequenceID
GO

Вот ссылки на СОЗДАТЬ ПОСЛЕДОВАТЕЛЬНОСТЬ и СЛЕДУЮЩЕЕ ЗНАЧЕНИЕ ДЛЯ