ceci est le quatrième article d’une série d’apprentissage de L’instruction CREATE VIEW SQL. Jusqu’à présent, nous avons beaucoup créé et modifié des vues à l’aide de T-SQL. Dans cette dernière partie, je veux jeter un grand coup d’œil sur la façon de travailler avec les vues indexées.

Comme toujours, pour suivre la série, il est fortement recommandé de lire d’abord les parties précédentes, puis celle-ci., C’est principalement parce que nous avons créé notre propre base de données d’échantillons et des objets à partir de zéro que nous allons utiliser dans cette partie aussi, mais aussi parce qu’il sera beaucoup plus facile de voir la grande image.

Voici les trois éléments précédents de la série CREATE VIEW SQL:

• création de vues dans SQL Server
• modification de vues dans SQL Server
• insertion de données via des vues dans SQL Server

alors, allez-y et lisez-les avant de continuer avec celle-ci.

Introduction

La première chose que nous allons faire est de créer une vue indexée., Nous utiliserons bien sûr L’instruction CREATE VIEW SQL pour cela, comme nous l’avons fait plusieurs fois dans la série. Mais l’idée générale, comme le dit le titre, est de voir comment travailler avec les vues indexées, voir quelles sont les exigences pour ajouter un index à une vue et comment le faire par programme. De plus, pour expliquer les avantages des vues indexées, nous examinerons les plans d’exécution dans SQL Server. Ils sont un excellent outil pour les DBA et les développeurs lorsqu’il s’agit de trouver et de corriger un goulot d’étranglement dans la requête lente.,

Sans plus tarder, nous allons créer une vue à l’aide de la VUE de CRÉER instruction SQL à partir de ci-dessous et voir ce que ça donne:

Notez que ce point de vue a un AVEC option SCHEMABINDING sur elle. La raison pour laquelle cette option est activée est que lors de la création d’index sur les vues, ils sont physiquement stockés dans la base de données. En d’autres termes, tout ce sur quoi cette vue repose, en ce qui concerne les tables, la structure ne peut pas changer par rapport à ce que nous référençons.

Par conséquent, il doit être lié aux tables sous-jacentes afin que nous ne puissions pas les modifier d’une manière qui affecterait la définition de la vue., Si nous essayons de les changer de quelque manière que ce soit, SQL Server lancera une erreur disant que cette vue dépend de quelque chose. Alors, regardez-le comme une exigence difficile pour créer un index sur une vue.

Une fois la commande exécutée avec succès, vous devriez voir la vue vEmployeeSalesOrders sous le dossier Views dans L’Explorateur D’objets comme indiqué ci-dessous:

La définition de la vue est une requête un peu plus complexe. Nous avons obtenu un agrégat dans L’instruction SELECT suivi de la clause GROUP BY., Rappelez-vous, lorsque nous avons un agrégat dans la requête, il ajoute les nombres ensemble, nous devons donc avoir la clause GROUP BY.

fondamentalement, lorsqu’il y a un groupe par dans une requête, nous devons regrouper par tout ce qui se trouve dans la liste de sélection, sauf l’agrégat.,

maintenant, j’ai déjà créé la vue, mais rappelez-vous que c’est toujours une bonne idée de tester la définition de la vue en exécutant uniquement la partie SELECT de L’instruction CREATE VIEW SQL pour voir ce qu’elle renvoie:

En continuant, voici comment vous pouvez vérifier si L’option Schema bound est activée ou désactivée., Dirigez-vous vers L’Explorateur D’objets, développez vues, faites un clic droit sur la vue et sélectionnez Propriétés:

parmi toutes les autres informations dans la fenêtre Propriétés de la vue, vous verrez si L’option liée au schéma est définie sur True ou False sous la page Général.

création de vues indexées

passons à autre chose et créons un index sur notre vue., Consider the script from below for creating a clustered index on the vEmployeeSalesOrders view:

Si nous appuyons sur le bouton Exécuter dans SSMS, SQL Server génère une erreur indiquant que l’index ne peut pas être créé:

Voici le message d’erreur complet qui ne peut pas être vu dans le plan ci-dessus:

