• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

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
0
Dale Fye
Asked:
Dale Fye
  • 2
1 Solution
 
Phillip BurtonCommented:
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 BurtonCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now