Other DBMS Features

​

1. Indexes

​

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.

• Pros:
  • Dramatically faster SELECT queries on indexed columns.
• Cons:
  • INSERT, UPDATE, and DELETE operations incur extra overhead to maintain each index.

​

2. Views

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;

• 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

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;

• 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.

​

Links and References

• Azure SQL Database Concepts
• SQL Server Index Architecture
• Creating Views in SQL Server
• Working with Stored Procedures