May 5, 2008 at 5:23 am
This is probably a real novice question but I am unsure of the answer and so I hope that you can help me! Basically, I want to know whether it is better to query data against a view of a table rather than directly against the table? If using a view is recommended please can you explain why.
Thanks
May 5, 2008 at 5:35 am
[font="Verdana"]Views stores query with execution plan. So I think using views might be better over using tables directly.
Mahesh[/font]
MH-09-AM-8694
May 5, 2008 at 6:29 am
It depends. All the data is stored in tables. So even when you query a view, except for materialized views, you're querying the tables that make up the view. The view is a layer in front of your data. You use them to hide really complex query logic that you don't want to have to recreate over and over again, for example. You can also use them as a security mechanism, changing the names of tables or columns or lmiting access to the underlying data to certain users or groups. There are also other uses. It's not really tables or views, since all queries, exception noted, go to the tables. It's a question of what do you need to do and how do you want to do it.
BTW, Actually, a view doesn't store the query with an execution plan. It just stores a query. The execution plan is generated against the view when the view is referenced by another query and usually entails exploding out the view into the component tables and joins.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2008 at 7:50 am
Because of the way views work, think of them as a way to give yourself a shortcut for complex queries that you will use over and over again.
So, the answer to your question depends on what you mean by "better". It's very specific to your particular situation.
For example, I have two tables, one for event dates, one for even locations. Almost every query that needs to pull data from one, also needs to pull data from the other. So I have a view that selects the most commonly needed columns from both tables. It saves me a little time in writing queries that use those columns.
Another example I've seen was a query that joined six tables and selected most of the columns from all of them. This view was used in procs that only needed one or two of the tables. That means joins were done to all of the tables, even ones that weren't needed. This means that the queries were very easy to write, but were much slower than they should have been. (This was built by a developer who didn't understand how this worked, and I've fixed most of it at this time.)
So, in the first case, the view is better, in the second case, directly to the tables is better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2008 at 10:06 am
Thanks for the replies, I suppose that what I am unsure about is whether querying the table directly performs some sort of lock on the table that maybe a view doesn't?
May 5, 2008 at 11:15 am
as stated above, views are used to simplify complex queries that are used repeatedly. I use views for report writers who have difficulty with the join syntax and of course they are great when encompassing CTEs.
Views make the difficult more simple. Your truely experienced SQL writers should create the views and have the junior/not as versed SQL writers query against those views.
In other words, views limits the number of questions others may ask the DB developer. You can create the complex view and most developers can write code against the view since it should contain a simple column structure.
Marvin Dillard
Senior Consultant
Claraview Inc
May 5, 2008 at 11:28 am
David (5/5/2008)
Thanks for the replies, I suppose that what I am unsure about is whether querying the table directly performs some sort of lock on the table that maybe a view doesn't?
No. There is no difference in locking. If anything, a view that includes a table you don't need for your current query, will create more locks, because it will potentially also put locks in that table.
The only difference between querying a view and directly querying the table(s) involved, is how much you have to write to query them.
For example:
create table Table1 (
ID int identity primary key,
Val varchar(50))
go
create table Table2 (
ID int identity primary key,
Table1ID int not null refernces dbo.Table1(id),
Val varchar(50))
go
create view T1and2
as
select Table1.ID, Table1.Val as Val1, Table2.Val as Val2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID
Then:
select *
from dbo.T1and2
And:
select Table1.ID, Table1.Val as Val1, Table2.Val as Val2
from dbo.Table1
inner join dbo.Table2
on Table1.ID = Table2.Table1ID
Both select statements will do exactly the same thing. Same locks, same execution plan, same IO, etc. The first one, however, is much easier to write than the second one. That is the purpose of views, the only real purpose of non-indexed views. It's as simple as that.
Here's where you get a difference:
select ID, Val1
from dbo.T1and2
vs
select ID, Val
from dbo.Table1
The select from the view will still have Table2 in it, while the direct select against the table doesn't. That means the second query will have less locks, less IO, etc., and will probably be faster.
Does that make it more clear?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 5, 2008 at 1:58 pm
There is another reason to us views besides the the aspect of making difficult joins easier for others by putting them in the view so that the just need to query the view.
In some systems, you want to isolate the end user from directly working with tables. To do this, you would use views and stored procedures to isolate the tables thus having more control on the interaction with the tables and how they are used.
Dave Novak
May 5, 2008 at 2:18 pm
MD (5/5/2008)
as stated above, views are used to simplify complex queries that are used repeatedly. I use views for report writers who have difficulty with the join syntax and of course they are great when encompassing CTEs.Views make the difficult more simple. Your truely experienced SQL writers should create the views and have the junior/not as versed SQL writers query against those views.
In other words, views limits the number of questions others may ask the DB developer. You can create the complex view and most developers can write code against the view since it should contain a simple column structure.
..if used correctly. Overusing views (where views all yet other views, calling other views, etc...) can be a direct recipe to making certain queries "harder" than they have to be to accomplish the goal (and therefore - are much slower performance-wise, and then to take sizeable chunks of your tempDB to complete).
Hiding too many levels just hurts readbility IMO, so again - if you use it correctly (just like CTE's), you can make something look simpler.
If performance is what you're after, and the results are fairly static - look at creating indexed views.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 6, 2008 at 4:31 am
Thanks all for your responses, much appreciated.
May 7, 2008 at 5:55 am
Mahesh Bote (5/5/2008)
[font="Verdana"]Views stores query with execution plan. So I think using views might be better over using tables directly.Mahesh[/font]
mahesh,
even it has query plan. it should scan the base tables.
May 12, 2008 at 5:33 am
I work in an environment where tables are the main thing I query against, sometimes I query against views, but rarely.
My colleagues (more experiences in SQL than me, and the creators of the DB) informed me that querying against a view makes for a slower query than going against a table.
Not sure why that is, but it seems to make sense in our DB!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply