Introducing the Problem
We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.
When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.
One of the reasons for the degraded performance time is the output views large result sets, because these sets were not filtered properly. This fact was also not detected by QA when testing the application in the applications acceptance phase.
After a thorough examining of the source of the performance problems, management decides that a series of in line table functions that can be parameterized must be created so that the data from querying the views will be replaced by these functions and filtered at the source.
The reasoning behind this decision comes from the fact that In-line User Defined functions can be thought of as views that accept parameters and can be more efficient than simple views.
This flavor of User Defined function can be used effectively to return different data from the same tables based on the parameter values without losing the functionality of the view.
The in line user defined function return tables and can be treated as another row set and thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling books along with their writers, you can easily join it with other tables.
Another benefit of User Defined Inline function is that it the inline table value function is able to expose its execution plan to an external query, thus increasing throughput. Inline functions are also economic since it does not declare any local variables because its TSQL body consists of parameters and query and not any variable declarations.
The suggested solution
The suggested solution presented here involves creating a T-SQL stored procedure that will create the in line table function automatically, directly from the existing view definition taken from the SQL Meta data.
The function creation is done by using dynamic T-SQL. The function name will be like the view name only prefixed by ‘udf_’ and then the view name.
After the procedure execution, both the view and function will remain in the application database. It is up to the programmer to link the code to the new defined function or to keep the existing code that uses the view.
Here is the suggested stored procedure T-SQL code:
CREATE PROCEDURE usp_convert_view_to_inline_TF (
@schemaName NVARCHAR(30) = 'dbo'
,@compare Token NVARCHAR(40) )
DECLARE @udf_def NVARCHAR(max)
DECLARE @view_def NVARCHAR(max)
DECLARE @udf_name NVARCHAR(100)
DECLARE @query_start INT
SET @udf_name = @schemaName + '.' + 'udf_' + @viewName + ' (' + @filterparam + ' ' + @paramtype + ') '
IF EXISTS (SELECT 1
FROM SYS.OBJECTS a
WHERE object_name(a.object_id) = @viewName
AND schema_name(a.schema_id) = @schemaName
AND a.type = 'V' )
SET @udf_def = 'CREATE FUNCTION ' + @udf_name + 'RETURNS TABLE AS RETURN '
SELECT @view_def = DEFINITION
WHERE [object_id] = OBJECT_ID(@viewName);
SET @query_start = CharIndex('SELECT ', @view_def, 1)
SET @udf_def += SUBSTRING(@view_def, @query_start,
len(@view_def) - @query_start + 1) +
' AND ' + @compareToken + ' = ' + @filterparam
PRINT 'Function ' + @udf_name + ' Created.'
PRINT 'Function ' + @udf_name + ' Not Created. View ' + @schemaName + '.' +
@viewName + ' Not Found.'
Here are some explanations for the code:
I called the procedure usp_convert_view_to_inline_TF, It should be compiled in the application’s database, along with the others view and functions.
The procedure gets the current view name and the views schema name (the default value defaults to dbo), it gets the new function filter parameter name , the parameter type and the expression inside the view’s definition that needs to be compared to the new parameter.
Please note that the procedure’s assumption is that there is only one filtering condition that will be added to the original view’s definition. This is assumed for code Simplicity, however , in many cases , the filtering done is much more complex and consists of several filtering conditions, from one to ten or even twenty filtering conditions.
In order to add more than one condition there are two main methods for accomplishing this task:
Adding additional procedure input parameters for each condition added in the following manner:
For each added condition, three more parameters: filter parameter, parameter type and compare to token.
You can limit it to N conditions. Also, you can add as many as you wish you wish. You can also default the input parameters to NULL.
In that case, when calling the procedure, just send the non-empty parameters but check fir null values of the input when constructing the TSQL that creates the function.
The second method is changing the filter parameter; parameter type and compare to token parameters to comma separated lists. In the procedures API change them to:
@compareToTokenList nvarchar (400)
When constructing the dynamic SQL statement, add a loop that parses the comma separated strings and adds the conditions to the body of the query.
If you use the UDF name TSQL variable in order to construct the new function signature, please enlarge this variable to 1000 characters (instead of 100 as it is written in the code here).
The procedure checks the sys.objects system view for a view in that name and schema. If no such view exists, it reports an error and that the function will not be created and exits.
If the view exists, the procedure extracts its definition from the sys.sql_modules system view and constructs a dynamic T-SQL in line tables creation statement that includes the schema name, the function name that is actually the view name with ‘UDF_’ as a prefix and the views query extracted from the definition string.
The procedure then adds to the views condition list the filtering clause constructed by comparing the given parameter name to the compared expression from the view definition.
The procedure executes the constructed string dynamically and reports the creation of the function.
Example of testing the procedure
For our test, let’s take Microsoft’s Northwind sample database that can be downloaded from Microsoft site (it’s called sample databases package).
For example. Let’s create a view that joins categories and products giving category name, product name and product price
CREATE VIEW ProductsCatalog
WHERE categories.CategoryID = products.CategoryID
After creating the view, let’s execute our procedure, filtering on the category name:
@viewName = 'ProductsCatalog'
,@filterparam = '@catname'
,@paramtype = 'nvarchar(15)'
,@compareToken = 'categories.CategoryName'
The procedure operates on the ProductsCatalog View, extracts its definition , take the SELECT statement and uses it to create an in line table function creation statement that accepts the filtering parameter, the query from the view and the additional filtering clause from the @filterparam and @compareToken parameters.
Now, we get the following in line table function
CREATE FUNCTION dbo.udf_ProductsCatalog (@catname nvarchar(15))
RETURNS TABLE AS
select categories.CategoryName, products.ProductName, products.UnitPrice
from categories, products
where categories.CategoryID = products.CategoryID AND
categories.CategoryName = @catname
Now we can execute it and filter on category name
If we try to execute the procedure with a non-existing view like ProductsCatalogX (For example), we will getFunction dbo.udf_ProductsCatalogX (@catname nvarchar(15)) Not Created. View dbo.ProductsCatalogX Not Found.
- Creating a stored procedure to fix orphaned database users - January 25, 2016
- Creating a gap in sequences – TSQL Stored Procedure advisor - January 6, 2016
- Construct a special multi-statement table function for checking SQL Server’s health - December 24, 2015