La présente fonction permet de recomposer les caractéristiques logiques d’un index existant, à savoir, la liste des colonnes indexées avec leur position ordinale et leur sens ainsi que le lise des colonnes incluses et l’éventuel filtre (SQL 2008 only).
1 – Pour SQL Server 2008 (avec les filtres)
CREATE FUNCTION dbo.F_GET_INDEX_KEY (@OID INT, @IID INT)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @RETVAL VARCHAR(max);
WITH
T0 AS
(
SELECT '[' + c.name +']' as name, key_ordinal,
is_descending_key, is_included_column,
c.column_id
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @OID
AND ic.index_id = @IID
),
TK AS
(
SELECT CAST(name
+ CASE is_descending_key
WHEN 1 THEN ' DESC, '
WHEN 0 THEN ' ASC, '
ELSE ''
END AS VARCHAR(max)) AS Index_Key,
key_ordinal
FROM T0
WHERE key_ordinal = 1
AND is_included_column = 0
UNION ALL
SELECT CAST(Index_Key + name
+ CASE is_descending_key
WHEN 1 THEN ' DESC, '
WHEN 0 THEN ' ASC, '
ELSE ''
END AS VARCHAR(max)),
T0.key_ordinal
FROM T0
INNER JOIN TK
ON T0.key_ordinal = TK.key_ordinal + 1
WHERE is_included_column = 0
),
TC AS
(
SELECT name, ROW_NUMBER() OVER(ORDER BY column_id) AS N,
ROW_NUMBER() OVER(ORDER BY column_id DESC) AS NN
FROM T0
WHERE is_included_column = 1
),
TI AS
(
SELECT CAST(name +', ' AS VARCHAR(max)) AS Include_Key,
N, NN
FROM TC
WHERE N = 1
UNION ALL
SELECT CAST(Include_Key + name + ', ' AS VARCHAR(max)),
TC.N, TC.NN
FROM TC
INNER JOIN TI
ON TC.N = TI.N + 1
),
TF AS
(
SELECT filter_definition AS F
FROM sys.indexes
WHERE object_id = @OID
AND index_id = @IID
)
SELECT @RETVAL = '(' + SUBSTRING(Index_Key, 1, LEN(Index_Key) - 1) +')'
+ COALESCE(' INCLUDE (' + SUBSTRING(Include_Key, 1, LEN(Include_Key) - 1) +')', '')
+ COALESCE(' WHERE (' + (SELECT F FROM TF) + ')', '')
FROM TK
LEFT OUTER JOIN TI
ON NN = 1;
RETURN @RETVAL
END
GO
2 – Pour SQL Server 2005 (sans les filtres)
CREATE FUNCTION dbo.F_GET_INDEX_KEY (@OID INT, @IID INT)
RETURNS VARCHAR(max)
AS
BEGIN
DECLARE @RETVAL VARCHAR(max);
WITH
T0 AS
(
SELECT '[' + c.name +']' as name, key_ordinal,
is_descending_key, is_included_column,
c.column_id
FROM sys.index_columns AS ic
INNER JOIN sys.columns AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = @OID
AND ic.index_id = @IID
),
TK AS
(
SELECT CAST(name
+ CASE is_descending_key
WHEN 1 THEN ' DESC, '
WHEN 0 THEN ' ASC, '
ELSE ''
END AS VARCHAR(max)) AS Index_Key,
key_ordinal
FROM T0
WHERE key_ordinal = 1
AND is_included_column = 0
UNION ALL
SELECT CAST(Index_Key + name
+ CASE is_descending_key
WHEN 1 THEN ' DESC, '
WHEN 0 THEN ' ASC, '
ELSE ''
END AS VARCHAR(max)),
T0.key_ordinal
FROM T0
INNER JOIN TK
ON T0.key_ordinal = TK.key_ordinal + 1
WHERE is_included_column = 0
),
TC AS
(
SELECT name, ROW_NUMBER() OVER(ORDER BY column_id) AS N,
ROW_NUMBER() OVER(ORDER BY column_id DESC) AS NN
FROM T0
WHERE is_included_column = 1
),
TI AS
(
SELECT CAST(name +', ' AS VARCHAR(max)) AS Include_Key,
N, NN
FROM TC
WHERE N = 1
UNION ALL
SELECT CAST(Include_Key + name + ', ' AS VARCHAR(max)),
TC.N, TC.NN
FROM TC
INNER JOIN TI
ON TC.N = TI.N + 1
),
SELECT @RETVAL = '(' + SUBSTRING(Index_Key, 1, LEN(Index_Key) - 1) +')'
+ COALESCE(' INCLUDE (' + SUBSTRING(Include_Key, 1, LEN(Include_Key) - 1) +')', '')
FROM TK
LEFT OUTER JOIN TI
ON NN = 1;
RETURN @RETVAL
END
GO
————–
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *