Certaines opérations sur les index ne peuvent pas être réalisées via la commande ALTER. Ainsi, par exemple, la modification de données telles que le taux de remplissage (Fillfactor) ou encore les paramétrages d’autorisation de verrous ligne / page, nécessite le passage par la commande de création CREATE INDEX.

Le problème est qu’il est nécessaire de générer un script de reconstruction des index, en y apportant éventuellement quelques modifications pour notre besoin propre (modifier le taux de remplissage, …).

Pour cela, je vous propose le script suivant, qui générera un ensemble de lignes de commandes que vous pourrez aisément intégrer dans votre processus d’upgrade de base.

-- Générateur de script de création d'index
-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)

set nocount on

-- Déclaration de variables
declare @Liste table (index_id int,
						object_id int,
						DebutCreate varchar(max),
						FinCreate varchar(max),
						MilieuInclude varchar(max) DEFAULT ') INCLUDE (',
						ListeColonnes varchar(max),
						ListeColonnesIncluses varchar(max))

declare @index_id int,@object_id int
declare @ListeColonnes varchar(max), @ListeColonnesIncluses varchar(max)

-- Index à prendre en compte, avec mise en place des paramètres
insert into @Liste(index_id,object_id,DebutCreate,FinCreate)
select i.index_id,t.object_id,
		'CREATE '
		+ case when i.is_unique=1 then 'UNIQUE ' else '' end
		+ case when i.index_id=1 then 'CLUSTERED ' else 'NONCLUSTERED ' end
		+ 'INDEX '+QUOTENAME(i.name)+' ON '+QUOTENAME(s.name)+'.'+QUOTENAME(t.name)+' (',
		case when i.filter_definition is null then '' else ') WHERE ('+ i.filter_definition end
			+') WITH ('
			+ 'PAD_INDEX=' + case when i.is_padded=1 then 'ON' else 'OFF' end
			+ ',FILLFACTOR = ' + convert(varchar,case when i.fill_factor=0 then 100 else i.fill_factor end)
			+ ',SORT_IN_TEMPDB = ON'
			+ ',IGNORE_DUP_KEY = ' + case when i.ignore_dup_key=1 then 'ON' else 'OFF' end
			+ ',DROP_EXISTING = ON'
			+ ',ALLOW_ROW_LOCKS = ' + case when i.allow_row_locks=1 then 'ON' else 'OFF' end
			+ ',ALLOW_PAGE_LOCKS = ' + case when i.allow_page_locks=1 then 'ON' else 'OFF' end
			+') ON '+QUOTENAME(ds.name)
			+case when ds.type='PS' then '('+QUOTENAME(cp.name)+')' else '' end
		 from sys.indexes i
join sys.tables t on t.object_id=i.object_id
join sys.schemas s on s.schema_id=t.schema_id
join sys.data_spaces ds on ds.data_space_id=i.data_space_id
left join sys.index_columns icp on icp.object_id=i.object_id
								and icp.index_id=i.index_id
								and icp.partition_ordinal=1
left join sys.columns cp on cp.object_id=t.object_id
						and cp.column_id=icp.column_id
where i.type in (1,2) and t.type='U' and i.is_primary_key=0

-- Pour chaque index
DECLARE curseur CURSOR FOR 
SELECT index_id,object_id
FROM @Liste

OPEN curseur
FETCH NEXT FROM curseur INTO @index_id,@object_id

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Construction de la liste des colonnes principales
	SELECT @ListeColonnes=','
	SELECT @ListeColonnes+=quotename(c.name)+case when ic.is_descending_key=1 then ' DESC' else ' ASC' end+','
	FROM sys.index_columns ic
		join sys.columns c on c.object_id=ic.object_id
							and c.column_id=ic.column_id
	where ic.object_id=@object_id
		and ic.index_id=@index_id
		and ic.key_ordinal>0
	order by key_ordinal

	-- Construction de la liste des colonnes incluses
	SELECT @ListeColonnesIncluses=','
	SELECT @ListeColonnesIncluses+=quotename(c.name)+','
	FROM sys.index_columns ic
		join sys.columns c on c.object_id=ic.object_id
							and c.column_id=ic.column_id
	where ic.object_id=@object_id
		and ic.index_id=@index_id
		and ic.key_ordinal=0

	-- Mise en forme des listes de colonnes
	SELECT @ListeColonnes=SUBSTRING(@ListeColonnes,2,len(@ListeColonnes)-2)
	select @ListeColonnesIncluses=nullif(@ListeColonnesIncluses,',')
	SELECT @ListeColonnesIncluses=SUBSTRING(@ListeColonnesIncluses,2,len(@ListeColonnesIncluses)-2)

	update @Liste
	set ListeColonnes=@ListeColonnes,
		ListeColonnesIncluses=@ListeColonnesIncluses
	where object_id=@object_id
		and index_id=@index_id

	FETCH NEXT FROM curseur INTO @index_id,@object_id
END

CLOSE curseur
DEALLOCATE curseur

-- Présentation du résultat final
select DebutCreate + ListeColonnes + isnull(MilieuInclude+ListeColonnesIncluses, '') + FinCreate +';' from @Liste

Comme vous pouvez le constater, ce script présente les principaux paramètres de configuration d’index (colonnes normales avec ordre ascendant ou descendant, colonnes incluses, filtres, partitionnement, …).

Il se concentre uniquement sur les index hors clé primaire, un script dédié étant présenté pour cela dans un autre article.

N’hésitez pas à le tester et à m’en donner des nouvelles.