Best practises : is sql views really worth it?

<>

This question already has an answer here:

Answers


This can not be answered really objectively, since it depends on case by case.

With views, functions, triggers and stored procedures you can move part of the logic of your application into the database layer.

This can have several benefits:

  • performance -- you might avoid roundtrips of data, and certain treatment are handled more efficiently using the whole range of DBMS features.
  • consisness -- some treatment of data are expressed more easily with the DBMS features.

But also certain drawback:

  • portability -- the more you rely on specific features of the DBMS, the less portable the application becomes.
  • maintenability -- the logic is scattered across two different technologies which implies more skills are needed for maintenance, and local reasoning is harder.

If you stick to the SQL92 standard it's a good trade-off.

My 2 cents.


I think your question is a little bit confusing in what you are trying to achieve (Gain knowledge regarding SQL Views or how to structure your application).

I believe all database logic should be stored at the database tier, ideally in a stored procedure, function rather in the application logic. The application logic should then connect to the database and retrieve the data from these procedures/functions and then expose this data in your application.

One of the the benefits of storing this at the database tier is taking advantage of the execution plans via SQL Server (which you may not get by accessing it directly via the application logic). Another advantage is that you can separate the application, i.e. if the database needs to be changed you don't have to modify the application directly.

For a direct point on Views, the advantages of using them include:

  • Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor- tracking table.

  • Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

  • Join columns from multiple tables so that they look like a single table.

http://msdn.microsoft.com/en-us/library/aa214068(v=sql.80).aspx


Personally I prefer views, especially for reports/apps as if there are any problems in the data you only have to update a single view rather than re-building the app or manually editing the queries.


SQL views have many uses. Try first reading about them and then asking a more specific question:


I have seen that views are used a lot to do two things:

  1. Simplify queries, if you have a HUGE select with multiple joins and joins and joins, you can create a view that will have the same performance but the query will be only a couple of lines.
  2. For security reason, if you have a table with some information that shouldn't be accessed for all the developers, you can create views and grant privileges to see the views and not the main table, I.E: table 1: Name, Last_name, User_ID, credit_card, social_security. You create a view table.table view: name, last_name, user_id .

You can run into performance issues and constraints on the types queries you can run against a view.

Restrictions on what you can do with views.

In my experience a well indexed table, using the right engine, and properly tuned (for example setting an appropriate key_buffer value) can perform better than a view.

Alternatively you could create a trigger that updates a table based on the results of other tables. http://dev.mysql.com/doc/refman/5.6/en/triggers.html


The technic you are saying is called denormalization. Cal Henderson, software engineer from Flickr, openly supports this technic.

In theory JOIN operation is one of the most expensive operations, so it is a good practice to denormalize, since you are transforming n queries with m JOIN in 1 query with m JOIN and n queries that select from a view.

That said, the best way is to test it for yourself. Because what could be incredibly good for Flickr may not be so good for your application.

Moreover, the performance of views may vary a lot from one RBDMS to another. For instance, depending on the RBDMS views can be updated when the orginal table is changed, can have indexes, etc.


Need Your Help

Generate exponential distribution of bucket sizes

math testing statistics

Given a series of incoming items, I want to assign each one to a bucket as it comes in. The bucket can either be a new one (one that has never been used before, of which there are an infinite suppl...

How can I redirect mobile devices to a different URL?

javascript browser mobile

I want to check whether the user is viewing my site from a mobile device or PC. If it's a mobile device, I want to redirect my site URL like Google does...