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