impossible de créer un index sur la vue ‘sqlshackdb.dbo.vEmployeeSalesOrders ‘ parce que sa liste de sélection n’inclut pas une utilisation correcte de COUNT_BIG. Pensez à ajouter COUNT_BIG (*) pour sélectionner la liste.,

Nous avons besoin de COUNT_BIG dans ce cas étant donné que nous utilisons GROUP BY à notre avis.

en général, si nous utilisons des agrégats comme COUNT, SUM, AVG, etc. dans la liste de sélection de l’index, nous devons également inclure COUNT_BIG afin de créer un index dessus.

C’est exactement ce que nous allons faire.,à l’aide de la VUE de CRÉER instruction SQL et ajouter COUNT_BIG à la liste de sélection à l’aide du script à partir de ci-dessous:

Si vous vous demandez pourquoi cela se produit dans ce cas, la réponse est parce que SQL Server a besoin d’un moyen de suivre le numéro de l’enregistrement que nous tournons pour l’index et c’est aussi l’une

Jusqu’à présent, tout semble bien. Nous avons réussi à changer la définition de notre vue:

maintenant, nous pouvons revenir à la création d’un index sur la vue en exécutant le script précédemment utilisé une fois de plus., Cette fois, l’opération sera exécutée en douceur. Si nous allons dans L’Explorateur D’objets et développons le dossier index de notre vue, nous verrons l’index nouvellement créé:

à partir d’ici, si nous faisons un clic droit sur l’index et sélectionnons Propriétés, sous la page Générale, vous pouvez voir le nom de la vue, le nom de,:

Si nous passons au stockage, vous verrez qu’il a un groupe de fichiers car il est physiquement stocké dans une base de données:

de plus, si nous passons à la fragmentation, il faut dire que la fragmentation totale est de zéro pour cent car nous n’avons que quelques enregistrements dans nos tables:

vous cherchez une introduction détaillée mais amusante et facile à lire sur la maintenance et la surveillance des index SQL? Consultez la maintenance de L’index SQL.,

Suppression d’index

Avant d’aller plus loin, nous allons voir comment nous pouvons supprimer un index. Le moyen le plus simple est de cliquer avec le bouton droit sur l’index dans L’Explorateur D’objets et d’utiliser L’option Supprimer. Mais au cas où vous auriez besoin de supprimer plusieurs index à la fois, L’instruction DROP INDEX est utile. C’est ce que nous allons faire, car, après tout, il s’agit d’une série T-SQL sur l’apprentissage de L’instruction CREATE VIEW SQL.,

Use the script from below to drop the CAK_vEmployeesSalesOrders index:

Lorsque vous avez besoin de déplacer plusieurs indices, il suffit de spécifier tous les noms séparés par une virgule.

générer des données aléatoires

maintenant, que nous nous sommes débarrassés de l’index, générons des données aléatoires dans notre table afin que nous puissions regarder le plan d’exécution et voir comment SQL Server récupère les données sous le capot. L’analyse du plan d’exécution montrera la différence dans la façon dont les performances sont affectées par l’exécution de la requête avec et sans index sur la vue.,

utilisez le script ci-dessous pour insérer 50000 enregistrements aléatoires dans la table des ventes:

Je ne vais pas vous guider dans le script en détail, mais c’est essentiellement une boucle qui s’exécutera 50000 fois et insérera des données aléatoires dans la table des ventes., pour vérifier si les enregistrements ont été insérées correctement, exécutez l’instruction SELECT suivante compte de tous les enregistrements de la table de Vente:

Le nombre retourné montre qu’il n’y a 50006 les lignes de la table Sales., C’est le nombre d’enregistrements que nous venons de générer + 6 que nous avions initialement:

analyse des plans d’exécution

maintenant que nous avons quelques données dans notre tableau, nous pouvons vraiment démontrer l’utilisation d’un index sur la vue. Interrogeons la vue vEmployeeSalesOrders et voyons comment SQL Server récupère les données., Before executing the SELECT statement from below, make sure to include the Actual Execution Plan as shown below:

Ce script a retourné 23814 lignes, mais ce qui est plus important, il a généré le plan d’exécution de la requête. Rappelez-vous que nous avons précédemment abandonné l’index sur notre vue. Donc, en ce moment, il n’y a pas d’index sur notre point de vue. Par conséquent, SQL Server effectuera quelques analyses de table comme indiqué ci-dessous:

