CODEWITHSUNDEEP
sql-server-2005indexingalter
JL.
JL.

Reputation: 81342

SQL Server: how to write an alter index statement to add a column to the unique index?

I have a UNIQUE, NON CLUSTERED index on a table that is currently using 4 columns for the index.

I want to create an alter script that can merely add another column to this index. The new column type is varchar.

The database is SQL Server 2005.

Upvotes: 70

Views: 131578

Answers (5)

Gwen Gamblin
Gwen Gamblin

Reputation: 1

For UNIQUE keys, DROP may not work with any solutions above, so I add this option.

Delete the index from the table. In the tables list, for the table, for Keys, find the key, right click, select Delete and approve the deletion.

Then execute the create SQL, making sure DROP_EXISTING = OFF (because it does not exist anymore).

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME]
(
    [COLUMN1] DESC,
    [COLUMN2] ASC
) 
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = ON,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) 
ON [PRIMARY]

Upvotes: 0

jgondev
jgondev

Reputation: 246

I might be 9 years late but this is how I do it (this drops the existing index and creates the new one with the columns in the list):

CREATE UNIQUE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME]
(
    [COLUMN1] DESC,
    [COLUMN2] ASC
) 
WITH
(
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = ON,
    DROP_EXISTING = ON,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
) 
ON [PRIMARY]

Upvotes: 2

Jayanth Balina
Jayanth Balina

Reputation: 11

I hope alter index means first we have to drop the index and create the index again

syntax:

if exists
(
select * from sys.indexes where name ='ix_name'
)
BEGIN
DROP INDEX Table.index name
END

IF NOT EXISTS
(
select * from sys.indexes where name ='ix_name'
)
BEGIN
CREATE NONCLUSTERED INDEX 
ON TABLENAME
(
COLUMN1,
COLUMN2,
COLUMN3,
COLUMN4,
--Whatever column u want to add
)
end
go

Upvotes: 0

marc_s
marc_s

Reputation: 755207

You cannot alter an index - all you can do is

  1. drop the old index (DROP INDEX (indexname) ON (tablename))

  2. re-create the new index with the additional column in it:

       CREATE UNIQUE NONCLUSTERED INDEX (indexname)
   ON dbo.YourTableName(columns to include)

The ALTER INDEX statement in SQL Server (see docs) is available to alter certain properties (storage properties etc.) of an existing index, but it doesn't allow changes to the columns that make up the index.

Upvotes: 108

Ed Avis
Ed Avis

Reputation: 1502

If the new column you are adding to the index is on the end of the list of columns - in other words, if the column list of the old index is a prefix of the column list of the new index - then rows which are sorted by the old columns will still be sorted by the new columns. In Sybase SQL Server and perhaps older versions of Microsoft SQL Server, there was a with sorted_data option to let you declare that the rows were already sorted. But on MSSQL 2008 R2 it appears to have no effect; the option is accepted but silently ignored. In any case I think the option was mostly useful with clustered indexes.

Others mentioned with drop_existing, which sounds great, but is for more expensive versions of MSSQL only.

Upvotes: 3

Related Questions