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 feature – You 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

2) Change of view = Alter view

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

CREATE VIEW udv_Name
WITH SCHEMABINDING
AS
SELECT columns...
FROM dbo.table
WHERE condition;

CREATE UNIQUE CLUSTERED INDEX IDX_udv_Name ON dbo.udv_Name(ID);

Creating indexed view and limitations:

• T SQL Views must be WITH SCHEMABINDNING – meaning 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 view – this 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