溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
小編給大家分享一下如何使用系統SQL實體自動創建非聚集索引，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一直以來，關于索引的常見問題是：判斷哪部分索引對保證數據庫的良好性能是必需的。在本文中，筆者將提供針對該問題的解決方案。本文用例中的所有代碼都基于名為dm_db_missing_index_details 的 SQL Server 系統視圖。
在開始安裝前，進一步了解 dm_db_missing_index_details 會更有益處。
dm_db_missing_index_details 會返回缺失索引的細節信息。在本文中，我們將更關注以下幾列：
index_handle：它是一個獨特的跨服務器標識符，并且標志一個特定的缺失索引。
equality_columns：包含用于相等謂詞的所有列
inequality_columns:包含用于其他比較的所有列
included columns索引中所包含的查詢必要出現列
statement: 補充完整索引缺失的表名
本系統的實現基于以下三個實體：
一個可以計算出待創建索引名稱的簡單函數
一個用于簡化 dm_db_missing_index_details的用戶視圖
一個為每個索引創建聲明的進程
筆者選擇將這個系統分為三段進程，但實際上合并存儲過程和視圖也是可行的。筆者之所以沒有選擇后一種做法是因為想在創建索引之前先從業務邏輯檢查一下存在哪些索引。
函數 fn_Index_CreateIndexName
在這個函數中，有三個輸入參數：
@equality_columns @equality_columns @index_handlE
該函數的目的是為每個期望創建的索引都創建一個唯一名稱。
因此，首先拼接@equality_columns和@equality_columns兩個輸入變量，如果拼接后所得結果超過120個字符，那就截取至第120個字符。
為什么是120個字符？
因為在SQL Server中，命名最大長度為128個字符。這個函數在 @index_handlE 名字結尾添加字段以便保證唯一的索引名。
CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _ @Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128) AS BEGIN DECLARE @IndexName NVARCHAR(255) SET @IndexName = ISNULL(@equality_columns,@Inequality_columns) SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_')) SET @IndexName = RTRIM(REPLACE(@IndexName,']','_')) SET @IndexName = REPLACE(@IndexName,',','') SET @IndexName = REPLACE(@IndexName,'_ _','_') IF LEN(@IndexName) > 120 BEGIN SET @IndexName = SUBSTRING(@IndexName,0,120) END SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15)) RETURN @IndexName END
視圖 vw_Index_MissingIndex
該視圖基于dm_db_missing_index_details和 sys.databases 表，并使用fn_Index_CreateIndexName 函數來計算缺失的索引名。
CREATE VIEW [dbo].[vw_Index_MissingIndex] AS SELECT '[' + d.name + ']' as DBName, [dbo].[fn_Index_CreateIndexName]_ (mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID, REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns, REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns, mid.Included_columns, mid.[statement] FROM sys.dm_db_missing_index_details mid INNER JOIN sys.databases d on d.database_id = mid.database_id
存儲過程 usp_Index_MissingIndexCreationStatements
該存儲過程基于 vw_Index_MissingIndex，并且輸出索引創建語句。
CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
AS
DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX)
-- PREPARE PLACEHOLDER
SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS(SELECT * _
FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )
BEGIN
CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'
SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, _
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, _
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END;' + char(13) + char(10)
-- STATEMENT CREATION
SELECT
DBName,
CASE
WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _
mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.equality_columns,'') +
' ASC,' +
COALESCE(mid.Inequality_columns,'') +
' ASC
)' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,_
'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.Inequality_columns,'') +
' ASC
) ' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _
mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.equality_columns,'') + ' ASC
) ' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
ELSE NULL
END AS Index_Creation_Statement,
' DROP INDEX [IX_' + mid.ID + '] ON ' + mid.[statement] _
+ + char(13) + char(10) AS Index_Drop_Statement
FROM [dbo].[vw_Index_MissingIndex] AS mid -- CREATE FUNCTION fn_Index_CreateIndexName
CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _
@Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
AS
BEGIN
DECLAR
E @IndexName NVARCHAR(MAX)
SET @IndexName = ISNULL(@equali
ty_columns,@Inequality_columns)
SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))
SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))
SET @IndexName = REPLACE(@IndexName,',','')
SET @IndexName = REPLACE(@IndexName,'_ _','_')
IF LEN(@IndexName) > 120
BEGIN
SET @IndexName = SUBSTRING(@IndexName,0,120)
END
SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))
RETURN @IndexName
END
GO
-- CREATE FUNCTION vw_Index_MissingIndex
CREATE VIEW [dbo].[vw_Index_MissingIndex]
AS
SELECT '[' + d.name + ']' as DBName,
[dbo].[fn_Index_CreateIndexName]_
(mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID,
REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns,
REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns,
mid.Included_columns,
mid.[statement]
FROM sys.dm_db_missing_index_details mid
INNER JOIN sys.databases d
on d.database_id = mid.database_id
GO
CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
AS
DECLARE @IndexCreationPlaceholder_Start AS NVARCHAR(MAX)
DECLARE @IndexCreationPlaceholder_End AS NVARCHAR(MAX)
-- PREPARE PLACEHOLDER
SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS_
(SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )
BEGIN
CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}'
SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF, _
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, _
ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END;' + char(13) + char(10)
-- STATEMENT CREATION
SELECT
DBName,
CASE
WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _
mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.equality_columns,'') +
' ASC,' +
COALESCE(mid.Inequality_columns,'') +
' ASC
)' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _
mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.Inequality_columns,'') +
' ASC
) ' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', _
mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
+ '
( ' +
COALESCE(mid.equality_columns,'') + ' ASC
) ' +
COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
+ @IndexCreationPlaceholder_End
ELSE NULL
END AS Index_Creation_Statement,
' DROP INDEX [IX_' + mid.ID + '] ON ' + mid.[statement] _
+ + char(13) + char(10) AS Index_Drop_Statement
FROM [dbo].[vw_Index_MissingIndex] AS mid
GO以上是“如何使用系統SQL實體自動創建非聚集索引”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
