IDENTITY (Function) (Transact-SQL)
Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.
To create an automatically incrementing number that can be used in multiple tables or that can be called from applications without referencing any table, see Sequence Numbers.
Transact-SQL Syntax Conventions
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Arguments
data_type
Is the data type of the identity column. Valid data types for an identity column are any data types of the integer data type category, except for the bit data type, or decimal data type.
seed
Is the integer value to be assigned to the first row in the table. Each subsequent row is assigned the next identity value, which is equal to the last IDENTITY value plus the increment value. If neither seed nor increment is specified, both default to 1.
increment
Is the integer value to add to the seed value for successive rows in the table.
column_name
Is the name of the column that is to be inserted into the new table.
Return Types
Returns the same as data_type.
Remarks
Because this function creates a column in a table, a name for the column must be specified in the select list in one of the following ways:
Examples
Identity sql что это
Аналогом этого свойства в FoxPro является тип данных Integer-AutoIncrement. Только не надо считать, что Integer-AutoIncrement это и есть поле со свойством Identity. Вовсе нет. Это именно аналог. В основном они похожи, но имеют ряд отличий. В данной статье речь пойдет о свойстве IDENTITY в MS SQL сервере.
Поля со свойством IDENTITY обладают следующими особенностями:
Есть еще некоторые особенности, но они уже являются следствием перечисленных особенностей
Другими словами, в последовательности значений поля со свойством IDENTITY вполне допустимы «дыры». Список значение вовсе не непрерывный
Как правило, в качестве шага приращения указывают 1, но это может быть и любое целое число. В том числе и отрицательное.
В связи с такими особенностями полей со свойством IDENTITY такие поля часто используют в качестве первичных ключей. Другими словами, в качестве полей, по значению которых всегда можно однозначно идентифицировать запись таблицы.
Следует иметь в виду, что свойство IDENTITY никак не контролирует уникальность данных. Например, если изначально поле имело тип INTEGER, и в него были введены ряд значений. А затем была изменена структура таблицы, и на данное поле было наложено свойство IDENTITY, то новые записи вполне могут иметь те же данные, что уже были введены ранее в эту таблицу. Поэтому, если поле со свойством IDENTITY используется в качестве первичного ключа, то на это поле следует наложить дополнительное ограничение по уникальности.
Недостаток использования полей со свойством IDENTITY как первичного ключа
Однако, несмотря на явные достоинства использования полей со свойством IDENTITY в качестве первичного ключа, они имеют и серьезный недостаток.
Значение полей со свойством IDENTITY невозможно узнать до того, как запись будет физически создана.
Ну и что? Какие проблемы-то? Создадим запись и узнаем ее новое значение.
Проблема заключается в том, что для того, чтобы узнать значение поля какой-либо записи эту запись надо сначала найти. А поиск записи как раз и осуществляется по значению первичного ключа. Того самого, значение которого необходимо определить. Замкнутый круг: чтобы прочитать значение это значение надо знать!
Структура хранения данных в MS SQL сервере принципиально отличается от структуры хранения данных в DBF-файлах. В нем нет таких понятий как «физический номер записи», «следующая запись», «последняя запись» и т.п. Т.е. невозможно перейти к «последней записи» чтобы прочитать значение ее первичного ключа.
Дело тут в том, что, как правило, MS SQL сервер используется в многопользовательских приложениях. Это значит, что сразу несколько пользователей, одновременно, могут создавать новые записи. Получается, что один пользователь создал новую запись, затем начал вычислять максимальное значение и в этот момент другой пользователь также создал новую запись. В результате, первый пользователь в качестве максимального значения получит значение записи созданной вторым пользователем.
Так что же, отказаться от использования полей со свойством IDENTITY в качестве первичного ключа? Вовсе нет. Все-таки существуют способы определения значения поля со свойством IDENTITY у новой записи.
Как определить значение поля со свойством IDENTITY в новой записи
Собственно, есть три принципиальные стратегии определения значения поля со свойством IDENTITY в новой, только что созданной, записи
Теперь рассмотрим более подробно достоинства и недостатки каждой стратегии
Значение, возвращаемое системной переменной @@IDENTITY
В MS SQL сервере есть ряд системных переменных, значение которых изменяется автоматически при наступлении определенных событий. В частности, значение системной переменной @@IDENTITY автоматически устанавливается равным значению поля со свойством IDENTITY последней созданной записи в текущем соединении. Т.е. создание новых записей в другом соединении (другим пользователем) никак не повлияет на ее значение в данном соединении.
Ну, так вот оно, решение. Просто после создания новой записи читаем значение системной переменной @@IDENTITY и имеем искомое значение.
В целом, верно. Проблема только в том, что системная переменная @@IDENTITY меняет свое значение при создании записи в любой таблице.
На практике, это означает, что если на таблицу установлен триггер на вставку, в теле которого дается команда INSERT на создание записи в другой таблице, которая, в свою очередь, также имеет поле со свойством IDENTITY, то системная переменная @@IDENTITY получит значение поля из этой второй таблицы.
Другими словами, опираться на значение системной переменной @@IDENTITY можно, но, помня о том, что эта переменная не привязана к значению поля одной таблицы.
Значение, возвращаемое функцией SCOPE_IDENTITY()
В версии MS SQL 2000 была введена системная функция SCOPE_IDENTITY(). Эта функция также возвращает значение поля со свойством IDENTITY последней созданной записи, но созданной в пределах текущего SCOPE.
Даже если два пользователя одновременно вызвали одну и ту же процедуру, но каждый вызвал процедуру в своем SCOPE. Т.е. опять-таки нет конфликта.
К недостаткам этой функции относится то, что ее следует вызывать в пределах того SCOPE, где и была создана новая запись интересующей нас таблицы. А это не всегда возможно.
Другими словами, для корректного использования SCOPE_IDENTITY() необходимо всегда следить за областью действия SCOPE. Зачастую, создавая специальные процедуры.
Нахождение новой записи по значению других полей
Если помните, то основная проблема с определением значения поля со свойством IDENTITY заключается в том, что данное поле используется в качестве первичного ключа. Т.е. по его значению как раз и находят нужную запись.
Однако, зачастую, таблицы имеют поле или набор полей, по которым также можно однозначно определить запись. Например, если речь идет о справочнике, то, разумеется, справочник имеет поле «Название». Также очевидно, что это поле должно быть уникально в пределах справочника. Иначе просто теряется смысл использования самого справочника. Зачем вводить в справочник записи с одинаковым значением?
Значение поля со свойством IDENTITY в новой записи не известно. Но значение поля «Название» в этой новой записи вполне известно. Пользователь сам же его и ввел! Значит, после создания новой записи можно найти эту новую запись по значению поля «Название» и прочитать значение поля со свойством IDENTITY.
Проблема только в том, что далеко не всегда существует такое поле или набор полей для однозначной идентификации записи. Это, кстати, одна из причин ввода, так называемых, суррогатных ключей. Тех самых полей со свойством IDENTITY.
Если, тем не менее, Вы решите использовать эту стратегию для поиска новой записи, то обязательно наложите на поле «Название» (или выбранный Вами набор полей) ограничение по уникальности. Т.е., чтобы случайно не оказалось двух записей с одинаковым значением в этом поле.
Как работать с полями со свойством IDENTITY в FoxPro
С теоретической частью закончили, теперь «попробуем со всем этим добром взлететь». Т.е. определимся, как же воспользоваться всеми этими знаниями в FoxPro. Еще раз уточним задачу, которую необходимо решить.
Добавляется запись в таблицу MS SQL сервера имеющую поле со свойством IDENTITY. Необходимо сразу после создания новой записи получить значение поля со свойством IDENTITY на стороне FoxPro.
У FoxPro есть три принципиальные возможности организации работы с MS SQL сервером
Тут следует остановиться на том, какое именно событие собственно создает запись на MS SQL сервер. Ну, с Pass-Trough все ясно. Это собственно прямая команда серверу создать новую запись. А вот с Remote View и Cursor Adapter несколько иначе.
Результатом работы как Remote View, так и Cursor Adapter является курсор. Т.е. некая временная таблица, физически расположенная на машине клиента. По умолчанию, этот курсор автоматически открывается в режиме оптимистической буферизации строк (3) и может быть переключен только в режим оптимистической буферизации таблиц (5). Переключиться в режим пессимистической буферизации или отключить буферизацию совсем для этого курсора невозможно
Следовательно, новая запись сначала физически будет создана именно на клиентской машине в этом самом курсоре. Точнее, в буфере этого курсора. Физическое создание записи на MS SQL сервере произойдет только после сброса буфера.
Для строковой буферизации сброс буфера может произойти автоматически при выполнении одного из следующих действий:
Для табличной буферизации сброс буфера может произойти только по команде TableUpdate() и никак иначе.
Никакие другие действия и операции ни с Remote View, ни с Cursor Adapter не приведут к созданию новой записи на MS SQL сервере. Если при выполнении какой-либо операции оказалось, что на MS SQL сервере создалась новая запись, это означает, что курсор находился в режиме строковой буферизации, и произошло одно из событий вызвавших автоматический сброс буфера.
Например, такое может произойти по команде Requery() для Remote View. Но это вовсе не означает, что команда Requery() сбрасывает буфер. Вовсе нет. Просто одним из условий выполнения команды Requery() является закрытие ранее существовавшего курсора. А вот это событие как раз и вызовет автоматический сброс буфера, если курсор находится в режиме строковой буферизации.
Чтобы избежать подобных недоразумений, переключайте курсор в режим табличной буферизации (5). В этом случае Вы всегда сможете контролировать процесс сброса буфера.
Однако следует понимать, что даже если Вы установите режим табличной буферизации, измените несколько записей в Remote View или Cursor Adapter, а потом дадите команду TableUpdate() все равно сброс буфера будет происходить по одной записи за раз. Т.е. на сервер будет послана не одна команда, например, на модификацию, а набор команд по модификации каждой записи в отдельности.
Применительно к операциям создания новой записи из этого следует вывод, что во всех событиях объекта Cursor Adapter всегда происходит вставка только одной записи за раз.
Прямое использование Pass-Through технологии через функцию SQLEXEC()
При таком способе работы программист как бы напрямую работает с MS SQL сервером. Сам формирует все команды, отсылаемые на сервер, получает результат, и сам же его обрабатывает. В этом случае не составляет никакого труда послать дополнительный запрос серверу на значение функции SCOPE_IDENTITY
После выполнения второго запроса в результирующем курсоре NewIdent в поле NewIdent первой записи и получим искомое значение. В данном синтаксисе и команда вставки и вызов функции SCOPE_IDENTITY() происходят в одном SCOPE. Поэтому, получаем нужное значение.
Использование Remote View
Предполагается, что создание новой записи уже произошло. Либо явно была дана команда TableUpdate(), либо Remote View находится в режиме строковой буферизации, и была попытка перехода на другую запись.
А почему во втором случае не было использовано, казалось бы, более очевидное решение поиска после Requery()? Что-то вроде
На это есть несколько причин.
Во-первых, Requery() предполагает выполнение запроса заново. Т.е. предполагается, что произошел сброс буфера всех записей Remote View. Но ведь это может быть не так. Например, сбрасывают буфер по одной записи за раз в цикле.
Во-вторых, обычно Remote View содержит в себе дополнительные условия отбора записей, и только что созданная запись может вообще не попасть в условия отбора. Т.е. после Requery() запись, хотя и будет физически создана на сервере, но в самом Remote View отображена не будет.
Использование Cursor Adapter
Объект Cursor Adapter был введен в FoxPro, начиная с версии Visual FoxPro 8. Он призван облегчить работу с удаленными данными при выполнении некоторых стандартных операций. В частности, операции создания новой записи. Через Cursor Adapter можно реализовать все три варианта получения значения поля со свойством IDENTITY в новой записи.
Значение, возвращаемое системной переменной @@IDENTITY
После того, как будет выполнен сброс буфера и произойдет физическая создание новой записи на MS SQL сервере, в объекте Cursor Adapter сработает событие AfterInsert. Вот в нем как раз и надо выполнить дополнительный запрос на сервер и прочитать значение системной переменной @@IDENTITY
Думаю, код достаточно понятный и не нуждается в пояснениях. После успешного создания новой записи на сервере при помощи SQLExec() для того же самого соединения определили значение @@IDENTITY и записали это значение в текущую запись курсора в ключевое поле.
В паре с другим новым свойством InsertCmdRefreshFieldList можно выполнить обновление, опираясь на значение системной переменной @@IDENTITY проще. Для этого всего лишь надо сделать следующие настройки объекта CursorAdapter
Значение, возвращаемое функцией SCOPE_IDENTITY()
Здесь все несколько сложнее. Дело в том, что действовать, так же как и в случае определения системной переменной @@IDENTITY нельзя. SQLExec() и Cursor Adapter работают в разных SCOPE. Поэтому при подобном подходе SCOPE_IDENTITY() всегда будет возвращать NULL. Чтобы преодолеть это противоречие используют специальные процедуры.
Для начала, надо создать на самом MS SQL сервере вот такую хранимую процедуру
Как видите, цель данной процедуры просто присвоить значение входного параметра выходному параметру. Теперь можно использовать эту процедуру для определения значения SCOPE_IDENTITY() в Cursor Adapter. Для этого в событии BeforeInsert делается такая подмена
В данном случае, по сути, формируется динамическая хранимая процедура. Т.е. не просто одна команда INSERT, а последовательность команд. Команды отделяются друг от друга символом точки с запятой, хотя это вовсе не обязательно. Достаточно отделять команды друг от друга простым пробелом.
Нахождение новой записи по значению других полей
Если у Вас Visual FoxPro 8, то Вам остается использовать метод аналогичный методу, примененному для поиска значения системной переменной @@IDENTITY. Т.е. в методе AfterInsert объекта Cursor Adapter выполнить дополнительный запрос через SQLExec()
Начиная с версии Visual FoxPro 9, в объекте Cursor Adapter появились дополнительные свойства, позволяющие автоматизировать эту процедуру без написания дополнительного кода
Следует отметить, что в этих свойствах указываются не имена полей таблицы собственно MS SQL сервера, а соответствующие им имена полей курсора, полученного на стороне клиента. Применительно к тому же примеру это будет примерно так:
Другими словами, опираясь на значение поля NickName, будет найдена запись в таблице на MS SQL сервере и прочитано значение поля TabID, после чего это значение будет записано в новую запись на стороне клиента.
Более подробно как обсуждение данной темы, так и примеры использования Вы можете посмотреть здесь
@@IDENTITY (Transact-SQL)
Is a system function that returns the last-inserted identity value.
Transact-SQL Syntax Conventions
Syntax
To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Return Types
numeric(38,0)
Remarks
After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions because they all return the last value inserted into the IDENTITY column of a table.
@@IDENTITY and SCOPE_IDENTITY 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 (Transact-SQL).
The scope of the @@IDENTITY function is current session on the local server on which it is executed. This function cannot be applied to remote or linked servers. To obtain an identity value on a different server, execute a stored procedure on that remote or linked server and have that stored procedure (which is executing in the context of the remote or linked server) gather the identity value and return it to the calling connection on the local server.
Replication may affect the @@IDENTITY value, since it is used within the replication triggers and stored procedures. @@IDENTITY is not a reliable indicator of the most recent user-created identity if the column is part of a replication article. You can use the SCOPE_IDENTITY() function syntax instead of @@IDENTITY. For more information, see SCOPE_IDENTITY (Transact-SQL)
The calling stored procedure or Transact-SQL statement must be rewritten to use the SCOPE_IDENTITY() function, which returns the latest identity used within the scope of that user statement, and not the identity within the scope of the nested trigger used by replication.
Examples
The following example inserts a row into a table with an identity column ( LocationID ) and uses @@IDENTITY to display the identity value used in the new row.
Автоинкременты и все, все, все…
Автор: Иван Бодягин
The RSDN Group
Источник: RSDN Magazine #1-2004
Опубликовано: 07.02.2004
Исправлено: 10.12.2016
Версия текста: 1.3
О чем речь
Уникальная идентификация записей в таблице, является практически основой реляционных СУБД. Вообще в реляционной теории предполагается, что если две записи ни чем друг от друга не отличаются, то это явная избыточность, и количество таких записей можно сократить до одной. Собственно вопросам этой самой идентификации, каковых возникает на удивление много, и посвящен этот FAQ.
Идентификация
Как уже говорилось уникальная идентификация записей – это основа реляционных СУБД. Вообще данный вопрос довольно тесно связан с давним теоретическим спором «Суррогатные ключи» vs. «Естественные ключи». Но поскольку существует замечательная статья Анатолия Тенцера “Естественные ключи против искуственных ключей”, то не думаю, что здесь имеет смысл освещать этот вопрос. Вне зависимости от используемого подхода, очень часто, по тем или иным причинам, данные, относящиеся непосредственно к приложению, не удается заведомо однозначно разделить по записям. И логично, что в таких случаях прибегают к помощи сервера, который генерирует искусственные идентификаторы для каждой записи.
Особого внимания заслуживает термин «уникальный». Уникальность серверного идентификатора имеет свои границы. В каких-то случаях гарантируется уникальность в пределах таблицы, то есть если не предпринимать специальных усилий, то есть шанс получить одинаковые идентификаторы в разных таблицах, что не всегда приемлемо. В каких-то случаях этот идентификатор гарантировано уникален в пределах БД, чего тоже не всегда бывает достаточно. И, наконец, есть идентификаторы, гарантирующие уникальность в пределах всех СУБД вообще…
Способ генерации и вид этого идентификатора стандартом не оговорен, да и вообще стандарт подобными мелочами не занимается, поэтому каждый производитель СУБД реализует эту функциональность так, как ему удобно. В итоге, мы имеем потрясающий набор различных способов, выудить из сервера уникальный идентификатор, беда только в том, что у каждого сервера этот способ свой. Однако, несмотря на все многообразие подходов, практически во всех СУБД, в том или ином виде, реализована автоматическая генерация последовательных идентификаторов, это так называемые «автоинкременты». Иными словами, при обращении к серверу за таким идентификатором гарантируется, что возвращенное число будет больше, чем число, полученное при предыдущем обращении.
| ПРИМЕЧАНИЕ Говоря «больше», я несколько упрощаю. В принципе, в большинстве СУБД, ничто не мешает задать и отрицательное приращение. То есть, правильнее было бы сказать, что каждое последующее число отличается от предыдущего на величину заданного приращения, которое вполне может быть отрицательным, но думаю, что в данном случае лучше упростить. Microsoft SQL ServerЭта СУБД предоставляет два способа получить обеспечить уникальность записи. Один работает в пределах таблицы и является автоинкрементным, а другой замахивается на уникальность «вообще», и в принципе подобное решение покрывает практически все потребности в уникальных идентификаторах. АвтоинкрементНачнем с обычного автоинкремента. В отличие от большинства других СУБД, здесь эта функциональность реализована не в виде отдельной функции, а в виде свойства таблицы, поэтому и гарантируется уникальность лишь в пределах таблицы, а не БД или экземпляра сервера. Для того чтобы не болела голова об уникальности записей, один из столбцов в таблице объявляется идентификатором, после этого, при добавлении каждой новой записи, в этом столбце автоматически появляется число, которое заведомо больше, чем то, которое было сгенерировано для предыдущей добавленной записи. Вообще здесь есть ряд тонкостей, но о них чуть позже. Итак, при объявлении столбца уникальным, с помощью ключевого слова IDENTITY устанавливается начальное значение и величина, на которую будет это значение увеличиваться при добавлении каждой записи. Здесь, как не сложно догадаться, seed – это самое первое значение, а increment – это величина, которая каждый раз будет прибавляться к предыдущему значению. По умолчанию seed и increment равны единице, то есть выставить у столбца свойство IDENTITY, равноценно выставлению IDENTITY(1,1). Ключевое слово IDENTITY может быть указано при создании CREATE TABLE, или изменении таблицы ALTER TABLE. При этом тип столбца должен быть tinyint, smallint, int, bigint, decimal(p,0) или numeric(p,0), то есть автоникремент должен быть целочисленным. Следует так же помнить, что только один столбец может быть объявлен IDENTITY. Таким образом, создание таблицы с автоинкрементным полем выглядит примерно так: Здесь столбец ID объявлен автоинкрементным, и при каждом добавлении новой записи в таблицу, в него автоматически будет записываться новое число, хотя бы на единицу большее того, что было записано при предыдущем добавлении. При этом явно писать значения в автоинкрементное поле нельзя, произойдет ошибка, и оператор вставки не отработает. Однако прямо в сообщении об ошибке есть подсказка как это ограничение обойти. Для того чтобы писать напрямую в автоинкрементный столбец, необходимо установить свойство IDENTITY_INSERT в ON. Но здесь другая тонкость, если при отключенной автогенерации не указать явно, какое значение необходимо вставить в автоинкрементное поле, то вставка опять-таки успехом не увенчается. Таким образом, возможно два варианта заполнения автоинкрементного столбца, либо этим занимается сервер, и тогда невозможно явно изменить значение в этом поле, либо это делается вручную, но тогда это поле обязательно к заполнению.
|
