Best practises : is sql views really worth it? [duplicate]

Question

I am building a new web applications with data stored in the database. as many web applications, I need to expose data from complexe sql queries (query with conditions from multiple table). I was wondering if it could be a good idea to build my queries in the database as sql view instead of building it in the application? I mean what would be the benefit of that ? database performance? do i will code longer? debug longer?

Thank you

Answer 1

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.

Answer 2

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