Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


why use inline functions to increase the power of indexed views

Posted on 2003-03-31
Medium Priority
Last Modified: 2008-03-04
You can also use inline functions to increase the power of indexed views. An
indexed view cannot use parameters in the WHERE clause search conditions to
tailor a stored result set to specific users. However, you can define an indexed
view that stores the complete set of data that matches the view, and then define
an inline function over the indexed view, which contains parameterized search
conditions that allow users to tailor their results. If the view definition is
complex, most of the work that is performed to build a result set involves operations
such as building aggregates or joining several tables when the clustered index
is created on the view. If you then create an inline function that references
the view, the function can apply the user's parameterized filters to pull specific
rows from the result set that was built by the CREATE INDEX statement. The complex
aggregations and joins are performed once, at CREATE INDEX time, and all subsequent
queries that reference the inline function filter rows from the simplified, stored
result set. For example:

Define a view named vw_QuarterlySales that aggregates all sales data into a
result set, which reports summarized sales data by quarter for all stores.

Create a clustered index on vw_QuarterlySales to materialize a result set that contains the summarized data.

Create an inline function to filer the summarized data:

CREATE FUNCTION fn_QuarterlySalesByStore
@StoreID int
FROM SalesDB.dbo.vw_QuarterlySales
WHERE StoreID = @StoreID

Users can then get the data for their specific store by selecting from the inline function:

SELECT * FROM fn_QuarterlySalesByStore( 14432 )
Most of the work that is needed to satisfy the queries that are issued in step 4
is to aggregate the sales data by quarter. This work is performed once in step 2.
Each individual SELECT statement in step 4 uses the fn_QuarterlySalesByStore
function to filter out the aggregated data that is specific to the user's store.

Can somebody explain why the use of inline functions increase the power of indexed views?
Why is the function better than using

SELECT * FROM fn_QuarterlySalesByStore where OrderId = 14432

Or creating a stored procedure?
Question by:Wouter Boevink
LVL 12

Expert Comment

ID: 8243500
just a quess. at least i spare some network traffic when requesting.

CREATE FUNCTION dbo.stringf ( @userId int)  
     from  sysString s
     where a=1 and s.lcid= dbo.userLcId(@userId)

create view dbo.stringv
from  sysString s
where a=1

select * from dbo.stringv where lcid=dbo.userLcId(-1)
select * from dbo.stringf(-1)

LVL 12

Author Comment

by:Wouter Boevink
ID: 8243868
I don't see any real performance improvement or power increasement of indexed views. I don't get why a UDF can enhance the power of an INDEXED view.

You've a point stating it's decreasing network traqffic so performance is increased but it's just a marginal improvement. When using a function the execution plan of the query is cached which improves performance but the same can be accomplished by using a stored procedure.

In all kinds of articles and powerpoint presentation I see the statement that the power of indexed views is increeased by using udf's but nowhere an explanation.
LVL 12

Expert Comment

ID: 8244363
"but the same can be accomplished by using a stored procedure" ...you can't do  select * from myProc

"the power of indexed views is increeased by using udf's" ... i think it doesn't mean only inline
for example tree structures traversing or parsing the string cannot be done in view, but you can do it with udf returning table

CREATE function  dbo.strSplit ( @string nvarchar( 4000), @splitter char( 1) )
returns @res table ( id int primary key, value nvarchar( 4000) )
declare @start int, @word nvarchar(4000), @charindex int
set @start=1
set @charindex= charindex( @splitter, @string, @start)
while (@charindex <> 0)begin
     set @word= substring( @string, @start, @charindex - @start)
     set @start= @charindex +1
     set @charindex= charindex( @splitter, @string, @start)
     insert into @res  values ( @start, @word)

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 12

Author Comment

by:Wouter Boevink
ID: 8244732
I agree UDF's increase power on views. But the question was why does it increase power particulary on INDEXED views.
LVL 23

Accepted Solution

adathelad earned 1500 total points
ID: 8253388

I may be barking up the wrong tree, but I spose it's better to speak up rather than say nothing! Also, please not that I have not actually tested this so I'm speaking purely on my theory.

From what I understand, there are limitations with what you can do in indexed views (e.g. you cannot use AVG()).
Can these restrictions be effectively lifted by using inline functions from within the view?

e.g. inside a view:

SELECT CustomerId, dbo.fxn_GetAverageSpend(CustomerId)
FROM Transactions

So here, a UDF would do the work of finding the average amount spent by a customer.

Also, if a CREATE VIEW statement cannot used derived tables, you could use an inline UDF to return a table of results which are then queried by the view.

Please point out if I am wrong or have missed the point!

Expert Comment

ID: 9275418
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.
LVL 12

Expert Comment

ID: 11183876
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: adathelad http:#8253388

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

EE Cleanup Volunteer

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question