CODEWITHSUNDEEP
sqlsql-server
Will
Will

Reputation: 59

AutoID column for a table

I was asked by my manager to add an AutoID column to the table UserTracking, so that all the existing entries could be populated with a value, and for this to be in the same order as the CreateDate field (i.e. ID 1 is in the earliest entry).

I did this:

alter table dbo.UserTracking add  AutoID bigint NULL
go

update dbo.UserTracking
set AutoId=AutoId.AutoId
from dbo.UserTracking as ut
join 
    (
      select  CreateDate,row_number() over (order by CreateDate) as AutoId
      from UserTracking
    ) as AutoId
on ut.CreateDate=AutoId.CreateDate
go

create index IDX__UserTracking__AutoId on dbo.UserTracking (AutoId)  
go

BUT I was told that we need the AutoID column to be an actual auto ID (so that it has automatic new values on record insert) and it should be called ID to be consistent with other tables.

From performance point of view it would be perfect if the new ID (INT) column is our primary key but it can be hard to do. For now we need the auto ID with an index on it.

I'm not entirely sure how to do it myself and I wouldn't want to mess it up. Could someone please help?

Upvotes: 0

Views: 1179

Answers (1)

M.Ali
M.Ali

Reputation: 69564

Drop the existing column (AutoID) , you cannot make an existing column an Identity column.

Just add another column and make it Identity column. 

ALTER TABLE dbo.UserTracking 
ADD ID INT IDENTITY(1,1) 
GO

It will populates the column itself you don't have to do anything .

Upvotes: 3

Related Questions