How to change column datatype in MS SQL Server

I started a new contract last week, and the first task for me was to change column datatype from int to bigint in Microsoft SQL Server.

Also I have to provide database scripts for this changes, so this changes can be implemented in all databases than we have in our project.

Change column datatype is very easy task, you have to run T-SQL script like this:

ALTER TABLE your_table_name ALTER COLUMN your_column_name new_data_type

For example for my table it looks like:

ALTER TABLE dbo.Account ALTER COLUMN AccountNumber bigint;

The first problem with changing datatype was because of indexes. If you have an index on your column you can't change column type because it has references. I get this error message:

Msg 5074, Level 16, State 1, Line 1
The index 'SK_Account_AccountNumber' is dependent on column 'AccountNumber'.
ALTER TABLE ALTER COLUMN AccountNumber failed because one or more objects access this column.

So at first I have to remove index, change column type and then create index again.

How to remove index in MS SQL Server

To remove index I can use Drop index statement. The best way is to click on index with the right mouse button, select "Script index as" and "Drop to"

But do not forget to create script for creating index first.

In my first script I forget to add NOT NULL at the new datatype, and column was changed so it was allow nulls, so I have to change script.

ALTER TABLE dbo.Account ALTER COLUMN AccountNumber bigint NOT NULL;

Final T-SQL code for changing column datatype in MS SQL Server 2000 – 2008

use Accounts_Database;
GO
/* ------------------------------------------------------
Changing type of AccountNumber from int to bigint for DBO.Accounts
---------------------------------------------------------*/

/* Drop existing indexes */
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[Accounts]')
 AND name = N'SK_Accounts_AccountNumber')
DROP INDEX [SK_Accounts_AccountNumber] ON [dbo].[Accounts]
WITH ( ONLINE = OFF )
GO
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id =
OBJECT_ID(N'[dbo].[Accounts]')
 AND name = N'Accounts0')
DROP INDEX [Accounts0] ON [dbo].[Accounts] WITH
( ONLINE = OFF )
GO

/* Alter table Accounts*/
alter table dbo.Accounts
alter column AccountNumber bigint not null;
go

/* Recreate indexes for Accounts table*/
/* SK_Accounts_AccountNumber */
CREATE UNIQUE NONCLUSTERED INDEX [SK_Accounts_AccountNumber]
ON [dbo].[Accounts]
(
    [AccountNumber] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]
GO
/* Accounts0 */
CREATE NONCLUSTERED INDEX [Accounts0] ON [dbo].[Accounts]
(
    [Account_ID] ASC,
    [AccountNumber] ASC,
    [StatusCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)
ON [PRIMARY]
GO