30.11.2024
Последние:

Портал agro-sss.ru

Мы подобрали для вас самые интересные ответы на самые популярные вопросы "Как"

Как 

Scope identity sql что это

admin 0 Comments

SCOPE_IDENTITY (Transact-SQL)

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Scope identity sql что это. topic link. Scope identity sql что это фото. Scope identity sql что это-topic link. картинка Scope identity sql что это. картинка topic linkTransact-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

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.

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

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

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY return different values at the end of an INSERT statement on T1. @@IDENTITY returns the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() returns the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function returns the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

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.

Examples

A. Using @@IDENTITY and SCOPE_IDENTITY with triggers

Result set: This is how table TZ looks.

Result set: This is how TY looks:

Create the trigger that inserts a row in table TY when a row is inserted in table TZ.

FIRE the trigger and determine what identity values you obtain with the @@IDENTITY and SCOPE_IDENTITY functions.

Источник

SCOPE_IDENTITY (Transact-SQL)

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

Scope identity sql что это. topic link. Scope identity sql что это фото. Scope identity sql что это-topic link. картинка Scope identity sql что это. картинка topic linkСинтаксические обозначения в Transact-SQL

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Типы возвращаемых данных

numeric(38,0)

Примечания

Функции SCOPE_IDENTITY, IDENT_CURRENT и @@IDENTITY идентичны друг другу, так как возвращают значения, вставленные в столбцы идентификаторов.

Функция IDENT_CURRENT не ограничена областью действия и сеансом, но ограничена указанной таблицей. Функция IDENT_CURRENT возвращает значение, созданное для указанной таблицы в любом сеансе и области. Дополнительные сведения см. в статье IDENT_CURRENT (Transact-SQL).

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

Например, существует две таблицы, T1 и T2, и для таблицы T1 определен триггер INSERT. Когда в таблицу T1 вставляется строка, триггер срабатывает и добавляет строку в таблицу T2. В этом сценарии используются две области: вставка в таблицу T1 и вставка триггером в таблицу T2.

При условии, что столбец идентификаторов имеется в обеих таблицах, T1 и T2, функции @@IDENTITY и SCOPE_IDENTITY вернут разные значения в конце инструкции INSERT в таблице T1. Функция @@IDENTITY возвращает значение столбца идентификаторов, добавленное в текущем сеансе последним во всех областях. Это значение, вставленное в таблицу T2. Функция SCOPE_IDENTITY() возвращает значение IDENTITY, вставленное в таблицу T1. Это было последним добавлением, произошедшим в заданной области. Функция SCOPE_IDENTITY() возвращает значение NULL, если она была вызвана до того, как какая-либо инструкция INSERT была выполнена для столбца идентификаторов в этой области.

Неудачно завершившиеся инструкции и транзакции могут изменить текущий идентификатор таблицы и создать пропуски в значениях столбца идентификаторов. Для значения идентификатора никогда не производится откат, несмотря на то, что транзакция, пытавшаяся вставить в таблицу значение, не была зафиксирована. Например, если инструкция INSERT привела к ошибке из-за нарушения ограничения IGNORE_DUP_KEY, текущее значение идентификатора для таблицы все равно увеличивается.

Примеры

A. Использование функций @@IDENTITY и SCOPE_IDENTITY с триггерами

Результирующий набор: вот как выглядит таблица TZ:

Результирующий набор: вот как выглядит таблица TY:

Создайте триггер, вставляющий строку в таблицу TY при вставке строки в таблицу TZ.

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

Источник

Scope 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, после чего это значение будет записано в новую запись на стороне клиента.

Более подробно как обсуждение данной темы, так и примеры использования Вы можете посмотреть здесь

Источник

Автоинкременты и все, все, все…


Автор: Иван Бодягин
The RSDN Group
Источник: RSDN Magazine #1-2004

Опубликовано: 07.02.2004
Исправлено: 10.12.2016
Версия текста: 1.3

Scope identity sql что это. mag0104. Scope identity sql что это фото. Scope identity sql что это-mag0104. картинка Scope identity sql что это. картинка mag0104

О чем речь

Уникальная идентификация записей в таблице, является практически основой реляционных СУБД. Вообще в реляционной теории предполагается, что если две записи ни чем друг от друга не отличаются, то это явная избыточность, и количество таких записей можно сократить до одной. Собственно вопросам этой самой идентификации, каковых возникает на удивление много, и посвящен этот 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.

Но здесь другая тонкость, если при отключенной автогенерации не указать явно, какое значение необходимо вставить в автоинкрементное поле, то вставка опять-таки успехом не увенчается.

Таким образом, возможно два варианта заполнения автоинкрементного столбца, либо этим занимается сервер, и тогда невозможно явно изменить значение в этом поле, либо это делается вручную, но тогда это поле обязательно к заполнению.

ПРЕДУПРЕЖДЕНИЕ

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

Если же теперь опять вернуть серверу возможность вставлять номера в автоинкрементное поле, то следующее значение будет больше, на число указанное в increment при создании столбца, самого большого значения в этом поле. Не важно вручную было введено это самое большое значение или сгенерировано сервером.

Все это конечно здорово, но, как правило, просто уникально идентифицировать запись недостаточно, необходимо еще связать эту запись с записью из другой таблицы по этому самому идентификатору. А для этого надо уметь получать этот идентификатор сразу же после его генерации. Для выполнения этой задачи в Microsoft SQL Server существуют 3 функции: @@IDENTITY, SCOPE_IDENTITY() и IDENT_CURRENT().

Функция @@IDENTITY возвращает последнее значение, записанное севером в автоинкрементный столбец в текущей сессии. Что это означает? Если между вызовом INSERT и вызовом @@IDENTITY успеет пролезть вставка из другой сессии, то @@IDENTITY вернет идентификатор, который был записан при первой вставке. То есть, при пользовании @@IDENTITY нет необходимости заботиться о том, что параллельные вставки будут мешать друг другу при получении правильных идентификаторов, сервер все сделает сам.

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

Для того чтобы избежать таких неприятностей, служит SCOPE_IDENTITY(), который возвращает значение, записанное сервером в автоинкрементный столбец не только в рамках сессии, но и в рамках текущего пакета (batch).

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

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

Однако следует учитывать еще ряд нюансов. Во-первых, очевидно, что никто не гарантирует отсутствия «дырок» при автоматической генерации значений в столбце. А, во вторых, генерация нового значения для автоинкремента выполняется в не явной автономной транзакции. Это означает, что если сама по себе операция добавления записи не увенчается успехом, или транзакция, в которой будет производиться добавление, закончится отменой, то сервер следующее автоинкрементное значение сгенерирует так, как будто бы предыдущее добавление новой записи произошло успешно. И таким образом, образуется разрыв в автоматической нумерации.

И можно наблюдать разрыв в идентификации записей.

И еще один нюанс, даже если удалить все записи из таблицы, последующие идентификаторы, возвращаемые сервером, не обнулятся, а будут продолжать увеличиваться, как будто записи не удалялись. В некоторых случаях может понадобиться обнулить серверный генератор последовательностей или проинициализировать его каким-нибудь другим значением. Для этих целей существует системная функция DBCC CHECKIDENT. С ее помощью можно проверить счетчик «на правильность» и/или проинициализировать его новым значением. Если же необходимо сбросить счетчик в начальное значение при удалении всех записей, то можно воспользоваться не оператором DELETE, а оператором TRUNCATE. В этом случае счетчик автоматически сбросится в первоначальное значение, но следует помнить, что TRUNCATE нельзя пользоваться, если на таблицу ссылаются внешние ключи других таблиц.

Существуют так же специальные команды, с помощью которых можно узнать начальное значение генератора определенной таблицы и приращение, то есть те значения, которые были установлены при указании IDENTITY. Это IDENT_INCR и IDENT_SEED соответственно.

Таким образом, в свете всего вышесказанного, самый типичный сценарий применения автоинкремента выглядит примерно так:

Глобальный идентификатор

