2 identity columns on a table - SQL Server Forums

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

2 identity columns on a table

Author

Topic

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-10-17 : 08:55:20

Hi,

If I already have an identity column on my table, can i create another identity column on the same table ? If yes then what is the Alter table syntax for this ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 09:10:02

nope you cant have two identity columns on a table. Can I ask why you need two identity columns?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-10-17 : 09:18:28

Hi,

I was asked this question in an interview and I wrote an ALter Statement to add this new column. CAn i not add an another identity column using Alter ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 09:22:55

nope. In fact you cant even modify an existing column to be of type identity using alter. There's no direct way to modify a column into identity type. You can add a new identity column using ALTER table if it already doesn't have one though.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nicky_river
Yak Posting Veteran

55 Posts

Posted - 2010-10-17 : 09:29:25

Hi,

Thanks !!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-17 : 10:06:25

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-18 : 11:41:26

u can computed column for this

shaggy
Posting Yak Master

248 Posts

Posted - 2010-10-18 : 11:44:17

create table #tmp (a int identity)

alter table #tmp add b as (a)

insert #tmp default values
go 10

select * from #tmp

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-10-18 : 15:30:27

The right answer is that IDENTITY is not a column. It is a table property (i.e. a count of physical insertion attempts having nothing to do with any logical data model).

Now, if you want to look at the ANSI/ISO Standards, there is a SEQUENCE object you can use.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 12:41:45

quote:
Originally posted by shaggy

create table #tmp (a int identity)

alter table #tmp add b as (a)

insert #tmp default values
go 10

select * from #tmp

but this will always copy value of a onto b isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources