Colonne clé d'index et colonne d'index incluse

Question

Quelqu'un peut-il expliquer ces deux - Index Key Column VS Index Included Column?

Actuellement, j'ai un index qui a 4 colonnes de clé d'index et 0 colonne incluse.

Merci

Answer 1

La clé d'Index de colonnes font partie du b-arbre de l'index. Inclus les colonnes ne sont pas.

Prendre deux indices:

CREATE INDEX index1 ON table1 (col1, col2, col3)
CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3)

index1 est mieux adapté pour ce genre de requête:

SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z

Alors qu' index2 est mieux adapté pour ce genre de requête:

SELECT col2, col3 FROM table1 WHERE col1 = x

Dans la première requête, index1 fournit un mécanisme pour identifier rapidement les lignes d'intérêt. La requête sera (probablement) d'exécuter une recherche d'index, suivie par une recherche de signet pour récupérer la ligne complète(s).

Dans la seconde requête, index2 agit comme un index de couverture. SQL Server n'a pas de frapper la table de base à tous, depuis que l'indice fournit toutes les données dont il a besoin pour satisfaire à la requête. index1 pourrait également agir comme un index de couverture dans ce cas.

Si vous souhaitez un index de couverture, mais vous ne voulez pas ajouter toutes les colonnes de la b-arbre parce que vous ne cherche pas, ou ne peuvent pas car ils ne sont pas admis type de données (par exemple, XML), l'utilisation de la clause include.

Answer 2

Nous allons réfléchir sur le livre. Chaque page du livre a le numéro de page. Toutes les informations dans ce livre est présenté de manière séquentielle basée sur ce numéro de page. S'exprimant dans la base de données de termes, numéro de la page est l'index cluster. Maintenant, pensez à le glossaire à la fin du livre. C'est dans l'ordre alphabétique et vous permettent de trouver rapidement le numéro de la page glossaire terme appartient. Cela représente index non-cluster avec glossaire terme comme la colonne de la clé.

Maintenant, en supposant que chaque page indique également "chapitre" titre en haut. Si vous voulez trouver dans ce chapitre est le terme du glossaire, vous avez à la recherche de ce numéro de page décrit glossaire terme, la prochaine ouvrir la page correspondante et voir le titre du chapitre sur la page. Il s'agit clairement de la clé de recherche quand vous en avez besoin pour trouver les données de colonne non indexée, vous devez trouver la réelle enregistrement de données (index cluster) et regardez cette valeur de la colonne. Inclus colonne permet en termes de performance - pensez-glossaire où chaque titre de chapitre comprend, en outre, au terme du glossaire. Si vous avez besoin de savoir ce que le chapitre le terme du glossaire appartient - vous n'avez pas besoin d'ouvrir la page - vous pouvez obtenir quand vous recherche le terme du glossaire.

Ainsi inclus colonne sont comme ceux des titres de chapitres. Non clustered Index (glossaire) a plus en tant qu'attribut de l'index non cluster. L'indice n'est pas triée par des colonnes incluses - il seulement des attributs supplémentaires qui permet d'accélérer la recherche (par exemple, vous n'avez pas besoin d'ouvrir la page parce que l'information est déjà dans le glossaire, l'index).

Exemple:

Script De Création De Table

