SQL Server : comment écrire une instruction alter index pour ajouter une colonne à l'index unique ?

Question

J'ai un UNIQUE, NON CLUSTERED sur une table qui utilise actuellement 4 colonnes pour l'index.

Je veux créer un script alter script qui peut simplement ajouter une autre colonne à cet index. Le nouveau type de colonne est varchar .

La base de données est SQL Server 2005.

Merci d'avance.

Answer 1

Vous ne pouvez pas modifier un index - tout ce que vous pouvez faire, c'est

1. supprimer l'ancien index ( DROP INDEX (indexname) ON (tablename) )

2. recréer le nouvel index avec la colonne supplémentaire :

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

En ALTER INDEX dans SQL Server (voir documents ) est disponible pour modifier certaines propriétés (propriétés de stockage, etc.) d'un index existant, mais il ne permet pas de modifier les colonnes qui composent l'index.

Answer 2

Si la nouvelle colonne que vous ajoutez à l'index se trouve à la fin de la liste des colonnes - en d'autres termes, si la liste des colonnes de l'ancien index est un préfixe de la liste des colonnes du nouvel index - les lignes qui sont triées en fonction des anciennes colonnes seront toujours triées en fonction des nouvelles colonnes. Dans Sybase SQL Server et peut-être dans des versions plus anciennes de Microsoft SQL Server, il existait une fonction with sorted_data qui permet de déclarer que les lignes sont déjà triées. Mais sur MSSQL 2008 R2, cela ne semble avoir aucun effet ; l'option est acceptée mais ignorée silencieusement. Quoi qu'il en soit, je pense que l'option était surtout utile avec les index en grappe.

D'autres ont mentionné with drop_existing qui a l'air génial, mais qui est réservé aux versions plus chères de MSSQL.

Answer 3

J'ai peut-être 9 ans de retard mais voici comment je procède (cela supprime l'index existant et en crée un nouveau avec les colonnes de la liste) :

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]

Answer 4

J'espère que modifier l'index signifie qu'il faut d'abord supprimer l'index et le recréer.

syntaxe :

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