2017-02-02

Designing and implementing views

Reducing the complexity and facilitating the readability of queries are realizing by implementing views. Possible to name it as 'virtual table' too. From both performance and complexity aspects, it allows us to turn situation to an advantageous side. Let's look at where to use views:in calling the queries without any executing of table, in giving permission to any user to view specific column,data, in boosting the performance mainly.
From security aspect, let's assume that person A gives table permission to person B, except displaying employees' salary info.Here A prefer to create a view and grant B a permission to access just only view, not whole table.


On the other instance, let's assume that there are 3 users: A,B and C. User A gives permission to B access to view managing by A only, however B has not any permission to grant  permission to C accessing to same view.In this situation A can give table permission to B or directly view permission to C for accessing C to same view.As you see view limits in granting a permission.
View is dividing into 4 main types:

  • Standard views: combine data from base table(s) into a new virtual table
  • System views: show broad info about system catalog, part of DMO(Dynamic Management Objects) and named DMV
  • Indexed views: views created through clustered index, effects performance of system considerably
  • Partitioned views: gathering data from multiple tables into one view

To create,change and remove any view check this out:

Encryption clause limits getting access to related view by any user,adding 'WITH ENCRYPTION' before AS is enough to set it as encrypted.
Moreover, there are some rules and limitation in using views,to get broad information please visit following link.

Indexed view has tremendous impact on server's performance boosting. Beforehand, to create indexed view you should use 'schemabinding' option.Lets look at this example:

Execution plan of written query is the same as in picture, moreover Estimated Subtree Cost is 9.05654 value.
Execution plan of query

Next, create the view and set index on it.
Display result set of indexed view and observe execution plan.
Execution plan of indexed plan
And Estimated Subtree Cost is 0.162187, as you see performance has been increased by indexing view.Another advantageous side of indexed view is acting as index on any query is covered by indexed view,although this query is indexed or not.Lets execute same query as new one and look at the execution plan.You will see that same result,performance has been realized as indexed view.
By updatable view it is possible to change both view and base table,not only view as in altering view.

To display all stored views in database use sys.views inside select statement.
For broad explanation in Turkish check my YouTube video out:

No comments:

Post a Comment