SQL-Ex blog

Обзор

Начнем с копии WideWorldImporters, создадим таблицу и индексы для неё. Обратите внимание, что я добавляю столбец в данную таблицу с именем OrderID_and_OrderLineID, который содержит конкатенацию значений OrderID и OrderLineID. В Sales.OrderLines столбец OrderLine уникален, а OrderID - нет. Конкатенация этих двух столбцов является уникальной, и я делаю её моим первичным ключом, а также кластеризованным ключом (напомню, что вы можете иметь первичный ключ, который не является кластеризованным ключом).

USE [WideWorldImporters]

GO

 DROP TABLE IF EXISTS [Sales].[SQLskills_OrderLines];

GO

 CREATE TABLE [Sales].[SQLskills_OrderLines](

     [OrderLineID] [int] NOT NULL,

     [OrderID] [int] NOT NULL,

     [OrderID_and_OrderLineID] [bigint] NOT NULL,

     [StockItemID] [int] NOT NULL,

     [Description] [nvarchar](100) NOT NULL,

     [PackageTypeID] [int] NOT NULL,

     [Quantity] [int] NOT NULL,

     [UnitPrice] [decimal](18, 2) NULL,

     [TaxRate] [decimal](18, 3) NOT NULL,

     [PickedQuantity] [int] NOT NULL,

     [PickingCompletedWhen] [datetime2](7) NULL,

     [LastEditedBy] [int] NOT NULL,

     [LastEditedWhen] [datetime2](7) NOT NULL,

     CONSTRAINT [PK_Sales_SQLskills_OrderLines] PRIMARY KEY CLUSTERED

     (

          [OrderID_and_OrderLineID] ASC

     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]

     ) ON [USERDATA];

GO

 INSERT INTO [Sales].[SQLskills_OrderLines](

     [OrderLineID]

     ,[OrderID]

     ,[OrderID_and_OrderLineID]

     ,[StockItemID]

     ,[Description]

     ,[PackageTypeID]

     ,[Quantity]

     ,[UnitPrice]

     ,[TaxRate]

     ,[PickedQuantity]

     ,[PickingCompletedWhen]

     ,[LastEditedBy]

     ,[LastEditedWhen]

     )

SELECT

     [OrderLineID]

     ,[OrderID]

     ,CAST(CAST([OrderID] AS VARCHAR(20)) + CAST([OrderLineID] AS VARCHAR(20)) AS BIGINT)

     ,[StockItemID]

     ,[Description]

     ,[PackageTypeID]

     ,[Quantity]

     ,[UnitPrice]

     ,[TaxRate]

     ,[PickedQuantity]

     ,[PickingCompletedWhen]

     ,[LastEditedBy]

     ,[LastEditedWhen]

FROM [Sales].[OrderLines];

GO

Мы будем использовать версию Kimberly для процедуры sp_helpindex - sp_SQLskills_helpindex, чтобы посмотреть, что на самом деле находится в индексе, и где. Я предпочитаю эту версию встроенной sp_helpindex, т.к. она включает информацию о включенных столбцах, фильтрах и говорит о том, какие столбцы находятся в дереве, а какие на листовом уровне.

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Кластеризованный индекс для SQLskills_OrderLines

Выход широкий, который не годится для публикации, поэтому вам может потребоваться увеличить изображение. Я также обрезала включенные и фильтрованные столбцы, как несущественные для данного обсуждения.

Обратите внимание, что только столбец, который является конкатенированными OrderID и OrderLineID находится в columns_in_tree, и все столбцы находятся в columns_in_leaf (поскольку листовой уровень кластеризованного индекса содержит все данные). Теперь мы добавим некластеризованный индекс по OrderID:

CREATE NONCLUSTERED INDEX [NCI_OrderID]

     ON [Sales].[SQLskills_OrderLines] ([OrderID])

     ON [USERDATA];

GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Некластеризованный индекс для SQLskills_OrderLines

В выводе sp_SQLskills_helpindex обратите внимание на то, что OrderLineID является единственным столбцом в columns_in_tree, а оба OrderLineID и OrderID_and_OrderLineID находятся в columns_in_leaf.

Помните, что когда вы определяете некластерзованный индекс как уникальный, он не добавляет кластеризованный ключ на уровень дерева, а только на листовой уровень. Но если некластеризованный индекс объявлен неуникальным, то SQL Server добавляет кластеризованный ключ на уровень дерева, чтобы однозначно идентифицировать строку.

Если мы создали кластеризованный индекс, который мы также не определили как уникальный, то SQL Server добавляет UNIQUIFIER (уникальный идентификатор) в ключ кластеризованного индекса, который затем также добавляется в некластеризованный индекс.

Определение кластеризованного ключа как части некластеризованного ключа

Глядя на индексы, я заметила следующее:

CREATE NONCLUSTERED INDEX [NCI_OrderID_2]     

     ON [Sales].[SQLskills_OrderLines] ([OrderID], [OrderID_and_OrderLineID])

     ON [USERDATA];

GO

Тот же индекс, что и NCI_OrderID, который я создавала, но кто-то специально поместил кластерный ключ в ключ некластеризованного индекса. Я всегда спрашиваю, зачем они добавили кластеризованный ключ. Обычно отвечают так: "Я знаю, что это понадобится для запроса". Прекрасно, но поймите, что не имеет значения, добавляете вы кластеризованный ключ в ключ некластеризованного индекса, или нет; если вы не сделаете это, SQL Server добавит его.

Пока мы рассматривали простой кластеризованный ключ, состоящий из единственного столбца. Что если у нас будет несколько столбцов? Немного изменим нашу таблицу:

USE [WideWorldImporters]

GO

DROP TABLE IF EXISTS [Sales].[SQLskills_OrderLines];

GO

CREATE TABLE [Sales].[SQLskills_OrderLines](

     [OrderLineID] [int] NOT NULL,

     [OrderID] [int] NOT NULL,

     [OrderID_and_OrderLineID] [bigint] NOT NULL,

     [StockItemID] [int] NOT NULL,

     [Description] [nvarchar](100) NOT NULL,

     [PackageTypeID] [int] NOT NULL,

     [Quantity] [int] NOT NULL,

     [UnitPrice] [decimal](18, 2) NULL,

     [TaxRate] [decimal](18, 3) NOT NULL,

     [PickedQuantity] [int] NOT NULL,

     [PickingCompletedWhen] [datetime2](7) NULL,

     [LastEditedBy] [int] NOT NULL,

     [LastEditedWhen] [datetime2](7) NOT NULL,

     CONSTRAINT [PK_Sales_SQLskills_OrderLines] PRIMARY KEY CLUSTERED

     (

     [OrderID] ASC, [OrderLineID] ASC, [StockItemID] ASC, [PackageTypeID] ASC

     )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [USERDATA]

     ) ON [USERDATA];

GO 

INSERT INTO [Sales].[SQLskills_OrderLines](

     [OrderLineID]

     ,[OrderID]

     ,[OrderID_and_OrderLineID]

     ,[StockItemID]

     ,[Description]

     ,[PackageTypeID]

     ,[Quantity]

     ,[UnitPrice]

     ,[TaxRate]

     ,[PickedQuantity]

     ,[PickingCompletedWhen]

     ,[LastEditedBy]

     ,[LastEditedWhen])

SELECT

     [OrderLineID]

     ,[OrderID]

     ,CAST(CAST([OrderID] AS VARCHAR(20)) + CAST([OrderLineID] AS VARCHAR(20)) AS BIGINT)     

     ,[StockItemID]

     ,[Description]

     ,[PackageTypeID]

     ,[Quantity]

     ,[UnitPrice]

     ,[TaxRate]

     ,[PickedQuantity]

     ,[PickingCompletedWhen]

     ,[LastEditedBy]

     ,[LastEditedWhen]

FROM [Sales].[OrderLines];

GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Кластеризованный индекс из четырех столбцов для SQLskills_OrderLines

Я обычно не одобряю широких кластеризованных индексов, однако в определенных сценариях они могут быть необходимы/полезны. Если для этой таблицы мы добавим некластеризованный индекс, неуникальный и состоящий из одного столбца, то вы увидите все четыре столбца кластеризованного ключа на уровне дерева:

CREATE NONCLUSTERED INDEX [NCI_Quantity]

     ON [Sales].[SQLskills_OrderLines] ([Quantity])

     ON [USERDATA];

GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Некластеризованный индекс (с широким кластеризованным индексом) для SQLskills_OrderLines

Для большой таблицы размер некластеризованного индекса потенциально может быть большим, значительно больше, чем таблица с кластеризованным ключом из единственного столбца.

Если мы добавим уникальный некластеризованный индекс, тогда этот кластеризованный ключ находится только на листовом уровне:

CREATE UNIQUE NONCLUSTERED INDEX [NCI_OrderID_and_OrderLineID]

     ON [Sales].[SQLskills_OrderLines] ([OrderID_and_OrderLineID])

     ON [USERDATA];

GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Уникальный некластеризованный индекс (с широким кластеризованным индексом) для SQLskills_OrderLines

Но тогда...что случится, если мы добавим один или два столбца из кластеризованного ключа в ключ некластеризованного индекса?

CREATE NONCLUSTERED INDEX [NCI_PickingCompletedWhen]

     ON [Sales].[SQLskills_OrderLines] ([PickingCompletedWhen], [OrderID], [OrderLineID])

     ON [USERDATA];

GO

EXEC sp_SQLskills_helpindex 'Sales.SQLskills_OrderLines';

GO

Некластеризованный индекс со столбцом из кластеризованного ключа для SQLskills_OrderLines

Неважно! Все столбцы из кластеризованного ключа добавлены на уровень дерева, поскольку я не определил его как уникальный! Если бы я определил некластеризованный индекс уникальным, тогда только эти два столбца, которые я определил в ключе, были бы в дереве, а остальные два столбца из кластеризованного ключа были бы частью листового уровня.

Заключение

Есть пара момента, которые следует иметь в виду при проектировании некластеризованных индексов. Во-первых, когда вы знаете, что столбец или комбинация столбцов создают уникальный ключ для некластеризованного индекса, то я всегода рекомендую задавать спецификацию UNIQUE при создании индекса. Это информация имеет большое значение для оптимизатора при генерации планов запроса. Во-вторых, хорошенько подумайте о влиянии вашего кластеризованного ключа на ваши некластеризованные индексы. Любой неуникальный некластеризованный индекс будет больше (больше страниц, и, в конечном итоге, больше уровней), поскольку весь кластеризованный ключ будет находиться на уровне дерева. Кроме того, если кластеризованный ключ будет иметь столбец (или несколько столбцоы), который часто модифицируется, то эти модификации будут также распространяться на уровни дерева некластеризованных индексов.

Приятного индексирования!