Changing data type of column in SQL Server

Question

I have to change datatype of a column in SQL Server. So what are the constraints in doing?

I know I have to remove index and other constraints?

Do I have to remove not null check ?

What other things do I have to check before altering the datatype?

I need to remove the constraints and alter the table and then add the constraints again.

Is this the right way to do so ?

DROP INDEX UX_1_COMPUTATION ON  dbo.Computation 

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) 

CREATE UNIQUE INDEX UX_1_COMPUTATION ON dbo.Computation (ComputationID);

Here UX_1_COMPUTATION is the unique index name, Computation is the table name and ComputationID is the column name.

Is this correct ?

Update

So if there is a composite index where there are more than one column involved , How do i deal with it ? These indexes contains primary key column with non primary key columns .

When i tried executing the following statement

DROP INDEX UX_2_COMPUTATION ON  dbo.Computation 

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) 

CREATE UNIQUE INDEX UX_2_COMPUTATION ON dbo.Computation (ComputationID , ComputeGuid);

It is throwing the following exception

SQL DROP INDEX UX_2_COMPUTATION

        ON dbo.Computation

        ALTER TABLE dbo.Computation

        ALTER COLUMN ComputationID  NVARCHAR(10) Not Null

        CREATE INDEX UX_2_COMPUTATION 

        ON dbo.Computation (ComputationID , ComputeGuid): The object 

'PK_Computation' is dependent on column 'ComputationID '.:

      Caused By: Error executing SQL DROP INDEX UX_2_COMPUTATION 

        ON dbo.Computation

        ALTER TABLE dbo.Computation

        ALTER COLUMN ComputationID  NVARCHAR(10) Not Null

        CREATE INDEX UX_2_COMPUTATION 

        ON dbo.Computation (ComputationID , ComputeGuid): The object 

'PK_Computation' is dependent on column 'ComputationID '.:

      Caused By: The object 'PK_Computation' is dependent on column 'ComputationID '.

Thanks,

-Sam

It would be helpful to know what you need to change it from, and what to? What is the type before - what is it supposed to be after? This change here will create a **nullable** `NVARCHAR(25)` column - is that what you want? — marc_s, Dec 18 '12 at 13:43
@marc_sIts a not null column . I need to create a not null column — Sam, Dec 18 '12 at 13:48
So it should be like `ALTER TABLE dbo.Computation ALTER COLUMN ComputationID NVARCHAR(25) Not null ` right? and there is no problem with the syntax for the index? — Sam, Dec 18 '12 at 13:50
That's right - default is `NULL` - if you want something else, you need to explicitly say so. And no, I don't see any issues with the `CREATE INDEX` statement... — marc_s, Dec 18 '12 at 13:52
You have to drop (and re-create) **all indices** (composite or not, primary or not) that include this column that you're about to change — marc_s, Dec 18 '12 at 14:33
@marc_S i get the above exception when i tried dropping a composite index and recreating it — Sam, Dec 19 '12 at 07:15

marc_s · Accepted Answer · 2012-12-18T14:35:49.983

2

Your current statement will change the column to be a nullable NVARCHAR(25) column - is that what you want?

If you want to be sure that your column is going to be NON NULL, you have to explicitly state this:

ALTER TABLE dbo.Computation
ALTER COLUMN ComputationID NVARCHAR(25) NOT NULL

edited Dec 18 '12 at 14:35

answered Dec 18 '12 at 13:59

marc_s

732,580
175
1,330
1,459

4

For **created** columns the default nullability if not specified depends on `SET ANSI_NULL_DFLT_ON` and the "ANSI null default" database option. For altered columns if not specified it will always make them `NULL` even if originally they were `NOT NULL` – Martin Smith Dec 18 '12 at 14:17
@MartinSmith: thanks for the update - always good to have your extra guru-level advice, too! :-) – marc_s Dec 18 '12 at 14:35

Changing data type of column in SQL Server

1 Answers1

Linked