automatic identifier count column

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

automatic identifier count column

Author

Topic

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-11 : 05:05:48

hello all..
is there a way to define a column that will contain the id of a record as a integer ex

id | name
1 | record 1
2 | record 2..etc
this id column surly there is a way of creating it automatically without checking the
count every time and adding 1 to it right? thanks in advance

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-04-11 : 05:44:30

hi try with the below idea

declare @id int
set @id=1
select 'record'+cast(@id as varchar(10))

ok tanx...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-11 : 07:10:43

You can use an identity column. Lookup identity in SQL Books On Line. The syntax is something like this:

create table YourTable
(
   id int identity(1,1) not null primary key clustered,
   record varchar(127) not null
)

This assumes you want the identity to be a clustered primary key, and that you want the id column to start at 1 etc.

The one downside of identity key, if you can call it a downside, is that you cannot change its value. So, if you decided that you wanted to get rid of gaps resulting from deletions, it is hard.

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-11 : 09:18:29

ok thank you sunitabeck but is there a way that i can add it to a already created table:S what i found in BOL shows only how to do that with create table command

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-04-11 : 10:08:21

I think it is not straightforward to change the identity property of the column from T-SQL script. However, you can use the SQL Server Management Studio to do it. Right click on the table in the object explorer and select Modify.

When you use the SSMS to add/remove identity property of a column, I think SQL server recreates the whole table under the covers (taking great care to not lose any data).

bobz_0585
Yak Posting Veteran

55 Posts

Posted - 2009-04-12 : 11:02:50

i couldnt find ssms:S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 02:32:24