SQL Server Stored Procedures vs Functions vs Views (2023)

Watch this week's video on YouTube

SQL Server has several ways to store queries for later executions.

This makes developers happy because it allows them to follow DRY principles: D on't R epeat Y ourself. The more code you have, the more difficult it is to maintain. Centralizing frequently used code into stored procedures, functions, etc... is attractive.

While following the DRY pattern is beneficial in many programming languages, it can often cause poor performance in SQL Server.

Today's post will try to explain all of the different code organization features available in SQL Server and when to best use them ( thank you to dovh49 on YouTube for recommending this week's topic and reminding me how confusing all of these different features can be when first learning to use them ).

Scalar Functions

CREATE OR ALTER FUNCTION dbo.GetUserDisplayName( @UserId int)RETURNS nvarchar(40)ASBEGIN DECLARE @DisplayName nvarchar(40); SELECT @DisplayName = DisplayName FROM dbo.Users WHERE Id = @UserId RETURN @DisplayNameEND
(Video) SQL Stored Procedures, Functions, and Views
SELECT TOP 10000 Title, dbo.GetUserDisplayName(OwnerUserId) FROM dbo.Posts

Scalar functions run statements that return a single value.

You'll often read about SQL functions being evil, and scalar functions are a big reason for this reputation. If your scalar function executes a query within it to return a single value, that means every row that calls that function runs this query . That's not good if you have to run a query once for every row in a million row table.

SQL Server 2019 can inline a lot of these , providing better performance in most cases. However, you can already do this yourself today by taking your scalar function and including it in your calling query as a subquery. The only downside is that you'll be repeating that same logic in every calling query that needs it.

Additionally, using a scalar function on the column side of a predicate will prevent SQL Server from being able to seek to data in any of its indexes; talk about performance killing .

For scalar functions that don't execute a query, you can always use WITH SCHEMABINDING to gain a performance boost.

Inline Table Valued Functions

CREATE OR ALTER FUNCTION dbo.SplitTags( @PostId int)RETURNS TABLE ASRETURN ( SELECT REPLACE(t.value,'>','') AS Tags FROM dbo.Posts p CROSS APPLY STRING_SPLIT(p.Tags,'<') t WHERE Id = @PostId AND t.value <> '')GO
(Video) Choosing Between Views, Functions, and Stored Procedures in SQL With Examples
SELECT * FROM dbo.SplitTags(4)