CREATE TABLE [dbo].[Profile](
    [EnrollMentId] [int] IDENTITY(1,1) NOT NULL,
    [FName] [varchar](50) NULL,
    [MName] [varchar](50) NULL,
    [LName] [varchar](50) NULL,
    [NickName] [varchar](50) NULL,
    [DOB] [date] NULL,
    [Qualification] [varchar](50) NULL,
    [Profession] [varchar](50) NULL,
    [MaritalStatus] [int] NULL,
    [CurrentCity] [varchar](50) NULL,
    [NativePlace] [varchar](50) NULL,
    [District] [varchar](50) NULL,
    [State] [varchar](50) NULL,
    [Country] [varchar](50) NULL,
    [UIDNO] [int] NOT NULL,
    [Detail1] [varchar](max) NULL,
    [Detail2] [varchar](max) NULL,
    [Detail3] [varchar](max) NULL,
    [Detail4] [varchar](max) NULL,
PRIMARY KEY CLUSTERED 
(
    [EnrollMentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

Procédure stockée script

CREATE Proc [dbo].[InsertIntoProfileTable]
As
BEGIN
SET NOCOUNT ON
Declare @currentRow int
Declare @Details varchar(Max)
Declare @dob Date
set @currentRow =1;
set @Details ='Let''s think about the book. Every page in the book has the page number. All information in this book is presented sequentially based on this page number. Speaking in the database terms, page number is the clustered index. Now think about the glossary at the end of the book. This is in alphabetical order and allow you to quickly find the page number specific glossary term belongs to. This represents non-clustered index with glossary term as the key column.        Now assuming that every page also shows "chapter" title at the top. If you want to find in what chapter is the glossary term, you have to lookup what page # describes glossary term, next - open corresponding page and see the chapter title on the page. This clearly represents key lookup - when you need to find the data from non-indexed column, you have to find actual data record (clustered index) and look at this column value. Included column helps in terms of performance - think about glossary where each chapter title includes in addition to glossary term. If you need to find out what chapter the glossary term belongs - you don''t need to open actual page - you can get it when you lookup the glossary term.      So included column are like those chapter titles. Non clustered Index (glossary) has addition attribute as part of the non-clustered index. Index is not sorted by included columns - it just additional attributes that helps to speed up the lookup (e.g. you don''t need to open actual page because information is already in the glossary index).'
while(@currentRow <=200000)
BEGIN
insert into dbo.Profile values( 'FName'+ Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'MName' + Cast(@currentRow as varchar), 'NickName' + Cast(@currentRow as varchar), DATEADD(DAY, ROUND(10000*RAND(),0),'01-01-1980'),NULL, NULL, @currentRow%3, NULL,NULL,NULL,NULL,NULL, 1000+@currentRow,@Details,@Details,@Details,@Details)
set @currentRow +=1;
END

SET NOCOUNT OFF
END

GO

À l'aide de la SP vous pouvez insérer 200000 enregistrements à la fois.

Vous pouvez voir qu'il y a un index cluster sur la colonne "EnrollMentId".

Maintenant, Créez un index non ordonné en Clusters sur "UIDNO" de la Colonne.

Script

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-223309] ON [dbo].[Profile]
(
    [UIDNO] ASC
)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]
GO

Maintenant, Exécutez la Requête suivante

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile 
--Takes about 30-50 seconds and return 200,000 results.

Requête 2

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile
where DOB between '01-01-1980' and '01-01-1985'
 --Takes about 10-15 seconds and return 36,479 records.

Désormais le ci-dessus index non ordonné en clusters et de re-créer avec le script suivant

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231011] ON [dbo].[Profile]
(
    [UIDNO] ASC,
    [FName] ASC,
    [DOB] ASC,
    [MaritalStatus] ASC,
    [Detail1] ASC
)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]
GO

Il va jeter l'erreur suivante

Msg 1919, Niveau 16, État 1, Ligne 1 La colonne "Detail1" dans la table " dbo.Profil " est d'un type qui n'est pas valide pour l'utiliser comme une clé de colonne dans un index.

Parce que nous ne pouvons pas utiliser de type varchar(Max) type de données que la colonne de la clé.

Maintenant, Créez un Index non-Cluster avec des colonnes à l'aide du script suivant

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20140216-231811] ON [dbo].[Profile]
(
    [UIDNO] ASC
)
INCLUDE (   [FName],
    [DOB],
    [MaritalStatus],
    [Detail1]) 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]
GO

Maintenant, Exécutez la Requête suivante

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile --Takes about 20-30 seconds and return 200,000 results.

Requête 2

select UIDNO,FName,DOB, MaritalStatus, Detail1 from dbo.Profile
where DOB between '01-01-1980' and '01-01-1985'
 --Takes about 3-5 seconds and return 36,479 records.

Answer 3

Inclus colonnes ne font pas partie de la clé de l'index, mais elles n'existent pas sur l'index. Essentiellement, les valeurs seront dupliqués, donc il y a une surcharge de stockage, mais il y a une grande chance que votre indice de couverture (c'est à dire être sélectionné par l'optimiseur de requête pour) plus de requêtes. Cette duplication améliore également les performances lors de l'interrogation, car le moteur de base de données peut retourner la valeur sans avoir à regarder le tableau lui-même.

Seuls les index non-cluster peut avoir inclus des colonnes, parce que dans un index cluster, chaque colonne est en fait inclus.