Create parameterized VIEW in SQL Server 2008

sql

performance

best-practices

execution-plans

byNikita Barsukov·Aug 26, 2024

In SQL Server 2008, parameterized views are not directly supported. However, you can emulate this feature using a table-valued function (TVFs) and a stored procedure. This setup resembles the effect of parameterized view as the function accepts parameters and returns a table of results. Then, select from this function via a procedure, passing the required parameters.

Here's a simplified illustration:

// Creating parameterized views? SQL Server: "Hold my beer" 🍺

CREATE FUNCTION dbo.FilteredResults (@Key INT, @Value VARCHAR(100)) // Gimme some data...
RETURNS TABLE
AS
RETURN (
  SELECT * FROM YourTable 
  WHERE ID = @Key 
  AND Data = @Value); // ...and I shall fetch it! 🐕

CREATE PROCEDURE dbo.GetFilteredResults
    @Key INT, 
    @Value VARCHAR(100) // Time for action!
AS
BEGIN
    SELECT * FROM dbo.FilteredResults(@Key, @Value); // Get 'em, boy! 🚀
END;

Replace YourTable, ID, Data with your actual table and column names. Run dbo.GetFilteredResults with suitable parameters to mimic a parameterized view's functionality.

Using inline functions for maximizing efficiency

Inline table-valued functions (iTVFs) can provide better performance due to their nature that strongly resemblances parameterized views in terms of SQL Server's optimization for queries that involve these functions. Rather than multi-statement TVFs, iTVFs can lead to simpler query plans and faster execution times, making them far more desirable when dealing with parameterized-view-like behaviors.

Alternate approaches & concerns

Avoid multi-statement functions

As an alternate solution for simulating parameterized views, one could consider multi-statement TVFs. Unfortunately, they have significant performance drawbacks, as SQL Server does not make use of underlying table statistics for devising the execution plan. This typically leads to slower performance, especially with large datasets. iTVFs are far more efficient in this regard, so stick with them!

Stored procedures as substitutes

A stored procedure can do more than just passing parameters. If you need more complex logic or wish to perform additional operations like data manipulation, stored procedures can return temporary tables or table variables for further processing before sending the data to the client.

Unusual yet possible methods

An idea that's somewhat out of left field is to use the context_info trick, where a session-level variable can be set and accessed within a view, giving the semblance of a parameter. This, however, is less conventional, and while interesting, it's important to note that this could introduce concurrency issues. Proceed with caution! 🚧

Common Table Expressions (CTEs)

CTEs offers a way to create dynamic filtering, similar to parameterized views. CTEs support query recursion and are an excellent choice for scenarios where complex filtering is needed, adding a valuable level of flexibility to your query design.

Performance considerations

Remember that when implementing solutions in SQL Server 2008, being aware of server limitations and characteristics is crucial. For instance, scalar UDFs could drastically impact performance when applied over larger datasets. It's always best to choose solutions that harness the database engine's capabilities for optimal performance.

Deep-diving for performance

Review your queries with the help of execution plans to fine-tune performance. Remember that indexing the underlying tables appropriately would lead to improved efficiency.

Timestamped for success

While SQL Server 2008 has limitations, keep in mind that TVFs work differently across different versions of SQL Server. Performance improvements seen in newer versions might not apply in 2008. As best practice, ensure compatibility and test performance thoroughly in your target environment.

Building on existing blocks

Advanced techinques like dynamic SQL within stored procedures can lend extended functionality, similar to parameterized views, while staying on top of the execution plan and performance.

explain-codes / Sql / Create parameterized VIEW in SQL Server 2008

Linked

Can we pass parameters to a view in SQL?



Passing List<> to SQL Stored Procedure



Parameterize an SQL IN clause



When should I use stored procedures?



Reference alias (calculated in SELECT) in WHERE clause

