CODEWITHSUNDEEP
sqlsql-serversequenceauto-incrementazure-sql-database
sharptooth
sharptooth

Reputation: 170499

How do I generate a unique increasing integer value in SQL Server?

I have the following problem. An SQL table stores job items and each item has state. Items can change states and also other properties either together or separately. I want to be able to assign each job item a unique increasing integer (64 or more bits) that will only be assigned when I ask to, not updated on each update like timestamp behaves. So I want to be able to do two operations:

  1. update some other fields without changing that integer on the row and
  2. update state and that integer

and when the integer changes it should be greater than all other such integers ever generated for that column (per-table or per-database will of course do as well).

This needs to be scalable so that multiple clients can work with the database without serious penalties.

How can I do that?

Upvotes: 2

Views: 6288

Answers (2)

gbn
gbn

Reputation: 432271

Sounds like you need a SEQUENCE.

This is decoupled from your table (unlike an IDENTITY) and can be table or database wide: up to you.

Sequences are supported natively in the forthcoming SQL Server 2012, but until then you can emulate one as per this dba.se question: https://dba.stackexchange.com/q/3307/630

Upvotes: 2

twilson
twilson

Reputation: 2062

Look here http://blogs.msdn.com/b/sqlazure/archive/2010/07/15/10038656.aspx. Should help

But in a nutshell, you need a field declared as follows in your table:

Id bigint PRIMARY KEY IDENTITY (1,1)

Upvotes: 3

Related Questions