The purpose of View?

[motil]

i saw "si_the_geek" mention it in the other thread so i decided to go a head and ask, i'm working with SQL for quite some time and i still can't really get the purpose of views , when and what the benefits of using them, can some one will please explain this to me?

Best Regards.

[si_the_geek]

A View is basically a Select statement stored in the database.

It can contain things like any other Select statement (such as Joins, a Where clause, Group By, etc), and you can use it from your program (or another SQL statement) as if it was a table - but usually you cannot edit the data as you would with a table.

Being able to do that means you can simplify several SQL statements which perform similar tasks (such as joining multiple tables).

As a bonus, it tends to run faster too - because the database system not only stores the query, but also works out (and stores) the quickest way to run it.

[motil]

Thanks,
I still don't fully understand the logic of using it, i'll read some more about it soon.

[szlamany]

There are so many reasons to use VIEW's - here is one that I created for the purpose of making other queries easier to build.

This VIEW creates columns that add up various counts of status codes found in cases this app manages.

Create View CaseCounts_V
As
Select CA.CaseId
	,Count(Distinct CI.FileId)					"SourceFiles"
	,Sum(Case When CI.FileId is not null Then 1 Else 0 End)		"InfoCount"
	,Sum(Case When CI.InfoStatus=0 Then 1 else 0 End)		"NotApproved"
	,Sum(Case When CI.InfoStatus>=1 Then 1 else 0 End)		"Approved"
	,Case When Sum(Case When CI.InfoStatus>=1 Then 1 else 0 End)<Sum(1)
		Then '[' + Cast(Sum(Case When CI.InfoStatus>=1 Then 1 else 0 End) as varchar(10))
			+' of ' + Cast(Sum(Case When CI.InfoStatus=0 Then 1 else 0 End) as varchar(10))+']'
		Else '[ All ' + Cast(Sum(Case When CI.InfoStatus=0 Then 1 else 0 End) as varchar(10))+']' End	"Stage"
	,Sum(Case When CI.InfoStatus=0 Then 1 Else 0 End)		"NeedEstimate"
	,Sum(Case When CI.InfoStatus=1 Then 1 Else 0 End)		"Estimated"
	,Sum(Case When CI.InfoStatus=2 Then 1 Else 0 End)		"Ready"
	,Sum(Case When CI.InfoStatus between 4 and 10 Then 1 Else 0 End)
									"Intaken"
	,Sum(Case When CI.InfoStatus in (5,6,7) Then 1 Else 0 End)	"Placed"
	,Sum(Case When CI.InfoStatus=9 Then 1 Else 0 End)		"Proofreader"
	,Sum(Case When CI.InfoStatus in (11,12) Then 1 Else 0 End)	"Review"
	,Sum(Case When CI.InfoStatus=13 Then 1 Else 0 End)		"Back"
	,Sum(Case When CI.InfoStatus in (14,15) Then 1 Else 0 End)	"Delivery"
	,Sum(Case When CI.InfoStatus in (18,99) Then 1 Else 0 End)	"Delivered"
From Case_T CA
Left Join CaseFile_T CF on CF.CaseId=CA.CaseId
Left Join CaseInfo_T CI on CI.FileId=CF.FileId and Left(CF.FileType,1)='*'
Group by CA.CaseId

Now when I want to know one of these "amounts" in another query I simply JOIN to the VIEW.

Select CA.CaseId, CA.CaseNum, CA.DateLoaded, CA.Status1, CA.Status2, CA.ClientId, CA.MatterNumber
		, CA.PatentApp, CA.CaseType,CA.LanguageFrom, CA.LanguageTo, CA.Rolling
		, CA.ContactEMail1, CA.ContactEMail2, CA.ContactEMail3, CA.ContactEMail4
		, CA.StaffId, CA.StandardTurnAround, CA.VendorsNeeded, CA.RelatedCaseId
		, CA.LegalParty, CA.Opposing, CA.Applicant, CA.ClientComment
		, CA.Comment
.
.
.
		,CV.NeedEstimate
		,CV.Estimated
		,CV.Ready
		,CV.Intaken
		,CV.Placed
		,CV.Proofreader
		,CV.Review
		,CV.Back
		,CV.Delivery
		,CV.Delivered
		From Case_T CA
		Left Join CaseCounts_V CV on CV.CaseId=CA.CaseId
		Left Join Client_T CL on CL.ClientId=CA.ClientId

I join to the CASECOUNTS_V view and all these summary figures are easily and quickly available to me.

Plus - and this is really important - since we are still developing this app we can change the VIEW (if status codes are modified) and all the queries that use it get the updated logic.

[motil]

Thanks for the example szlamany,

I found it a bit hard to understand but it give me some idea of how it works.

[leinad31]

Purpose is not entirely technical, rather it is also for practical reasons.

"Single version of truth." A view guarantees that all applications/reports and users will have access to the same dataset/information (post join) rather than having their own "interpretation" of how the tables are to be related/joined. Have you ever encountered reports from accounting and sales whose figures did not match? Then you don't employ single version of truth in enterprise architecture. Just don't overdo it, prioritize common datasets across business units.

Ensure implementation of business logic. This is related to single version of truth when SQL becomes more complex and business rules determine what records are to be returned. If implementation is done at application level then inconsistencies are possible, e.g. not reflected in later versions or overlooked in replacement programs.

Centralize dataset management and SQL tuning. You only have to update one view for table changes, e.g. additional column. You can adjust index strategy accordingly based on view join condition and how it is generally used (filtering).

Maximize database SQL optimization technologies. Would you consider recompiling/rebuilding a VB component every time it is to be run. No you wouldn't, you build once and run multiple times. So why aren't you doing the same with SQL (statement caching, cursor caching, one-time SQL parse, etc)? Most front-end programmers just keep passing queries without considering database CPU overhead from re-parse and performance hit is felt in multi-user environments.

Dependency tracking and easier to document. What if you wanted to change how a table is treated? Off the top of your head can you identify all applications, all app modules, all databound controls, all reports, all etc that will be affected? No you can't, but with views you can rely on internal database dependency tracking to simplify the chaos a bit. You also don't have to keep track of table usage per module (or worse per procedure), rather you have less items to document when app module to view dependency is tracked instead. Data flow diagrams are also facilitated, e.g. client info view instead of client basic info + client contact info + client address info + client etc info.

Facilitate fine grained and regression testing. I say fine grained to differentiate from system or end-to-end (entire transaction) tests. Do you have to run reports just to check if the totals are correct? Couldn't you have tested at the database level using SQL (and use historical data as sample data to simulate "parallel" run). You can dump data from views into temp table and perform analysis using SQL and speed up the testing process as compared to having to run applications and reports (dependent on their output because join logic is at application level) for even the most trivial changes.

View is backed up along with database. In case you lose your scripts, local CVS repository, etc. Just ask help from dbadm.

etc etc