Inline table-valued functions allow a function to return a table result set instead of just a single value. They essentially are a way for you to reuse a derived table query (you know, when you nest a child query in your main query's FROM or WHERE clause).

These are usually considered "good" SQL Server functions - their performance is decent because SQL Server can get relatively accurate estimates on the data that they will return, as long as the statistics on that underlying data are accurate. Generally this allows for efficient execution plans to be created. As a bonus, they allow parameters so if you find yourself reusing a subquery over and over again, an inline table-valued function (with or without a parameter) is actually a nice feature.

Multi-Statement Table-Valued Functions

CREATE OR ALTER FUNCTION dbo.GetQuestionWithAnswers( @PostId int)RETURNS @results TABLE ( PostId bigint, Body nvarchar(max), CreationDate datetime)ASBEGIN -- Returns the original question along with all of its answers in one result set -- Would be better to do this with something like a union all or a secondary join.  -- But this is an MSTVF demo, so I'm doing it with multiple statements. -- Statement 1 INSERT INTO @results (PostId,Body,CreationDate) SELECT Id,Body,CreationDate FROM dbo.Posts WHERE Id = @PostId; -- Statement 2 INSERT INTO @results (PostId,Body,CreationDate) SELECT Id,Body,CreationDate FROM dbo.Posts WHERE ParentId = @PostId; RETURNEND
SELECT * FROM dbo.GetQuestionWithAnswers(4)

Multi-statement table-valued functions at first glance look and feel just like their inline table-value function cousins: they both accept parameter inputs and return results back into a query. The major difference is that they allow multiple statements to be executed before the results are returned in a table variable:

This is a great idea in theory - who wouldn't want to encapsulate multiple operational steps into a single function to make their querying logical easier?

(Video) Choosing Between Views, Functions, and Stored Procedures in SQL

However, the major downside is that prior to SQL Server 2017, SQL Server knows nothing about what's happening inside of a mutli-statement table-valued function in the calling query. This means all of your estimates for MSTVFs will be 100 rows ( 1 if you are on a version prior to 2014, slightly more accurate if you are on versions 2017 and above ). This means that execution plans generated for queries that call MSTVFs will often be...less than ideal. Because of this, MSTVFs help add to the "evil" reputation of SQL functions.

Stored Procedures

CREATE OR ALTER PROCEDURE dbo.InsertQuestionsAndAnswers @PostId intASBEGIN SET NOCOUNT ON; INSERT INTO dbo.Questions (Id) SELECT Id FROM dbo.Posts WHERE Id = @PostId; INSERT INTO dbo.Answers (Id, PostId) SELECT Id, ParentId FROM dbo.Posts WHERE ParentId = @PostId;END
EXEC dbo.InsertQuestionsAndAnswers @PostId = 4

Stored procedures encapsulate SQL query statements for easy execution. They return result sets, but those result sets can't be easily used within another query.

This works great when you want to define single or multi-step processes in a single object for easier calling later.

Stored procedures also have the added benefit of being able to have more flexible security rules placed on them, allowing users to access data in specific ways where they don't necessarily have access to the underlying sources.

Views

CREATE OR ALTER VIEW dbo.QuestionsWithUsersWITH SCHEMABINDINGASSELECT p.Id AS PostId, u.Id AS UserId, u.DisplayNameFROM dbo.Posts p INNER JOIN dbo.Users u ON p.OwnerUserId = u.IdWHERE p.PostTypeId = 1;GOCREATE UNIQUE CLUSTERED INDEX CL_PostId ON dbo.QuestionsWithUsers (PostId);
(Video) Day35 Difference Between Stored Procedure, Functions and Views in SQL Server
SELECT * FROM dbo.QuestionsAndAnswersView;

Views are similar to inline table valued function - they allow you centralize a query in an object that can be easily called from other queries. The results of the view can be used as part of that calling query, however parameters can't be passed in to the view.

Views also have some of the security benefits of a stored procedure; they can be granted access to a view with a limited subset of data from an underlying table that those same users don't have access to.

Views also have some performance advantages since they can have indexes added to them, essentially materializing the result set in advance of the view being called (creating faster performance). If considering between an inlined table function and a view, if you don't need to parameterize the input, a view is usually the better option.

Natively Compiled Stored Procedures and Scalar Functions

CREATE TABLE dbo.QuestionsStaging (Id int PRIMARY KEY NONCLUSTERED) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );CREATE TABLE dbo.AnswersStaging (Id int PRIMARY KEY NONCLUSTERED, PostId int) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );GOCREATE PROCEDURE dbo.InsertQuestionsAndAnswersCompiled @PostId intWITH NATIVE_COMPILATION, SCHEMABINDINGAS BEGIN ATOMIC WITH( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english') INSERT INTO dbo.Questions (Id) SELECT Id FROM dbo.Posts WHERE Id = @PostId; INSERT INTO dbo.Answers (Id, PostId) SELECT Id, ParentId FROM dbo.Posts WHERE ParentId = @PostId;END

These are same as the stored procedures and scalar functions mentioned above, except they are pre-compiled for use with in-memory tables in SQL Server.

This means instead of SQL Server interpreting the SQL query every time a procedure or scalar function has to run, it created the compiled version ahead of time reducing the startup overhead of executing one of these objects . This is a great performance benefit, however they have several limitations . If you are able to use them, you should, just be aware of what they can and can't do.

(Video) stored procedures vs view

Conclusion

While writing this post I thought about when I was first learning all of these objects for storing SQL queries. Knowing the differences between all of the options available (or what those options even are!) can be confusing. I hope this post helps ease some of this confusion and helps you choose the right objects for storing your queries.

FAQs

What is difference between stored procedure and function and view? ›

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table. A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Should I use a view or a stored procedure? ›

Views should be used to store commonly-used JOIN queries and specific columns to build virtual tables of an exact set of data we want to see. Stored procedures hold the more complex logic, such as INSERT, DELETE, and UPDATE statements to automate large SQL workflows.

What is the difference between view and function in SQL Server? ›

A view returns a specific pre-defined statement as exactly one result set. A function returns a single values or a single result set. This however can differ from different types of database.

Are views faster than stored procedures? ›

A view is essentially a saved SQL statement. Therefore, I would say that in general, a stored procedure will be likely to be faster than a view IF the SQL statement for each is the same, and IF the SQL statement can benefit from optimizations. Otherwise, in general, they would be similar in performance.

Why use a function instead of a stored procedure? ›

as to the benefits of a udf vs an sps: Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it. Procedures can't be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Why use a stored procedure over a view? ›

A View represents a virtual table. You can join multiple tables in a view and use the View to present the data as if the data were coming from a single table. A Stored Procedure is precompiled database query that improves the security, efficiency and usability of database client/server applications.

What are the disadvantages of views in SQL? ›

Although there are many advantages to views, the main disadvantage to using views rather than real tables is performance degradation. Because views only create the appearance of a table, not a real table, the query processor must translate queries against the view into queries against the underlying source tables.

What is the disadvantage of stored procedure? ›

Testing of a logic which is encapsulated inside a stored procedure is very difficult. Any data errors in handling stored procedures are not generated until runtime. Depending on the database technology, debugging stored procedures will either be very difficult or not possible at all.

Do views improve query performance? ›

Views make queries faster to write, but they don't improve the underlying query performance. However, we can add a unique, clustered index to a view, creating an indexed view, and realize potential and sometimes significant performance benefits, especially when performing complex aggregations and other calculations.

Can we do DML on views? ›

DML operations could be performed through a simple view. DML operations could not always be performed through a complex view. 5. INSERT, DELETE and UPDATE are directly possible on a simple view.

Why are views better than tables? ›

Views enable us to hide some of the columns from the table. It simplifies complex queries because it can draw data from multiple tables and present it as a single table. It helps in data security that shows only authorized information to the users.

Is view better than query? ›

there is no difference. A view is just a stored query which can be referred to in sql queries as though they are tables. Note that this does not apply to materialized views. A view is only a query stored in the data dictionary: it is not going to make your query run faster or slower.

What are three advantages of using views? ›

Views can provide advantages over tables:
  • Views can represent a subset of the data contained in a table. ...
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) ...
  • Views can hide the complexity of data.

