Black Friday50% off your first month on any monthly plan with EARLY50 See plans
All 35 lessons
  1. Start here: cloud, data, Fabric and Power BI basics

    1. 1. Start here: the exam, the plan and what you already know
    2. 2. Cloud in plain words: SaaS, PaaS, IaaS and where Fabric fits
    3. 3. Data basics: files, tables, formats and the lakehouse idea
    4. 4. A tour of Microsoft Fabric: OneLake, workspaces and items
    5. 5. Power BI in plain words: semantic models, reports and DAX
  2. Prepare data: get data in (exam area 2, 45-50%)

    1. 6. Connect to data and find it: connections, gateways, OneLake catalog and Real-Time hub
    2. 7. Choosing a data store: lakehouse, warehouse or eventhouse
    3. 8. Use data without copying: shortcuts, mirroring and OneLake integration
    4. 9. Ingest data: Dataflow Gen2, pipelines, Copy job and notebooks
    5. 10. Power Query in Dataflow Gen2: filter, data types, merge and new columns
    6. 11. Messy data: duplicates, missing values and nulls
  3. Prepare data: transform and query (exam area 2)

    1. 12. Views, functions and stored procedures in T-SQL
    2. 13. Build a star schema in a lakehouse or warehouse
    3. 14. Aggregate, join and enrich: SQL and PySpark side by side
    4. 15. Query with the Visual query editor and SQL
    5. 16. Query with KQL
    6. 17. Query with DAX: DAX query view and EVALUATE
  4. Design and build semantic models (exam area 3, 25-30%)

    1. 18. Choose a storage mode: Import, DirectQuery, Direct Lake and Dual
    2. 19. Relationships: star schemas, bridge tables and many-to-many
    3. 20. DAX foundations: measures, filter context, CALCULATE and variables
    4. 21. DAX functions the exam tests: iterators, table filters, windows and information functions
    5. 22. Calculation groups, dynamic format strings and field parameters
    6. 23. Composite models and large semantic model storage format
  5. Optimize models, then secure them (exam areas 3 and 1)

    1. 24. Direct Lake in depth: fallback, refresh and the two flavors
    2. 25. Incremental refresh for semantic models
    3. 26. Faster reports and DAX: finding and fixing slow queries
    4. 27. Who can see what: workspace roles and item permissions
    5. 28. Fine-grained security: rows, columns, objects and files
    6. 29. Governance: sensitivity labels and endorsement
  6. Lifecycle, then exam practice (exam area 1)

    1. 30. Version control: Git integration and Power BI projects (.pbip)
    2. 31. Deployment pipelines and impact analysis
    3. 32. XMLA endpoint and reusable assets: .pbit, .pbids and shared semantic models
    4. 33. Mock exam 1
    5. 34. Final review and exam day: every decision on one page
    6. 35. Mock exam 2
See the leaderboard

22 min

Views, functions and stored procedures in T-SQL

Create views, functions and stored procedures in a warehouse and know what the SQL analytics endpoint allows.

By the end of this lesson you can

  • Write a view and use it as a shared, secure query
  • Write an inline table-valued function and a scalar function with parameters
  • Write a stored procedure for a repeatable load and run it from a pipeline
  • Say what you can create on a Warehouse versus a SQL analytics endpoint

From earlier lessons · 1 of 2

Your team writes only T-SQL and must run INSERT, UPDATE and MERGE on tables. Which Fabric item fits?

Pick one answer.

From earlier lessons · 2 of 2

A Dataflow Gen2 query has exact repeat rows from the till. Which Power Query step removes them?

Pick one answer.

At Juniper Coffee, finance and three regional analysts each wrote their own revenue by store query. On Monday, North showed three different totals in three meetings. The nightly summary load is a 40-line script that someone pastes into the Warehouse by hand. You need one shared definition, and a load that runs itself.

[6]

The data in this lesson. Two tiny Juniper Coffee tables in a Warehouse. Every result below is traced from these rows.

dbo.Stores

StoreIDRegionStoreName.
1NorthHarbour St.
2SouthMill Lane.
3NorthStation Rd.

dbo.Sales

