indices en sql server

ÍndicesIntroducción.- Los índices son objetos de base de datos diseñados para mejorar el rendimiento de las consultas. En este punto veremos la estructura y el propósito de los índices y sus tipos y características. Se verá como determinar cuando un índice es necesario y apropiado, que tipo de índice usar y como crearlos. Una vez que se crean los índices se deben mantener para maximizar la performance de las consultas, para ello existen varias herramientas que asisten en la tarea de administración y mantenimiento de los índices. La administración comprende las tareas de reconstrucción, renombrado, y eliminación de índices.

Arquitectura de los índices Los índices están estructurados para facilitar una respuesta rápida de conjuntos de resultados. Los dos tipos de índices que SQL Server soporta son agrupados y no agrupados. Los índices son aplicados a una o más columnas en tablas o vistas. Tablas indexadas son soportadas por todas las ediciones de SQL Server 2005, y vistas indexadas son soportadas por las ediciones SQL Server Entreprise y SQL Server Developer. Las características de un índice afecta el uso de los recursos del sistema y performance general. El Query Optimizer usará un índice si este mejorará la performance de la consulta.

Lección 07ÍndicesPropósito y estructura .-Un índice en SQL Server asiste al motor de base de datos en la ubicación de los registros, tal como un índice en un libro ayuda a ubicar información rápidamente. Sin índices, una consulta deberá buscar en todos los registros de la tabla en orden a encontrar coincidencias. Un índice de base de datos una o más columnas de valores de la tabla (llamadas clave del índice) y punteros a los correspondientes registros de la tabla. Cuando se ejecuta una consulta usando la clave del índice, el Query Optimizer utilizará el índice para ubicar los registros que cumplen con la consulta.Un índice es estructurado por el SQL Server Index manager como un árbol balanceado (B-tree). Un B-tree es análogo a un árbol invertido con la raíz del árbol arriba, y los niveles hoja abajo, con niveles medios entre ambos. Cada objeto en la estructura de árbol es un grupo de claves del índice ordenadas llamadas páginas del índice.Para un rendimiento óptimo, se crean sobre columnas que son comúnmente usadas en las consultas. Por ejemplo, los usuarios pueden consultar la tabla de Clientes en base al apellido o al ID del cliente. Por lo tanto se deberían crear dos índices para la tabla: un índice por apellido y otro por ID del cliente. Para ubicar eficientemente a los registros, el Query Optimizer usa un índice que concuerde con la consulta. El Query Optimizer usará el índice por el ID del cliente cuando se ejecute la siguiente consulta:SELECT * FROM Clientes WHERE Cliente_ID = 798

Lección 07ÍndicesTipos de índices .-Hay dos tipos de índices: agrupados (clustered) y no agrupados (non clustered). Ambos tipos de índices son estructuras tipo B-tree. Un índice agrupado contiene los registros de la tabla a nivel hoja del B-tree. Un índice no agrupado es una estructura de índice separada, independiente del ordenamiento físico de los registros en la tabla. Si existe un índice agrupado en un tabla, un índice no agrupado utilizará al índice agrupado para la búsqueda de los registros. En la mayoría de los casos se creará antes un índice agrupado que los índices no agrupados sobre una tabla.

Índices agrupados :Puede haber solo un índice agrupado por tabla o vista, dado que estos índices ordenan físicamente la tabla o vista según la clave del índice agrupado. Este tipo de índices es particularmente eficiente para consultas, dado que los registros de datos completos (en páginas de datos) son guardados a nivel de hoja del B-tree. El ordenamiento y la ubicación de los datos en un índice agrupado es análogo al de un diccionario donde las palabras son ordenadas en forma alfabética y las definiciones aparecen junto a las palabras.

Lección 07ÍndicesTipos de índices .-

Índices agrupados :

Cuando se crea una restricción PRIMARY KEY en un tabla que no contiene un índice agrupado, SQL Server creará uno y utilizará la columna de clave primaria como clave para el índice agrupado. Si ya existe un índice agrupado SQL Server creará un índice no agrupado sobre la columna definida con una restricción PRIMARY KEY. Una columna definida como la clave primaria es un índice muy útil porque los valores de la columna están garantizados que son únicos. Índices sobre columnas de valores únicos son de menor tamaño que los índices sobre columnas con valores duplicados y generan estructuras de búsqueda más eficientes.

Una columna definida con una restricción UNIQUE genera automáticamente un índice no agrupado.

