Auto-incrementing a non-identity field

Question

Is it possible to auto-increment a field that is not defined as Primary Key in a table?

Lets say that i have three fields in a table. One of them is Primary Key with IDENTITY(1,1) set.

The second field is a int type that i want to auto-increment with a INSERT statement on a third field called column_3.

So for example: INSERT INTO table_name(column_3) values('something')

I could easily add a trigger for this but i was wondering if there is a way of doing this without a trigger.

http://stackoverflow.com/questions/349092/can-a-sql-server-table-have-two-identity-columns?answertab=votes#tab-top — sgeddes, May 22 '14 at 00:53
you cannot have 2 identity columns and there is one big reason for this - you don't need this, there is no sense in 2 columns which will have the same value — Iłya Bursov, May 22 '14 at 00:55
right, I'm not asking for second IDENTITY column or them having same values. The PK can increment 1,1 the second one can be 100,1 or whatever as long as it is auto-incrementing — , May 22 '14 at 00:58
With a high enough version of sql server, you can use a sequence. — Dan Bracuk, May 22 '14 at 01:06

score 0 · Answer 1 · answered May 22 '14 at 01:43

0

You could do this with a computed column, although then it will always be directly related to your IDENTITY column.

You can also so this in SQL Server 2012 or later with a SEQUENCE: http://technet.microsoft.com/en-us/library/ff878058.aspx

The whole concept seems rather odd to me, though.

answered May 22 '14 at 01:43

SeeJayBee

I agree. I found a way where I don't have to do this since it is odd. But it is nice to know about what you guys explained in answers. Thanks – May 22 '14 at 17:57

score 0 · Accepted Answer · answered May 22 '14 at 03:35

I think we can create a table as given below for your purpose

CREATE TABLE [LUName]
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name CHAR(25),
EmpID AS id + 100,
InsertTS DATETIME
)

The INSERT script will be as given below.

INSERT INTO [LUName] VALUES
('Bless',GETDATE()),('Sus',GETDATE()),('Sani',GETDATE()),('Sumi',GETDATE())

The resulting Data will be as given below.

SELECT * FROM [LUName]

enter image description here

2 Answers2