Creating 7 digit codes in database for every new insertion

Question

I am aware that using identity column we can auto-increment the keys from 0, one-by-one. What I want to do is:

Generate codes in SQL server 2014 which will be of 9 digits (operational codes).

First 2 digits are going be pre-defined and remaining 7 will increment by 1 for every new insertion of record. For example:

51 (predefined ) and remaining 7 are 0000001 and so insertions will happen as:

510000001
510000002
510000003
510000004
510000005
510000006

and so on.

Answer 1

If it always needs to start with 51 then you can use a sequence (or identity) that starts at 510,000,001.

CREATE TABLE #Test (
    ID BIGINT IDENTITY(510000001, 1),
    SomeColumn VARCHAR(10)
);

INSERT  INTO #Test (SomeColumn) 
VALUES ('Test'), ('Test2');

SELECT * FROM #Test AS t;

Just keep in mind that once the sequence reaches 519,999,999, it will not restart at 510,000,001 (particularly if you use the identity property) but will continue at 520,000,000.

Also, since you are on SQL Server 2014, please consider the fact that starting from the 2008 version IDENTITY values are cached and can have gaps in them after a restart. If you want to have the values follow exactly (i.e. no gaps) you should either use a SEQUENCE starting at 510000001 or apply Trace Flag 272.
This seems to be documented in this connect item

Answer 2

I guess you can try something like below. This is combination of identity column primary key with the computed column. Hope this helps.

CREATE TABLE dbo.TestTable (
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , CombID AS LEFT('51000000'+ LTRIM(CAST(Id AS VARCHAR(9))), 9)
    , SomeColumn VARCHAR(16)
)