Mssql identity: SQL Server Identity Column By Practical Examples

Содержание

Совместное использование IDENTITY в MSSQL и TDataSet

← →
AlexL

(2002-12-12 05:27)
[0]

Народ!

Общая ситуация:

Есть таблица в MSSQL с ключевым полем Id, имеющим тип int IDENTITY(1, 1) (Primary Index), и есть TDataSet по этой таблице.

Возникла следующая проблема:

При попытке добавления данных в указанный TDataSet возникает ошибка, что полю Id нельзя присвоить значение. Менял тип указанного поля в TDataSet на ptIncrement — результат тот же.

← →
KSergey

(2002-12-12 09:23)
[1]

А этому полю программно присваивается значение?

← →
Shaman

(2002-12-12 10:15)
[2]

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

Ежели че другое придумаешь, дай знать.

← →
wicked

(2002-12-12 11:50)
[3]

как вариант — сменить FieldKind поля Id с fkData на fkInternalCalc… у меня такое срабатывало с TBetterADODataSet, возможно, сработает и с другими DataSet»ами….

естественно, речь идёт НЕ о BDE….

← →
Севостьянов Игорь

(2002-12-12 12:32)
[4]

Что-то я не пойму автора вопроса

Интересно как ты в ДатаСет добавляешь запись — не принцип, а код ?

И еще объясни мне — зачем существет понятие IDENTITY (@@IDENTITY) ?

Насколько мне известно — это поле должно формироваться самим SQL Server»ом

@@IDENTITY Returns the last-inserted identity value.

Syntax

@@IDENTITY

Return Types

numeric

Remarks

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value 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. 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.

@@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.

Examples

This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)

VALUES («Accountant»,12,125)

SELECT @@IDENTITY AS «Identity»

Отчет о текущем значении идентификатора SQL Server для всех таблиц

Автор: Eli Leiba |
Комментарии (4) | Связанный: Еще > Личности

Проблема

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

Решение

Мое решение включает создание хранимой процедуры T-SQL, содержащей идентификатор
отчет столбца для всей базы данных, выполнив запрос, который присоединяется к INFORMATION_SCHEMA.TABLES
и системные представления INFORMATION_SCHEMA.COLUMNS. Эти два взгляда связаны
столбцы имен каталога, схемы и таблицы.

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

Идентификатор может быть одним из следующих типов данных:

Целое число (4 байта)
Большое целое (8 байт)
Маленькое целое (2 байта)
Миниатюрное целое число (1 байт)

Десятичное или числовое значение с нулевой шкалой (0) 93-1 =
999 .

Максимальное значение оценивается с помощью
CASE оператор запроса.

Хранимая процедура SQL для отчета о значении удостоверения

 -- ============================================== =================
-- Автор: Эли Лейба
-- Дата создания: 06-2018
-- Описание:
-- Создание подробного отчета о столбцах идентификации для всей базы данных.
-- Это включает в себя базу данных, схему, таблицу, столбец, тип,
-- Начальное значение, приращение и ограничение значения типа данных
-- ================================================ ================
СОЗДАТЬ ПРОЦЕДУРУ dbo.usp_DetailedIdentityColumnsReport
КАК
ВЫБЕРИТЕ A.TABLE_CATALOG КАК КАТАЛОГ,
   A.TABLE_SCHEMA КАК "СХЕМА",
   A.TABLE_NAME КАК "ТАБЛИЦА",
   B.COLUMN_NAME КАК "COLUMN",
   IDENT_SEED (A.TABLE_NAME) КАК Сид,
   IDENT_INCR (A.TABLE_NAME) КАК Увеличение,
   IDENT_CURRENT (ИМЯ_ТАБЛИЦЫ) КАК Curr_Value,
   B.DATA_TYPE как "Тип",
   Type_Limit = CASE ниже (B.DATA_TYPE)
      КОГДА 'большой'
         ТОГДА 9223 372 036 854 775 807 '
      КОГДА 'целое'
         ТОГДА "2 147 483 647"
      КОГДА 'маленький'
         ТОГДА '32 767'
      КОГДА 'маленький'
         ТОГДА '255'
      КОГДА 'десятичный'
         ЗАТЕМ РЕПЛИКАЦИЯ ('9', B. NUMERIC_PRECISION)
КОГДА 'числовой'
         ЗАТЕМ РЕПЛИКАЦИЯ ('9', B.NUMERIC_PRECISION)
      КОНЕЦ
ИЗ INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
ГДЕ A.TABLE_CATALOG = B.TABLE_CATALOG И
A.TABLE_SCHEMA = B.TABLE_SCHEMA И
   A.TABLE_NAME = B.TABLE_NAME И
   COLUMNPROPERTY (OBJECT_ID (B.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') = 1 И
   OBJECTPROPERTY (OBJECT_ID (A.TABLE_NAME), 'TableHasIdentity') = 1 И
   A.TABLE_TYPE = 'БАЗОВАЯ ТАБЛИЦА'
ЗАКАЗАТЬ ПО A.TABLE_SCHEMA, A.TABLE_NAME
ИДТИ

Пример использования процедуры

Давайте создадим подробный отчет о личности в базе данных «Борей»:

 ИСПОЛЬЗОВАНИЕ Северный ветер
ИДТИ
exec dbo.usp_IdentityColumnsReport

И результаты (на моем сервере):

Следующие шаги

Вы можете создать и скомпилировать эту простую процедуру в базе данных вашего приложения
и используйте его как простой инструмент T-SQL для создания этого отчета.
Процедура была протестирована для версии SQL Server: SQL Server 2014, 2016 и
2017, но должно работать и для других версий.

Об авторе

Эли Лейба — старший администратор баз данных приложений, преподаватель и старший консультант по базам данных с 19-летним опытом работы с РСУБД.

Посмотреть все мои советы

Комментарии к этой статье

Суббота, 20 февраля 2021 г. — 22:40:57 — Эрик Э. Айзекс

Вернуться к началу (88273)

Отличный совет по SQL Server! Я обнаружил, что если я упорядочу следующее, я увижу таблицы, которые требуют внимания, вверху (при условии отсутствия проблем с TINYINT и т. д., которые не являются проблемой для меня).

ORDER BY
LEN(FORMAT(IDENT_CURRENT(A.TABLE_NAME) , ‘N0’)) DESC
, FORMAT(IDENT_CURRENT(A.TABLE_NAME) , ‘N0’) DESC
, A.TABLE_SCHEMA
, A.TABLE_NAME;

, среда, 23 сентября 2020 г. — 18:13:05 — Кори Шанкс

Вернуться к началу (86527)

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

т.е. выбрать где type_limit — curr_value < 200 000.

Вторник, 13 августа 2019 г. — 12:34:45 — Слава

Вернуться к началу (82048)

Привет, Эли!

Спасибо за скрипт.

Чтобы использовать его с несколькими именами схем, требуется некоторое исправление: мы должны использовать «A.TABLE_SCHEMA + ‘.’ + A.TABLE_NAME» вместо «A.TABLE_NAME» во всех вызовах системных функций.

ХТН.

 ВЫБРАТЬ A.TABLE_CATALOG КАК КАТАЛОГ,
A.TABLE_SCHEMA КАК "СХЕМА",
A.TABLE_NAME КАК "ТАБЛИЦА",
B.COLUMN_NAME КАК "COLUMN",
IDENT_SEED (A.TABLE_SCHEMA + '. ' + A.TABLE_NAME) КАК начальное число,
IDENT_INCR (A.TABLE_SCHEMA + '.' + A.TABLE_NAME) КАК Инкремент,
IDENT_CURRENT (A.TABLE_SCHEMA + '.' + A.TABLE_NAME) КАК Curr_Value,
B.DATA_TYPE как "Тип",
Type_Limit = CASE ниже (B.DATA_TYPE)
КОГДА 'большой'
ТОГДА 9223 372 036 854 775 807 '
КОГДА 'целое'
ТОГДА "2 147 483 647"
КОГДА 'маленький'
ТОГДА '32 767'
КОГДА 'маленький'
ТОГДА '255'
КОГДА 'десятичный'
ЗАТЕМ РЕПЛИКАЦИЯ ('9', B.NUMERIC_PRECISION)
КОГДА 'числовой'
ЗАТЕМ РЕПЛИКАЦИЯ ('9', B.NUMERIC_PRECISION)
КОНЕЦ
ИЗ INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
ГДЕ A.TABLE_CATALOG = B.TABLE_CATALOG И
A.TABLE_SCHEMA = B.TABLE_SCHEMA И
A.TABLE_NAME = B.TABLE_NAME И
COLUMNPROPERTY (OBJECT_ID (A.TABLE_SCHEMA + '.' + A.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') = 1 И
OBJECTPROPERTY (OBJECT_ID (A.TABLE_SCHEMA + '.' + A.TABLE_NAME), 'TableHasIdentity') = 1 И
A.TABLE_TYPE = 'БАЗОВАЯ ТАБЛИЦА'
ЗАКАЗАТЬ ПО A.TABLE_SCHEMA, A.TABLE_NAME

Четверг, 28 июня 2018 г. – 7:34:33 – Марсело Миорелли

Вернуться к началу (76450)

Привет Эли,

спасибо за сборку этого скрипта.

Я использовал его, однако мне нужно внести 2 изменения:

1) необходимо добавить столбец «не для репликации», потому что это очень важная информация при работе со столбцами идентификаторов (если у вас есть репликация)

2) уменьшил вывод столбца «Type_limit», иначе было бы 8000, а это не подходит для моих требований.

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

 ВЫБЕРИТЕ
   A.TABLE_CATALOG КАК КАТАЛОГ,
   A.TABLE_SCHEMA КАК "СХЕМА",
   A.TABLE_NAME КАК "ТАБЛИЦА",
   B.COLUMN_NAME КАК "COLUMN",
   [Не для репликации] = СЛУЧАЙ, КОГДА COLUMNPROPERTY( OBJECT_ID (B.TABLE_NAME),B.COLUMN_NAME,'IsIdNotForRepl') = 1 THEN 'Да'
       ELSE 'Нет' КОНЕЦ,
   IDENT_SEED (A. TABLE_NAME) КАК Сид,
   IDENT_INCR (A.TABLE_NAME) КАК Увеличение,
   IDENT_CURRENT (ИМЯ_ТАБЛИЦЫ) КАК Curr_Value,
   B.DATA_TYPE как "Тип",
   Type_Limit = LEFT (CASE ниже (B.DATA_TYPE)
      КОГДА 'большой'
         ТОГДА 9223 372 036 854 775 807 '
      КОГДА 'целое'
         ТОГДА "2 147 483 647"
      КОГДА 'маленький'
         ТОГДА '32 767'
      КОГДА 'маленький'
         ТОГДА '255'
      КОГДА 'десятичный'
         THEN CASE WHEN B.NUMERIC_PRECISION

SQL Server: изменить начальное значение идентификатора

спросил
4 года, 7 месяцев назад

Изменено
4 года, 7 месяцев назад

Просмотрено
7к раз

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

У меня есть таблица Customer . В этой таблице есть столбец customer_id , который является столбцом идентификаторов. Я хочу изменить начальное значение/приращение идентификатора с (1,1) на (200,1) без изменения идентификаторов клиентов для существующих данных, которые я буду вставлять в таблицу.

Старые данные: 101-108. По сути, мы хотим сохранить старые данные такими же, чтобы они совпадали со старыми записями в других системах, но мы хотим, чтобы новые данные начинались с 200.

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

sql
sql-сервер
смс

Вы можете использовать DBCC CHECKIDENT:

 DBCC CHECKIDENT ('dbo. customer', RESEED, 200)

Это изменит текущее начальное значение столбца идентификаторов указанной таблицы. Если вам нужно вставить определенные значения идентификатора, вы можете SET IDENTITY_INSERT ON в инструкции вставки.

IDENTITY_INSERT

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

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.