Alter Column Counter

Trying to alter a table to make a column a counter using TSQL.

Then, is there a way to set the start value of that counter also in TSQL
LVL 50
Dale FyeAsked:
Who is Participating?
 
Phillip BurtonConnect With a Mentor Director, Practice Manager and Computing ConsultantCommented:
A counter? Do you mean an IDENTITY column (similar to AutoNumber in Access)?

If so, you can't change an existing field to it. You would have to drop that column then recreate it using IDENTITY()

You asked how you could set the start value. The syntax of IDENTITY is IDENTITY(startvalue,increment) - see https://msdn.microsoft.com/en-us/library/ms186775.aspx for more information.

If dropping an existing column is not an option, then you can use a Sequence, and set that to be the DEFAULT for that column. Here's the syntax for that: https://msdn.microsoft.com/en-gb/library/ff878091.aspx
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Trying to alter a table to make a column a counter using TSQL.
Nope, you can't alter a column to be an identity column.   Essentially this is the same dilemma as Access changing an int to be an AutoNumber.  Your only options are:
Add a column as an identity
Drop the column, re-add it as an identity
Keep the column as an int (?) but add a trigger that gets the max() value, and adds one more for inserts

>Then, is there a way to set the start value of that counter also in TSQL
Yes, the seed value can be changed, say from id(1,1) to id(1000,1)
0
 
Dale FyeAuthor Commented:
Thanks, Phillip.

Dropping the column was an option, so I did that, then added it again as an Identity column.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.