Bytes and Bites of Internet
Views
SQL Server Views … gotcha while using sp_refreshview
Oct 6th
Last week I blogged about use of Views in SQL Server. We also saw different types of view and best practices for designing new view and limitations that may prevent us creating a view too. As a part of best practice while designing we should always include keyword, WITH SCHEMABINDING which prevents any accidental changes to base table on which we have defined the view.
Today’s post is about case when we have not use SCHEMABINDING with view, and in that case if we change underlying structure it will not appear in view till we use system stored procedure called SP_REFRESHVIEW. The syntax is something like this,
-- Sudo Code sp_refreshview [ @viewname= ] 'viewname'
SQL Server Views … to make sure others “view” what you want them to view !!!
Sep 28th
In essence, SQL Server view can be considered as a “Virtual Table”. When we create a view, user “feel” no difference between it and a table. And we can query it exactly same way as we query any other table. View can be really handy when we are using some super complex query to get data, by shortening the query. But keep in mind that when we define a view, actually nothing happens … consider view as a just shortcut to execute some very long query … period.
Creating view by any means doesn’t “improve” the performance of query. It just simplifies the way user can execute a query . So if you original query generates super big , complex execution plan then it will do SAME for view.
With that said, following are few use of VIEWs.
First example is demo. of just simple view.
-- Simple View CREATE VIEW dbo.vFoo1 AS SELECT * FROM viewTest GO
But what if underlying table gets changed ?? Well, to prevent any changes to underlying tables used in views we have to create a view with a parameter called SCHEMABINDING. So when someone tries to change underlying table structure (say, adding or removing column) error is generated.