October 26, 2005 at 6:56 am
Hi Guys.. I just wonder how views work in sql server.
I have pulled up a result set as per my requirement through a view by doing some inner joins. Though I do not have any relationship between 2 tables but my view pulls up records from those 2 tables by having an inner join. Is it possible tht I can do inner joins though i do not have relationship in my schema.
As I am new to SQL, can any one tell me clearly where and how we can make use of views or can give any link where i read abt them.
October 26, 2005 at 8:36 am
Foreign keys, joins and views are not related subjects. FKs are constraints, used to make protect data integrity. Joins are used to combine rows from different rowsets into a result. Views are basically query definitions that are stored with an associated name that can be used to query them with. When you query a view the definition of the view (the query) is 'extracted' into the resulting rows and you can then further query that.
October 26, 2005 at 8:52 am
In addition to what Chris mentioned, there are indexed views as well. These are different than the normal views since the result set in the case of indexed views is stored in the database in the same way a table with a clustered index is stored. Look up BOL to see when you can make use of indexed views.
October 26, 2005 at 10:00 am
what I understand is only if I have relationship between 2 tables then I can use inner joins to query out the required resultset.
Do u mean tht u dont need relationships assigned to get resultset in views, we can just use joins to any table. Ok here is what i am doing.
SELECT TOP 100 PERCENT
p.PerfSection AS Expr1,
p.SubSection AS Expr2,
p.MeasureNum AS Expr3,
p.RefNum AS Expr4,
p.Measure AS Expr6,
y.FiscalYearYr AS Expr7,
y.OfficeYr AS Expr8,
y.Actual_PriorYr,
q.ProjectedQtr,
q.ActualQtr,
q.Diff,
q.DiffPercent,
q.DiffReason,
q.CorrectiveAction,
y.Projected_CurYr,
q.Yr_EndProjected
FROM
PerformanceMeasures p
INNER JOIN YearlyEntry y ON p.MeasureNum = y.MeasureNum
INNER JOIN QuarterlyEntry q ON y.MeasureNum = q.MeasureNum AND
y.OfficeYr = q.OfficeQtr AND
y.FiscalYearYr = q.FiscalYearQtr
WHERE (p.MeasureNum = 147)
ORDER BY p.PerfSection,p.SubSection,p.PMOrderBy
But I see that there is no link between QuarterlyEntry and YearlyEntry in my scheme, but still join works fine.
October 26, 2005 at 10:06 am
The relationship is required in the sens that the data must relate to the data in the other table. You wouldn't want to join a userid and on ProductPartID since that just wouldn't make sens. However you don't need to have a declared relation in the database to use a join.
October 26, 2005 at 10:14 am
If am deleting a relationship from my view design mode, does it also deletes in my DB scehma?
October 26, 2005 at 10:16 am
... but the ones in the diagrams do.
October 26, 2005 at 10:16 am
The relationships you make in the views do not propagate to the schema.
October 26, 2005 at 10:25 am
I tried deleting relationship from view but tht did not reflect in the daigram too.
October 26, 2005 at 10:30 am
I understand that I can get my resultset as required from the view by adding and deleting joins irrelated to the schema I have.
October 26, 2005 at 11:21 am
So what is it you do not understand?
October 26, 2005 at 11:24 am
<<I tried deleting relationship from view but tht did not reflect in the daigram too. >>
Correct Diagram Has to be refreshed / updated
<<I understand that I can get my resultset as required from the view by adding and deleting joins irrelated to the schema I have.>>
Joins do not require FK or PK. If that's what you mean by "schema"
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply