• 15. 12. 2019
  • Ing. Jan Zedníček - Data Engineer & Controlling
  • 0

Views have definitely their place in databases and data warehouses. We are talking about objects which are good to use since they do not contain data (do not take up place in storage). They consist only of table query. More complex views can be more expensive for logical operations mostly with complicated queries with multiple joins into big tables. This situation can be solved by Indexed Views (materialized views).

Views are good servants but bad masters. Unthoughtful operations with Views without management one by one will lead to performance problems now or later.

What Can Be T SQL View Used For? Specific Tips

Examples and situations when Views can be used can be summarized like so:

  1. Views as Security featureYou display data you want only some users to see and you can restrict it for others.
  2. Summary of complex logic can be put into Views for better clarity
    1. Repetitive procedures – if it is needed to join 5 tables repeatedly and also apply multiple identical conditions, why not wrap this logic into just one view and query it next time easily?
    2. Aggregation or more complicated queries – View is a good way to display information over atomic data in aggregated form.
  3. Views as a tool to pass information to business users (logic containers) You do not have power users in your company and you do not want your regular users with some SQL knowledge to access your db? Then there is possibility to get rid of routine requirements for simple data sets. Make Views with aggregated data for business users. They will import them into Excel pivot tables and be happy. You will not lose whole day inventing a report.
  4. Building reporting architecture – Views are good tools to define datasets for reports in business intelligence. These datasets can then be under control in organized and transparent form.
  5. Possibility of creating indexed Views and speeding up slow queries

Create, Alter, Drop View – Syntax

1 Creating view = Create View

SELECT colums...
FROM dbo.table
WHERE condition;

2) Change of view = Alter view

SELECT columns...
FROM dbo.table
WHERE condition;

3) Deleting view = Drop view

DROP VIEW udv_Name 

Indexed view (materialized view) and SCHEMABINDING

We should use this type of view whenever we want to profit from some advantages of using View (for example Security of complexity summary), but we have complex and very slow query putting a huge load on logical operations. We will use indexed tsql view and basically create dynamically updated table.

Syntaxe – view will be created followed by clustered unique index

SELECT columns...
FROM dbo.table
WHERE condition;


Creating indexed view and limitations:

  • T SQL Views must be WITH SCHEMABINDNINGmeaning that view is bound by structure of underlying objects. If you try to change the structure of underlying table (e.g. deleting/renaming of column which is using view) it will let you complete the change only after dropping the view. Indexed view is this way protected against changes and has always up-to-date structure.
  • Deterministic definition of indexed viewthis is sometimes a stumbling block. Deterministic definition means that view must return same results with application of same requirements. Doesn’t it need to be like that? If we use view for example in function GETDATE() view will return different results in different time and this requirement is therefore not met.
  • With view, it is mandatory to link to objects in identical database
  • No query can be pointed at different view
  • Clauses UNION and DISTINCT cannot be used
Rate this post

Ing. Jan Zedníček - Data Engineer & Controlling

My name is Jan Zedníček and I have been working as a freelancer for many companies for more than 10 years. I used to work as a financial controller, analyst and manager at many different companies in field of banking and manufacturing. When I am not at work, I like playing volleyball, chess, doing a workout in the gym.

🔥 If you found this article helpful, please share it or mention me on your website

Leave a Reply

Your email address will not be published. Required fields are marked *