Identify common database objects
Database objects are essential components that you will interact with while working with data-bases. These objects are utilized to store, manipulate, and retrieve the data housed within your database.
- Tables: At the heart of any database are tables. They are the structure that holds the data. A table consists of rows (or records) and columns (or fields). Each column represents a category of data, and each row contains the actual data values, as shown in Table 2-11.
TABLE 2-11 Table object
- Views: A view is a virtual table based on the resultset of a SQL statement. They allow you to present the data in a different perspective from that of the base table, and they can hide complexity or secure sensitive data. Figure 2-19 shows how you would create a view called TopStudent that shows only those students with a GPA greater than 3.5.
FIGURE 2-19 Simplifying data queries with a view to display top students
- Indexes: As you delve deeper into your interaction with databases, you’ll find that indexes are fundamental to optimizing performance and enhancing the speed of data retrieval.
An index, in the context of a database, is similar to an index in a book. In a book, an index allows you to find information quickly without having to read every page. Similarly, a database index provides a quick lookup pathway to the data in a table.
42 CHAPTER 2 Identify considerations for relational data on Azure
Creating an index involves the CREATE INDEX statement. When you create an index on a table column, the database management system (DBMS) generates a data structure that allows it to find the data associated with the indexed columns more swiftly.
Let’s look at an example. Say you frequently execute a query as shown in Figure 2-20 that searches students based on their last names in the Students table.
FIGURE 2-20 The search query
To speed up this query, you can create an index on the LastName column, as shown in Figure 2-21.
FIGURE 2-21 Enhancing query efficiency with an index on the LastName column in the Students table
Now, the DBMS will use the idx_lastname index to find Smith rapidly, which can lead to significantly faster query performance.
However, while indexes are powerful, they should be used judiciously. Creating an index is not a trivial task for the DBMS. It takes time and consumes storage space. Moreover, every time a table’s data changes (via an INSERT, UPDATE, or DELETE statement), the associated indexes also need to be updated. Thus, having too many indexes, especially on a table that frequently changes, can actually degrade the performance.
Therefore, a balanced approach is required when dealing with indexes. They should be cre-ated on columns that are frequently searched or used in the WHERE clause, JOIN operations, or sorting data (ORDER BY).
By implementing indexes in your database, you’re well on your way to creating efficient,
performance-tuned data retrieval options. This knowledge will greatly enhance your ability to effectively manage data in a relational database.
- Stored procedures: These are a group of SQL statements that form a logical unit and perform a particular task. Stored procedures can be used to encapsulate logic, enforce security, and increase performance.
In Figure 2-22 you could create a stored procedure to add a new student to the Students table.
Skill 2.1: Describe relational concepts CHAPTER 2 43
FIGURE 2-22 Stored procedure
- Triggers: Triggers are special types of stored procedures that automatically execute when an event occurs in the database server. Triggers can help maintain the integrity of the data. You can create a trigger that automatically updates a LastModified field in the
Students table whenever a record is updated, as shown in Figure 2-23.
FIGURE 2-23 Trigger implementation to auto-update the LastModified timestamp in the Students table
By understanding and utilizing these objects, you can effectively manage your data and optimize your interactions with the database.