Autonumber in a SQL Server table
 

I know this is probably a stupid post but I cannot think of how to accomplish this. How do you do the equivilent to an autonumber in a SQL Server table? Please let me know my project is very close to deadline
Thanks in advance

Re: Autonumber in a SQL Server table
 

Ok, just to clarify? Replicate autonumber in SQL how? You mean within an SQL function/store procedure or in another coding lanuage (if so which one)?

IDENTITY(x,y) is how you use autonumber in SQL ( x = starting point, and y = increment by)

Re: Autonumber in a SQL Server table
 

I just needed to know syntax on how to get an autonumber like column for MS SQL Server. So in your example if my field was "bill" lets say when I set it up in the design table area of SQL server I would enter "bill" in the column name field and then enter numeric in the data type field. Then set up the incrementation in a stored proc? Am I in the ball park here?

Re: Autonumber in a SQL Server table
 

Well kind of.

Ok here is the syntax on creating a table in SQL and having an autonumber column

CREATE TABLE NorthWindUsers 

         (UserID INT IDENTITY(1,1) NOT NULL,

          UserName VARCHAR(50) NOT NULL,

          Password VARCHAR(50) NOT NULL)

The userID will start a 1, and increment each time you add a new user to the table; automatically


Hope this helps

Re: Autonumber in a SQL Server table
 

You know, this feature isn't in Oracle as I recently discovered. Is it considered bad to use autoincrementing numbers? Certainly makes it easy to maintain a primary key with little effort. I am just trying to understand in what situation you wouldn't want to use that feature. Obviously Oracle doesn't seem to want you to use it at all.

Re: Autonumber in a SQL Server table
 

I don't know that Oracle thinks it is a bad thing, they just choose to solve the problem a different way--with "sequences". In Oracle, as you may know, a sequence is it's own thing outside of any table. Personally, I wish the databases I work with had both an autonumber/identity and a feature like a sequence. They are both very handy. A sequence can be useful if you need a way to have an id unique across multiple tables or multiple databases.

It's easy enough to code your own sequence-like feature using a one column/one row table and a function, but I assume Oracle's sequence feature is highly optimized to do what it does.

Re: Autonumber in a SQL Server table
 

Perhaps I don't understand the functionality of the sequence clearly, how exactly are you to you use the sequence if it isn't attached to an individual entity? Or are you saying the sequence is like a counter maintained at the DB level which updates a regular identifying entity? If that is the case, that seems great.

Unfortunately the Oracle database I am working on at work for a supply chain application is so de-normalized I don't think uniqueness was too much of an issue when designing the schema.

I don't understand why people still design tables which uses multiple columns to create a unique primary key, each column by itself being non-unique. They must have been out back smoking pot during that class. :eek:

We have serious data integrity problems, as you can imagine.

Re: Autonumber in a SQL Server table
 

I found a page on techonthenet.com that explained an Oracle sequence better than I could have, so I plagerized it. Below is what it said. I think it makes it clear that a sequence object maintains a unique number but is not tied to a table entitity. Yes, in practice, you normally create a sequence for a specific table, and you then only use it on that one table, but there is nothing about the sequence that prevents you from using it anytime you want. You can even just "SELECT supplier_seq.nextval" to increment the counter arbitrarily.

Quoted from techonthenet.com:
----------------------------------------------------------
In Oracle, you can create an autonumber field by using sequences. A sequence is an object in Oracle that is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.

The syntax for a sequence is:

CREATE SEQUENCE sequence_name

MINVALUE value

MAXVALUE value

START WITH value

INCREMENT BY value

CACHE value;

For example:

CREATE SEQUENCE supplier_seq

MINVALUE 1

MAXVALUE 999999999999999999999999999

START WITH 1

INCREMENT BY 1

CACHE 20;

This would create a sequence object called supplier_seq. The first sequence number that it would use is 1 and each subsequent number would increment by 1 (ie: 2,3,4,...}. It will cache up to 20 values for performance.

Now that you've created a sequence object to simulate an autonumber field, we'll cover how to retrieve a value from this sequence object. To retrieve the next value in the sequence order, you need to use nextval.

For example:

supplier_seq.nextval

This would retrieve the next value from supplier_seq. The nextval statement needs to be used in an SQL statement. For example:

INSERT INTO suppliers

(supplier_id, supplier_name)

VALUES

(supplier_seq.nextval, 'Kraft Foods');

This insert statement would insert a new record into the suppliers table. The supplier_id field would be assigned the next number from the supplier_seq sequence. The supplier_name field would be set to Kraft Foods.

Re: Autonumber in a SQL Server table
 

I believe that would be superior DB design because not only do you have a unique PK for each identifying row, it is unique across the DB. Maybe that is 4 or 5 NF...I never read much past 3 :-) I only wish identifying columns would autoincrement the "master counter" so it was automated like MS SQL. A combination of the two if you will. That may present a locking issue...but I am sure they thought of that.

Thanks for the great response.

Re: Autonumber in a SQL Server table
 

Mad Max
colum name = id
data type = int


down at the colum properties box change
identity to yes
identity seed to what number you want to start using
identity increment to how much the seed is incremented each time.