Stored procedure vs view in SQL Server: which one to use

A view is a saved SELECT that behaves like a table: you query it, join it and filter it. A stored procedure is a saved program: you execute it with parameters, and it can do anything from returning rows to changing several tables in a transaction.

They overlap only in one case, “give me some rows”, and that’s where people wonder which to pick. The examples run against the SpDevDemo database.

The same rows, two ways

CREATE OR ALTER VIEW dbo.OpenOrders
AS
SELECT o.OrderId, o.OrderNumber, o.OrderDate, o.Total, c.CustomerId, c.Name AS CustomerName, c.Country
FROM dbo.Orders AS o
JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
WHERE o.Status = 'Open';
GO

CREATE OR ALTER PROCEDURE dbo.GetOpenOrdersByCountry
    @Country CHAR(2)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT o.OrderId, o.OrderNumber, o.OrderDate, o.Total, c.CustomerId, c.Name AS CustomerName
    FROM dbo.Orders AS o
    JOIN dbo.Customers AS c ON c.CustomerId = o.CustomerId
    WHERE o.Status = 'Open' AND c.Country = @Country;
END

The view is used like a table, and the caller adds whatever it needs:

SELECT Country, COUNT(*) AS OpenOrders, SUM(Total) AS Value
FROM dbo.OpenOrders
GROUP BY Country
ORDER BY Value DESC;

The procedure is executed, and returns exactly what it was written to return:

EXEC dbo.GetOpenOrdersByCountry @Country = 'PK';

How they differ

	View	Stored procedure
Used as	A table, in FROM and JOIN	A command, with EXEC
Parameters	None	Yes
Logic	One SELECT	Any T-SQL: variables, IF, loops, several statements
Changes data	Simple views can be updated through	Yes, anything
Caller can filter, join, aggregate	Yes	No, unless it captures the rows first
Result shape	Fixed columns	One or more result sets
Plan	Expanded into the caller’s query	Compiled and cached per statement

Performance: a view is just its query

A (non-indexed) view stores no data and has no plan of its own. When you query it, SQL Server replaces the view’s name with its definition and optimizes the combined query. Filtering a view on Country = 'PK' is exactly as fast as writing that filter into the original query, because that’s what happens.

The trap is views stacked on views. Each level looks harmless, but the optimizer receives one enormous query, joins tables the outer query never needed, and runs out of time looking for a good plan. If you find a view selecting from three other views, flatten it.

A procedure’s statements are compiled when first run, and the plans are cached and reused, with the parameter sniffing that comes with that.

Indexed views: when a view does store data

Add a unique clustered index to a view created WITH SCHEMABINDING and SQL Server stores its result, keeping it up to date on every change to the base tables:

CREATE OR ALTER VIEW dbo.CustomerTotals
WITH SCHEMABINDING
AS
SELECT CustomerId, COUNT_BIG(*) AS Orders, SUM(Total) AS Spent
FROM dbo.Orders
GROUP BY CustomerId;
GO

CREATE UNIQUE CLUSTERED INDEX IX_CustomerTotals ON dbo.CustomerTotals (CustomerId);
GO

SET STATISTICS IO ON;
SELECT Orders, Spent FROM dbo.CustomerTotals WITH (NOEXPAND) WHERE CustomerId = 1;
SET STATISTICS IO OFF;

Table 'CustomerTotals'. Scan count 0, logical reads 2

Customer 1’s 20,000 orders, summed in 2 reads. The price is paid on writes: every insert, update and delete on dbo.Orders now also maintains the view. Indexed views have strict rules (COUNT_BIG(*) with GROUP BY, no outer joins, schema-qualified names, specific SET options). Outside Enterprise edition, query the view with WITH (NOEXPAND) so its index is used.

Security

Both are used to give access to data without giving access to the tables. Grant SELECT on a view, or EXECUTE on a procedure, and the user needs no permission on the tables underneath, as long as the view or procedure and the tables have the same owner (ownership chaining).

The difference is in what the user can do with it. A view lets them write any query over the columns you exposed. A procedure lets them run exactly one operation, with the parameters you allow.

Changing data through them

You can INSERT, UPDATE or DELETE through a simple view on one table, and SQL Server applies it to the table. Add WITH CHECK OPTION so changes can’t make a row disappear from the view. Anything more complex needs an INSTEAD OF trigger, and at that point a procedure is clearer.

Which one to use

• Reusable shape of the data, which callers will filter, join or aggregate: a view.
• A fixed operation with parameters, or anything that changes data: a stored procedure.
• A view that needs parameters: an inline table-valued function. It’s a view with parameters, and it’s just as cheap.
• An expensive aggregate read far more often than the data changes: an indexed view.

Many systems use both: views to define the shapes of the data, and procedures for the operations, reading from those views.