Alter a table which has non-unique non-clustered index to add a column

I have a table named Person which already have few non-unique non-clustered index and one clustered index (based on primary key).

I have to write two queries:

• I need to alter the table to add column birthplace. Do I need to check any index while writing the alter table person add birthplace varchar(128) not null?

• My next requirement is I have another column named cityCode narvar(10). This I need to include in my existing non-unique, non-clustered index. How should I write my script? should I drop my index and recreate it?

Please suggest. I am able to find some related questions, but not clear about non-unique non-clustered index.

Answer

alter table person add birthplace varchar(128) not null

Be aware when you specify not null without default value you will get error. It’s recommended to add default value. But in case you can not consider any, then add column as nullable then update it later. At the end make it NOT NULL using below code:

alter table person Alter column birthplace varchar(128) not null

And if you want to involve new column to an existing index then use CREATE INDEX with DROP_EXISTING option. The performance is more efficient. To do so, generate a create index script then modify script by adding new column to the column list of index and then add this at the end of CREATE INDEX statement.

WITH (DROP_EXISTING = ON)