"Ian Evitable" wrote:

> Hello
>
> This newsgroup seems to have deleted my previous messages?
> So thanks to Louis here for previous help. I am now getting the resultsets i
> want. Another question:
>
> For the select query below, does the function dbo.ACEOr get evaluted once or
> twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
> criteria or does the sql server engine just treat it as a variable and
> recognise that its already been evaluated in the SELECT DISTINCT part of the
> query.
>
> SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId,
> @Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> @Authority) >0
>
> There is no noticable performance hit at the moment but i expect i should
> know this for future reference.
>
> Thanks.
> Ian
>
>
>

> > @Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> > @Authority) >0
>
> SELECT DISTINCT
>    tblSalesTerritory.ACEId,
>    dbo.ACEOR(tblSalesTerritory.ACEId, @Authority) AS AuthLevel
> from
>    tblSalesTerritory
> WHERE
>    dbo.ACEOR(tblSalesTerritory.ACEId, @Authority) > 0
> go
>
> I think that the function will be evaluated one time per each row to
filter
> the rows based on the where clause and one time per each row that passed
the
> filter.
>
> You can verify this with the execution plan or you can create a dummy
stored
> procedure, include the statement in the sp and then debug the sp. Do the
> debug with few rows.
>
> Example:
>
> use northwind
> go
>
> create function dbo.ufn_f1 (
> @orderid int
> )
> returns int
> as
> begin
> return (@orderid)
> end
> go
>
> set showplan_text on
> go
>
> select orderid, customerid, orderdate, dbo.ufn_f1(dbo.orders.orderid) as
c1
> from dbo.orders
> where dbo.ufn_f1(dbo.orders.orderid) between 10250 and 10260
> go
>
> set showplan_text off
> go
>
> drop function dbo.ufn_f1
> go
>
> Result:
>
>
> StmtText
>
> --------------------------------------------------------------------------
----------------------------------------------------------
>   |--Compute
> Scalar(DEFINE:([Expr1002]=[dbo].[ufn_f1](Convert([Orders].[OrderID]))))
>        |--Filter(WHERE:([dbo].[ufn_f1](Convert([Orders].[OrderID]))>=10250
> AND [dbo].[ufn_f1](Convert([Orders].[OrderID]))<=10260))
>             |--Clustered Index
> Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))
>
>
> AMB
>
> "Ian Evitable" wrote:
>
> > Hello
> >
> > This newsgroup seems to have deleted my previous messages?
> > So thanks to Louis here for previous help. I am now getting the
resultsets i
> > want. Another question:
> >
> > For the select query below, does the function dbo.ACEOr get evaluted
once or
> > twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
> > criteria or does the sql server engine just treat it as a variable and
> > recognise that its already been evaluated in the SELECT DISTINCT part of
the
> > query.
> >
> > SELECT DISTINCT tblSalesTerritory.ACEId,

> > @Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> > @Authority) >0
> >
> > There is no noticable performance hit at the moment but i expect i
should
> > know this for future reference.
> >
> > Thanks.
> > Ian
> >
> >
> >

:)