Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How To Convert int column to int IDENTITY column in SQL 2005?

Posted on 2009-02-12
10
Medium Priority
?
834 Views
Last Modified: 2012-05-06
Under SQL 2005 Management Studio I opened the properties of the column in question, I found the IDENTITY is set to FALSE and it is grayed out. It does not allow me to change that.
0
Comment
Question by:rafiq01
  • 4
  • 3
  • 3
10 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23624735
You cannot change a column to have the identity property.  You would have to create a NEW table, copy the data over using "set identity_insert TABLENAME" to preserve the values, copy over all constraints (foreign keys, defaults, indexes) then drop the old table and rename it.

You CAN'T change a column to identity.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23624744
In SQL Server, you cannot ALTER a column to make it an IDENTITY column.  

You also cannot update the value of an IDENTITY column, although you can INSERT values into an IDENTITY column.

And that hints at what you have to do to get where you want.

1) Copy the existing rows into another "save" table.

2) Delete/truncate the existing rows from the main table.

3) Add an IDENTITY column to the main table.
    If desired, remove the column that contains what you want to become the IDENTITY value.

4) SET IDENTITY_INSERT ON for the main table.

5) INSERT the rows from the save table back into the main table specifying .

6) Reset the seed of the IDENTITY value to the next value you want to be INSERTed into the table.
0
 

Accepted Solution

by:
rafiq01 earned 0 total points
ID: 23625169
Thanks for taking time to extend your hands for the help.

I was actually looking at the following link and found that we can do it in the Enterprise Manager. As a related question, I submitted this one since I could not find the place in SQL 2005. After I got both of your reply, I searched in the MS SQL server Management Studio. I did the following and was able to change it:

1. Right click on the Table name and select 'Modify' - Column Midify Window Opens.
2. Click on the 'Properties Window' button (not right-clicking on the column) - 'Properties' window opens.
3. Under the 'Table Designer 'select the drop-dwon for 'Identity Column'
4. Changed it to the desired int type colum from the list.

It worked and I did not lose any data.

Thanks,
0
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.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23625409
The tool is creating and running the script "under the covers" to add the column :-) .
0
 

Author Comment

by:rafiq01
ID: 23625556
Thanks folks again for the time and effort you rendered for helping me.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23625622
SP:

I prefer to create the new table then rename the old, or rename the old then create the new.  The reason I do it this way instead of copying the data out, fixing the structure, then copying the data back is that you are double the data moving this way.  If you have a large table with several million records, it will take significantly longer.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23625641
I think that you should accept SP's and/or my answer.  SSMS is doing the process that we both outlined.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23625972
>> I prefer to create the new table then rename the old <<

Yes, you're right about the overhead.

Generally, though, I still don't do that.  To me it's too easy to miss too many other objects related to the table -- FKs, indexes, triggers, etc..

So, me personally, I generally feel it's safer to copy data out and back in, unless:
1) the table has a text column(s) that's (actually) used
2) the table is very large (>500K rows) and/or the rows are long

Generally most tables don't have a huge number of rows, just selected ones.
0
 

Author Comment

by:rafiq01
ID: 23627770
Thanks folks putting comments again.

I agree that it is not wise to alter a table's column type without saving data. But, changing the IDENTITY column has no risk of loosing data. What you are suggesting to do, changing the IDENTITY column, SQL Server Managemtnt Studio is doing the same thing "Under the covers".  

"If we use SQL Server Management Studio to get rid of the identity value on a column, a new temporary table is created, the data is moved to the temporary table, the old table is dropped and the new table is renamed."

The same way, if we change or assign an the IDENTITY value of a column, a new temporary table is created with the IDENTITY value to that column, the data is moved to the temporary table, the old table is dropped and the new temporary table is renamed to the old."

 To test, change the IDENTITY value to a column, right click the Designer window and select "Generate Change Script...", you will see that it is doing what we are talking.

The reason I did not accept either of your solutions, because both of you mentioned that we cannot ALTER a column to make it an IDENTITY.  It is true that it is not changed directly in an existing table. But to users, it is not true since they can do it and they don't see the cahgnes in the back are done on another table. It would be more acceptable to say we cannot use ALTER command to change IDENTITY column. Choosing any of your solution the future solution seekers would get confised. Otherwise both of you are 100% correct with the in-depth knowledge in SQL.

Thanks again.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23627805
True; I actually disagree with Brandon on that, I do not think you should accept our answers.

Personally I would use my own script to do this, but I did not provide a script, NOR did I tell you that the GUI would.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

578 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