c’est à peu près la pire chose dans le monde des bases de données, en particulier sur les tables avec une grande quantité de données., Il est acceptable d’avoir des analyses de table avec une petite quantité de données, par exemple le cas avec nos employés et nos tables de produits, mais c’est mauvais pour la table des ventes car elle contient plus de 50 000 enregistrements.

le moyen le plus simple de se débarrasser des scans de table est de créer un index dessus, car cela accélère considérablement les choses. Donc, ce que nous allons faire pour résoudre ce problème est de réexécuter le script pour créer l’index clusterisé unique sur la vue vEmployeeSalesOrders.

maintenant, si nous réexécutons simplement L’instruction SELECT, il n’y aura pas de différences même si nous venons de créer l’index. Pourquoi est-ce?, Parce que j’utilise L’édition SQL Server Express aux fins de cette série, et ce n’est que dans les éditions Enterprise et Developer de SQL Server que L’Optimiseur de requête prendra réellement en compte l’index.

Pas de soucis car nous pouvons effectivement forcer SQL Server à utiliser un index lors de la génération de plans d’exécution. Ceci est fait en utilisant L’option NOEXPAND., NOEXPAND s’applique uniquement aux vues indexées:

tout comme ça, nous avons forcé SQL Server à utiliser l’index clustered, ce qui signifie essentiellement ne pas utiliser les tables sous-jacentes lors de la récupération de données., Comme on peut le voir ci-dessous, nous avons fait quelques progrès en éliminant un certain nombre d’opérations:

En fait, nous pouvons executeboth instructions SELECT simultanément et de comparer les résultats en regardant les plans d’exécution:

regarde ça? Si nous comparons le coût de requête de la première instruction SELECT (sans index 95%) à la deuxième instruction SELECT (avec index 5%), je dirais que c’est un énorme gain de performance en utilisant un seul index.,

Conclusion

Les index sont excellents car ils accélèrent les performances et avec un index sur une vue, il devrait vraiment accélérer les performances car l’index est stocké dans la base de données. L’indexation des vues et des tables est l’un des moyens les plus efficaces d’améliorer les performances des requêtes et des applications qui les utilisent.

je voudrais conclure les choses et terminer cette série d’apprentissage de L’instruction CREATE VIEW SQL avec cet article. Nous avons à peu près tout couvert sur la création et la modification de vues avec T-SQL., Nous avons commencé avec L’instruction CREATE VIEW SQL, créé quelques vues, Les avons modifiées, supprimées et bien plus encore.

j’espère que cette série d’apprentissage de L’instruction CREATE VIEW SQL vous a été instructive et je vous remercie de l’avoir lue.,

CREATE VIEW SQL: insertion de données via des vues dans SQL Server

CREATE VIEW SQL: travailler avec des vues indexées dans SQL Server

• auteur
• articles récents

Bojan aka « boksi”, un diplômé AP en technologie informatique axée sur les réseaux et la technologie électronique de la Copenhagen School of design and Technology, est un analyste logiciel avec une expérience dans l’assurance qualité, le support logiciel, l’évangélisation des produits et l’engagement des utilisateurs.,
il a beaucoup écrit sur SQL Shack et le centre de solutions ApexSQL, sur des sujets allant des technologies client comme la résolution et la thématisation 4K, la gestion des erreurs aux stratégies d’indexation et le suivi des performances.
Bojan travaille chez ApexSQL à Nis, en Serbie, en tant que partie intégrante de l’équipe se concentrant sur la conception, le développement et le test de la prochaine génération d’outils de base de données, y compris MySQL et SQL Server, ainsi que des outils autonomes et des intégrations dans Visual Studio, SSMS et VSCode.,
Voir plus sur Bojan sur LinkedIn
voir tous les messages de Bojan Petrovic

derniers messages de Bojan Petrovic (voir tout)

• Visual Studio Code for MySQL and MariaDB development – August 13, 2020
• explication de la syntaxe de la mise à jour SQL – 10 juillet 2020
• créer une vue SQL: travailler avec des vues indexées dans SQL Server – 24 mars 2020