Views and user-defined functions

Overview

Views and user-defined functions are SQL Server programmability features that package reusable query logic. A view presents a named query as a virtual table. A user-defined function packages reusable logic that can return a scalar value or a table. Both can improve clarity and reuse, but both can also hide complexity and create performance surprises when used carelessly.

This topic matters because views and functions appear in application queries, reporting layers, data-access APIs, security models, and legacy databases. They can simplify a schema, expose a stable interface, and reduce duplicated SQL. They can also stack layers of abstraction until queries become hard to understand, hard to tune, or accidentally expensive.

For interviews, views and functions test whether you understand the difference between logical abstraction and physical performance. Strong candidates can explain ordinary views, indexed views, updatable views, inline table-valued functions, multi-statement table-valued functions, scalar functions, schema binding, and when a function in a predicate can hurt index usage.

Core Concepts

Views

A view is a stored SELECT statement that appears to callers like a table.

CREATE OR ALTER VIEW sales.vwCustomerOrderSummary
AS
SELECT
    c.CustomerId,
    c.CustomerName,
    COUNT(o.OrderId) AS OrderCount,
    SUM(o.TotalAmount) AS TotalAmount
FROM sales.Customers AS c
LEFT JOIN sales.Orders AS o
    ON o.CustomerId = c.CustomerId
GROUP BY
    c.CustomerId,
    c.CustomerName;

Common uses include:

• Simplifying complex joins.
• Hiding columns from users.
• Providing a stable compatibility layer.
• Centralizing common reporting projections.
• Exposing a smaller interface over base tables.

Most views are virtual. SQL Server expands the view definition into the outer query and optimizes the combined query.

View Limitations

A view is not automatically a performance feature. It is usually an abstraction over a query.

Important limitations include:

• A view does not guarantee ordered results unless the outer query uses ORDER BY.
• Deeply nested views can hide expensive joins and filters.
• SELECT * inside a view can create fragile contracts.
• A view can become invalid or stale when underlying objects change.
• Updating through a view has restrictions.
• Indexed views have strict requirements and write-side costs.

Example:

SELECT *
FROM sales.vwCustomerOrderSummary
WHERE TotalAmount > 10000;

This may be readable, but the optimizer still needs to execute the underlying joins and aggregation unless an appropriate indexed view is used.

SCHEMABINDING

SCHEMABINDING binds a view or function to referenced objects. It prevents underlying schema changes that would break the module.

CREATE OR ALTER VIEW sales.vwActiveCustomer
WITH SCHEMABINDING
AS
SELECT
    CustomerId,
    CustomerName,
    Status
FROM sales.Customers
WHERE Status = 'Active';

Schema binding can improve safety because dependent columns and tables cannot be changed in incompatible ways without first changing the view or function. It is also required for indexed views.

WITH CHECK OPTION

WITH CHECK OPTION applies to updates through a view. It ensures modified rows still satisfy the view predicate after the change.

CREATE OR ALTER VIEW sales.vwActiveCustomer
AS
SELECT CustomerId, CustomerName, Status
FROM sales.Customers
WHERE Status = 'Active'
WITH CHECK OPTION;

If a caller updates a row through this view and sets Status = 'Inactive', the row would no longer be visible through the view, so the update is rejected.

Updatable Views

Some simple views can be updated. SQL Server must be able to map the modification unambiguously to one base table. Views with aggregates, grouping, distinct results, computed expressions, or multi-table ambiguity are usually not directly updatable.

INSTEAD OF triggers can make more complex views accept writes, but this adds hidden procedural behavior and should be used carefully.

Indexed Views

An indexed view materializes view results in a unique clustered index. This can help expensive aggregate or join-heavy workloads, but it adds maintenance cost to writes on the underlying tables.

CREATE VIEW sales.vwDailySales
WITH SCHEMABINDING
AS
SELECT
    OrderDate,
    COUNT_BIG(*) AS OrderCount,
    SUM(TotalAmount) AS TotalAmount
FROM sales.Orders
GROUP BY OrderDate;

CREATE UNIQUE CLUSTERED INDEX CX_vwDailySales
ON sales.vwDailySales (OrderDate);

Use indexed views when read benefits justify write overhead and when the strict requirements are acceptable.

User-Defined Functions

