Changing Data Type Best Practice. (1 Viewer)

[zgray]

I have a few tables that have a int data type field as its primary key. That field used to hold a 7 digit record number and it worked fine. Now they are using a 10 digit number and the INT data type is too small I need to change that field to a BIGINT.

When I just change it and go to save it gives me the save changes is not permitted error. I read that I can disable the option that gives me this error.

What im asking is what is the best way to go about changing this fields data type from INT to BIGINT. I know very little SQL. We are using sql 2012.

Edit: Would it be wise to just make a new field, transfer all that data over and name it the same and make it the primary and get rid of the old one?

 

[Rx_]

In the past, on 2008, changing up was usually followed by a warning. Don't know about 2012.

Yes, import it into a new table.
Go to your existing table and right click for the menu.
See attachment:
Script Table As - Create to - New Query Window

Delete everything after EXEC sys.sp_addextendedproperty so that you don't create duplicate schema.

With the SQL Edit Replace - change the table name, then run the query to create the empty table. You could have changed the script to change the field's data type. But in an empty table, you can probably do that with the visual design tool. Then do an append query to populate the empty table.

Too many steps?
Another way would be to remove all of the Constraints and Keys from the original table. That might let you change the data type.
It might be worthwhile to make a backup database to try that first.

Your idea of creating a new field is valid.
Remember for any of the above, review any dependencies or related joins using that table/field name.
When finished, open the table and right-click on Index - use the Rebuild All since it is a primary key.