title: Changing data type of column in SQL Server
body: <p>I have to change datatype of  a column in SQL Server. So what are the constraints in doing?</p>
  
  <p>I know I have to remove index and other constraints?</p>
  
  <p>Do I have to remove not null check ?</p>
  
  <p>What other things do I have to check before altering the datatype?</p>
  
  <p>I need to remove the constraints and alter the table and then add the constraints again.</p>
  
  <p>Is this the right way to do so ? </p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Here <code>UX_1_COMPUTATION</code> is the unique index name, <code>Computation</code> is the table name and 
  <code>ComputationID</code> is the column name.</p>
  
  <p>Is this correct ?</p>
  
  <p><code>Update</code></p>
  
  <p>So if there is a <code>composite index</code> 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 .</p>
  
  <p>When i tried executing the following statement</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>It is throwing the following exception</p>
  
  <blockquote>
    <p>SQL DROP INDEX UX_2_COMPUTATION </p>
  </blockquote>
  
  <pre><code>        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 '.
  </code></pre>
  
  <p>Thanks,</p>
  
  <p>-Sam</p>