Understanding Views, Indexes, Functions & Stored Procedures in SQL Server

Working with SQL Server becomes much easier when you understand its four most important database objects: Views, Indexes, Functions, and Stored Procedures.

These objects help in improving performance, simplifying queries, and maintaining clean and reusable code in real-world applications.

This article explains each concept in a simple way with syntaxes and examples.

1. What is a View in SQL Server?

A View is a virtual table that retrieves data from one or more underlying tables.
It does not store data physically (except Indexed Views). It only stores the query.

Why Views Are Used

To simplify complex SQL queries
To provide restricted access for security
To make reporting easier
To reuse SQL logic across applications

View – Basic Syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example – Create a View

CREATE VIEW vwEmployeeDetails AS
SELECT EmpId, EmpName, Job, Salary
FROM Employee
WHERE Active = 1;

Using the View

SELECT * FROM vwEmployeeDetails;

Views make long queries short, reusable, and secure.

2. What is an Index in SQL Server?

An Index is similar to an index in a book — it helps SQL Server find data quickly.

Indexes greatly improve performance for:

Searching
Filtering
Sorting
Joining tables

Without indexes, SQL Server scans the entire table → slow
With indexes, SQL Server jumps directly to the needed data → fast

Non-Clustered Index – Syntax

CREATE NONCLUSTERED INDEX index_name
ON table_name(column_name);

Example

CREATE NONCLUSTERED INDEX IX_Employee_EmpName
ON Employee(EmpName);

Clustered Index – Syntax

CREATE CLUSTERED INDEX index_name
ON table_name(column_name);

Example

CREATE CLUSTERED INDEX IX_Employee_EmpId
ON Employee(EmpId);

Note:
A table can have only one Clustered Index, because it defines the physical order of data.

3. What is a Function in SQL Server?

A Function is a reusable object that performs calculations and returns a value or table.

Types of Functions

Scalar Function → returns 1 value
Table-Valued Function → returns a table

Functions help to maintain clean and reusable SQL logic.

Scalar Function – Syntax

CREATE FUNCTION function_name (@param datatype)
RETURNS datatype
AS
BEGIN
    RETURN value;
END;

Example — Get Age

CREATE FUNCTION dbo.GetAge(@DOB DATE)
RETURNS INT
AS
BEGIN
    RETURN DATEDIFF(YEAR, @DOB, GETDATE());
END;

Use the Function

SELECT dbo.GetAge('1995-05-01');

Table-Valued Function – Syntax

CREATE FUNCTION function_name (@param datatype)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM table WHERE column = @param
);

Example — Get Employees by Job

CREATE FUNCTION dbo.GetEmployeesByJob(@Job NVARCHAR(50))
RETURNS TABLE
AS
RETURN
(
    SELECT EmpId, EmpName, Salary
    FROM Employee
    WHERE Job = @Job
);

Use the Function

SELECT * FROM dbo.GetEmployeesByJob('Developer');

4. What is a Stored Procedure in SQL Server?

A Stored Procedure is a pre-compiled group of SQL statements used to perform operations such as:

Insert
Update
Delete
Select
Business logic

They improve performance and maintain clean code in applications.

Stored Procedure – Basic Syntax

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

Example – Select Procedure

CREATE PROCEDURE spGetActiveEmployees
AS
BEGIN
    SELECT EmpId, EmpName, Job, Salary
    FROM Employee
    WHERE Active = 1;
END;

Execute Procedure

EXEC spGetActiveEmployees;

Example – Insert Procedure with Parameters

CREATE PROCEDURE spInsertEmployee
(
    @EmpName NVARCHAR(50),
    @Job NVARCHAR(50),
    @Salary DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO Employee(EmpName, Job, Salary)
    VALUES(@EmpName, @Job, @Salary);
END;

Conclusion

SQL Object	Purpose
View	Simplify queries & secure data
Index	Improve performance of searches
Function	Reusable logic that returns value/table
Stored Procedure	Execute a group of SQL statements

These four objects are the foundation of SQL Server development and are used in every real-time project.