increment

Question

Post reply

increment

raghu-sql

Mr or Mrs. 500

Points: 517
More actions
July 28, 2011 at 7:04 am

#245053

Is there a functional difference between SQL Servers Identity column implementation and the autoincrement feature found in other DB systems?

Viewing 5 posts - 1 through 4 (of 4 total)

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

HowardW One Orange Chip Points: 29109 More actions · Answer 1

There are some minor differences, but they fundamentally do the same thing (at least for auto increment in mySQL)...

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

I can say it's the same as access. But I've not used other RDBMS.

Brandie Tarvin SSC Guru Points: 173045 More actions · Answer 3

I don't know how it works in other RDBMS, but in SQL Server, the identity function prevents you from inserting new numbers unless you specifically tell SQL that you're inserting new numbers. You can also "re-use" identity numbers, assuming the number was deleted from the system to begin with.

And I don't remember if Access allows you to set the incremental, but in SQL, you can tell it to autonumber by every 1, 5, 23, N, count. So you could potentially have autonumbers that go 2,4,6,8...N instead of just 1,2,3,4...N. And you can start with whatever number you want (such as 1000).

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

You also have the "increment" be a negative number in sql. Set your identity seed to a large number (10,000) and the increment value to -1.

First record will be 10000

next insert will be 9999

Not sure there is a real reason for this but it will work (and yes the value can go negative).

create table #test

(

id int identity(1, -1),

myVal varchar(20)

)

insert #test select 'first insert'

insert #test select 'second insert'

insert #test select 'third insert'

select * from #test

drop table #test

--edited to add code sample. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/