Identify Common Database Objects

The content in this section will help you learn about some common database objects that are critical to know about, such as tables, views, indexes, and stored procedures.

A fundamental understanding of the purpose of a table, a view, an index, and a stored procedure helps manage data efficiently, improve performance and security, and attain other goals a database system might have.

SQL database objects are entities or structures within a database that help to organize and store data in a structured manner. Examples of SQL database objects included in the DP-900 exam scope are as follows:

• Tables are the containers that store the actual data
• Views are windows into the data, providing a simplified, access-controlled, secured view
• Indexes are maps that help speed the retrieval of data
• Stored procedures are code containers that can enclose a set of interrelated operations for portable reuse and consistency

You will now look at each of these objects in the following sections.

Tables

Relational Database Management Systems (RDBMSs) store and retrieve data from tables, providing a simple but powerful and organized method of storing large amounts of data for most database-driven applications.

Tables are the most fundamental and common database objects. Data is contained in rows and columns; a table contains as many rows as necessary (to capture as much data as needed).

Each row of a table is an instance (a record) of data, and each column of that record represents an attribute of the instance.

Tables are created and modified using SQL commands, and queries are made using SQL statements to retrieve and manipulate datasets.

Tables are interrelated through key fields, and data can be queried across all tables related to a specific table using a specific key.

An example SQL CREATE TABLE command would be as follows:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

Views

Views provide a way to customize and manage data visibility in a database. They are virtual tables that display data from one or more tables; these are considered predefined SQL queries, which typically means SQL queries that are written and stored in the database ahead of time for specific tasks or functions.

Unlike physical tables, views do not store any data themselves but, instead, present data from the underlying tables in an easier way to work with or understand. Views are created using SQL SELECT queries that specify the fields and conditions to include in the view.

An example SQL CREATE VIEW command would be as follows:

CREATE VIEW EmployeeDetails
AS
SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees;

You can query the EmployeeDetails view created using the following statement:

SELECT *
FROM EmployeeDetails;

Once created, views can be queried and manipulated just like physical tables, allowing users to retrieve, update, insert, and delete data as needed.

Views can be used when presenting data in a specific format or masking sensitive or confidential data from certain users. They can also simplify complex queries or provide a simplified interface to data for non-technical users.

Indexes

Indexes improve the performance of database queries; they form tree-based structures.

Tree-based structures are data structures that organize data in a hierarchical way, where each node has a parent node and zero or more child nodes. Tree-based structures can store data with a natural hierarchy, such as files and folders, categories and subcategories, or family trees.

Indexes are data structures used to improve the speed and efficiency of database queries. They work by creating a searchable index of the data in a table or set of tables, which allows the database to quickly find and retrieve specific data without scanning the entire table.

An example SQL CREATE INDEX command would be as follows:

CREATE INDEX idx_salary ON Employees(Salary);

One or more columns in a table can have indexes created and can be either clustered or non-clustered.

Clustered indexes determine the physical order of data in a table. Non-clustered indexes consist of index keys pointing to the data’s location (row) in the table. Database queries can be executed much faster using indexes, particularly for large tables with many records. However, indexes can also have a downside – they can increase the time required to insert, update, or delete data from a table. As a result, it is important to use indexes judiciously and carefully consider the trade-offs between query performance and data modification performance when designing database schemas.

Stored Procedures

These are precompiled sets of SQL statements with parameters that can be executed multiple times. By grouping SQL statements in a stored procedure, a developer can create a single unit of code (which is reusable) that can be called from various places within a database or application. This can help simplify application development, reduce the amount of code to be created, and make it more straightforward to maintain and update complex database operations over time. One of the key benefits of using stored procedures is that they can encapsulate complex business logic and implement sophisticated data processing workflows.

An example SQL CREATE PROCEDURE command would be as follows:

CREATE PROCEDURE GetEmployeeByID(IN emp_id INT)
BEGIN
    SELECT *
    FROM Employees
    WHERE EmployeeID = emp_id;
END;

The command used to execute this stored procedure would be as follows:

EXECUTE GetEmployeeByID(1);

Stored procedures can also be used to improve the performance of database operations by reducing the amount of data that needs to be transferred between the database and the application. By performing data processing tasks within the database server rather than in the client application, stored procedures can significantly reduce the amount of network traffic and improve the overall efficiency of database operations.

This section on the common relational database objects concludes this part for Azure. This section also concludes the learning content for this chapter. Now, it’s time to summarize what skills you have learned in this chapter.