SQL Server - Database Objects

In SQL Server, database objects are essential components that allow to store and manage data effectively. These objects can range from tables and views to stored procedures and indexes.

Understanding the various types of database objects is important for database design, management, and optimization. This article will provide an overview of the key database objects in SQL Server.

SQL Server - Database Objects

A database object in SQL Server refers to the defined structure that contains data or metadata. These objects can be created, modified, and deleted as needed to support the database operations and apply the business rules of an application.

Types of Database Objects

1. Tables

Tables are the fundamental building blocks of a database. They store data in rows and columns, where each row represents a record and each column represents a field in that record.

Query:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(100),
    DepartmentID INT,
    Salary DECIMAL(10, 2)
);

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID, Salary) 
VALUES 
(1, 'John Smith', 101, 50000.00),
(2, 'Jane Doe', 102, 60000.00),
(3, 'Michael Johnson', 101, 55000.00),
(4, 'Emily Davis', 103, 70000.00),
(5, 'David Wilson', 102, 48000.00);

Output:

2. Views

Views are virtual tables that provide a way to present data from one or more tables. They can simplify complex queries, provide data security by restricting access to specific columns, and present data in a specific format.

Query:

CREATE VIEW EmployeeDetails AS
SELECT EmployeeName, Salary
FROM Employees
WHERE Salary > 50000;

Output:

Explanation:

The EmployeeDetails table shows only the employees whose salary is greater than 50,000:

• John Smith and David Wilson are excluded since their salaries are 50,000 and 48,000, respectively.
  
• Jane Doe, Michael Johnson, and Emily Davis are included because their salaries exceed 50,000.

3. Stored Procedures

Stored procedures are precompiled SQL statements that can be executed to perform a specific task. They allow for reusable code, contain business logic, and can accept parameters.

Query:

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

Executing the stored procedure:

EXEC GetEmployeeByID @EmployeeID = 101;

Output:

Explanation:

This case assumes that the Employees table has an employee with EmployeeID = 101. If the employee does not exist, the result would be an empty set.

4. Functions

Functions are similar to stored procedures but are used primarily for calculations and can return a value. They can be scalar (return a single value) or table-valued (return a table).

Query:

CREATE FUNCTION GetEmployeeCount()
RETURNS INT
AS
BEGIN
    DECLARE @Count INT;
    SELECT @Count = COUNT(*) FROM Employees;
    RETURN @Count;
END;

Executing the function:

SELECT dbo.GetEmployeeCount() AS TotalEmployees;

Output:

Explanation:

• Function Purpose: The GetEmployeeCount function calculates and returns the total number of employees in the Employees table.
  
• Return Value: The function returns an integer (INT) representing the total count.
  
• Execution Result: The result from the SELECT statement displays the total number of employees as TotalEmployees. In this case the value would be 5 reflecting the five employees inserted earlier.

5. Indexes

Indexes improve the performance of queries by allowing SQL Server to find rows more quickly. They can be created on one or more columns of a table.

Example:

CREATE INDEX IDX_EmployeeName ON Employees (EmployeeName);

Output:

Explanation:

This output confirms that the index IDX_EmployeeName exists on the EmployeeName column of the Employees table. While creating an index it does not produce a direct output table. It enhances query performance and can be verified through system catalog queries, helping us to understand how the index impacts data retrieval.

6. Triggers

Triggers are special types of stored procedures that automatically execute in response to specific events on a table, such as INSERT, UPDATE, or DELETE operations. They are useful for applying business rules or maintaining data integrity.

Query:

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'New employee added.';
END;

Inserting a new employee to trigger the message:

INSERT INTO Employees (EmployeeID, EmployeeName, DepartmentID, Salary)
VALUES (104, 'Alice Brown', 1, 70000);

Output:

Explanation:

• The trigger trgAfterInsert executes after a new employee is added, printing a message to the output.
  
• The Employees table is updated to include the new employee, confirming the successful insertion.

Conclusion

Understanding database objects in SQL Server is crucial for effective database management and design. Each type of database object has a unique purpose and can work together to build strong and efficient databases.

By using these objects effectively, we can make sure that data is stored, accessed, and managed according to the need of the applicant.