Monthly Archives: July 2013

Reduce nvarchar size on an indexed column in SqlServer

You migth encounter the situation that someone created a column thats way bigger than needed.
In my case it was an nvarchar(255) where a nvarchar(50) would suffice.

The column also had an index. You can’t “reduce” the size with a simple alter.

You have to:
– create a new column with the desired size
– “copy” the original values in the new column
– (set the new column not nullable) depends on the case
– drop the original index
– create the new index on the new column
– drop the “old” column
– rename the new column so it matches the expected name

Here’s the code

alter table SampleTable add Id1 nvarchar(50)

go

update SampleTable set Id1 = Id

alter table SampleTable alter column Id1 nvarchar(50) not null

go

ALTER TABLE [dbo].[SampleTable] DROP CONSTRAINT [PK_SampleTable]

go

ALTER TABLE [dbo].[SampleTable] ADD  CONSTRAINT [PK_SampleTable] PRIMARY KEY CLUSTERED
(
	Id1 ASC
)
go

alter table [SampleTable] drop column Id

go
exec sp_RENAME 'SampleTable.Id1', 'Id' , 'COLUMN'