Лучший способ получить идентичность вставленной строки?
Каков наилучший способ получить IDENTITY
вставленного ряда?
Я знаю о @@IDENTITY
а также IDENT_CURRENT
а также SCOPE_IDENTITY
но не понимаю плюсы и минусы каждого.
Может кто-нибудь объяснить, пожалуйста, различия и когда я должен использовать каждый?
16 ответов
@@IDENTITY
возвращает последнее значение идентификатора, сгенерированное для любой таблицы в текущем сеансе, во всех областях. Вы должны быть осторожны здесь, так как это выходит за рамки. Вы можете получить значение из триггера вместо текущего оператора.SCOPE_IDENTITY()
возвращает последнее значение идентификатора, сгенерированное для любой таблицы в текущем сеансе и текущей области. Вообще то, что вы хотите использовать.IDENT_CURRENT('tableName')
возвращает последнее значение идентификатора, сгенерированное для конкретной таблицы в любом сеансе и любой области видимости. Это позволяет вам указать, из какой таблицы вы хотите получить значение, в случае, если два приведенных выше не совсем то, что вам нужно (очень редко). Кроме того, как заметил @ Гай Старбак: "Вы можете использовать это, если хотите получить текущее значение IDENTITY для таблицы, в которую вы не вставили запись".OUTPUT
пункт оINSERT
оператор позволит вам получить доступ к каждой строке, которая была вставлена через этот оператор. Поскольку он ограничен конкретным оператором, он более прост, чем другие функции выше. Однако, это немного более многословно (вам нужно вставить в таблицу переменную / временную таблицу, а затем запросить это), и это дает результаты даже в случае ошибки, когда оператор откатывается. Тем не менее, если ваш запрос использует план параллельного выполнения, это единственный гарантированный метод получения идентификатора (за исключением отключения параллелизма). Однако он выполняется перед триггерами и не может использоваться для возврата сгенерированных триггером значений.
Я считаю, что самый безопасный и точный метод получения вставленного идентификатора будет использовать предложение вывода.
например (взято из следующей статьи 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
Я говорю то же самое, что и другие парни, так что все правы, я просто пытаюсь прояснить это.
@@IDENTITY
возвращает идентификатор последней вещи, которая была вставлена подключением вашего клиента к базе данных.
В большинстве случаев это работает нормально, но иногда запускается триггер и вставляется новая строка, о которой вы не знаете, и вы получаете идентификатор из этой новой строки вместо той, которую вы хотите
SCOPE_IDENTITY()
решает эту проблему. Он возвращает идентификатор последней вещи, которую вы вставили в код SQL, который вы отправили в базу данных. Если триггеры идут и создают дополнительные строки, они не приведут к возвращению неверного значения. Ура
IDENT_CURRENT
возвращает последний идентификатор, который был вставлен кем-либо. Если какое-то другое приложение вставит еще одну строку в несчастное время, вы получите идентификатор этой строки вместо своей.
Если вы хотите быть осторожным, всегда используйте SCOPE_IDENTITY()
, Если вы придерживаетесь @@IDENTITY
и кто-то решит добавить триггер позже, весь ваш код сломается.
Лучший (читай: самый безопасный) способ получения идентификатора вновь вставленной строки - использование 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);
в конце вашего оператора вставки SQL, затем
NewId = command.ExecuteScalar()
получит это.
@@ IDENTITY, SCOPE_IDENTITY и IDENT_CURRENT - похожие функции в том смысле, что они возвращают последнее значение, вставленное в столбец IDENTITY таблицы.
@@ IDENTITY и SCOPE_IDENTITY будут возвращать последнее значение идентификатора, сгенерированное в любой таблице в текущем сеансе. Однако SCOPE_IDENTITY возвращает значение только в текущей области; @@IDENTITY не ограничивается конкретной областью применения.
IDENT_CURRENT не ограничен областью действия и сессией; он ограничен указанной таблицей. IDENT_CURRENT возвращает значение идентификатора, созданное для конкретной таблицы в любом сеансе и любой области видимости. Для получения дополнительной информации см. IDENT_CURRENT.
- IDENT_CURRENT - это функция, которая принимает таблицу в качестве аргумента.
- @@ IDENTITY может дать неверный результат, если у вас есть триггер на столе
- SCOPE_IDENTITY - ваш герой большую часть времени.
Когда вы используете 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 или более поздней версии.
Я не могу говорить с другими версиями SQL Server, но в 2012 году вывод напрямую работает просто отлично. Вам не нужно беспокоиться о временном столе.
INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES (...)
Кстати, этот метод также работает при вставке нескольких строк.
INSERT INTO MyTable
OUTPUT INSERTED.ID
VALUES
(...),
(...),
(...)
Выход
ID
2
3
4
@@IDENTITY - это последний идентификатор, вставленный с использованием текущего соединения SQL. Это хорошее значение для возврата из хранимой процедуры вставки, где вам просто нужно вставить идентификатор для вашей новой записи, и вам все равно, будет ли после этого добавлено больше строк.
SCOPE_IDENTITY - это последний идентификатор, вставленный с использованием текущего соединения SQL, и в текущей области - то есть, если после вставки была вставлена вторая IDENTITY, основанная на триггере, она не будет отражена в SCOPE_IDENTITY, только вставка, которую вы выполнили, Честно говоря, у меня никогда не было причин использовать это.
IDENT_CURRENT (имя таблицы) является последней вставленной идентификацией независимо от соединения или области действия. Вы можете использовать это, если хотите получить текущее значение IDENTITY для таблицы, в которую вы не вставили запись.
ВСЕГДА используйте scope_identity(), НИКОГДА больше не нужно ничего.
Еще один способ гарантировать идентичность вставляемых строк - указать значения идентификаторов и использовать 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
Это может быть очень полезным методом, если вы загружаете данные из другого источника или объединяете данные из двух баз данных и т. Д.
Создать uuid
а также вставьте его в столбец. Тогда вы можете легко идентифицировать вашу строку с помощью uuid.
Несмотря на то, что это более старый поток, существует новый способ сделать это, который позволяет избежать некоторых ошибок столбца 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
Если вы ищете последний добавленный / обновленный идентификатор, это может быть немного старомодно, но есть много людей, использующих более старый PHP, Pre 5.5, если быть более точным. Более подробную информацию можно найти на http://php.net/manual/en/function.mysql-insert-id.php
$last = mysql_insert_id();
Полное решение в 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);
После вашей вставки вы должны добавить это. И убедитесь, что имя таблицы, куда вставляются данные. Вы получите текущую строку, а не строку, на которую сейчас влияет оператор вставки.
IDENT_CURRENT('tableName')