Indexes, Views, and Stored Procedures

An index is a database structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage. Without an index, the database engine must perform a full table scan — reading every row — to find matching data. With an index, the engine can perform an index seek, reading only the relevant pages.

A clustered index determines the physical order of data in a table. There can be only one clustered index per table because the data rows themselves can be sorted in only one physical sequence. When you create a primary key on a table, SQL Server by default creates a clustered index on that key column. If no clustered index exists, the table is a heap — rows are stored in no particular order.

Internally, a clustered index is structured as a B-tree (balanced tree). The leaf level of the B-tree contains the actual data pages. The intermediate levels contain index pages that guide the search from the root to the leaf. For example, if you search for an ID of 500, the root page might point to a page covering IDs 1-1000, then a lower page covering 400-600, then the leaf page containing row 500. This structure allows an index seek in O(log n) time versus O(n) for a full scan.

A nonclustered index is a separate structure from the data table. It contains a sorted list of index key values and, for each key, a pointer to the actual data row. If the table has a clustered index, the pointer is the clustered index key. If the table is a heap, the pointer is the row's physical location (RID — Row Identifier).

You can create up to 999 nonclustered indexes per table in SQL Server (though practically far fewer). Each nonclustered index requires its own storage and must be maintained on data modifications. The leaf level of a nonclustered index contains the index key columns plus included columns (if any) and the row locator.

A view is a virtual table based on the result set of a SELECT query. It does not store data physically (unless it is an indexed view). Views provide security by restricting access to specific columns or rows, simplify complex queries by encapsulating joins and filters, and ensure consistency by presenting a standardized schema.

#### Types of Views in SQL Server

#### Limitations of Views

A stored procedure is a precompiled collection of Transact-SQL statements stored under a name and processed as a unit. Stored procedures accept input parameters, return output parameters, and can return result sets. They are stored in the database and executed on the server.

#### Benefits of Stored Procedures

#### How Stored Procedures Work Internally

When a stored procedure is first executed, SQL Server parses it, checks for syntactic errors, and creates an execution plan. The plan is stored in the plan cache. On subsequent executions, if an existing plan is found and still valid, it is reused. Parameters are sniffed — the first execution's parameter values are used to optimize the plan. This can lead to parameter sniffing issues if subsequent calls use very different values.

#### Stored Procedures vs. Functions

All three objects work identically in Azure SQL Database and SQL Server on-premises. However, there are some Azure-specific considerations:

#### Index Commands

-- Create a clustered index
CREATE CLUSTERED INDEX IX_Orders_OrderDate ON dbo.Orders (OrderDate);

-- Create a nonclustered index
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID) INCLUDE (OrderDate, TotalAmount);

-- Rebuild an index
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REBUILD;

-- Reorganize an index
ALTER INDEX IX_Orders_CustomerID ON dbo.Orders REORGANIZE;

#### View Commands

-- Create a view
CREATE VIEW Sales.vOrdersSummary AS
SELECT CustomerID, COUNT(*) AS OrderCount, SUM(TotalAmount) AS TotalAmount
FROM Sales.Orders
GROUP BY CustomerID;

-- Create an indexed view
CREATE VIEW Sales.vOrdersSummary WITH SCHEMABINDING AS
SELECT CustomerID, COUNT_BIG(*) AS OrderCount, SUM(TotalAmount) AS TotalAmount
FROM Sales.Orders
GROUP BY CustomerID;
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOrdersSummary ON Sales.vOrdersSummary (CustomerID);

#### Stored Procedure Commands

-- Create a stored procedure
CREATE PROCEDURE Sales.GetOrdersByCustomer
    @CustomerID INT
AS
BEGIN
    SELECT OrderID, OrderDate, TotalAmount
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;
END;

-- Execute a stored procedure
EXEC Sales.GetOrdersByCustomer @CustomerID = 123;

A large e-commerce platform processes millions of orders daily. The Orders table has over 500 million rows. Without indexes, any search by CustomerID or OrderDate would take minutes. The DBA creates a clustered index on OrderID (the primary key) and a nonclustered index on CustomerID including OrderDate and TotalAmount. This reduces typical customer history queries from 45 seconds to under 100 milliseconds. However, during peak sales events like Black Friday, the high volume of inserts causes page splits on the clustered index because OrderID is sequential — but the clustered index on an identity column is actually optimal because new rows always go to the last page. The real problem is the nonclustered index on CustomerID: each insert also updates that index, causing additional I/O. The DBA monitors index fragmentation and schedules nightly rebuilds during low traffic. In production, they use ONLINE index rebuilds to avoid blocking.

A bank needs to generate daily summaries of transactions by branch and product type. The raw Transactions table has 2 billion rows, and the summary query aggregates by branch and product, taking 30 minutes. The DBA creates an indexed view with SCHEMABINDING that groups by BranchID and ProductID, with COUNT_BIG(*) and SUM(Amount). A unique clustered index is created on (BranchID, ProductID). Now, the summary query runs instantly because the aggregated data is precomputed. The trade-off: every insert into Transactions now must update the indexed view, adding about 15% overhead to each transaction. The bank accepts this because reporting queries run hundreds of times per day while inserts are continuous but moderate.

A SaaS company exposes a REST API that queries a customer database. Instead of allowing direct table access, all data access is through stored procedures. For example, GetCustomerOrders(@CustomerID, @StartDate, @EndDate) encapsulates the join logic between Customers, Orders, and OrderDetails. This provides a security boundary — the application service account only has EXECUTE permission on the stored procedures, not SELECT on the tables. It also centralizes business logic: if the schema changes, only the stored procedure needs updating, not the application code. Performance is improved because the execution plan is cached. The team uses sp_executesql with parameterized queries to prevent SQL injection. They also implement error handling with TRY...CATCH and return custom error codes. In production, they monitor the procedure cache for plan evictions due to memory pressure.