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

Question

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.

I'm sure I'm missing something but why can't you just use DROP INDEX Table.<Index>; CREATE UNIQUE INDEX <Index> ON Table (Col1, Col2, Col3, Col4) — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Mar 19, 2012 at 7:57
@Lieven, I was thinking about this too. Is it possible to safely DROP and then CREATE the index with existing data in the table? — JL.
– JL., Commented Mar 19, 2012 at 7:59
Yes. You can drop and create indexes at will. All you might notice is a drop in performance executing queries while the indexes are gone but dropping and creating a (none-clustered) index has no impact whatsoever on the actual data stored in your tables (Creating a clustered index impacts the physical ordering of your data but again, no data is lost) — Lieven Keersmaekers
– Lieven Keersmaekers, Commented Mar 19, 2012 at 8:11
Unless you have code somewhere that uses the index as a HINT — sqltracy
– sqltracy, Commented Feb 10 at 20:44

Henrik Høyer · Accepted Answer · 2020-12-16 08:43:10Z

112

You cannot alter an index - all you can do is

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

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.

edited Dec 16, 2020 at 8:43

Henrik Høyer

1,8191 gold badge26 silver badges31 bronze badges

answered Mar 19, 2012 at 8:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JL. Over a year ago

Thanks Marc, your proposed solution worked, and script approved by DBA's on our team.

Lieven Keersmaekers Over a year ago

Now you've got me stumped... why ask the question if you have DBA's on your team?

Gabe Over a year ago

Look into DROP_EXISTING in the Create Index page on MSDN. With certain restrictions, you can modify an index. See the reply by Paul White here. sqlservercentral.com/Forums/Topic913722-391-1.aspx

Ozkan Over a year ago

@LievenKeersmaekers why ask the question if you have DBA's on your team? For people having the same question maybe? Overall purpose of StackOverflow?

Brandt Solovij Over a year ago

@LievenKeersmaekers - Code First ~ index(es) should be maintained in Migrations. Migrations written in a non-sql language. Programmers write migrations, not DBAs

jgondev · Accepted Answer · 2021-07-05 09:18:50Z

4

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]

answered Jul 5, 2021 at 9:18

jgondev

2651 silver badge7 bronze badges

Comments

Ed Avis · Accepted Answer · 2017-03-30 08:54:26Z

3

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.

edited Mar 30, 2017 at 8:54

answered Oct 16, 2015 at 10:14

Ed Avis

1,62224 silver badges40 bronze badges

3 Comments

Tom Over a year ago

Where are you getting with sorted_data from? I don't see sorted_data listed as a with, aka "relational_index_option", of the create index Statement in the SQL Server 2008+ docs for that Statement (at: link).

Ed Avis Over a year ago

with sorted_data is something I remember from the old days of Sybase SQL Server. Microsoft has removed some of the funky index creation options that Sybase supported, but not this one. However a quick experiment on MSSQL 2008 R2 shows that it may now be a no-op; no error is given if the data in the table isn't sorted. So I will revise my answer.

Ed Avis Over a year ago

A quick search shows that the option was documented for MSSQL 2000.

bguiz · Accepted Answer · 2021-03-12 03:06:56Z

0

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

edited Mar 12, 2021 at 3:06

bguiz

28.5k49 gold badges165 silver badges257 bronze badges

answered Mar 6, 2021 at 5:40

Jayanth Balina

11

Comments

Gwen Gamblin · Accepted Answer · 2024-08-04 11:01:45Z

0

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]

answered Aug 4, 2024 at 11:01

Gwen Gamblin

11 bronze badge

Collectives™ on Stack Overflow

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

5 Answers 5

5 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related