Skip to main content
Welcome to Module 2: Structured Data of the DP-900 Azure Data Fundamentals series. When you normalize data, you often end up with multiple related tables. By defining relationships, queries across tables—like fetching a sales order along with its customer and product details—become seamless. In this article, we’ll cover three essential relational database management system (RDBMS) features that boost performance, simplify development, and centralize business logic:
FeaturePurposeExample
IndexesAccelerate row retrievalCREATE INDEX idx_City ON Customers(City);
ViewsSimplify complex joins & enforce securityCREATE VIEW v_OrderSummary AS SELECT o.OrderID, c.Name, p.ProductName FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID JOIN Products p ON o.ProductID = p.ProductID;
Stored ProceduresEncapsulate business logic & reduce latencyEXEC ShipSalesOrder @OrderID = 10248;

​
1. Indexes

An index works like a book’s index, providing a fast lookup path into large tables. Instead of scanning every row, the database navigates the index structure to find matching records.

​
Clustered vs. Non-Clustered

  • Clustered Index
    Defines the physical order of rows in a table. Typically created on the primary key. Only one clustered index per table.
  • Non-Clustered Index
    Maintains a separate structure that points back to the data rows. You can define multiple non-clustered indexes on different columns.
The image explains database management system (DBMS) features related to indexes, highlighting their role in speeding up row retrieval, sorting table rows for faster access, and potentially slowing down updates. It includes a visual example of a table with customer data sorted by city.
Trade-offs
  • Pros:
    • Dramatically faster SELECT queries on indexed columns.
  • Cons:
    • INSERT, UPDATE, and DELETE operations incur extra overhead to maintain each index.
Adding too many indexes can degrade DML (Data Manipulation Language) performance. A good rule of thumb is to limit each table to around six non-clustered indexes.

​
2. Views

A view is a virtual table defined by a SQL query. It can join, filter, and project columns from one or more base tables—simplifying repeated query logic and enforcing column-level security. 
CREATE VIEW v_SalesOrderDetails AS
SELECT
  so.OrderID,
  so.OrderDate,
  c.CustomerName,
  p.ProductName,
  od.Quantity,
  od.UnitPrice
FROM SalesOrders so
JOIN OrderDetails od ON so.OrderID = od.OrderID
JOIN Customers c ON so.CustomerID = c.CustomerID
JOIN Products p ON od.ProductID = p.ProductID;
The image shows two tables illustrating database management system (DBMS) views, with columns for sales order details and product information.
Benefits
  • Simplifies complex joins for developers.
  • Restricts access to sensitive columns (e.g., hide CustomerID) by granting permissions on the view instead of the base tables.

​
3. Stored Procedures

A stored procedure is a precompiled collection of SQL statements stored on the database server. Executing a stored procedure requires only one network round-trip, improving performance and centralizing business logic. 
CREATE PROCEDURE ShipSalesOrder
  @OrderID INT
AS
BEGIN
  BEGIN TRANSACTION;
  
  UPDATE SalesOrders
    SET Status = 'Shipped', ShippedDate = GETDATE()
    WHERE OrderID = @OrderID;

  INSERT INTO Shipments (OrderID, ShipDate)
    VALUES (@OrderID, GETDATE());

  -- Additional validation or logging steps
  COMMIT TRANSACTION;
END;
The image illustrates the concept of stored procedures in a database management system, showing an application program interacting with a database server to execute commands for improved performance and centralized control.
Advantages
  • Performance: One call replaces multiple client-server interactions.
  • Consistency: Business rules run identically every time.
  • Maintainability: Updates apply immediately to all applications using the procedure.

Watch Video