SQL View – Create, Alter, Drop View? Do you know Indexed view?

Last modified date:

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

CREATE VIEW udv_Name
AS
SELECT colums...
FROM dbo.table
WHERE condition;

2) Change of view = Alter view

ALTER VIEW udv_Name
AS
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

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 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

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Tutorials Tags:

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.