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