sql-server-2005 - una - nomenclatura indices sql server

Índice Columna clave VS Índice incluido Columna

Question

¿Alguien puede explicar esto dos - Columna incluida en el índice de columna clave del índice?

Actualmente, tengo un índice que tiene 4 Columna de clave de índice y 0 Columna incluida.

Gracias

Answer 1

Las columnas de las claves de índice son parte del árbol b del índice. Las columnas incluidas no lo son.

Toma dos índices:

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

index1 es más adecuado para este tipo de consulta:

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

Considerando que index2 es más adecuado para este tipo de consulta:

SELECT col2, col3 FROM table1 WHERE col1 = x

En la primera consulta, index1 proporciona un mecanismo para identificar rápidamente las filas de interés. La consulta se ejecutará (probablemente) como una búsqueda de índice, seguida de una búsqueda de marcador para recuperar la (s) fila (s) completa (s).

En la segunda consulta, index2 actúa como un índice de cobertura. SQL Server no tiene que llegar a la tabla base, ya que el índice proporciona todos los datos que necesita para satisfacer la consulta. index1 también podría actuar como índice de cobertura en este caso.

Si desea un índice de cobertura, pero no desea agregar todas las columnas al árbol b porque no las busca, o no puede hacerlo porque no son un tipo de datos permitido (por ejemplo, XML), use el Cláusula INCLUDE.

Answer 2

Las columnas incluidas no forman parte de la clave del índice, pero sí existen en el índice. Esencialmente, los valores se duplicarán a continuación Tome dos tipos de índices con una columna de ejemplo

CREATE clustered INDEX NC_index1 ON tableName (column1, column1, column1,column4) CREATE clustered INDEX NC_index2 ON tableName (column1) INCLUDE (column2, column3,column4)

NC_index1 es más adecuado para este tipo de consulta:

SELECT * FROM tableName WHERE column1 = x AND column1 = y AND column1 = z and column4=n

Mientras que NC_index2 es más adecuado para este tipo de consulta:

SELECT column1, column2 FROM tableName WHERE column1 = a

porque el servidor sql no puede crear índices en el tipo de datos (por ejemplo, XML, texto, etc.)

Answer 3

Las columnas incluidas no forman parte de la clave del índice, pero sí existen en el índice. Esencialmente, los valores se duplicarán, por lo que hay una sobrecarga de almacenamiento, pero hay una mayor probabilidad de que su índice cubra (es decir, sea seleccionado por el optimizador de consultas para) más consultas. Esta duplicación también mejora el rendimiento al realizar consultas, ya que el motor de la base de datos puede devolver el valor sin tener que mirar la tabla en sí.

Solo los índices no agrupados pueden haber incluido columnas, porque en un índice agrupado, cada columna está efectivamente incluida.

Answer 4

Pensemos en el libro. Cada página en el libro tiene el número de página. Toda la información en este libro se presenta secuencialmente en función de este número de página. Hablando en términos de la base de datos, el número de página es el índice agrupado. Ahora piense en el glosario al final del libro. Esto está en orden alfabético y le permite encontrar rápidamente el número de página al que pertenece el término específico del glosario. Esto representa el índice no agrupado con el término del glosario como la columna clave.

Ahora suponiendo que cada página también muestra el título "capítulo" en la parte superior. Si desea encontrar en qué capítulo está el término del glosario, debe buscar qué número de página describe el término del glosario, a continuación - abra la página correspondiente y vea el título del capítulo en la página. Esto representa claramente la búsqueda clave: cuando necesita encontrar los datos de la columna no indexada, debe buscar el registro de datos real (índice agrupado) y observar el valor de esta columna. La columna incluida ayuda en términos de rendimiento: piense en el glosario donde se incluye el título de cada capítulo además del término del glosario. Si necesita saber a qué capítulo pertenece el término del glosario, no necesita abrir la página real, puede obtenerlo cuando busque el término del glosario.

Así que la columna incluida es como esos títulos de capítulos. El índice no agrupado (glosario) tiene un atributo de adición como parte del índice no agrupado. El índice no está ordenado por columnas incluidas; solo son atributos adicionales que ayudan a acelerar la búsqueda (por ejemplo, no es necesario abrir la página porque la información ya está en el índice del glosario) .

Ejemplo:

Crear secuencia de comandos de tabla

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

Guión de procedimiento almacenado

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

Usando el SP anterior puedes insertar 200000 registros a la vez.

Puede ver que hay un índice agrupado en la columna "EnrollMentId".

Ahora crea un índice no agrupado en la columna "UIDNO".

Guión

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

Ahora ejecuta la siguiente consulta

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

Consulta 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.

Ahora suelte el índice anterior no agrupado y vuelva a crear con el siguiente script

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

Lanzará el siguiente error

Msg 1919, nivel 16, estado 1, línea 1 La columna ''Detail1'' en la tabla ''dbo.Profile'' es de un tipo que no es válido para usar como una columna clave en un índice.

Porque no podemos usar el tipo de datos varchar (Max) como columna clave.

Ahora crea un índice no agrupado con las columnas incluidas usando la siguiente secuencia de comandos

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

Ahora ejecuta la siguiente consulta

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

Consulta 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.