Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

alter is identity

Posted on 2013-11-29
4
Medium Priority
?
362 Views
Last Modified: 2013-12-13
How can I alter is identity from NO to Yes from tsql?  We have sql 2008 server R2.
table.jpg
0
Comment
Question by:VBdotnet2005
  • 2
4 Comments
 
LVL 9

Expert Comment

by:guswebb
ID: 39686021
When you click on 'No' (to the right of Is Identity)  do you not get a drop-down menu allowing you to select Yes?
0
 
LVL 9

Expert Comment

by:guswebb
ID: 39686033
If this is a pre-existing column you can't alter it. You need to create a new column and set Is Identity at that point, then drop your current column.
0
 

Author Comment

by:VBdotnet2005
ID: 39690395
Under Identity Specification, I want to set Is Identity to Yes in TSQL. I know how to do it in design. This is a brand new table.

Link
0
 
LVL 1

Accepted Solution

by:
YaraKnight earned 2000 total points
ID: 39691320
In a new query window where Names is your table name, and 'Column' is your desired column name:

Alter Table Names
Add Id_new Int Identity(1, 1)
Go

Alter Table Names Drop Column ID
Go

Exec sp_rename 'Names.Id_new', 'ID', 'Column'
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question