Este es el cuarto artículo de una serie de aprendizaje de la instrucción CREATE VIEW SQL. Hasta ahora, hemos hecho una gran cantidad de crear y alterar vistas utilizando T-SQL. En esta última parte, quiero echar un vistazo a cómo trabajar con vistas indexadas.
Como siempre, para seguir con la serie, es muy recomendable leer las partes anteriores primero y luego este., Esto se debe principalmente a que creamos nuestra propia base de datos de muestra y los objetos en ella desde cero que utilizaremos en esta parte también, pero también porque será mucho más fácil ver el panorama general.
Aquí están las tres piezas anteriores de la serie Crear vista SQL:
- Crear vistas en SQL Server
- modificar vistas en SQL Server
- insertar datos a través de vistas en SQL Server
así que, diríjase y léalas antes de continuar con esta.
Introducción
Lo primero que haremos es crear una vista indexada., Por supuesto, usaremos la instrucción CREATE VIEW SQL para esto como lo hicimos muchas veces a través de la serie. Pero la idea general, como dice el título, es ver cómo trabajar con vistas indexadas, ver cuáles son los requisitos para agregar un índice a una vista y cómo hacerlo programáticamente. Además, para explicar los pros de las vistas indexadas, veremos los planes de ejecuciones en SQL Server. Son una gran herramienta para DBA y desarrolladores cuando se trata de encontrar y arreglar un cuello de botella en la consulta de ejecución lenta.,
Sin más preámbulos, vamos a crear una vista con la VISTA de CREAR instrucción SQL desde abajo y ver lo que hace:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
USO SQLShackDB;
IR
CREATE VIEW dbo.,vEmployeeSalesOrders
CON SCHEMABINDING
COMO
SELECCIONAR a los Empleados.EmployeeID,
productos.ProductID,
SUM (price * quantity) AS SaleTotal,
SaleDate
de dbo.Empleados
unirse a dbo.Ventas de empleados.EmployeeID = Ventas.EmployeeID
unirse a dbo.Productos en venta.ProductID = Productos.ProductID
grupo por empleados.EmployeeID,
productos.ProductID,
ventas.,SaleDate;
IR
|
Observe que este punto de vista tiene una CON opción SCHEMABINDING en él. La razón por la que tiene esta opción activada es porque al crear índices en vistas, en realidad se almacenan físicamente en la base de datos. En otras palabras, cualquier cosa en la que se Base esta vista, en lo que respecta a las tablas, la estructura no puede cambiar de lo que estamos haciendo referencia.
por lo tanto, debe estar vinculado a las tablas subyacentes para que no podamos modificarlas de una manera que afecte a la definición de la vista., Si intentamos cambiarlos de alguna manera, SQL Server lanzará un error diciendo que esta vista depende de algo. Por lo tanto, míralo como un requisito difícil para crear un índice en una vista.
una vez que el comando se ejecuta correctamente, debería ver la vista vEmployeeSalesOrders en la carpeta Views en el Explorador de objetos como se muestra a continuación:
La definición de la vista es una consulta un poco más compleja. Tenemos un agregado en la instrucción SELECT seguido por la cláusula GROUP BY., Recuerde, cuando tenemos un agregado en la consulta, suma los números juntos, por lo que necesitamos tener la cláusula GROUP BY.
Básicamente, cuando hay un grupo por en una consulta, necesitamos agrupar por todo lo que está en la lista de selección, excepto el agregado.,
ahora, ya he creado la vista, pero recuerde que siempre es una buena idea probar la definición de la vista ejecutando solo la parte SELECT de la instrucción SQL CREATE VIEW para ver lo que devuelve:
pasando, aquí es cómo puede comprobar si la opción schema bound está habilitada o deshabilitada., Diríjase al Explorador de objetos, expanda Vistas, haga clic con el botón derecho en la vista y seleccione Propiedades:
entre toda la información de la ventana Propiedades de la vista, verá si la opción enlazado de esquema se establece en Verdadero o falso en la página General.
crear vistas indexadas
vamos a seguir adelante y crear un índice en nuestra vista., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:
|
1
2
3
4
5
6
|
USE SQLShackDB;
GO
CREATE UNIQUE CLUSTERED INDEX CAK_vEmployeesSalesOrders
ON dbo.,vEmployeeSalesOrders(EmployeeID, ProductID, SaleDate);
GO
|
Si pulsamos el botón Ejecutar en SSMS, SQL Server lanzará un error diciendo que el índice no se puede crear:
Aquí está el mensaje de error completo que no se puede ver en la toma anterior:
no se puede crear índice en view ‘sqlshackdb.dbo.vEmployeeSalesOrders ‘ porque su lista de selección no incluye un uso adecuado de COUNT_BIG. Considere agregar COUNT_BIG (*) a la lista de selección.,
necesitamos COUNT_BIG en este caso dado el hecho de que estamos usando GROUP BY en nuestra vista.
en general, si estamos utilizando agregados como COUNT, SUM, AVG, etc. en la lista de selección del índice, también tenemos que incluir COUNT_BIG para crear un índice en él.
Eso es exactamente lo que vamos a hacer.,el uso de la VISTA de la instrucción SQL y agregar COUNT_BIG a la lista de selección utilizando el script de abajo:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
USO SQLShackDB;
IR
ALTER VIEW dbo.,vEmployeeSalesOrders
CON SCHEMABINDING
COMO
SELECCIONAR a los Empleados.EmployeeID,
productos.ProductID,
SUM (price * quantity) AS SaleTotal,
SaleDate,
COUNT_BIG (*) AS RecordCount
de dbo.Empleados
unirse a dbo.Ventas de empleados.EmployeeID = Ventas.EmployeeID
unirse a dbo.Productos en venta.ProductID = Productos.ProductID
grupo por empleados.EmployeeID,
productos.ProductID,
ventas.,SaleDate;
IR
|
Si usted se está preguntando por qué esto está sucediendo en este caso, la respuesta es porque SQL Server necesita una manera de rastrear el número del registro que estamos convirtiendo para el índice y este es también uno de un montón de limitaciones con la creación de las vistas indizadas.
hasta ahora, todo se ve bien. Cambiamos con éxito la definición de nuestra vista:
ahora, podemos volver a crear un índice en la vista ejecutando el script utilizado anteriormente una vez más., Esta vez, la operación se completará sin problemas. Si vamos al Explorador de objetos y expandimos la carpeta índices de nuestra vista, veremos el índice recién creado:
desde aquí, si hacemos clic derecho en el índice y seleccionamos Propiedades, en la página General, puede ver nombre de vista, nombre de índice, tipo, columnas clave, etc.,:
si cambiamos a almacenamiento, verá que tiene un grupo de archivos porque está almacenado físicamente en una base de datos:
Además, si cambiamos a fragmentación, debería decir que la fragmentación total es cero por ciento porque solo tenemos unos pocos registros en nuestras tablas:
¿busca un manual detallado pero divertido y fácil de leer sobre el mantenimiento y monitoreo de índices SQL? Consulte mantenimiento del índice SQL.,
eliminar índices
antes de ir más lejos, veamos cómo podemos eliminar un índice. La forma más fácil es hacer clic derecho en el índice en el Explorador de objetos y usar la opción Eliminar. Pero en caso de que necesite soltar varios índices a la vez, la instrucción DROP INDEX es útil. Eso es lo que vamos a hacer, porque, después de todo, esta es una serie de T-SQL sobre el aprendizaje de la instrucción CREATE VIEW SQL.,
Use the script from below to drop the CAK_vEmployeesSalesOrders index:
|
1
2
3
4
5
|
USE SQLShackDB;
GO
DROP INDEX CAK_vEmployeesSalesOrders ON dbo.,vEmployeeSalesOrders;
IR
|
Cuando usted necesita para soltar varios índices, sólo tiene que especificar todos los nombres separados por una coma.
generando datos aleatorios
ahora que nos hemos deshecho del índice, generemos algunos datos aleatorios en nuestra tabla para que podamos mirar el plan de ejecución y ver cómo SQL Server obtiene datos bajo el capó. El análisis del plan de ejecución mostrará la diferencia en cómo el rendimiento se ve afectado al ejecutar la consulta con y sin un índice en la vista.,
Use el script de abajo para insertar 50000 registros aleatorios en la tabla de ventas:
no voy a guiarlo a través del script en detalles, pero es básicamente un bucle que se ejecutará 50000 veces e insertará datos aleatorios en la tabla de ventas., para comprobar si los registros se han insertado correctamente, ejecute la siguiente instrucción SELECT que se tomarán en cuenta todos los registros de la tabla de Ventas:
|
1
2
3
4
|
USO SQLShackDB;
IR
SELECT COUNT(*) DE Ventas
|
El número devuelto muestra que hay 50006 filas en la tabla de Ventas., Este es el número de registros que acabamos de generar + 6 que inicialmente teníamos:
analizando planes de ejecución
ahora que tenemos algunos datos en nuestra tabla, realmente podemos demostrar el uso de un índice en la vista. Consultemos la vista vEmployeeSalesOrders y veamos cómo SQL Server recupera los datos., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:
|
1
2
3
4
|
USE SQLShackDB;
GO
SELECT * FROM dbo.,vEmployeeSalesOrders;
|
Este script devuelve 23814 filas, pero lo que es más importante, se genera el plan de ejecución de la consulta. Recuerde que anteriormente eliminamos el índice en nuestra vista. Por lo tanto, en este momento no hay un índice en nuestra opinión. Por lo tanto, SQL Server hará algunas exploraciones de tablas como se muestra a continuación:
esto es prácticamente lo peor en el mundo de las bases de datos, especialmente en tablas con una gran cantidad de datos., Está bien tener escaneos de tablas con una pequeña cantidad de datos, por ejemplo, el caso de nuestros empleados y tablas de productos, pero es malo para la tabla de ventas porque tiene registros de más de 50K.
la forma más fácil de deshacerse de los escaneos de tabla es crear un índice en él, ya que acelera drásticamente las cosas. Por lo tanto, lo que vamos a hacer para solucionar este problema es volver a ejecutar el script para crear el índice clúster único en la vista vEmployeeSalesOrders.
ahora, si acabamos de volver a ejecutar la instrucción SELECT, no habrá diferencias a pesar de que acabamos de crear el índice. ¿Por qué es eso?, Porque estoy usando la edición SQL Server Express para el propósito de esta serie, y solo en las ediciones Enterprise y Developer de SQL Server el optimizador de consultas realmente tendrá en cuenta el índice.
No se preocupe porque en realidad podemos forzar a SQL Server a usar un índice al generar planes de ejecución. Esto se hace utilizando la opción NOEXPAND., NOEXPAND sólo se aplica a las vistas indizadas:
|
1
|
SELECT * FROM dbo.vEmployeeSalesOrders WITH (NOEXPAND)
|
Al igual que, forzamos a SQL Server a usar el índice agrupado que básicamente significa no usar las tablas subyacentes al obtener datos., Como puede verse a continuación, hemos hecho algunos progresos mediante la eliminación de un número de operaciones:
De hecho, podemos executeboth instrucciones SELECT simultáneamente y comparar los resultados mirando los planes de ejecución:
puedes ver eso? Si comparamos el costo de consulta de la primera instrucción SELECT (sin índice 95%) con la segunda instrucción SELECT (con índice 5%), diría que es una gran ganancia de rendimiento usando un solo índice.,
conclusión
los índices son geniales porque aceleran el rendimiento y con un índice en una vista realmente debería acelerar el rendimiento porque el índice está almacenado en la base de datos. La indexación de vistas y tablas es una de las formas más eficientes de mejorar el rendimiento de las consultas y las aplicaciones que las utilizan.
Me gustaría terminar esta serie de aprendizaje de la instrucción CREATE VIEW SQL con este artículo. Hemos cubierto prácticamente todo sobre la creación y alteración de vistas con T-SQL., Comenzamos con la instrucción CREATE VIEW SQL, creamos algunas vistas, las alteramos, eliminamos y mucho más.
espero que esta serie de aprendizaje de la instrucción CREATE VIEW SQL haya sido informativa para usted y le agradezco por leerla.,
Crear vista SQL: insertar datos a través de vistas en SQL Server
Crear vista SQL: trabajar con vistas indexadas en SQL Server
- Autor
- publicaciones recientes
ha escrito extensamente sobre SQL Shack y ApexSQL Solution Center, sobre temas que van desde tecnologías de cliente como resolución 4K y tematización, manejo de errores a estrategias de índice y monitoreo de rendimiento.Bojan trabaja en ApexSQL en Nis, Serbia como parte integral del equipo que se enfoca en diseñar, desarrollar y probar la próxima generación de herramientas de base de datos, incluyendo MySQL y SQL Server, y tanto herramientas independientes como integraciones en Visual Studio, SSMS y VSCode.,
Ver más sobre Bojan en LinkedIn
ver todas las publicaciones de Bojan Petrovic
- Visual Studio Code for MySQL and MariaDB development – August 13, 2020
- SQL UPDATE syntax explained – July 10, 2020
- Create view SQL: trabajando con vistas indexadas en SQL Server – March 24, 2020