Do views take up memory? ›

The view is a query stored in the data dictionary, on which the user can query just like they do on tables. It does not use the physical memory, only the query is stored in the data dictionary.

Why are SQL views so slow? ›

When SQL Server processes a SELECT from a view, it evaluates the code in the view BEFORE it deals with the WHERE clause or any join in the outer query. With more tables joined, it will be slow compared to a SELECT from base tables with the same results.

Which is faster procedure or function? ›

As you can see, the scalar functions are slower than stored procedures. In average, the execution time of the scalar function was 57 seconds and the stored procedure 36 seconds.

Which is better procedure or function? ›

In a function, it is mandatory to use the RETURNS and RETURN arguments, whereas in a stored procedure is not necessary. In few words, a stored procedure is more flexible to write any code that you want, while functions have a rigid structure and functionality.

When should you use a function over a procedure? ›

Functions can have only input parameters for it whereas Procedures can have input/output parameters . Function takes one input parameter it is mandatory but Stored Procedure may take o to n input parameters.. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Can you execute stored procedure in view? ›

There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views. Step 2: Create Stored Procedure that will return all results.

Can a function call a stored procedure? ›

You can call a stored procedure inside a user-defined function. Consider this example: SQL> create table test_tab (tab_id number); Table created.

Can we write stored procedure in view? ›

You can't create a view from a stored procedure.

Is CTE better than view? ›

For queries that are referenced occasionally (or just once), it's usually better to use a CTE. If you need the query again, you can just copy the CTE and modify it if necessary. Frequently used queries. If you tend to reference the same query often, creating a corresponding view is a good idea.

What are the four types of views? ›

There are total four types of views, based on the way in which the view is implemented and the methods that are permitted for accessing the view data. They are - Database Views, Projection Views, Maintenance Views, and Helps Views,.

What Cannot be done on views? ›

What cannot be done on a view? Explanation: In MySQL, 'Views' act as virtual tables. It is not possible to create indexes on a view. However, they can be used for the views that are processed using the merge algorithm.

