Use View or Table?

[joe_pool_is]

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 didn't 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 layman's terms, please.

[John]

That's 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 it's own.

[joe_pool_is]

Thanks for that. I'll just stick with the main tables unless I need something more complex that is already created in one of those views.

[PlausiblyDamp]

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.

[TRANSLTR]

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