{"id":1043,"date":"2014-03-24T22:06:15","date_gmt":"2014-03-24T21:06:15","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=1043"},"modified":"2020-09-01T10:06:58","modified_gmt":"2020-09-01T08:06:58","slug":"vues-obsoletes","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/vues-obsoletes\/","title":{"rendered":"Vues obsol\u00e8tes"},"content":{"rendered":"

Par d\u00e9faut, les vues cr\u00e9\u00e9es sous SQL Server sont uniquement des alias, c’est-\u00e0-dire que le code qu’elles contiennent (les tables et colonnes utilis\u00e9es, …) est \u00e9valu\u00e9 \u00e0 chaque ex\u00e9cution. Mais avec ce param\u00e9trage par d\u00e9faut, il est possible que les tables sous-jacentes \u00e9voluent et que l’on obtienne une vue qui, bien que d\u00e9finie, ne peut plus \u00eatre utilis\u00e9e.<\/p>\n

Voici un petit papier au sujet des vues, et plus particuli\u00e8rement autour d’une m\u00e9thode permettant de d\u00e9tecter ces vues \u00ab\u00a0fant\u00f4mes\u00a0\u00bb.<\/p>\n

Tout d’abord, prenons l’exemple d’une petite vue assez simple. Nous nous appuierons sur la base AdventureWorks2012 disponible ici<\/a>.<\/p>\n

CREATE VIEW [dbo].[FullName_Sales]\r\nAS\r\nSELECT p.[FirstName] + N' ' + p.[LastName] as NomComplet,  p.AdditionalContactInfo\r\nFROM [Person].[Person] p\r\nJOIN [Sales].[SalesPerson] s on s.BusinessEntityID=p.BusinessEntityID\r\nGO<\/pre>\n
Cette vue nous retourne le nom complet des vendeurs et des informations de contact compl\u00e9mentaires. Lorsqu’on l’utilise et que l’on regarde le plan d’ex\u00e9cution, on constate bien que le moteur utilise en fait les tables physiques. En effet, la vue n’est pas mat\u00e9rialis\u00e9e (il n’y a pas d’index d\u00e9fini sur cette vue), et donc elle n’a qu’un r\u00f4le d’alias.<\/p>\n
\"Plan1\"<\/a><\/p>\n
Mais si l’une des tables \u00e9volue, la requ\u00eate n’est plus possible. Par exemple, modifions un peu la table Person. Par exemple, rempla\u00e7ons le nom de la colonne AdditionalContactInfo\u00a0par AdditionalContactInfos.<\/p>\n
ALTER TABLE [Person].[Person]\r\n\tADD AdditionalContactInfos XML;\r\nGO\r\nUPDATE [Person].[Person] SET AdditionalContactInfos=AdditionalContactInfo;\r\nGO\r\nDROP INDEX PXML_Person_AddContact ON [Person].[Person];\r\nGO\r\nALTER TABLE [Person].[Person]\r\n\tDROP COLUMN AdditionalContactInfo;<\/pre>\n
Et maintenant, cherchons \u00e0 r\u00e9utiliser la vue que nous avions pr\u00e9c\u00e9demment d\u00e9finie.<\/p>\n
\"Erreur\"<\/a><\/p>\n
Il n’est plus possible de trouver la colonne d’apr\u00e8s le nom d\u00e9fini dans la vue, et donc la vue ne fonctionne plus.<\/p>\n
Nous constatons donc qu’il est tr\u00e8s facile, lorsque l’on fait vivre un mod\u00e8le de donn\u00e9es, d’oublier derri\u00e8re soi des vues. Si ces vues ne sont plus utilis\u00e9es, cela ne pose pas de probl\u00e8me particulier, et tout continue \u00e0 fonctionner correctement.<\/p>\n
Une solution pour \u00e9viter ce genre de d\u00e9sagr\u00e9ment est de lier les vues au sch\u00e9ma de donn\u00e9es (mot-cl\u00e9 SCHEMABINDING dans la d\u00e9finition de la vue). Mais dans la pratique, on oublie tr\u00e8s souvent de mettre en place cette s\u00e9curit\u00e9, et on se retrouve donc au fil des \u00e9volutions de la base, avec un certain nombre de \u00ab\u00a0fant\u00f4mes\u00a0\u00bb de vues.<\/p>\n
Afin de tester une vue pour savoir si elle est toujours fonctionnelle, on a la possibilit\u00e9 de tout simplement la tester (via une requ\u00eate SELECT).<\/p>\n
Une autre solution consiste \u00e0 utiliser la proc\u00e9dure stock\u00e9e syst\u00e8me sp_refreshview<\/a>. Celle-ci permet de v\u00e9rifier les m\u00e9tadonn\u00e9es associ\u00e9es \u00e0 la vue sans pour autant la lancer. Ainsi, SQL Server Management Studio (SSMS) pr\u00e9sentera par exemple le bon type de donn\u00e9es pour les colonnes de la vue si le type des colonnes sous-jacentes a \u00e9volu\u00e9.<\/p>\n
L’utilisation de cette proc\u00e9dure provoque une erreur s’il n’est plus possible de d\u00e9terminer les m\u00e9tadonn\u00e9es associ\u00e9es \u00e0 la vue, c’est-\u00e0-dire si la vue n’est plus utilisable.<\/p>\n
Il suffit donc d’englober l’utilisation de cette proc\u00e9dure dans une boucle pour pouvoir tester d’un coup l’ensemble des vues d’une base.<\/p>\n
-- V\u00e9rification des vues utilisateur\r\n-- Jean-Nicolas BERGER - http:\/\/www.sqlserver.fr\r\n\r\nSET NOCOUNT ON\r\nDECLARE @Vue AS nvarchar(max)\r\nDECLARE @NbOk int, @NbKo int\r\nDECLARE @MessageErreur nvarchar(max)\r\nDECLARE @ListeErreurs table (Vue nvarchar(max), [Message] nvarchar(max))\r\n\r\nDECLARE liste INSENSITIVE CURSOR \r\nFOR SELECT QUOTENAME(s.name)+'.'+QUOTENAME(v.name)\r\n     FROM sys.views v\r\n\t join sys.schemas s on s.schema_id=v.schema_id\r\nWHERE OBJECTPROPERTY(v.object_id, 'IsSchemaBound')=0\r\n\r\nOPEN liste\r\nFETCH NEXT FROM liste into @Vue\r\nSET @NbKo=0\r\nSET @NbOk=0\r\n\r\nWHILE (@@FETCH_STATUS <> -1)\r\nBEGIN\r\n    BEGIN TRY\r\n        EXEC sp_refreshview @Vue\r\n\t\tselect @NbOk=@NbOk+1\r\n    END TRY\r\n    BEGIN CATCH\r\n\t\tIF XACT_STATE() = -1 ROLLBACK\r\n\t\tSET @MessageErreur=ERROR_MESSAGE()\r\n        insert into @ListeErreurs(Vue,[Message]) values (@Vue,@MessageErreur)\r\n\t\tselect @NbKo=@NbKo+1\r\n    END CATCH\r\n    FETCH NEXT FROM liste INTO @Vue\r\nEND\r\nCLOSE liste\r\nDEALLOCATE liste\r\n\r\nprint 'Nb de vue correctes : ' + convert(nvarchar(max),@NbOk)\r\nprint 'Nb de vue incorrectes : ' + convert(nvarchar(max),@NbKo)\r\nif @NbKo>0\r\n\tselect Vue,[Message] from @ListeErreurs<\/pre>\n
On constate que la modification de nom de colonne n’a pas seulement impact\u00e9 la vue de notre test, mais aussi d’autres.<\/p>\n
\"Liste_Soucis\"<\/a><\/p>\n
Lancer cette requ\u00eate sur vos bases de production risque fort de vous surprendre en vous rappelant du code que vous aviez oubli\u00e9 depuis quelques temps …<\/p>\n","protected":false},"excerpt":{"rendered":"
Par d\u00e9faut, les vues cr\u00e9\u00e9es sous SQL Server sont uniquement des alias, c’est-\u00e0-dire que le code qu’elles contiennent (les tables et colonnes utilis\u00e9es, …) est \u00e9valu\u00e9 \u00e0 chaque ex\u00e9cution. Mais avec ce param\u00e9trage par d\u00e9faut, il est possible que les … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,5],"tags":[],"class_list":["post-1043","post","type-post","status-publish","format-standard","hentry","category-article_sql","category-outils"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=1043"}],"version-history":[{"count":15,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1043\/revisions"}],"predecessor-version":[{"id":1773,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/1043\/revisions\/1773"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=1043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=1043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=1043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}