Auto increment a bigint column?

Question

Auto increment a bigint column?

blasto_max

SSCertifiable

Points: 5025
More actions
November 7, 2013 at 12:57 pm

#146434

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 ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

ID BIGINT IDENTITY NOT NULL

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

blasto_max SSCertifiable Points: 5025 More actions · Answer 2

GilaMonster (11/7/2013)
ID BIGINT IDENTITY NOT NULL

I used IDENTITY(1,1) only before and it said cannot insert null into ID. Clearly, it does not meet my requirements. I want sql server to create a number and put it in ID when i insert a row into the table. How do I do it ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

blasto_max (11/7/2013)
I want sql server to create a number and put it in ID when i insert a row into the table. How do I do it ?

Exactly as I just said

ID BIGINT IDENTITY NOT NULL

CREATE TABLE #Test (

ID BIGINT IDENTITY NOT NULL,

SomeOtherColumn char(1)

)

INSERT INTO #Test (SomeOtherColumn)

values ('a')

One number, created by SQL Server and put into the ID column when you insert a row.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

blasto_max SSCertifiable Points: 5025 More actions · Answer 4

blasto_max

SSCertifiable

Points: 5025

November 7, 2013 at 1:28 pm

#1664911

thanks

blasto_max SSCertifiable Points: 5025 More actions · Answer 5

GilaMonster (11/7/2013)
blasto_max (11/7/2013)
I want sql server to create a number and put it in ID when i insert a row into the table. How do I do it ?
Exactly as I just said
ID BIGINT IDENTITY NOT NULL
CREATE TABLE #Test (
ID BIGINT IDENTITY NOT NULL,
SomeOtherColumn char(1)
)
INSERT INTO #Test (SomeOtherColumn)
values ('a')
One number, created by SQL Server and put into the ID column when you insert a row.

Some concerns on this approach - http://stackoverflow.com/questions/19845163/auto-increment-a-bigint-column/19845937?noredirect=1#comment29517670_19845937

Wondering if you could tell me how to avoid them. Thanks.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

There's no way to avoid gaps with an identity column. I'm not sure if there's a secure method to achieve it that will perform correctly.

Gaps shouldn't be a concern especially when using bigint.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Why are gaps a concern?

They're normal with identities (on all data types), and unless you're using it for something that absolutely may not legally have gaps (some industry's invoice numbers or similar) it shouldn't be a problem. If it's just been used for a meaningless ID column (surrogate, artificial key), then the values are of no interest to anyone and so gaps aren't a problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Auto increment a bigint column?

Cookies on SQLServerCentral