66

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.

starball
  • 20,030
  • 7
  • 43
  • 238
JL.
  • 78,954
  • 126
  • 311
  • 459
  • 2
    I'm sure I'm missing something but why can't you just use `DROP INDEX Table.; CREATE UNIQUE INDEX ON Table (Col1, Col2, Col3, Col4)` – Lieven Keersmaekers Mar 19 '12 at 07: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. Mar 19 '12 at 07:59
  • 1
    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 Mar 19 '12 at 08:11

4 Answers4

104

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.

Henrik Høyer
  • 1,225
  • 1
  • 19
  • 27
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    Thanks Marc, your proposed solution worked, and script approved by DBA's on our team. – JL. Mar 19 '12 at 08:30
  • 51
    Now you've got me stumped... why ask the question if you have DBA's on your team? – Lieven Keersmaekers Mar 19 '12 at 08:40
  • 5
    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. http://www.sqlservercentral.com/Forums/Topic913722-391-1.aspx – Gabe Apr 21 '14 at 15:21
  • 3
    @LievenKeersmaekers `why ask the question if you have DBA's on your team?` For people having the same question maybe? Overall purpose of StackOverflow? – Ozkan Jun 17 '21 at 08:15
  • @LievenKeersmaekers - Code First ~ index(es) should be maintained in Migrations. Migrations written in a non-sql language. Programmers write migrations, not DBAs – Brandt Solovij Jun 17 '21 at 15:27
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.

Ed Avis
  • 1,350
  • 17
  • 36
  • 1
    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](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql)). – Tom Mar 29 '17 at 20:42
  • 2
    `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 Mar 30 '17 at 08:50
  • A quick search shows that the option was documented for MSSQL 2000. – Ed Avis Mar 30 '17 at 08:57
2

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]
jgondev
  • 236
  • 1
  • 7
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
bguiz
  • 27,371
  • 47
  • 154
  • 243
Jayanth Balina
  • 11