SaleIDStoreIDSaleDateQtyAmountProduct.
10112026-11-0229.00Latte.
10222026-11-0214.80Mocha.
10332026-11-0314.50Latte.
10412026-11-0337.50Scone.

Views: one shared query with a name

Exam depth: know it cold.

A view is a saved SELECT statement with a name. It stores no rows. Each time someone queries the view, the engine runs its SELECT on the current data.

Views give you two things.

  • Reuse: write the join and the business rules once. Every report and analyst then reads the same numbers.
  • A security layer: grant SELECT on the view, not on the tables under it. Users see only the columns and rows the view returns. This is object-level security with GRANT.

CREATE VIEW fails if the view already exists. CREATE OR ALTER VIEW creates it, or replaces its definition, in one statement. Altering a view keeps the permissions granted on it. Dropping and recreating it loses them.

[139][141][142][150][151][152]

sql
CREATE OR ALTER VIEW dbo.vw_StoreRevenue
AS
SELECT st.Region, st.StoreName, SUM(s.Amount) AS Revenue
FROM dbo.Sales AS s
JOIN dbo.Stores AS st ON s.StoreID = st.StoreID
GROUP BY st.Region, st.StoreName;
GO
GRANT SELECT ON dbo.vw_StoreRevenue TO [Finance Analysts];
Result of SELECT * FROM dbo.vw_StoreRevenue: North, Harbour St, 16.50 (9.00 + 7.50); South, Mill Lane, 4.80; North, Station Rd, 4.50. Finance can read the view but not dbo.Sales.

[141][150]

[141][142]

You are an analytics engineer at Juniper Coffee. Finance needs revenue by store from the Warehouse. They must not see the raw till rows in dbo.Sales, and the totals must always be current. You need the least privilege setup. What should you do?

Pick one answer.

[151]

Functions: inline table-valued and scalar

Exam depth: know it cold.

A user-defined function is a saved routine that takes parameters, values you pass in, and returns a result. In Fabric Warehouse and the SQL analytics endpoint, CREATE FUNCTION makes two kinds.

  • Inline table-valued function (inline TVF): its body is one SELECT, and it returns a table. You call it in FROM, so it works like a view that takes parameters.
  • Scalar function: returns one value, such as a number or a label. You call it in SELECT or WHERE, with its schema name: dbo.fn_SizeBand(Qty). Inline TVFs are generally available. Scalar functions are still listed as a preview feature.

Multi-statement table-valued functions, common in SQL Server, aren't among them.

Keep scalar functions simple. Fabric folds each one into the calling query, called inlining. By default, CREATE FUNCTION rejects a scalar function it can't inline. WITH INLINE = AUTO creates it anyway, but it still can't be used in a query over a table.

[139][143][144][155]

sql
CREATE OR ALTER FUNCTION dbo.fn_RegionSales (@Region varchar(10))
RETURNS TABLE
AS
RETURN (
SELECT s.SaleID, s.SaleDate, s.Amount
FROM dbo.Sales AS s
JOIN dbo.Stores AS st ON s.StoreID = st.StoreID
WHERE st.Region = @Region
);
GO
SELECT * FROM dbo.fn_RegionSales('North');
Result for 'North': three rows, sales 101 (9.00), 103 (4.50) and 104 (7.50). Sale 102 is in South, so the filter drops it.

[143]

sql
CREATE OR ALTER FUNCTION dbo.fn_SizeBand (@Qty int)
RETURNS varchar(10)
AS
BEGIN
RETURN CASE WHEN @Qty >= 3 THEN 'Large' ELSE 'Small' END;
END;
GO
SELECT SaleID, Qty, dbo.fn_SizeBand(Qty) AS Band
FROM dbo.Sales;
Result: 101 Small, 102 Small, 103 Small, 104 Large (Qty 3). One value per row, called with its schema name.

[143][144]

You are a BI developer at Juniper Coffee. Each regional report needs the sales rows for one region, with the region passed in at query time. The result must join to other tables in FROM. What should you create in the Warehouse?

Pick one answer.

[143]

Stored procedures: repeatable steps with parameters

Exam depth: know it cold.

A stored procedure is a saved batch of T-SQL statements with a name. It can take parameters, run several statements in order, and change data. In a Warehouse that includes INSERT, UPDATE, DELETE, MERGE and CREATE TABLE.

You run it with EXEC, short for EXECUTE. You can't use a procedure in FROM, because it isn't a table.

Procedures suit repeatable loads: the same steps every night with a different date. A parameter can have a default value, such as @LoadDate date = NULL. An OUTPUT parameter hands a value back to the caller.

CREATE OR ALTER PROCEDURE works in Fabric, as it does for views and functions.

[139][145][147]

sql
CREATE OR ALTER PROCEDURE dbo.usp_LoadDailySales
@LoadDate date
AS
BEGIN
DELETE FROM dbo.DailySales WHERE SaleDate = @LoadDate;
INSERT INTO dbo.DailySales (SaleDate, StoreID, Revenue)
SELECT SaleDate, StoreID, SUM(Amount)
FROM dbo.Sales
WHERE SaleDate = @LoadDate
GROUP BY SaleDate, StoreID;
END;
EXEC dbo.usp_LoadDailySales @LoadDate = '2026-11-03'; leaves two rows for that day: store 1 with 7.50 and store 3 with 4.50. The DELETE first makes a rerun safe, with no doubled rows.

[145]

Run it on a schedule from a pipeline. A Pipeline (older name: Data pipeline) is Fabric's item for ordering and scheduling steps. Its Stored procedure activity runs a procedure in a Fabric Warehouse or another SQL database.

  1. Add the Stored procedure activity to the pipeline canvas.
  2. On the Settings tab, choose the Warehouse connection and the procedure name.
  3. Expand Stored procedure parameters and select Import. Give each parameter a value: typed text, or an expression such as yesterday's date.
  4. Save, then select Run, or Schedule the pipeline.

The pipeline adds what a pasted script lacks: a schedule, run history and retries.

[148][149]

Choose: view, function or procedure

Exam depth: know it cold.

ViewInline TVFScalar functionStored procedure.
Takes parametersNoYesYesYes.
ReturnsA tableA tableOne valueResult sets, or nothing.
You call it inFROMFROMSELECT, WHEREEXEC.
Changes dataNoNoNoYes, in a Warehouse.
Typical useShared logic, securityRows for a passed valueA reusable calculationA repeatable load.

Decision rule: the same rows for everyone, a view. The same rows filtered by a value you pass in, an inline TVF. One value per row, a scalar function. Steps that change data, a stored procedure.

[139][143][145]

Match

Match each Juniper Coffee need to the object that fits

Pick an item on the left, then its match on the right.

[143]

A view is the recipe card. A function is the recipe with a dial. A procedure actually makes the coffee. Picture: Vintage instruction-manual drawing, 1950s line art with numbered callouts: a cheerful cartoon coffee robot has three labeled drawers.

What works where: Warehouse or SQL analytics endpoint

Exam depth: know it cold.

Every Lakehouse gets a SQL analytics endpoint: a read-only T-SQL view of its Delta tables. Spark, dataflows and pipelines write the tables; the endpoint can't.

WarehouseSQL analytics endpoint.
Create, alter or drop tables, incl. CTASYesNo.
INSERT, UPDATE, DELETE, MERGE on tablesYesNo.
ViewsYesYes.
Inline TVFs and scalar functionsYesYes.
Stored proceduresYesYes, but they can't change table data.
Permissions with GRANTYesYes.

Decision rule: anything that writes rows or makes tables needs a Warehouse. Views, functions and read-only procedures work on both.

[139][140][143]

You are an analytics engineer at Juniper Coffee. Loyalty data sits in a Lakehouse. An analyst connects to its SQL analytics endpoint and must add reusable T-SQL logic there, with no new copies of data. Which object can she create on the endpoint?

Pick one answer.

[139]

Cross-database queries in one workspace

Exam depth: recognize it.

A cross-database query reads tables from another Warehouse, SQL analytics endpoint or mirrored database in a single query. You name them with a three-part name: item.schema.table. Nothing gets copied.

Example from the Warehouse: SELECT * FROM JuniperLakehouse.dbo.loyalty_scans AS ls JOIN dbo.Stores AS st ON ls.store_id = st.StoreID.

Two rules. The other item must be in the same workspace, added to Explorer with + Warehouses. For another workspace, create a shortcut in a Lakehouse first, then query through it.

[153][154]

[139][146]

Try it in Fabric

On a free trial, in a Warehouse that holds a small table or two:

  1. On the Home ribbon, select New SQL query.
  2. Paste a CREATE OR ALTER PROCEDURE statement, such as the load above, and select Run.
  3. In Explorer, expand your schema. The procedure appears in the Stored Procedures folder; views and functions have their own folders.
  4. Open a new query, type EXEC with the procedure name and its parameter, and select Run.
  5. If a new table doesn't show, select ... next to Tables, then Refresh.

[147]

The Warehouse Explorer pane. Under the dbo schema are folders for Tables, Views, Functions and Stored Procedures; a procedure named populate_aggregate_sale_by_city is highlighted.
Microsoft Learn, CC BY 4.0

[147]

Exam-style practice

You are an analytics engineer at Juniper Coffee. A teammate runs SELECT * FROM dbo.usp_LoadDailySales('2026-11-03'); in the Warehouse and gets an error. They need the procedure's load to run for that date. What should they do?

Pick one answer.

[147]

You are a BI developer at Juniper Coffee. Finance reads dbo.vw_StoreRevenue through a GRANT on the view. You must add a Qty column to it. Last month a teammate ran DROP VIEW and then CREATE VIEW, and finance lost access. How should you change the view?

Pick one answer.

[142]

You are an analytics engineer at Juniper Coffee. The nightly load already lives in dbo.usp_LoadDailySales, which takes @LoadDate. It must run at 2 a.m. for yesterday's date, with run history and retries, and the logic must not be rewritten. What should you use?

Pick one answer.

[148]

FlashcardsIndex cardsFlashcards ⚡

Card 1 of 13

[139][142][143][145][148][153]

Worth keeping

  1. View: shared logic and a security layer; no parameters; always current.
  2. Inline TVF: rows for a value you pass in, used in FROM. Scalar function: one value per row.
  3. Stored procedure: steps that change data, run with EXEC or a pipeline's Stored procedure activity.
  4. Use CREATE OR ALTER so grants survive a change.
  5. SQL analytics endpoint: views, functions and read-only procedures; no table writes.

One revenue definition now feeds every report. But the tables under it are still raw till rows, with store names typed by hand. Next lesson you reshape them into facts and dimensions: a star schema.

Leaderboard

Loading…

50 XP for each lesson you finish, 10 for each right answer. Names show only for learners who turn on Show me on leaderboards.

Sources for this lesson (18)

Each fact in the lesson is marked with the number of the page it comes from.

  1. [6]Microsoft Learn: Study guide for Exam DP-600: Implementing Analytics Solutions Using Microsoft Fabric
  2. [139]Microsoft Learn: T-SQL surface area in Fabric Data Warehouse
  3. [140]Microsoft Learn: What is Fabric Data Warehouse?
  4. [141]Microsoft Learn: CREATE VIEW (Transact-SQL)
  5. [142]Microsoft Learn: ALTER VIEW (Transact-SQL)
  6. [143]Microsoft Learn: CREATE FUNCTION (Fabric Data Warehouse)
  7. [144]Microsoft Learn: Create scalar user-defined functions
  8. [145]Microsoft Learn: CREATE PROCEDURE (Transact-SQL)
  9. [146]Microsoft Learn: Stored procedures (Database Engine)
  10. [147]Microsoft Learn: Tutorial: Transform data with a stored procedure in a Warehouse
  11. [148]Microsoft Learn: Use the stored procedure activity
  12. [149]Microsoft Learn: Incrementally load data from Data Warehouse to Lakehouse
  13. [150]Microsoft Learn: SQL granular permissions in Microsoft Fabric
  14. [151]Microsoft Learn: Secure your Fabric Data Warehouse
  15. [152]Microsoft Learn: Column-level security in Fabric Data Warehouse
  16. [153]Microsoft Learn: Query the warehouse or SQL analytics endpoint
  17. [154]Microsoft Learn: Ingest from warehouse tables and mirrored databases with T-SQL
  18. [155]Microsoft Learn: What's new in Microsoft Fabric?