Is stored procedures outdated? ›

Stored procedures have been falling out of favour with some organizations for several years now. The preferred approach of these businesses for accessing their database(s) is to employ an Object-relational Mapper (ORM) such as NHibernate or Entity Framework.

Why stored procedure is faster than query? ›

Because of this, it's probably more likely that your stored procedure plans are being ran from cached plans while your individually submitted query texts may not be utilizing the cache. Because of this, the stored procedure may in fact be executing faster because it was able to reuse a cached plan.

What is the difference between a trigger and stored procedure? ›

Stored procedures can be invoked explicitly by the user. It's like a java program , it can take some input as a parameter then can do some processing and can return values. On the other hand, trigger is a stored procedure that runs automatically when various events happen (eg update, insert, delete).

Can we do indexing on views? ›

Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored. The query optimizer may use indexed views to speed up the query execution.

Do views slow down database? ›

Any filtering should be done within the view because any filtering against the view itself is applied after the query in the view has completed execution. Views are typically useful for speeding up the development process but in the long run can completely kill database performance.

Is querying a view slower than table? ›

As long as the view has been run before there should be no difference. In fact, the view may be slightly faster because it can have a cached query plan.

Can we write a trigger for view? ›

If you create a trigger on a view, it won't fire on inserts to the base table. If you want something to happen when you insert data into a base table, create a trigger on the base table. You can create an INSTEAD OF trigger on a view, and have that trigger insert data into a base table.

Can we DELETE record from view in SQL? ›

SQL VIEW can be created by a SQL query by joining one or more table. If you want to delete a SQL view, It is done by SQL DROP command you should use the following syntax: SQL DROP VIEW syntax: DROP VIEW view_name.

Can we have triggers on views? ›

Triggers can be defined only on tables, not on views. However, triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view.

What are the two types of views? ›

The two main types of views (or “projections”) used in drawings are: pictorial. orthographic.

Can a table have multiple views? ›

You can define multiple views of a table, and add a chart, if required.

What are the 2 views of a table? ›

There are two types of database views: dynamic views and static views. Dynamic views can contain data from one or two tables and automatically include all of the columns from the specified table or tables. Dynamic views are automatically updated when related objects or extended objects are created or changed.

Do SQL views cache data? ›

A view is nothing more than a stored query definition, there is no caching or storing of data for a view.

What are the advantages of views? ›

Design Flexibility: By using a view instead of a query in an application, it is easier to make changes to the underlying table structure. Improved Security: By using a view to return data from tables instead of a SELECT, you can hide the WHERE clause or other columns to which you do not want the user to have access.

Are SQL views inefficient? ›

Are SQL views efficient compared to this table insert and update method? Depends on the complexity of the view. If it's view upon view upon view it's probably inefficient. If it's a single view that's not particuarly complex, there's probably little to no performance impact.

What is the limitation of view in SQL Server? ›

You cannot pass parameters to SQL Server views. Cannot use an Order By clause with views without specifying FOR XML or TOP. Views cannot be created on Temporary Tables. You cannot associate rules and defaults with views.

How many types of views are there? ›

There are two types under User Defined views, Simple View and Complex View.

What is the main purpose of views in databases? ›

Views are generally used to focus, simplify, and customize the perception each user has of the database. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view.

How long does SQL view last? ›

They'll stay in memory as long as anything else on your system does. It's completely dependent on the load on the system, the queries being run, and the amount of memory that you have. There is no hard and fast value. Let's say a system has 64gb of memory.

Are views stored in TempDB? ›

Not in TempDB. A view is just a saved select statement. It's stored in the system tables of the database it's in. When the view is queried, as part of the parsing of the query using the view, the name of the view is replaced by the definition and the resultant query optimised and executed.

Can views be updated? ›

The SQL UPDATE VIEW command can be used to modify the data of a view. All views are not updatable. So, UPDATE command is not applicable to all views. An updatable view is one which allows performing a UPDATE command on itself without affecting any other table.

Do sql views automatically update? ›

A view is basically a stored query, it holds no data so no, it won't get updated when the tables it's built on are. However as soon as you reference the view the query it's based on will run, so you will see the changes made to the base tables. Save this answer.

