November 7, 2013 at 12:57 pm
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 ?
November 7, 2013 at 1:07 pm
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
November 7, 2013 at 1:09 pm
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 ?
November 7, 2013 at 1:14 pm
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
November 7, 2013 at 1:28 pm
thanks
November 7, 2013 at 3:37 pm
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.
November 7, 2013 at 4:20 pm
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.
November 7, 2013 at 10:08 pm
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply