SQL Objects: Views, Functions & Stored Procedures

Why do we need SQL objects?

Simple explanation

Think of SQL objects like recipes in a kitchen.

A view is like a menu item description — it tells you what the dish includes without making you prepare it yourself. A function is like a recipe card — give it ingredients, it returns a result. A stored procedure is like a full meal prep guide — multiple steps, decisions, and actions in sequence.

These SQL objects let you encapsulate logic so users and reports can access complex data through simple names, rather than writing the same complex queries over and over.

Views

A view is a saved SQL query that acts like a virtual table. It does not store data — it runs the query each time it is accessed.

Creating a view

CREATE VIEW dbo.vw_monthly_revenue AS
SELECT
    d.year,
    d.month_name,
    s.region,
    SUM(f.total_amount) AS revenue,
    COUNT(DISTINCT f.sale_id) AS transaction_count
FROM dbo.fact_sales f
JOIN dbo.dim_date d ON f.date_key = d.date_key
JOIN dbo.dim_store s ON f.store_key = s.store_key
GROUP BY d.year, d.month_name, s.region;

Now anyone can query SELECT * FROM vw_monthly_revenue without knowing the underlying star schema joins.

When to use views

Use Case	Example
Simplify complex queries	Hide multi-table joins behind a simple name
Security abstraction	Show only certain columns/rows to specific users
Backward compatibility	Change the underlying table structure without breaking reports
Reusable logic	Same aggregation used by 10 reports

Scenario: James creates a reporting layer

James at Summit Consulting creates views for each client’s reporting needs. Client A sees vw_client_a_revenue which filters to their data only. Client B sees vw_client_b_costs which joins different tables.

Neither client sees the raw fact tables. If James restructures the underlying tables, the views absorb the change — reports continue working without modification.

Views in the lakehouse SQL analytics endpoint

The lakehouse SQL analytics endpoint auto-generates views for each Delta table. You can also create custom views on top of these auto-generated views. However:

Lakehouse SQL endpoint views are read-only — no DML
You cannot create stored procedures or functions in the lakehouse SQL endpoint
Custom views are useful for adding business logic on top of lakehouse data

Functions

Functions compute a value and can be used inside SELECT, WHERE, and other clauses.

Scalar function example

CREATE FUNCTION dbo.fn_CalculateMargin(
    @revenue DECIMAL(18,2),
    @cost DECIMAL(18,2)
)
RETURNS DECIMAL(18,4)
AS
BEGIN
    RETURN CASE
        WHEN @revenue = 0 THEN 0
        ELSE (@revenue - @cost) / @revenue
    END
END;

Usage:

SELECT
    product_name,
    revenue,
    cost,
    dbo.fn_CalculateMargin(revenue, cost) AS margin_pct
FROM dbo.product_summary;

Table-valued function example

CREATE FUNCTION dbo.fn_TopProducts(
    @region NVARCHAR(50),
    @top_n INT
)
RETURNS TABLE
AS
RETURN (
    SELECT TOP(@top_n)
        p.product_name,
        SUM(f.total_amount) AS total_revenue
    FROM dbo.fact_sales f
    JOIN dbo.dim_product p ON f.product_key = p.product_key
    JOIN dbo.dim_store s ON f.store_key = s.store_key
    WHERE s.region = @region
    GROUP BY p.product_name
    ORDER BY total_revenue DESC
);

Stored procedures

Stored procedures encapsulate multi-step logic — the most powerful SQL object.

Common patterns in Fabric warehouses

CREATE PROCEDURE dbo.sp_RefreshGoldLayer
    @refresh_date DATE
AS
BEGIN
    -- Step 1: Truncate and reload daily aggregate
    DELETE FROM dbo.agg_daily_store_sales
    WHERE date_key = CONVERT(INT, FORMAT(@refresh_date, 'yyyyMMdd'));

    -- Step 2: Rebuild from fact table
    INSERT INTO dbo.agg_daily_store_sales
    SELECT
        CONVERT(INT, FORMAT(@refresh_date, 'yyyyMMdd')) AS date_key,
        store_key,
        COUNT(*) AS transaction_count,
        SUM(quantity) AS total_units,
        SUM(total_amount) AS total_revenue
    FROM dbo.fact_sales
    WHERE date_key = CONVERT(INT, FORMAT(@refresh_date, 'yyyyMMdd'))
    GROUP BY store_key;
END;

Where SQL objects live

Warehouse = full SQL programmability. Lakehouse SQL endpoint = views only.
SQL Object	Fabric Warehouse	Lakehouse SQL Endpoint
Views (custom)	Full support — read and write through views	Read-only custom views on top of auto-generated views
Functions	Full support — scalar and table-valued	Not supported
Stored procedures	Full support — DML, control flow, parameters	Not supported
Auto-generated views	Not applicable	Automatically created for each Delta table

Question

What is a SQL view in Fabric?

Click or press Enter to reveal answer

Answer

A saved SQL query that acts as a virtual table. It does not store data — it runs the underlying query each time it is accessed. Use views to simplify complex joins, enforce security, and provide backward compatibility.

Click to flip back

Question

Can you create stored procedures in the lakehouse SQL analytics endpoint?

Click or press Enter to reveal answer

Answer

No. The lakehouse SQL analytics endpoint supports only views (custom and auto-generated). Stored procedures and functions require a Fabric Warehouse.

Click to flip back

Question

What is the difference between a scalar function and a table-valued function?

Click or press Enter to reveal answer

Answer

A scalar function returns a single value (use it in SELECT or WHERE). A table-valued function returns a table (use it in FROM clause). Both encapsulate reusable calculation logic.

Click to flip back

Knowledge Check

Raj at Atlas Capital needs to create a stored procedure that uses MERGE to update daily P&L calculations. Where should he create this stored procedure?

Knowledge Check

James at Summit Consulting has a lakehouse with Delta tables for Client A. He needs to create a view that joins three tables and filters to only Client A's data. Where should he create this view?

🎬 Video coming soon

Next up: Transforming Data: Reshape & Enrich — add columns, merge tables, and shape your data for analytics.