SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Auto-incrementing a field
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

llzamboni
Starting Member

5 Posts

Posted - 11/09/2006 :  17:12:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 11/09/2006 :  17:36:59  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 11/09/2006 :  17:38:14  Show Profile  Reply with Quote
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 - 11/09/2006 :  17:40:22  Show Profile  Reply with Quote
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 - 11/09/2006 :  17:45:11  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
5948 Posts

Posted - 11/09/2006 :  17:50:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000