Do indexes speed up views? ›

Indexes are great because they speed up the performance and with an index on a view it should really speed up the performance because the index is stored in the database. Indexing both views and tables is one of the most efficient ways to improve the performance of queries and applications using them.

Do views need to be refreshed sql? ›

Views need to be refreshed if the underlying tables change at all. That can change the datatypes of the view's columns or rearrange its indexes. Therefore, it needs to know. Otherwise, you'd run a query against it, and it'd blow up pretty quickly.

What is the difference between procedure and function? ›

A function would return the returning value/control to the code or calling function. The procedures perform certain tasks in a particular order on the basis of the given inputs. A procedure, on the other hand, would return the control, but would not return any value to the calling function or the code.

Can we use stored procedure in view? ›

There could be scenarios where you want to use a Stored Procedure in view and Join the result set returned by a Stored procedure with some other tables/views. Step 2: Create Stored Procedure that will return all results.

What is difference between function and procedure in SQL? ›

In SQL: A Procedure allows SELECT as well as DML ( INSERT , UPDATE , DELETE ) statements in it, whereas Function allows only SELECT statement in it. Procedures can not be utilized in a SELECT statement, whereas Functions can be embedded in a SELECT statement.

What is view and function in SQL? ›

A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL statements and functions to a view and present the data as if the data were coming from one single table. A view is created with the CREATE VIEW statement.

Are functions better than procedures? ›

A procedure performs a task, whereas a function produces information. Functions differ from procedures in that functions return values, unlike procedures which do not. However, parameters can be passed to both procedures and functions.

Can I call stored procedure in function? ›

According to Microsoft standard, stored procedures cannot be executed inside the function, but technically it is possible with some tweaks.

Can you call a SP in view? ›

You can do the subqueries-to-temp table stuff in a stored procedure, but you can't call a stored procedure from a view.

Are DML allowed on view? ›

DML operations could be performed through a simple view. DML operations could not always be performed through a complex view.

Are temp tables allowed in views? ›

Creating views on temporary tables is not allowed.

Which is best function or procedure in SQL? ›

The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.

Why is stored procedure faster than query? ›

Stored procedures are precompiled and optimised, which means that the query engine can execute them more rapidly. By contrast, queries in code must be parsed, compiled, and optimised at runtime. This all costs time.

What is difference between CTE and view? ›

The key thing to remember about SQL views is that, in contrast to a CTE, a view is a physical object in a database and is stored on a disk. However, views store the query only, not the data returned by the query. The data is computed each time you reference the view in your query.

What are the advantages of SQL views? ›

Views can provide advantages over tables:
  • Views can represent a subset of the data contained in a table. ...
  • Views can join and simplify multiple tables into a single virtual table.
  • Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) ...
  • Views can hide the complexity of data.

What are the different types of views? ›

There are three types of pictorial views: perspective. isometric. oblique.

Videos

1. Top 10 differences between Stored Procedures and Functions in SQL Server(English)
(Vipul Sachan)
2. SQL Server Tutorial - Comparing triggers, functions, and procedures
(LinkedIn Learning)
3. Day 15 of 60 - Stored Procedure VS View (Imp)
(Sql Is Easy)
4. SQL: Views, Functions & Stored Procedures (RDBMS) by Lekan Baruwa
(Andela)
5. Stored Procedure vs Adhoc Query Performance
(Bert Wagner)
6. Advanced SQL Tutorial | Stored Procedures + Use Cases
(Alex The Analyst)
Top Articles
Latest Posts
Article information

Author: Msgr. Benton Quitzon

Last Updated: 01/02/2023

Views: 5440

Rating: 4.2 / 5 (63 voted)

Reviews: 94% of readers found this page helpful

Author information

Name: Msgr. Benton Quitzon

Birthday: 2001-08-13

Address: 96487 Kris Cliff, Teresiafurt, WI 95201

Phone: +9418513585781

Job: Senior Designer

Hobby: Calligraphy, Rowing, Vacation, Geocaching, Web surfing, Electronics, Electronics

Introduction: My name is Msgr. Benton Quitzon, I am a comfortable, charming, thankful, happy, adventurous, handsome, precious person who loves writing and wants to share my knowledge and understanding with you.