Sql как изменить значение счетчика - Исправление ошибок и поиск оптимальных решений проблем

In MySQL, I have a table, and I want to set the auto_increment value to 5 instead of 1. Is this possible and what query statement does this?

John Joe

12.1k16 gold badges68 silver badges129 bronze badges

asked Jun 9, 2009 at 15:01

You can use ALTER TABLE to change the auto_increment initial value:

ALTER TABLE tbl AUTO_INCREMENT = 5;

See the MySQL reference for more details.

answered Jun 9, 2009 at 15:07

Yes, you can use the ALTER TABLE t AUTO_INCREMENT = 42 statement. However, you need to be aware that this will cause the rebuilding of your entire table, at least with InnoDB and certain MySQL versions. If you have an already existing dataset with millions of rows, it could take a very long time to complete.

In my experience, it’s better to do the following:

BEGIN WORK;
-- You may also need to add other mandatory columns and values
INSERT INTO t (id) VALUES (42);
ROLLBACK;

In this way, even if you’re rolling back the transaction, MySQL will keep the auto-increment value, and the change will be applied instantly.

You can verify this by issuing a SHOW CREATE TABLE t statement. You should see:

> SHOW CREATE TABLE t G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
...
) ENGINE=InnoDB AUTO_INCREMENT=43 ...

answered Sep 13, 2012 at 7:08

CosimoCosimo

2,8061 gold badge24 silver badges25 bronze badges

How to auto increment by one, starting at 10 in MySQL:

create table foobar(
  id             INT PRIMARY KEY AUTO_INCREMENT,
  moobar         VARCHAR(500)
); 
ALTER TABLE foobar AUTO_INCREMENT=10;

INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");

select * from foobar;

'10', 'abc'
'11', 'def'
'12', 'xyz'

This auto increments the id column by one starting at 10.

Auto increment in MySQL by 5, starting at 10:

drop table foobar
create table foobar(
  id             INT PRIMARY KEY AUTO_INCREMENT,
  moobar         VARCHAR(500)
); 
SET @@auto_increment_increment=5;
ALTER TABLE foobar AUTO_INCREMENT=10;

INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");

select * from foobar;
'11', 'abc'
'16', 'def'
'21', 'xyz'

This auto increments the id column by 5 each time, starting at 10.

answered Aug 18, 2014 at 14:54

Eric LeschinskiEric Leschinski

143k95 gold badges408 silver badges332 bronze badges

You can also do it using phpmyadmin. Just select the table than go to actions. And change the Auto increment below table options. Don’t forget to click on start

answered Sep 25, 2017 at 19:52

North-WindNorth-Wind

1461 silver badge12 bronze badges

Procedure to auto fix AUTO_INCREMENT value of table

DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(64))
BEGIN
    DECLARE _max_stmt VARCHAR(1024);
    DECLARE _stmt VARCHAR(1024);    
    SET @inc := 0;

    SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(`id`), 0) + 1 INTO @inc FROM ', _table);
    PREPARE _max_stmt FROM @MAX_SQL;
    EXECUTE _max_stmt;
    DEALLOCATE PREPARE _max_stmt;

    SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT =  ', @inc);
    PREPARE _stmt FROM @SQL;
    EXECUTE _stmt;
    DEALLOCATE PREPARE _stmt;
END//
DELIMITER ;

CALL update_auto_increment('your_table_name')

answered Aug 29, 2014 at 16:00

If you need this procedure for variable fieldnames instead of id this might be helpful:

DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(128), _fieldname VARCHAR(128))
BEGIN
    DECLARE _max_stmt VARCHAR(1024);
    DECLARE _stmt VARCHAR(1024);    
    SET @inc := 0;

    SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(',_fieldname,'), 0) + 1 INTO @inc FROM ', _table);
    PREPARE _max_stmt FROM @MAX_SQL;
    EXECUTE _max_stmt;
    DEALLOCATE PREPARE _max_stmt;

    SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT =  ', @inc);
    PREPARE _stmt FROM @SQL;
    EXECUTE _stmt;
    DEALLOCATE PREPARE _stmt;
END //
DELIMITER ;

CALL update_auto_increment('your_table_name', 'autoincrement_fieldname');

answered Feb 9, 2016 at 11:41

just export the table with data ..
then copy its sql like

CREATE TABLE IF NOT EXISTS `employees` (
  `emp_badgenumber` int(20) NOT NULL AUTO_INCREMENT,
  `emp_fullname` varchar(100) NOT NULL,
  `emp_father_name` varchar(30) NOT NULL,
  `emp_mobile` varchar(20) DEFAULT NULL,
  `emp_cnic` varchar(20) DEFAULT NULL,
  `emp_gender` varchar(10) NOT NULL,
  `emp_is_deleted` tinyint(4) DEFAULT '0',
  `emp_registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `emp_overtime_allowed` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`emp_badgenumber`),
  UNIQUE KEY `bagdenumber` (`emp_badgenumber`),
  KEY `emp_badgenumber` (`emp_badgenumber`),
  KEY `emp_badgenumber_2` (`emp_badgenumber`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111121326 ;

now change auto increment value and execute sql.

answered Jun 14, 2014 at 7:40

Источник

I googled and found this question, but the answer I am really looking for fulfils two criteria:

using purely MySQL queries
reset an existing table auto-increment to max(id) + 1

Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

the table in question is InnoDB
the table uses the field id with type as int as primary key
the only way to do this purely in MySQL is to use stored procedure
my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc;
      EXECUTE stmt;
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt;

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

When I run, I simply select the stored procedure and press Run Selection

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.

Источник

I googled and found this question, but the answer I am really looking for fulfils two criteria:

using purely MySQL queries
reset an existing table auto-increment to max(id) + 1

Since I couldn’t find exactly what I want here, I have cobbled the answer from various answers and sharing it here.

Few things to note:

the table in question is InnoDB
the table uses the field id with type as int as primary key
the only way to do this purely in MySQL is to use stored procedure
my images below are using SequelPro as the GUI. You should be able to adapt it based on your preferred MySQL editor
I have tested this on MySQL Ver 14.14 Distrib 5.5.61, for debian-linux-gnu

Step 1: Create Stored Procedure

create a stored procedure like this:

DELIMITER //
CREATE PROCEDURE reset_autoincrement(IN tablename varchar(200))
BEGIN

      SET @get_next_inc = CONCAT('SELECT @next_inc := max(id) + 1 FROM ',tablename,';');
      PREPARE stmt FROM @get_next_inc;
      EXECUTE stmt;
      SELECT @next_inc AS result;
      DEALLOCATE PREPARE stmt;

      set @alter_statement = concat('ALTER TABLE ', tablename, ' AUTO_INCREMENT = ', @next_inc, ';');
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Then run it.

Before run, it looks like this when you look under Stored Procedures in your database.

When I run, I simply select the stored procedure and press Run Selection

Note: the delimiters part are crucial. Hence if you copy and paste from the top selected answers in this question, they tend not to work for this reason.

After I run, I should see the stored procedure

If you need to change the stored procedure, you need to delete the stored procedure, then select to run again.

Step 2: Call the stored procedure

This time you can simply use normal MySQL queries.

call reset_autoincrement('products');

Originally from my own SQL queries notes in https://simkimsia.com/reset-mysql-autoincrement-to-max-id-plus-1/ and adapted for Stack Overflow.

Источник

In MySQL, I have a table, and I want to set the auto_increment value to 5 instead of 1. Is this possible and what query statement does this?

John Joe

12.1k16 gold badges68 silver badges129 bronze badges

asked Jun 9, 2009 at 15:01

You can use ALTER TABLE to change the auto_increment initial value:

ALTER TABLE tbl AUTO_INCREMENT = 5;

See the MySQL reference for more details.

answered Jun 9, 2009 at 15:07

In my experience, it’s better to do the following:

BEGIN WORK;
-- You may also need to add other mandatory columns and values
INSERT INTO t (id) VALUES (42);
ROLLBACK;

In this way, even if you’re rolling back the transaction, MySQL will keep the auto-increment value, and the change will be applied instantly.

You can verify this by issuing a SHOW CREATE TABLE t statement. You should see:

> SHOW CREATE TABLE t G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
...
) ENGINE=InnoDB AUTO_INCREMENT=43 ...

answered Sep 13, 2012 at 7:08

CosimoCosimo

2,8061 gold badge24 silver badges25 bronze badges

How to auto increment by one, starting at 10 in MySQL:

create table foobar(
  id             INT PRIMARY KEY AUTO_INCREMENT,
  moobar         VARCHAR(500)
); 
ALTER TABLE foobar AUTO_INCREMENT=10;

INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");

select * from foobar;

'10', 'abc'
'11', 'def'
'12', 'xyz'

This auto increments the id column by one starting at 10.

Auto increment in MySQL by 5, starting at 10:

drop table foobar
create table foobar(
  id             INT PRIMARY KEY AUTO_INCREMENT,
  moobar         VARCHAR(500)
); 
SET @@auto_increment_increment=5;
ALTER TABLE foobar AUTO_INCREMENT=10;

INSERT INTO foobar(moobar) values ("abc");
INSERT INTO foobar(moobar) values ("def");
INSERT INTO foobar(moobar) values ("xyz");

select * from foobar;
'11', 'abc'
'16', 'def'
'21', 'xyz'

This auto increments the id column by 5 each time, starting at 10.

answered Aug 18, 2014 at 14:54

Eric LeschinskiEric Leschinski

143k95 gold badges408 silver badges332 bronze badges

You can also do it using phpmyadmin. Just select the table than go to actions. And change the Auto increment below table options. Don’t forget to click on start

answered Sep 25, 2017 at 19:52

North-WindNorth-Wind

1461 silver badge12 bronze badges

Procedure to auto fix AUTO_INCREMENT value of table

DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(64))
BEGIN
    DECLARE _max_stmt VARCHAR(1024);
    DECLARE _stmt VARCHAR(1024);    
    SET @inc := 0;

    SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(`id`), 0) + 1 INTO @inc FROM ', _table);
    PREPARE _max_stmt FROM @MAX_SQL;
    EXECUTE _max_stmt;
    DEALLOCATE PREPARE _max_stmt;

    SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT =  ', @inc);
    PREPARE _stmt FROM @SQL;
    EXECUTE _stmt;
    DEALLOCATE PREPARE _stmt;
END//
DELIMITER ;

CALL update_auto_increment('your_table_name')

answered Aug 29, 2014 at 16:00

If you need this procedure for variable fieldnames instead of id this might be helpful:

DROP PROCEDURE IF EXISTS update_auto_increment;
DELIMITER //
CREATE PROCEDURE update_auto_increment (_table VARCHAR(128), _fieldname VARCHAR(128))
BEGIN
    DECLARE _max_stmt VARCHAR(1024);
    DECLARE _stmt VARCHAR(1024);    
    SET @inc := 0;

    SET @MAX_SQL := CONCAT('SELECT IFNULL(MAX(',_fieldname,'), 0) + 1 INTO @inc FROM ', _table);
    PREPARE _max_stmt FROM @MAX_SQL;
    EXECUTE _max_stmt;
    DEALLOCATE PREPARE _max_stmt;

    SET @SQL := CONCAT('ALTER TABLE ', _table, ' AUTO_INCREMENT =  ', @inc);
    PREPARE _stmt FROM @SQL;
    EXECUTE _stmt;
    DEALLOCATE PREPARE _stmt;
END //
DELIMITER ;

CALL update_auto_increment('your_table_name', 'autoincrement_fieldname');

answered Feb 9, 2016 at 11:41

just export the table with data ..
then copy its sql like

CREATE TABLE IF NOT EXISTS `employees` (
  `emp_badgenumber` int(20) NOT NULL AUTO_INCREMENT,
  `emp_fullname` varchar(100) NOT NULL,
  `emp_father_name` varchar(30) NOT NULL,
  `emp_mobile` varchar(20) DEFAULT NULL,
  `emp_cnic` varchar(20) DEFAULT NULL,
  `emp_gender` varchar(10) NOT NULL,
  `emp_is_deleted` tinyint(4) DEFAULT '0',
  `emp_registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `emp_overtime_allowed` tinyint(4) DEFAULT '1',
  PRIMARY KEY (`emp_badgenumber`),
  UNIQUE KEY `bagdenumber` (`emp_badgenumber`),
  KEY `emp_badgenumber` (`emp_badgenumber`),
  KEY `emp_badgenumber_2` (`emp_badgenumber`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=111121326 ;

now change auto increment value and execute sql.

answered Jun 14, 2014 at 7:40

Источник

Вставка значений identity вручную

По умолчанию невозможно вставить вручную значение непосредственно в столбец identity, но значения identity могут быть введены вручную, если включить сессионную опцию. Чтобы выяснить, что произойдет при попытке вставить значение identity без включения свойства Identity Insert, выполните код в листинге 1.

Листинг 1: Попытка вставить значение identity

CREATE TABLE Widget(WidgetID INT NOT NULL IDENTITY, 

       WidgetName NVARCHAR(50), WidgetDesc NVARCHAR(200));

INSERT INTO Widget

VALUES (110,'MyNewWidget','New widget to test insert');

Вставка значения 110 в столбец identity, наряду со значениями остальных столбцов, в таблицу Widget вернет ошибку, показанную ниже:

В сообщении об ошибке ясно говорится, что вы не можете явно вставлять значение identity, если не указываете список столбцов в операторе INSERT, и свойство IDENTITY_INSERT для таблицы Widget не установлено в ON.

Свойство IDENTITY_INSERT является сессионным, и оно управляет тем, может ли вставляться значение identity или нет. По умолчанию значение этого свойство равно OFF, но оно может быть включено для таблицы Widget с помощью кода в листинге 2.

Листинг 2: Включение свойства IDENTITY_INSERT

SET IDENTITY_INSERT Widget ON;

После включения свойства IDENTITY_INSERT для таблицы Widget можно выполнить код в листинге 3 и не получить ошибки.

Листинг 3: Код со списком столбцов, требуемым для вставки значения identity

INSERT INTO Widget(WidgetID,WidgetName,WidgetDesc) 

 VALUES (110,'MyNewWidget','New widget to test insert');

Только одна таблица в сессии может иметь включенным свойство INDENTITY_INSERT в одно и то же время. Если вам потребуется вставить значения identity более чем в одну таблицу, вам сначала нужно выключить свойство INDENTITY_INSERT для первой таблицы, используя код в листинге 4, до включения свойства INDENTITY_INSERT для другой таблицы.

Листинг 4: Выключение сессионного свойства INDENTITY_INSERT

SET IDENTITY_INSERT Widget OFF;

Следует соблюдать осторожность при вставке значений identity вручную. SQL Server не требует от значений identity уникальности. По этой причине вам нужно позаботиться при вставке значений identity вручную, чтобы не вставить значение identity, которое уже существует.

Избежать дублирующих значений identity

Дубликаты значений identity могут возникнуть в таблице при вставке значений identity или повторной установке значения identity. Наличие дубликатов значений identity не обязательно плохо, если нет требования к уникальности этих значений. Если все значения identity должны быть различными, то это требование должно поддерживаться созданием PRIMARY KEY, ограничения UNIQUE или индекса UNIQUE.

Использование функции IDENTITY

SQL Server предоставляет функцию IDENTITY для определения столбца identity при создании новой таблицы с помощью оператора SELECT с предложением INTO. Функция IDENTITY подобна, но не идентична свойству IDENTITY, которое используется в операторах CREATE или ALTER TABLE. Функция IDENTITY может использоваться только в операторе SELECT, содержащем предложение INTO, который создает и заполняет новую таблицу.

Ниже приведен синтаксис функции IDENTITY:

IDENTITY (data_type [ , seed , increment ] ) AS column_name

Здесь

data-type — допустимый числовой тип данных, который поддерживает целые значения, отличный от bit или decimal.
seed — определяет первое значение identity, которое будет вставлено в таблицу.
increment — целое значение, которое будет прибавляться к значению seed для каждой добавленной строки.
column_name — имя столбца identity, который будет создан в новой таблице.

Для демонстрации работы функции IDENTITY выполните код в листинге 5.

Листинг 5: Использование функции IDENTITY в команде SELECT INTO

USE AdventureWorks2019;

GO

SELECT  IDENTITY(int, 90000, 1) AS Special_ProductId,  

        Name AS Special_Name,  

        ProductNumber, 

        ListPrice

INTO Production.SpecialProduct

FROM Production.Product

WHERE Name like '%LL Road Frame%Black%';  

-- Вывод новой таблицы

SELECT * FROM Production.SpecialProduct;

Вывод кода в листинге 5 показан ниже.

Результаты показывают, что столбец с именем Special_ProductID является столбцом identity, который был создан при помощи функции IDENTITY. Первая строка получила значение seed. Каждое значение identity для последующих строк вычислялось прибавлением значения increment к значению identity текущей вставленной строки.

Иногда вам может потребоваться программным образом выяснить значения seed и increment или последнее вставленное значение в столбец identity. Для получения подобной информации SQL Server предоставляет несколько функций.

Для получения значения seed, вы можете использовать функцию IDENT_SEED. Эта функция использует следующий синтаксис:

IDENT_SEED ( 'table_or_view' )

Если вы меняли значение identity с помощью команды DBCC CHECKIDENT, то эта функция вернет исходное значение seed, назначенное столбцу identity при его начальном создании.

Парная функция с именем IDENT_INCR, которая позволяет получить значение приращения (increment), имеет следующий синтаксис:

IDENT_INCR ( 'table_or_view' )

Чтобы увидеть обе эти функции в действии, выполните код в листинге 6.

Листинг 6: Получение исходных значений seed и increment

SELECT IDENT_SEED('Production.SpecialProduct') AS OriginalSeed,

       IDENT_INCR('Production.SpecialProduct') AS IncrementValue;

Вот результат выполнения этого кода:

Здесь видно, что OriginalSeed и IncrementValue являются теми же, что и аргументы, которые использовались при создании таблицы SpecialProduct в коде из листинга 5.

Вам также может понадобиться знать последнее значение identity, вставленное в таблицу. Обычно это требуется, когда у вас есть две таблицы со связью «родитель-потомок», и дочернюю запись нужно привязать к родительской записи, используя значение identity родительской записи. Есть три различных способа вернуть значение identity последней вставленной записи, которые мы тут рассмотрим: @@IDENTITY, IDENT_CURRENT и SCOPE_IDENTITY.

@@IDENTITY

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

SCOPE_IDENTITY

Функция SCOPE_IDENTITY так же возвращает последнее вставленное значение identity, как и @@IDENTITY, но с одним отличием. Разница состоит в том, что функция SCOPE_IDENTITY возвращает значение identity для последнего оператора INSERT, выполненного в той же сессии и области действия (scope). Напротив, функция @@IDENTITY возвращает последнее вставленное значение независимо от области действия.

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

Листинг 7: Код, показывающий разницу между SCOPE_IDENTITY и @@IDENTITY

DROP TABLE IF EXISTS TestTable1, TestTable2;

CREATE TABLE TestTable1(

  ID INT IDENTITY(1,1),

  InsertText1 VARCHAR(100)

);

CREATE TABLE TestTable2(

  ID INT IDENTITY(100,100),

  InsertText2 VARCHAR(100)

);

GO

CREATE TRIGGER MyTrigger ON TestTable1 AFTER INSERT AS

BEGIN

  INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 1');

  INSERT INTO TestTable2(InsertText2) VALUES ('Trigger Insert 2');

END

GO

INSERT INTO TestTable1(InsertText1) VALUES ('Original Insert');

GO

-- Возвращаем значения Identity

SELECT @@IDENTITY AS [@@IDENTITY], SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

Код в листинге 7 сначала вставляет одну запись в таблицу TestTable1 в текущей области действия, затем еще 2 записи вставляются в таблицу TestTable2 в другой области действия, когда срабатывает триггер. После вставки и срабатывания триггера на вставку выполняется оператор SELECT, чтобы показать значения, возвращаемые функциями @@IDENTITY и SCOPE_IDENTITY(). Вывод показан ниже.

Следовательно, если вы хотите узнать последнее значение identity независимо от области действия, вы можете использовать @@IDENTITY. Если вам нужно знать последнее значение identity, вставленное в текущей области действия, вам нужно использовать функцию SCOPE_IDENTITY(). На представленных результатах видно, что функция @@IDENTITY вернула значение 200. Это произошло потому, что @@IDENTITY возвращает последнее вставленное значение вне зависимости от области действия. Значение identity для второй записи было вставлено в таблицу TestTable2 триггером «после вставки». Функция SCOPE_IDENTITY() вернула значение 1, это значение identity было присвоено, когда запись вставлялась в TextTable1 в той же области действия.

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

IDENT_CURRENT

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

Листинг 8: Определение последних значений identity, вставленных в таблицы TestTable1 и TestTable2

SELECT IDENT_CURRENT('TestTable1') AS IdentityForTestTable1, 

       IDENT_CURRENT('TestTable2') AS IdentityForTestTable2;

Выполнение кода в листинге 2 дает следующие результаты:

Функции @@IDENTITY и SCOPE_IDENTITY() не требуют передачи имени таблицы в качестве параметра, поэтому нелегко идентифицировать, из какой таблицы пришло значение identity. Напротив, IDENT_CURRENT() требует передать имя таблицы. Следовательно, если вы хотите знать последнее значение identity, вставленное в конкретную таблицу вне зависимости от сессии и области действия, вам следует обратить внимание на функцию IDENT_CURRENT().

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

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

Кэширование identity для повышения производительности

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

Кэширование identity было введено в SQL Server 2012. Проблема с кэшированием identity состоит в том, что когда SQL Server неожиданно падает, он теряет значения, сохраняемые во внутреннем кэше. При потере кэшированных значений эти значения identity больше никогда не будут использоваться. Это может привести к созданию зазора в значениях identity.

Новая опция конфигурации базы данных с именем IDENTITY_CACHE была введена в SQL Server 2017, чтобы помочь решить проблему с зазорами, которые могут вызываться кэшированием. Опция IDENTITY_CACHE включена по умолчанию, но может быть выключена (OFF). При выключении опции SQL Server не кэширует значения identity; тем самым они не будут потеряны при крушении или неожиданной остановке SQL Server. Конечно, отключение кэширования identity приведет к ухудшению производительности.

Чтобы проверить установку IDENTITY_CACHE для базы данных, выполните код из листинга 9.

Листинг 9: Вывод установки IDENTITY_CACHE для текущей базы данных

SELECT * FROM sys.database_scoped_configurations

WHERE NAME = 'IDENTITY_CACHE';

Вывод выполнения кода из листинга 9 на SQL Server 2017 показан ниже.

Видно, что значение IDENTITY_CACHE установлено в 1, что означает, что кэширование identity включено. Чтобы запретить кэширование identity для текущей базы данных, выполните код из листинга 10.

Листинг 10: Выключение кэширования identity

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF;

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

Недостатки столбцов identity

Столбцы identity отличный способ автоматизировать заполнение целочисленного столбца различными числами при каждой вставке новой строки. И все же есть несколько недостатков при использовании столбцов identity:

Только один столбец identity можно определить на таблицу.

Столбец identity нельзя изменить или удалить после его создания.

Столбцы identity не являются уникальными по умолчанию. Чтобы обеспечить их уникальность, необходимо определить первичный ключ, ограничение уникальности или уникальный индекс.

Источник