Performance Question Using Views

When creating views, do you follow the same rule to select as little as
possible? For instance, is it bad practice to create a view that selects
everything from one of my tables and certain columns from another table, and
when executing a stored procedure against the view to limit that to only the
columns I need at the time. So if I had a view that included 20 fields from
one table and 10 fields from another table, is that slower than if I create a
view that only shows the actual fields I'll need for the particular lookup
I'll be doing? Hope this makes sense.
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Yes it does. And if you don't reference the extra columns of the view,
there is no additional cost. SQL Server will even eliminate joins which
exist in the view if none of the joined columns are referneced and the join
can't add or remove rows from the result.
David
|||Mike
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
ably to use indexes in that case I mean COVERING indexs and as result you
will get a bad performance.
Actually , views are good choice for sequrity reasons I mean not letting
users an access to underlying tables directly
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> When creating views, do you follow the same rule to select as little as
> possible? For instance, is it bad practice to create a view that selects
> everything from one of my tables and certain columns from another table,
> and
> when executing a stored procedure against the view to limit that to only
> the
> columns I need at the time. So if I had a view that included 20 fields
> from
> one table and 10 fields from another table, is that slower than if I
> create a
> view that only shows the actual fields I'll need for the particular lookup
> I'll be doing? Hope this makes sense.
|||When you say views are good for security, does this apply to users of a web
application. They will not be able to do any adhoc reporting or running any
select statements directly against the database. So I'm wondering if we
should even use views, except for maybe a complex join that we don't want in
a stored procedure.
"Uri Dimant" wrote:

> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't be
> ably to use indexes in that case I mean COVERING indexs and as result you
> will get a bad performance.
> Actually , views are good choice for sequrity reasons I mean not letting
> users an access to underlying tables directly
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
>
>
|||That's good to know...As I replied to Uri, I'm still wondering whether to use
views since the users will not be directly quering the database...they'll be
only seeing our web pages and interacting with the database that way. Thanks.
"David Browne" wrote:

> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:D43D97EC-0705-4DA2-B094-6C8128CB2CFB@.microsoft.com...
> Yes it does. And if you don't reference the extra columns of the view,
> there is no additional cost. SQL Server will even eliminate joins which
> exist in the view if none of the joined columns are referneced and the join
> can't add or remove rows from the result.
> David
>
>
|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Mike
>
> Definitly ,Mike.When you using SELECT * in the view , SQL Server won't
> be ably to use indexes in that case I mean COVERING indexs and as result
> you will get a bad performance.
>
Um, no. SELECT * in a view does not force SQL Server to access all the
columns in the intermediate result. The views definition is incorproated
into the overall query before optimization, and if certian columns or joins
are not needed, they won't be touched.
EG
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23Otlon4%23FHA.208@.tk2msftngp13.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uEztbwz%23FHA.140@.TK2MSFTNGP12.phx.gbl...
> Um, no. SELECT * in a view does not force SQL Server to access all the
> columns in the intermediate result. The views definition is incorproated
> into the overall query before optimization, and if certian columns or
> joins are not needed, they won't be touched.
> EG
>
[oops]
Here's the example I intended to include. In it a view contains both a join
and a SELECT *, both of which are disregarded when querying against the
view, and and only a small covering index is accessed.
create table B
(
ID int primary key,
Description varchar(50)
)
Create table T
(
ID int primary key,
A varchar(10),
B int not null references B
)
create index IX_T_A on T(A)
go
create view VT
as
select T.*,B.Description BDescription
from T
join B
on T.B = B.ID
go
insert into B(ID, Description) values (1,'One')
insert into T(ID,A,B) values (1,'Hello', 1)
go
set showplan_xml on
go
select A from VT
go
set showplan_xml off
/*
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"
Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="select A from VT "
StatementId="1" StatementCompId="1" StatementType="SELECT"
StatementSubTreeCost="0.0032831" StatementEstRows="1"
StatementOptmLevel="TRIVIAL">
<StatementSetOptions QUOTED_IDENTIFIER="false" ARITHABORT="true"
CONCAT_NULL_YIELDS_NULL="false" ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" NUMERIC_ROUNDABORT="false" />
<QueryPlan CachedPlanSize="8">
<RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan"
EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581"
AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" Parallel="0"
EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</OutputList>
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[test]" Schema="[dbo]"
Table="[T]" Column="A" />
</DefinedValue>
</DefinedValues>
<Object Database="[test]" Schema="[dbo]" Table="[T]"
Index="[IX_T_A]" />
</IndexScan>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
David