Manage Learn to apply best practices and optimize your operations.

Using views

Understand how and why views are used by administrators -- and when is the right time to use them -- in this book excerpt.

The following tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

Using Views

Quite contrary to popular belief, views can actually hurt performance -- not necessarily because they exist, but because of the ways in which they are commonly used in commercial environments. Why should views not be implemented as a performance tuning tool, when in the database model design phase? This book is after all, all about database modeling.

Views are not the same thing as materialized views. A view creates a logical overlay without copying data from tables. Query a view and you query the underlying tables directly. Materialized views are physical copies of data. Query a materialized view and you query the materialized view, and not the underlying tables.

    Views are not conducive to performance. In other words, they can make your applications run a whole heck of a lot slower. Being the author of this book, I have to try to impress on you that I am not biased when it comes to views. How can I convince you of this? Well to begin with, I have worked as both a database administrator and an applications developer. What this means is that I have used views from both the perspective of building new software (development), and that of maintaining existing software (administration).

Views are usually used by administrators to implement security, down to the record and field levels. In other words, a view can be used to restrict specific users to access only specific records in a table. This is because a view contains a query. That query can have a WHERE clause filter, restricting the records returned from underlying tables:

CREATE VIEW BooksStartingWithA AS SELECT * FROM EDITION WHERE TITLE LIKE 'A%';

When querying this view, you will find only books with titles beginning with the letter A. The next query restricts access to fields in a table, as opposed to restricting access to records:

CREATE VIEW BooksWithoutRankings AS SELECT ISBN,PUBLISHER_ID,PUBLICATION_ID
,PRINT_DATE,PAGES,LIST_PRICE,FORMAT FROM EDITION WHERE TITLE LIKE 'A%';

The table from which this view retrieves records is shown in Figure 8-2.


Figure 8-2: Views can be used to provide restricted access.

The only way to access the RANK and INGRAM_UNITS field is by accessing the table directly in the following query:

SELECT * FROM EDITION;

Obviously, if you do not want particular users accessing ranks and Ingram numbers, you can restrict access to the table, allowing general access to the view only. End-users can only access the view. Different types of security access are required by different users. End-users only get to access data from the views that are specifically built for them; therefore, an executive-level manager can see all fields, for all records, in tables a non-management employee would be prohibited from looking at (such as employee salaries). Employees seeing each other's salaries can cause morale problems. Then again, an executive may simply access different information, perhaps wanting factual business-applicable information, avoiding technical details.

The problem with views is that they slow things down because they are often used by developers, or administrators, to make application coding easier. So, the issue is that there is absolutely nothing wrong with using views. The real problem is that views are very often inappropriately used. This is a highly significant consideration. You can never, ever assume that everyone having access to a database knows and understands everything about everything. And you, as a database modeler, cannot expect it even of yourself. Look at it this way. If executive managers use data warehouse reporting, they probably know as much about the internal workings of your database as you know about executive management -- Nothing!

Views are database objects even if they are only logical overlays. If they are used in a database, they do effectively become a part of the database model because they tend to break things down, even if only in a logical and not a physical sense. When considering views and database model performance, keep an eye on use of views. The more you allow the use of views by developers and the general user population, the more you must monitor and performance-tune those views. If managing those views becomes an overwhelming task you may eventually be faced with a difficult task to resolve performance problem.

      In my 20 years of development and administration experience, I've found that the best way to avoid abuse, misuse, and misunderstood use of views is to avoid using views altogether. That is my philosophical approach and is by no means an absolute rule. Be careful using views. Views have very specific roles and the biggest danger is misuse of views.

    Another interesting topic to discuss is application caching.

    The above tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close