如何使用系统SQL实体自动创建非聚集索引

• 攻略

技术如何使用系统SQL实体自动创建非聚集索引小编给大家分享一下如何使用系统SQL实体自动创建非聚集索引，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一

边肖将与您分享如何使用系统SQL实体来自动创建非聚集索引。相信大部分人都不太了解，所以分享这篇文章给大家参考。希望你看完这篇文章能有很多收获。我们一起来看看吧！

00-1010一直以来，关于索引的常见问题是：确定哪些部分的索引是保证数据库良好性能所必需的。在本文中，作者将提供一个解决这个问题的方法。这个用例中的所有代码都基于名为dm_db_missing_index_details的SQL Server系统视图。

00-1010在开始安装之前，多了解一些dm_db_missing_index_details会更有好处。

Dm_db_missing_index_details返回缺失索引的详细信息。在本文中，我们将更加关注以下几个专栏：

Index_handle:这是一个唯一的跨服务器标识符，它标记了一个特定的缺失索引。

Equality_columns:包含相等谓词的所有列。

不等式列3360包含用于其他比较的所有列。

包含在包含列索引中的查询需要列

Statement:补充了完整索引中缺少的表名。

引言

该系统的实施基于以下三个实体：

一个简单的函数，可以计算要创建的索引的名称

简化数据挖掘数据库缺失索引细节的用户视图

为每个索引创建声明的过程。

作者选择将这个系统分成三个进程，但事实上，将存储过程和视图结合起来是可行的。我没有选择后一种方法，因为我想在创建索引之前从业务逻辑中检查哪些索引存在。

00-1010函数fn_Index_CreateIndexName

在该功能中，有三个输入参数：

@equality_columns

@equality_columns

@index_handlE这个函数的目的是为每个需要的索引创建一个唯一的名称。

因此，首先拼接两个输入变量@equality_columns和@equality_columns。如果拼接后的结果超过120个字符，将被截断为第120个字符。

为什么是120个字符？

因为在SQL Server中，命名的最大长度是128个字符。该函数在名称@index_handlE的末尾添加一个字段，以确保唯一的索引名称。

CREATEFUNCTION[dbo]。[fn _ Index _ CreateIndexName](@ equality _ columns varchar(4000)，_

@不等式_列VARCHAR(4000)，@ index _ handlEINT)RETURNSVARCHAR(128)

如同

开始

DECLARE@IndexNameNVARCHAR(255)

SET @ index name=ISNULL(@ equality _columns，@不等式_ columns)

SET @ index name=LTRIM(REPLACE(@ index name，'['，' _ '))

SET @ index name=RTRIM(REPLACE(@ index name，']'，' _ '))

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实体自动创建非聚集索引”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！

内容来源网络，如有侵权，联系删除，本文地址：https://www.230890.com/zhan/113331.html

赞 (0)

相关推荐

  • python写自动化脚本用什么框架(python自动化脚本如何开展)

    技术Python的自动化脚本有哪些Python的自动化脚本有哪些，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。前言:大家平时有没有注意到你每

    攻略 2021年12月20日

  • ABAP如何显示表字段和描述

    技术ABAP如何显示表字段和描述这篇文章将为大家详细讲解有关ABAP如何显示表字段和描述，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。显示表字段和描述REPORT ZWT.TABLES

    攻略 2021年11月18日

  • 十大名木手串,全球十大最名贵木材有哪些

    技术十大名木手串,全球十大最名贵木材有哪些感谢推荐十大名木手串！我个人是很喜欢盘玩木材的，尤其是小叶紫檀。最近迷上了桃木，因为桃木那独有的纹理是非常吸引我的。如下这些就是最世界珍贵的十种木材。一、乌木乌木（阴沉木）兼备木

    生活 2021年11月1日

  • mysql入门基础知识点有哪些

    技术mysql入门基础知识点有哪些本篇内容介绍了“mysql入门基础知识点有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有

    攻略 2021年12月3日

  • Tomcat9请求处理流程与启动部署过程的示例分析

    技术Tomcat9请求处理流程与启动部署过程的示例分析这篇文章主要为大家展示了“Tomcat9请求处理流程与启动部署过程的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下

    攻略 2021年12月11日

  • 怎么解析zookeeper 原理

    技术怎么解析zookeeper 原理今天就跟大家聊聊有关怎么解析zookeeper 原理，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。一、zookeeper的角色

    攻略 2021年11月12日