Common Database Objects

Why Are Common Database Objects Important?

Understanding common database objects is foundational to working with relational databases on Azure and is a key topic in the Microsoft DP-900: Azure Data Fundamentals exam. These objects form the building blocks of any relational database system. Whether you are designing a database, querying data, or managing security, you will interact with these objects daily. For the DP-900 exam, Microsoft expects you to identify, describe, and differentiate between these objects confidently.

What Are Common Database Objects?

Common database objects are the structural components that make up a relational database. The primary objects you need to know for the DP-900 exam include:

1. Tables
Tables are the core objects in a relational database. They store data in rows (records) and columns (fields). Each table represents an entity, such as Customers, Orders, or Products. Each column has a defined data type (e.g., INT, VARCHAR, DATE), and each row represents a single instance of that entity. Tables enforce structure through schemas.

2. Views
A view is a virtual table based on the result of a SQL query. It does not store data physically; instead, it provides a saved query that can be referenced like a table. Views are useful for:
- Simplifying complex queries
- Restricting access to specific columns or rows (security)
- Presenting data in a specific format without modifying the underlying tables

3. Indexes
An index is a data structure that improves the speed of data retrieval operations on a table. Think of it like an index in a book — it helps you find information quickly without scanning every page. There are two main types:
- Clustered Index: Sorts and stores the actual data rows in the table based on the index key. A table can have only one clustered index.
- Non-Clustered Index: Creates a separate structure that contains the index key and a pointer to the data row. A table can have multiple non-clustered indexes.
Indexes speed up read operations (SELECT) but can slow down write operations (INSERT, UPDATE, DELETE) because the index must also be updated.

4. Stored Procedures
A stored procedure is a precompiled collection of one or more SQL statements stored in the database. They can accept input parameters, execute logic, and return results. Benefits include:
- Reusability of code
- Improved performance (precompiled execution plans)
- Enhanced security (users can execute the procedure without direct access to underlying tables)
- Reduced network traffic

5. Functions
Functions are similar to stored procedures but are designed to return a value. There are two types:
- Scalar Functions: Return a single value (e.g., a calculated total).
- Table-Valued Functions: Return a table result set.
Functions can be used within SQL statements, such as in SELECT or WHERE clauses, unlike stored procedures.

6. Triggers
A trigger is a special type of stored procedure that automatically executes in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE operations. Triggers are commonly used for:
- Enforcing business rules
- Auditing changes to data
- Maintaining data integrity across related tables

How Do These Objects Work Together?

In a relational database, tables hold the actual data. Views provide controlled, simplified access to that data. Indexes optimize how quickly data in tables can be retrieved. Stored procedures and functions encapsulate reusable business logic and queries. Triggers automatically enforce rules or perform actions when data changes. Together, these objects enable efficient, secure, and well-organized data management.

How These Objects Apply on Azure

Azure SQL Database, Azure SQL Managed Instance, and Azure Database for PostgreSQL/MySQL all support these common database objects. In Azure SQL Database, for example, you can create tables, define views, build indexes, write stored procedures, create functions, and configure triggers — just as you would in an on-premises SQL Server environment. The DP-900 exam does not require you to write SQL code, but you must understand what each object is and its purpose.

Exam Tips: Answering Questions on Common Database Objects

- Know the definitions: Be able to clearly distinguish between a table, a view, an index, a stored procedure, a function, and a trigger. Exam questions often present a scenario and ask you to identify which object is being described.

- Views vs. Tables: Remember that views do not store data physically. If a question asks about a virtual table or a way to restrict data access without creating a new table, the answer is likely view.

- Indexes and Performance: If a question mentions improving query performance or speeding up data retrieval, think index. Remember that a clustered index physically reorders data and there can be only one per table.

- Stored Procedures vs. Functions: If the scenario describes reusable SQL logic that can be called with parameters, consider stored procedure. If it describes returning a single computed value within a query, think function.

- Triggers and Automation: If a question describes an automatic action that occurs when data is inserted, updated, or deleted, the answer is trigger.

- Watch for distractors: The exam may include options like "constraint" or "schema" — know that constraints enforce rules on data (like PRIMARY KEY or FOREIGN KEY), while schemas are organizational containers for database objects.

- Focus on purpose, not syntax: The DP-900 exam is conceptual. You will not be asked to write SQL code. Focus on understanding what each object does and why you would use it, not the exact syntax to create it.

- Scenario-based questions: Practice mapping real-world scenarios to the correct database object. For example: "A database administrator wants to ensure that every time a row is deleted from the Orders table, a record is written to an AuditLog table." The correct answer is trigger.

- Remember key constraints: While not always categorized as a "database object" in the same way, understand that PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT are constraints applied to tables to enforce data integrity. These are frequently tested alongside common database objects.

By understanding the role and purpose of each common database object, you will be well-prepared to answer related questions on the DP-900 exam confidently and accurately.