Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Auto-incrementing a field

Author  Topic 

llzamboni
Starting Member

5 Posts

Posted - 2006-11-09 : 17:12:29
I am porting a database from Informix to SQL Server 2000. Several fields in the informix database are specified as SERIAL, which means that they auto-increment auto-magically if you insert a 0 into them. Well...I was looking for an equivalent in SQL Server 2000, and thought I had found it in the IDENTITY column concept.

With further investigation into IDENTITY columns, however, I discovered that only one column per table can be given the IDENTITY property. Why is this? Is there a way around that? If not, is there another way to achieve this feature in SQL Server 2000? If not, any suggestions on how I can do it?

Thanks!

Lisa

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-09 : 17:36:59
I guess it depends on the purpose these columns will serve.
Just out of curiosity, in your existing system, wouldn't the SERIAL columns in one table all have the same value?

Be One with the Optimizer
TG
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-09 : 17:38:14
Please don't cross post:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74720

Be One with the Optimizer
TG
Go to Top of Page

llzamboni
Starting Member

5 Posts

Posted - 2006-11-09 : 17:40:22
From what I can tell, these columns serve as unique identifiers in a table. I don't think they would all have the same value, but I can't be sure. I guess I'll go test that theory out.

quote:
Originally posted by TG

I guess it depends on the purpose these columns will serve.
Just out of curiosity, in your existing system, wouldn't the SERIAL columns in one table all have the same value?

Be One with the Optimizer
TG

Go to Top of Page

llzamboni
Starting Member

5 Posts

Posted - 2006-11-09 : 17:45:11
I didn't mean to cross post. This is my first post ever to this web-site, and I only realized after my first post that I had posted to the wrong discussion folder. I felt this topic was more relevant to a beginner forum, so I re-posted over here
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-11-09 : 17:50:02
Of course I mean the same value accross a given row, not throughout the whole table. Unless the seed or increment was set differently. But even if they were the values for subsequent columns could be derived based on the value of the first column.

And the whole purpose of an identity column is to act as a unique identifier for that row, so I doubt that is the purpose of the additional SERIAL columns in your existing system.

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -