SQL Server: How to modify existing table structure

Suppose you have a table in a production database and you need to modify table structures.

Let's say we have a table with following columns and properties

TableA

Columns:
ID int
FName varchar(50)
LName varchar(50)
version nvarchar(10)
file ntext
City char(10)


say we have primary key on ID


Now someone asked to change version nvarchar data type to varchar datatype and file ntext to text datatype. Also remember that we have data in this table.

So how would we modify the structure of table without change any values.

Here's How:

--Step 1. Drop any primary key if there is one.
--Step 2. Drop any index if it exists (may not have to do if step cover it).
--Step 3. Alter table--change column, insert or remove.
--step 4. Recreate primary key
--step 5. Recreate index.

--Step 1. Drop any primary key if there is one.

ALTER TABLE  [dbo].[TableA]

DROP CONSTRAINT constraintname_PK

GO

;

--Step 3. Alter table--change column, insert or remove.

ALTER TABLE [dbo].[TableA]

ALTER COLUMN [version ] [varchar](10) ;

when you try to convert ntext to text using above statement, it will give an error. To do that first convert to varchar(max) and then to text data type.

ALTER TABLE TableA

ALTER COLUMN  file VARCHAR(MAX)

ALTER TABLE TableA

ALTER COLUMN  file text

ALTER TABLE [dbo].[TableA] ADD  CONSTRAINT [constraintname_PK] PRIMARY KEY NONCLUSTERED 

(

[ID ] ASC,

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO