Functions & Views

Scalar function — returns one value

CREATE FUNCTION fn_GetFullName (@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + ' ' + @LastName;
END;
GO

SELECT dbo.fn_GetFullName(FirstName, LastName) AS FullName FROM Students;

Table-valued function — returns a table

CREATE FUNCTION fn_StudentsByYear (@Year INT)
RETURNS TABLE
AS
RETURN
(
    SELECT * FROM Students
    WHERE YEAR(EnrolledOn) = @Year
);
GO

SELECT * FROM fn_StudentsByYear(2024);

Views — saved, reusable queries

CREATE VIEW vw_StudentEnrollments AS
SELECT s.StudentId, s.FirstName, s.LastName, c.Title AS CourseTitle
FROM Students s
INNER JOIN Enrollments e ON e.StudentId = s.StudentId
INNER JOIN Courses c ON c.CourseId = e.CourseId;
GO

SELECT * FROM vw_StudentEnrollments WHERE LastName = 'Ahmed';

A view looks and behaves like a table for SELECT purposes, but it's just a saved query definition — the underlying data always stays live.