A user-defined function packages reusable logic and returns either a scalar value or a table.

Types include:

• Scalar functions.
• Inline table-valued functions.
• Multi-statement table-valued functions.
• CLR functions in some SQL Server environments.

Functions are useful for reusable calculations and reusable parameterized table expressions. However, they have restrictions and performance characteristics that must be understood.

Scalar Functions

A scalar function returns a single value.

CREATE OR ALTER FUNCTION dbo.CalculateDiscount
(
    @Subtotal decimal(12, 2),
    @CustomerTier varchar(20)
)
RETURNS decimal(12, 2)
AS
BEGIN
    RETURN
        CASE @CustomerTier
            WHEN 'Gold' THEN @Subtotal * 0.10
            WHEN 'Silver' THEN @Subtotal * 0.05
            ELSE 0
        END;
END;

Scalar functions can make code readable, but they can be expensive when called once per row in a large query. Modern SQL Server versions can inline some scalar UDFs, but not all functions are eligible.

Inline Table-Valued Functions

An inline table-valued function returns a table from a single RETURN SELECT expression. It is often optimized like a parameterized view.

CREATE OR ALTER FUNCTION sales.GetOrdersForCustomer
(
    @CustomerId int
)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM sales.Orders
    WHERE CustomerId = @CustomerId
);

Usage:

SELECT *
FROM sales.GetOrdersForCustomer(42)
WHERE TotalAmount > 100;

Inline table-valued functions are usually preferred over multi-statement table-valued functions for query performance because the optimizer can reason about the function body more directly.

Multi-Statement Table-Valued Functions

A multi-statement table-valued function fills and returns a table variable.

CREATE OR ALTER FUNCTION sales.GetRecentHighValueOrders
(
    @Days int
)
RETURNS @Result TABLE
(
    OrderId bigint,
    CustomerId int,
    TotalAmount decimal(12, 2)
)
AS
BEGIN
    INSERT @Result (OrderId, CustomerId, TotalAmount)
    SELECT OrderId, CustomerId, TotalAmount
    FROM sales.Orders
    WHERE OrderDate >= DATEADD(day, -@Days, SYSUTCDATETIME())
      AND TotalAmount >= 1000;

    RETURN;
END;

This style can express multi-step logic, but it can hide cardinality and indexing information from the optimizer. Use it only when the extra procedural flexibility is worth the performance trade-off.

Views Vs Inline Table-Valued Functions

Views and inline table-valued functions are similar because both expose reusable table-shaped logic.

Key difference:

• A view has no parameters.
• An inline table-valued function can accept parameters.

If you need a reusable filtered projection with parameters, an inline table-valued function is often cleaner than a view plus broad filtering outside the view.

Functions In Predicates

Wrapping a column in a function can make a predicate non-SARGable.

Poor:

WHERE dbo.NormalizeEmail(EmailAddress) = @Email;

Better options:

• Store normalized values in a computed persisted column.
• Normalize input before querying.
• Use a SARGable predicate against indexed data.
• Use a computed column index when appropriate.

A function call can be logically correct and still make a query slow.

Determinism And Side Effects

Functions are expected to return results based on inputs and allowed database context. They cannot perform arbitrary side effects like modifying tables in the way stored procedures can. This makes functions useful inside queries, but it also limits what they should contain.

For business workflows, use stored procedures or application code. For reusable expressions or table expressions, use functions.

Common Mistakes

Common mistakes include:

• Treating a view as a materialized performance cache.
• Nesting many views until the real query is unreadable.
• Using ORDER BY inside a view and assuming callers get ordered results.
• Using scalar functions row-by-row in large queries without checking performance.
• Using multi-statement table-valued functions where inline functions would work.
• Returning broad SELECT * view contracts.
• Using functions in predicates that prevent index seeks.
• Forgetting to refresh or schema-bind views when base tables change.

Best Practices

Best practices include:

• Use views to simplify and secure common projections.
• Keep views focused and avoid deep nesting.
• Use explicit column lists.
• Use SCHEMABINDING when dependency safety matters.
• Use indexed views only when read benefits justify write overhead.
• Prefer inline table-valued functions for parameterized reusable queries.
• Be cautious with scalar functions in large result sets.
• Test view and function usage inside the full calling query.
• Source-control definitions and review changes.