title: Best/most efficient way to convert Nvarchar(max) to nvarchar (30)
body: <p>I was wondering if anyone could give me a pointer with the problem. I assume others have experienced this before and was wondering what is the most efficient.</p>
  
  <p>I have production data that is taking rather long to process, mainly because we haven't set indexes on an ID field.</p>
  
  <p>The ID field is currently a nvarchar(max), so it can't be indexed. This ID is never longer than 30 characters, and I would therefore like to change the data type to nvarchar(30). However, I've noticed that using a statement such as:</p>
  
  <pre><code>ALTER TABLE [dbo].[Table] ALTER COLUMN [Column1] NVARCHAR ( 30 ) NOT NULL
  </code></pre>
  
  <p>Takes a significant amount of time for the smaller tables already. I'm worried it might not even work for the larger tables, as the DiskIO is incredibly high for the smaller tables already.</p>
  
  <p>Would the following be faster and could it work?</p>
  
  <ul>
  <li>Create a new column with the nvarchar(30) data type. </li>
  <li>Then copy the data from the nvarchar(max) column to the nvarchar(30) column. </li>
  <li>Then remove the nvarchar(max) column. </li>
  <li>Then rename the nvarchar(30) column to the name of the nvarchar(max) column?</li>
  </ul>
  
  <p>Does anyone have any suggestions as to what might be even better?</p>
  
  <p>Any pointers/help would be appreciated!</p>