Lección 07ÍndicesTipos de índices .-Índices agrupados :Para forzar el tipo de índice a ser creado para una columna o columnas, se puede especificar las cláusulas CLUSTERED o NONCLUSTERED en los comandos CREATE TABLE, ALTER TABLE o CREATE INDEX. Suponga que se crea una tabla Personas que contiene las siguientes columnas: PersonaID, Nombre, Apellido y NumDocumento. La columna PersonID se define con la restricción PRIMARY KEY, la columna NumDocumento con la restricción UNIQUE. Para hacer un índice agrupado para la columna NumDocumento y un índice no agrupado para la columna PersonID, se crea la tabla usando la siguiente sintaxis:

CREATE TABLE dbo.Personas(PersonID smallint PRIMARY KEY NONCLUSTERED,Nombre varchar(39),Apellido varchar(40),NumDocumento char(11) UNIQUE CLUSTERED)


Índices agrupados :

Los índices no se limitan a las restricciones. Se pueden crear índices sobre cualquier columna o combinación de columnas en una tabla o vista. Índices agrupados aseguran la unicidad internamente. Por lo que, si se crea un índice agrupado sobre columnas con valores no únicos SQL Server crea un único valor sobre las columnas duplicadas para servir de clave de ordenamiento secundaria. Para evitar el trabajo adicional requerido para mantener valores únicos sobre columnas duplicadas, generalmente se generan índices agrupados sobre columnas con la restricción PRIMARY KEY.


Índices no agrupados :

Sobre una tabla o vista se pueden crear 250 índice no agrupados o 249 índices no agrupados y un índice agrupado. Se debe primero crear un índice único agrupado sobre una vista previo a crear los índices no agrupados. Esta restricción no se aplica a las tablas. Un índice no agrupado es análogo a un índice al final de un libro. Se puede usar el índice del libro para ubicar las páginas que contienen una tema del índice del libro. La base de datos usa los índices no agrupados para encontrar registros según una clave.Si no existe un índices agrupado para la tabla, los datos de la tabla se encontrarán desordenados físicamente y se dice que la tabla tendrá la estructura de montón (heap). Un índice no agrupado sobre una tabla montón contiene punteros a las filas de la tabla. Cada entrada en las páginas de índice contiene un identificador de fila (RID, row ID). El RID es un puntero a una fila en un montón, y este consiste de un número de página, un número de archivo y un número de ranura. Si existe un índice agrupado, las páginas de un índice no agrupado contienen las claves del índice agrupado en vez del RID.

Búsquedas Sencillas (Ejemplos)

• Obtener el contenido de la tabla Articulos.

SELECT* FROM Articulos

• Listar el nombre y el teléfono de todos los Proveedores.

SELECT nombrpro as Nombre, telefpro as Telf FROM Proveedores

Lección 07ÍndicesObtener Información Almacenada (DML) .-

Búsquedas Cualificadas. Cond. de Comparación 1 (Ejemplos)

Las condiciones de comparación son expresiones lógicas que permiten comparar una columna o expresión con otra columna, expresión o lista de columnas. Pueden adoptar una de las formas siguientes:

exp operador_de_comparación exp exp [NOT] BETWEEN exp AND exp exp [NOT] IN (lista de valores) campo [NOT] LIKE 'cadena_de_caracteres' campo IS [NOT] NULL

• Encontrar los artículos cuyo precio unitario sea superior a 180 € y su stock sea inferior o igual a 100.

SELECT * FROM Articulos WHERE preunart > 180 AND stockart <= 100



• Listar los artículos cuyo precio unitario esté comprendido entre 180€ y 300€.

SELECT * FROM Articulos WHERE preunart BETWEEN 180 AND 300

• Hallar todos los proveedores de las ciudades de Santander, Madrid y Barcelona.

SELECT codigpro, nombrpro, direcpro, cpostpro, localpro FROM Proveedores WHERE localpro IN ('Santander', 'Madrid', 'Barcelona')



• Encontrar todos los proveedores cuyo primer apellido comience por una letra comprendida entre la A y la J.

SELECT codigpro, nombrpro, direcpro, cpostpro, localpro FROM Proveedores WHERE nombrpro LIKE '[A-J]%'

• Hallar todos los proveedores de los que no se tenga información sobre su correo electrónico.

SELECT codigpro, nombrpro, direcpro, cpostpro, localpro, telefpro FROM Proveedores

WHERE emailpro IS NULL


Búsquedas Cualificadas. Cond. de Combinación Combinación Simple (Ejemplo)

Las búsquedas cualificadas son las que afectan a datos de más de una tabla.

Una Combinación Simple es aquella en la que la condición de la cláusula FROM (o WHERE) contiene una comparación de igualdad entre campos pertenecientes a dos tablas distintas.

