Sequence Numbers In MS SQL

[SorryDog777]

Hi, Our company is switching over from Oracle to MS SQL.

I have searched through the archives and found the question below, but couldn't reply to it. So I started a new thread. I apologize in advance if this is a dumb question.

Question:
"Hi is there an equivalent to sequence (in oracle) in Sql Server?

I need to have a auto incrementing sequence of numbers which would be used as a primary key for the table."


I need the same thing, but not use the sequence number as a primary key. The sequence number is numeric and needs to auto increment like in Oracle. Example: 5551234, the next number would be 5551235 and so on. Oracle does something like lock the table when issuing out a number so there is no possibility of duplicate numbers.

Any idea how to achieve this?

Thanks for any help you can provide.

SD

 

[chrissie1]

identity (1,1)

seed = 1 (begins at 1)
step = 1 ((increments in steps of 1)

Christiaan Baes
Belgium

"My new site" - Me

 

[r937]

Christiaan, identity columns need to be the primary key, SorryDog said his sequence could not be the PK

the only way to do this that i can think of is to set up a separate table with an integer column and increment it yourself in a function

r937.com | rudy.ca

 

[chrissie1]

identity columns need to be the primary key

NO it doesn't I just checked and it works without being primary key.

Christiaan Baes
Belgium

"My new site" - Me

 

[r937]

whoa!! yes, you're right, what was i thinking??!!!

r937.com | rudy.ca

 

[chrissie1]

You were thinking about giving me a star.

Christiaan Baes
Belgium

"My new site" - Me

 

[r937]

actually, asking for stars is a RedFlaggable offence

r937.com | rudy.ca

 

[vongrunt]

> whoa!! yes, you're right, what was i thinking??!!!

Max. one PK per table, max. one identity column per table, the fact identity is widely used as primary key in a don't-let-me-think fashion... probably a little bit of everything.

> actually, asking for stars is a RedFlaggable offence

Exception: chrissie1

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[chrissie1]

I wasn't asking for a star, I was just saying what I thought you were thinking.


Christiaan Baes
Belgium

"My new site" - Me

 

[SQLDenis]

>>the fact identity is widely used as primary key in a don't-let-me-think fashion..


Mmmmm what would Celko say about this

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SorryDog777]

Thanks for all of your replies.

Christiaan,
Is this all there is to code for a sequence? Looks like I'm missing something or it doesn't look like it's all there?

Can you provide some sample code to create the sequence??
SD


identity (1,1)

seed = 1 (begins at 1)
step = 1 ((increments in steps of 1)

 

[SajidAttar]

Hi SD,

You need to set it when creating table . . like

Create table dbo.MyTable ( 
	My_Identity_Column_as_Primary int [B] identity(1,1)[COLOR=blue] primary key [/color][/b],
	My_Second_Column varchar(50))

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell

 

[chrissie1]

CREATE TABLE tbl_blah
(
  blah_Id int identity(1,1) NOT NULL
  , blah varchar(100) NOT NULL PRIMARY KEY
)

Or you can use it in an alter table sequence.

Christiaan Baes
Belgium

"My new site" - Me