Идентификатор в пределах таблицы – это конечно здорово, но отнюдь не предел мечтаний. В некоторых случаях вовсе не лишней была бы возможность получить запись, гарантировано уникальную в пределах базы данных, экземпляра сервера или даже в пределах всех серверов предприятия. Для уникальности в пределах БД тип данных int, может еще и сгодится, но вот если брать что-то более глобальное, то четырех миллиардов уникальных значений может и не хватить. Max(int) – это много, но не так много как хотелось бы, проблема в том, что назначая новое автоинкрементное поле, которое по идее должно быть гарантировано уникальным при любых обстоятельствах, приходится думать о других уникальных полях, чтобы ни коим образом диапазоны их идентификаторов не пересеклись, а отсюда и совершенно неестественные ограничения.

Для разработчиков под ADO.Net есть еще один удобный повод использовать подобный идентификатор. Поскольку, на самом деле, это обычный GUID, то при добавлении записей в отсоединенный набор данных (Dataset), можно совершенно спокойно получить этот идентификатор на клиенте и быть уверенным, что на сервере такого нет.

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

Производительность

Довольно часто возникает вопрос о производительности этих двух способов. Естественно, что в общем случае работа с identity быстрее, чем с GUID. Но заметно это становится только на больших объемах данных, порядка десятков миллионов записей на таблицу. На таких объемах выигрыш от использования identity составляет от 5 до 20% в зависимости от сложности запросов, но эти данные очень приблизительные и всерьез на них ориентироваться не стоит. Если стоит проблема выбора, то лучше померить на конкретных данных и структуре базы.

Основное замедление при работе с GUID возникает из-за того, что он длиннее. Но вовсе не потому, что четыре байта сравнить быстрее, чем шестнадцать. Дело в том, что, как правило, по идентификатору строится индекс, для более эффективного поиска. Узлы индекса хранятся на диске постранично, каждая страница имеет фиксированную длину. И чем больше ключей влезает на одну страницу, тем меньше обращений к диску приходится делать при поиске какого-либо значения, и, как следствие, тем эффективней поиск, поскольку дисковые операции самые дорогие. Таким образом, эффективность поиска по индексу зависит от размера ключа, а при использовании GUID ключ длиннее.

Timestamp (rowvesion)

Строго говоря, существует еще один тип данных, предназначенный для идентификации, но идентификации несколько другого рода. Тип данных rowversion предназначен для идентификации версии строки в пределах базы данных.

ПРИМЕЧАНИЕ

Вообще можно указывать как timestamp (старое название), так и rowversion, но Microsoft рекомендует использовать rowversion, поскольку, во-первых, это более точно отражает суть этого типа данных, а во вторых, ключевое слово timestamp зарезервировано в стандарте для другого типа.

Номер строки

Просьба подсказать способ вывести номер строки является лидером хит-парада. Этот вопрос по сути своей не корректен и, тем не менее, встречается наиболее часто. Но и вопрошающих тоже можно понять, ведь попросить вывести номер строки вполне естественно. Но дело в том, что в реляционной теории вообще нет такого понятия как «номер строки», в этом смысле записи в таблице абсолютно равноценны. Более того, если задуматься, то становится ясно, что в принципе нет таких задач, которые требовали бы нумерации строк на сервере перед отправкой их клиенту. Нет смысла просить номер у хранилища данных, поскольку эта информация не постоянная и зависит от порядка отображения, а, следовательно, не находится в компетенции хранилища. Клиентскому же приложению, занимающемуся отображением полученной информации, не составляет ни какого труда при получении строк пронумеровать их в требуемом порядке.

Безусловно, в каждом сервере баз данных существует способ, возможно относительно легкий, извернуться и пронумеровать строки нужным образом перед отправкой их клиенту, но подобных решений надо по возможности избегать.

ANSI SQL

Этот способ пронумеровать выводимые записи на сервере по идее должен работать на любых СУБД, минимально удовлетворяющих требованиям стандарта ANSI SQL.

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

Microsoft SQL Server

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

Oracle

Здесь можно отделаться более простым запросом, но тоже не совсем тривиальным. Эта СУБД дает некоторый доступ к своей внутренней информации, и внутри у нее записи пронумерованы. Но проблема в том, что сервер нумерует строки для своих нужд до сортировки, поэтому приходится делать вложенный запрос с сортировкой.

Но еще раз хочу напомнить, что если есть возможность избавить сервер от нумерации, значит надо сервер от нее избавлять. В приложении должно быть что-то сильно не так, если требуется подобная функциональность.

Постраничный вывод

Довольно часто, особенно при построении web-сайтов, приходится сталкиваться с задачей постраничного вывода записей. В некоторых СУБД есть специальные параметры для подобного вывода, а в некоторых все приходится делать самостоятельно. Но в любом случае серверу приходится выполнять примерно один и тот же объем работы. Сначала необходимо выбрать все записи, затем отсортировать их, а затем отправить клиенту нужный диапазон. Очевидно, что выдача диапазона без сортировки смысла не имеет, так как СУБД не гарантирует выдачу записей в каком-либо определенном порядке.

Microsoft SQL Server

Здесь можно придумать довольно много решений и все они будут по-своему хороши.

Во-первых, в данном случае, на удивление эффективным может оказаться применение динамических запросов. При удачном стечении обстоятельств (нужные индексы в нужных местах, и достаточно простые выборки пусть и на большом объеме данных) этот способ является самым быстрым.

Однако при таком подходе следует быть внимательным, поскольку в случае не оптимально написанного запроса производительность падает довольно резко. Впрочем, на таких объемах, где это будет заметно, к написанию любого запроса надо подходить вдумчиво и аккуратно.

Если же вы испытываете подсознательный страх и неприязнь к динамическим запросам, то есть еще ряд способов. Например, можно сделать практически то же самое, что и приведенный выше запрос, но разложить его по частям, воспользовавшись временной таблицей. Из таблицы (или таблиц), которую нужно отобразить постранично, берется поле, однозначно идентифицирующее каждую запись, и скидывается в нужном порядке во временную табличку с автоинкрементным полем. Причем скидывается не все, а только до последней записи отображаемой страницы. А результирующая выборка делается с объединением с вышеупомянутой временной таблицей, ограничивая диапазон по автоинкрементному полю.

Не составляет никаких проблем написать подобную хранимую процедуру, которая разбивала бы на станицы какую угодно комбинацию таблиц подобным образом.

Но можно использовать курсоры, и с помощью них осуществлять смещение до нужной записи и производить необходимую выборку.

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

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

Что касается относительной скорости всех методов, то тут можно смело использовать то, к чему душа больше лежит, разница составляет не больше нескольких процентов.

Ограничение первой выборки последней записью отображаемой страницы, позволяет довольно сильно повысить скорость выполнения запроса, так как в подавляющем большинстве случаев дальше второй-третьей страницы пользователи не заглядывают. Для очень больших выборок, критичных ко времени обращения к последним страницам, можно придумать хитрую процедуру, которая умела бы различать начальные и конечные страницы, и к последним применять обратную сортировку при выборке. Таким образом, к первым и последним страницам был бы наиболее быстрый доступ.

Oracle

В Оракле проблема постраничного вывода решается несколько проще. Стандартный способ, подходящий для подавляющего большинства задач выглядит примерно так:

Однако при большом желании, так же можно придумать хитрую процедуру, которая по-разному обрабатывала бы начальные и конечные станицы большой выборки.

Yukon

В новой версии Microsoft SQL Server специальных ключевых слов, для подобной функциональности не добавилось, но, тем не менее, постраничную выборку можно немного упростить. В этой версии ключевое слово TOP стало понимать переменные, и даже запросы, и постраничный вывод можно организовать примерно так:

Скорости это не добавит, так как сервер все равно выполняет примерно одну и ту же работу, но количество нажатий на клавиатуру несколько уменьшится, что, возможно, продлит срок ее службы. Ну и код выглядит компактнее, что конечно приятно.

Упростить постраничный вывод вряд ли возможно. Существуют СУБД, в которых введен специальный синтаксис для вывода данных постранично, но это не более чем syntactic sugar, так как производятся те же действия, что и в примерах выше, просто часть реализации остается за кадром.

Источник

Вам также понравится

Как измерить подмышки для одежды

admin 0

Адаптовит что это такое

admin 0

А что взамен то дружок а что взамен то

admin 0

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Все права сохранены. © 2024 Портал agro-sss.ru Внимание! Информация, опубликованная на сайте, носит исключительно ознакомительный характер и не является рекомендацией к применению. Материалы могут содержать информацию, предназначенную для пользователей старше 18 лет. 18+.