Use View or Table?

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
I am one of those older programmers that has spent most of his life in C++ without databases.

Now, I am working more with VB and C# using more and more SQL Server data.

In the database, there are Tables and there are Views. Most of the old code that we have here collects data by calling one of the Views instead of reading directly from the Tables.

Why?

I tried asking someone before, but the answer didnt fully explain anything. I was told something like "a View presents a snapshot of the data and consumes fewer resources that querying data from a Table." Could someone expand on this? ...in laymans terms, please.
 
Thats not true at all. You can think of a view more like a stored query that you can access like a table but which holds no data of its own.
 
Thanks for that. Ill just stick with the main tables unless I need something more complex that is already created in one of those views.
 
Views can make your life a bit easier in a couple of ways though - firstly security may be set on views to allow people to query the view but not requiring them to have permissions on the underlying table.
Secondly if the underlying tables change all code based on them will need to be modified, if views are being used then only the view will need to be updated to reflect the underlying changes - code using the view will still work.
 
The beauty of views is that they can hide complexity and present information in a denormalised form.

Just for examples sake consider a database where you have tables for Employee, Department and JobRole.

If the data is normalised then a row for an Employee might look like

EmployeeID EmployeeName DepartmentID JobRoleID .... Field n
123 Joe Bloggs 456 789 etc.

And the Department table might look like

DepartmentID DepartmentName
101 Finance
456 Sales

And similarly JobRole might look like

JobeRoleID JobRoleDescription
123 Receptionist
789 Software Mechanic

So, in a world without views, to get some meaningful information about employees your query might be:

select E.EmployeeName,
D.DepartmentName,
J.JobRoleDescription

from Employee E
inner join Department D on E.DepartmentID = D.DepartmentID
inner join JobRole J on E.JobRoleID = J.JobRoleID


If you take the sql above and create a view from it, your query will simply become

select EmployeeName, DepartmentName, JobRoleDescription from vwEmployee

Which makes the whole thing more manageable, particularly once your database grows beyond a few tens of tables!

J
 
Back
Top