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

Question

Каков наилучший способ получить IDENTITY вставленного ряда?

Я знаю о @@IDENTITY а также IDENT_CURRENT а также SCOPE_IDENTITY но не понимаю плюсы и минусы каждого.

Может кто-нибудь объяснить, пожалуйста, различия и когда я должен использовать каждый?

Answer 1

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

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

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

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

Answer 2

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

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

Answer 3

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

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

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

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

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

Answer 4

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

Answer 5

Добавлять

SELECT CAST(scope_identity() AS int);

в конце вашего оператора вставки SQL, затем

NewId = command.ExecuteScalar()

получит это.

Answer 6

MSDN

@@ IDENTITY, SCOPE_IDENTITY и IDENT_CURRENT - похожие функции в том смысле, что они возвращают последнее значение, вставленное в столбец IDENTITY таблицы.

@@ IDENTITY и SCOPE_IDENTITY будут возвращать последнее значение идентификатора, сгенерированное в любой таблице в текущем сеансе. Однако SCOPE_IDENTITY возвращает значение только в текущей области; @@IDENTITY не ограничивается конкретной областью применения.

IDENT_CURRENT не ограничен областью действия и сессией; он ограничен указанной таблицей. IDENT_CURRENT возвращает значение идентификатора, созданное для конкретной таблицы в любом сеансе и любой области видимости. Для получения дополнительной информации см. IDENT_CURRENT.

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

Answer 7

Когда вы используете 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 или более поздней версии.

Answer 8

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

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

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

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

Выход

ID
2
3
4

Answer 9

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

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

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

Answer 10

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

Answer 11

Еще один способ гарантировать идентичность вставляемых строк - указать значения идентификаторов и использовать 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 

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

Answer 12

Создать uuid а также вставьте его в столбец. Тогда вы можете легко идентифицировать вашу строку с помощью uuid.

Answer 13

Несмотря на то, что это более старый поток, существует новый способ сделать это, который позволяет избежать некоторых ошибок столбца 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

Вот ссылки на CREATE SEQUENCE и NEXT VALUE FOR

Answer 14

Если вы ищете последний добавленный / обновленный идентификатор, это может быть немного старомодно, но есть много людей, использующих более старый PHP, Pre 5.5, если быть более точным. Более подробную информацию можно найти на http://php.net/manual/en/function.mysql-insert-id.php

$last = mysql_insert_id();

Answer 15

Полное решение в SQL и ADO.NET

       const string sql = "INSERT INTO [Table1] (...) OUTPUT INSERTED.Id VALUES (...)";

using var command = connection.CreateCommand();
command.CommandText = sql;
 var outputIdParameter = new SqlParameter("@Id", SqlDbType.Int) { Direction = ParameterDirection.Output };
        command.Parameters.Add(outputIdParameter);

        await connection.OpenAsync();

        var outputId= await command.ExecuteScalarAsync();

        await connection.CloseAsync();
        
        int id = Convert.ToInt32(outputId);

Answer 16

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

IDENT_CURRENT('tableName')