why use inline functions to increase the power of indexed views
Posted on 2003-03-31
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
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?