How to reset IDENTITY in Memory-Optimized Tables

Even though SEQUENCE object is available with much more flexibility, we still use IDENTITY property for adding sequence values to tables, specifically when we need to introduce a surrogate key. However, if you need the same with Memory-Optimized tables, you need to know certain things.

Can I add the IDENTITY property to Memory-Optimized tables?

Yes, it is possible but it should be always IDENTITY(1,1). You cannot use a different values for seed and increment, they should be always set as 1.

CREATE TABLE dbo.MemoryOptimizedTable
(
 Id int IDENTITY(1,1) NOT NULL primary key nonclustered,
 CurrentDate datetime NULL default (getdate())
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO 5

Can I reset the IDENTITY seed using DBCC CHECKIDENT?

No, this is not supported. If you need to reset the IDENTITY seed, only way is inserting a new value explicitly by turning SET IDENTITY_INSERT on. As shown in the example, once the record with value 100 is inserted, the next value of the seed is set to 100+1.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (100, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

What if insert a lower value explicitly?

It is possible as long as it does not violate any rules. Look at the code below. It inserts a record with value 50 explicitly. But it does not mean that the seed is getting reset to 50+1. This does not happen because the last generated value is greater than 50+1. Therefore, the value of next record is 102, not 51.

SET IDENTITY_INSERT dbo.MemoryOptimizedTable ON
GO
INSERT INTO dbo.MemoryOptimizedTable (Id, CurrentDate) VALUES (50, DEFAULT);
GO
SET IDENTITY_INSERT dbo.MemoryOptimizedTable OFF
GO

INSERT INTO dbo.MemoryOptimizedTable (CurrentDate) VALUES (DEFAULT);
GO

SELECT * FROM dbo.MemoryOptimizedTable;

SQL Server Identity value suddenly became 1000+

I noticed that values of a column related to newly inserted records which is an identity property enabled column in one of the tables, suddenly started from 1000+. The last values before this sudden jump were 27, 28, 29 but suddenly it started from 1000+ values. How did it happen without resetting the seed or increment of the identity property?

This is not a bug or something related to SQL Server 2016. It a result of a new implementation related to SQL Server 2012. As per my readings, since SQL Server 2012, it caches 1000 values for an identity property if the data type is int and it caches 10,000 values for big int and numeric. If an unexpected restart happens (in my case, I believe that an update caused the restart), there is a possibility to lose the cache, hence it starts from next available value. This is normal and you do not need to worry if you experience it.

Enabling Trace flag 272, you can get the old behavior or you can use Sequence instead. Read this thread for understanding these two options:  http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server

SQL Server Brain Basher of the Week #052 - Identity property

The Identity property is not something new and it has been widely used with most of table when a surrogate key is required. It can be simply used with a numeric data type and it inserts a value automatically when an insert operation is done, based on the seed and increment set. The Identity property is used with CREATE TABLE statement like blow;

USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int identity(1,1) primary key
 , CustomerName varchar(200) not null
);

Now the question is, where else you see the keyword Identity with similar functionality?

Many assume that the functionality we get from Identity is only available with CREATE TABLE statement. But there is a function, named as Identity that offers the same functionality.

See the following code. It is SELECT INTO statement that allows us to get some records from a table and create a new table with the recordset. You can use Identity function with SELECT INTO statement for adding a column and populating values as per seed and increment. The Identity function needs three parameters: data type, seed and increment. And the good thing is, it adds the Identity property to the column as well.

USE tempdb;
GO

-- This creates a table with PersonId
-- which based on Identity function
SELECT Identity(int, 1, 1) As PersonId, FirstName, LastName
INTO dbo.Person
FROM AdventureWorks2014.Person.Person;

SQL Server Brain Basher of the Week #042 - Identity property

Let's talk about an important property we always use; Identity property. This property has been used for generating Ids automatically, and most of the cases, this has been used as the surrogate key. Usage is very simple, all you have to do is, set it with CREATE TABLE statement, assigning the seed (starting value) and increment value. SQL Server will automatically generate a value for this when an insert is performed.

Since the value is not based on an application, we have no clue on the value generated. If we need the newly generated identity value for performing the next step of the process we have written, we used to call either @@identity or Scope_Identity function. Here is this week question based on it;

What is the different between @@identity and Scope_Identity function? Which one should be used for getting the last generated value within the scope?

Let's try to understand the purpose of these two. @@identity is a function that returns the last generated identity value regardless of the scope but for the current session. But Scope_Identity function returns the last generated value within the scope for the current session. That is the different between these two functions. For most cases, Scope_Identity is the best unless you have an unique requirement for getting the value via @@identity.

See this example, it shows how these two functions works.

-- Creating Customer table
-- Id is an identity, starting number is 1
CREATE TABLE dbo.Customer
(
 Id int identity(1,1) Primary key
 , Name varchar(100) not null
);

-- Creating Customer Log table
-- Id is and identity, starting number is 100
CREATE TABLE dbo.CustomerLog
(
 Id int identity(100,1) Primary key
 , Info varchar(100) not null
);
GO

-- This stored procedure accepts a name
-- and insert a record
CREATE PROC dbo.InsertCustomer @Name varchar(100)
AS 
BEGIN

 INSERT INTO dbo.Customer
 (Name) VALUES (@Name);

 -- Getting CustomerId value via @@identity
 SELECT @@IDENTITY AS CustomerIdFromIdentityFunction;
 -- Getting Customer ID via Scope_identity
 SELECT SCOPE_IDENTITY() AS CustomerIdFromScopeIdentityFunction;
END
GO


-- This trigger will insert a record to
-- CustomerLog table
CREATE TRIGGER dbo.InsertTriggerForCustomer
ON dbo.Customer
FOR INSERT
AS
BEGIN

 INSERT INTO dbo.CustomerLog
  (Info) VALUES ('Record is inserted');
END

-- Executing the first procedure
EXEC dbo.InsertCustomer 'Dinesh';

As you see, @@identity returns 100 and Scope_Identity returns 1. The reason for showing 100 for @@identity is, the insert happened inside the trigger. Trigger inserted a record and its Id is 100. Since @@identity for the current session, not for the current scope, it returned the last generated identity regardless of the scope.

My Application needs a new sequence number to the table without inserting the record

This is something I discussed with one of my colleagues, it was all about obtaining the next sequence number from Identity type column without inserting the record and use it for other entities inside the application, and later submit all to the database for insertion. However, if this is the requirement, it is not possible with Identity because the next value cannot be obtained from Identity-Column without inserting the record. This issue can be seen often with Entity Framwork enabled applications.

What would be the best way of handling this? Traditional way of handling this is, maintaining another table that holds key values for tables and using it for issuing next available values. It needs additional coding, extra effort and it introduces unexpected locking issues too. Modern way and best way of handling this is, using SEQUENCE object which was introduced with SQL Server 2012.

The SEQUENCE is a user-defined schema-bound object that generates sequence numbers based on the setting added. Multiple properties can be set with this such as Starting number, interval, and order. Unlike Identity, defined SEQUENCE object can be used with any table because there is no link between SEQUENCE object and a table. Although one SEQUENCE object can be used with all tables, it is advisable to create multiple SEQUENCE objects, one per table because it makes the maintainability easy and minimizes gaps between numbers added to tables.

Here is the way of creating a SEQUENCE object.

CREATE SEQUENCE Sales.OrderSequence
    START WITH 1
    INCREMENT BY 1;
GO

Once created, application can request for the next number which can be used as key of the table, calling below command;

SELECT NEXT VALUE FOR Sales.OrderSequence;

For more info on SEQUENCE object, refer: https://msdn.microsoft.com/en-us/library/ff878091.aspx

What is $IDENTITY?

This was in one of my slides of SQL Brain Bashers presentation which was done at SQL Server User Group Meeting.

What is $IDENTITY? Everybody knows that we use IDENTITY property on columns where we need automatically incrementing identification number. The $IDENTITY does not set anything, but allows us to reference the column using it. Here is an example;

CREATE TABLE dbo.Employee

(

    EmployeeKey int IDENTITY(1,1) PRIMARY KEY

    ,FirstName varchar(50) NULL

    ,LastName varchar(50) NOT NULL

    ,

    ,

)

GO

When query for column like EmployeeKey, $IDENTITY can be used instead on column name;

-- both queries return same resultset

SELECT EmployeeKey, LastName

FROM dbo.Employee

SELECT $IDENTITY, LastName

FROM dbo.Employee