• Listar todos los proveedores a los que se ha efectuado algún pedido entre el 20/1/2006 y el 15/9/2006.

SELECT DISTINCT Proveedores.codigpro, nombrpro, direcpro,

localpro FROM Proveedores INNER JOIN Pedidos

ON Proveedores.codigpro = Pedidos.codigpro

WHERE fechaped BETWEEN '2006/01/20' AND '2006/09/15'


Búsquedas Cualificadas. Cond. de Combinación Combinación Simple (Ejemplo. Continuación)

SELECT DISTINCT Proveedores.codigpro, nombrpro, direcpro, localpro

FROM Proveedores INNER JOIN Pedidos

ON Proveedores.codigpro = Pedidos.codigpro


Informació de proceso


Búsquedas Cualificadas. Cond. de Combinación Combinación Múltiple (Ejemplo)

Una Combinación Múltiple es aquella combinación que relaciona varios campos de más de dos tablas.

• Encontrar todos los artículos que han sido pedidos entre el 15/5/2006 y el 30/5/2006.

SELECT DISTINCT Articulos.codigart, descrart FROM Pedidos INNER JOIN

(Lineas INNER JOIN Articulos ON Lineas.codigart =

Articulos.codigart) ON Pedidos.numped = Lineas.numped



COUNT(*) Nº de filas que componen Funciones de grupo (Ejemplo)

el grupo. Con las filas de la información de

COUNT(campo) Nº de filas con valor

asignado al campo (nulos proceso correspondiente a una

no cuentan). instrucción SELECT se pueden establecer grupos. SUM(exp) Suma de valores obtenidos

con la expresión en cada En cada uno de estos grupos, fila. mediante las funciones de grupo, se

AVG(exp) Media. pueden efectuar ciertos cálculos. MAX(exp) Máximo. MIN(exp) Mínimo. • Encontrar cuántos artículos hay

STDEV(exp) Desviación típica. registrados, el máximo y el mínimo

precio unitario, el precio unitario VAR(exp) Varianza.

medio y la valoración del almacén.

SELECT COUNT(codigart) AS Cantidad, MAX(preunart) AS Max,

MIN(preunart) AS Min, AVG(preunart) AS

Precio_medio, SUM(preunart*stockart) AS Valoración FROM Articulos


DAY(fecha) Devuelve el día de mes de Funciones de fecha (Ejemplo)

fecha. Los datos de tipo fecha son MONTH (fecha) Devuelve el mes de fecha.

almacenados como una unidad de YEAR (fecha) Devuelve el año de fecha.

información. Cuando se necesita DATEPART(dw,fecha) Devuelve el día de la

trabajar con componentes de una semana correspondiente

fecha, es preciso utilizar funciones a fecha (el 1 domingo,

de fecha. el 2 lunes, ...).

• Listar día, mes y año de cada pedido, así como el día de la semana al que corresponden sus fechas.

SELECT numped, fechaped,

DAY(fechaped) as dia_mes, MONTH(fechaped) as mes,

YEAR(fechaped) as año, DATEPART(dw,fechaped) as dia_sem

FROM Pedidos


Agrupamiento de datos (Ejemplo)

La cláusula GROUP permite formar grupos con las filas de datos que tengan valores iguales para determinados campos.

La respuesta tiene tantas filas como grupos haya establecido la instrucción.

• Obtener el importe de cada pedido sin aplicar el IVA.

SELECT numped, SUM((preunlin*unilin)*(1-desculin/100)) as

Importe FROM Lineas

GROUP BY numped


Agrupamiento de Datos. Cond. de agrupamiento (Ejemplo)

• Listar el importe, sin aplicar el IVA, de los pedidos que tienen más de una línea.

SELECT numped, SUM((preunlin*unilin)*(1-desculin/100)) as

Importe FROM Lineas

GROUP BY numped

HAVING COUNT(*) > 1


Ordenación del Resultado (Ejemplo)

[ORDER BY colum1 { [ASC] | DESC } [, colum2 { [ASC] | DESC }, .... ] ]

colum1, colum2, ...: son nombres de elementos (campos, expresiones o funciones) de la lista de selección o la posición que ocupan en ella. ASC: quiere decir ordenación ascendente (opción por defecto) y DESC descendente.

• Listar los valores de los campos que componen el índice de unicidad de la tabla Lineas (numped, numlin), por orden decreciente de nº de pedido y de nº de línea.

SELECT numped, numlin

FROM Lineas

ORDER BY 1 DESC, 2 DESC


indices en sql server

Education