Auto increment a bigint column?

Question

I want a bigint ID column for every row of data that i insert into a table. I want Sql server to generate the numbers. I tried to create a table with a bigint column ID. I want this to be autoincrement with the first value as 1. I tried using [ID] [bigint] AUTO_INCREMENT NOT NULL, in my create table statement, but I got the error - Incorrect syntax near 'AUTO_INCREMENT'. How do I do this ?

Answer 1

Can you not just declare it as an IDENTITY column:

[ID] [bigint] IDENTITY(1,1) NOT NULL;

The 1,1 refers to the start index and the amount it is being incremented by.

NOTE: You do not have to provide a value for the ID column when you do an insert. It will automatically choose it. You can modify these values later if required.

EDIT:

Alternatively, you can use a stored procedure to handle all the inserts.

Example:
Stored Procedure will take in variables as you would a normal insert (one variable for every column). The logic within the stored procedure can select the max value currently existing in the table and choose that as its max value.

DECLARE @yourVariable = SELECT MAX(ID) FROM YourTable

Use @yourVariable as your insert value. You can increment it or change value as necessary.