Identify common database objects

Database objects form the backbone of any database system, including Microsoft Azure. These objects help organize and manage data effectively. In this article, we will discuss some common database objects related to the Microsoft Azure Data Fundamentals exam. Let’s dive in!

1. Tables:

Tables are fundamental database objects that store structured data. They consist of rows and columns, similar to spreadsheets. In Azure, you can create tables using SQL languages like Transact-SQL or NoSQL databases like Azure Cosmos DB.

Example:

CREATE TABLE Customers (
CustomerID int PRIMARY KEY,
CustomerName varchar(255),
ContactNumber varchar(15),
Email varchar(255)
);

2. Views:

Views are virtual tables that display a subset of data from one or multiple tables. They are defined by queries and don’t store any actual data. Views provide a way to simplify complex queries and control access to data by defining specific columns visible to different users or roles.

Example:

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, CustomerName, ContactNumber
FROM Customers
WHERE IsActive = 1;

3. Indexes:

Indexes help improve the performance of queries by providing quick access to data. They are created on one or more columns of a table and are used to speed up searching, sorting, and filtering operations. Azure supports various types of indexes, including clustered, non-clustered, and full-text indexes.

Example:

CREATE INDEX IX_Customers_City ON Customers (City);

4. Stored Procedures:

Stored Procedures are pre-compiled sets of SQL statements stored in the database. They allow you to encapsulate complex logic and execute it repeatedly. Stored procedures enhance security and performance by reducing network round trips. Azure offers support for stored procedures in various Azure database services like Azure SQL Database.

Example:

CREATE PROCEDURE GetCustomerByID
@CustomerID int
AS
BEGIN
SELECT * FROM Customers WHERE CustomerID = @CustomerID;
END;

5. Functions:

Functions are similar to stored procedures but return values instead of executing a set of statements. They are often used to encapsulate reusable calculations or data transformations. Azure provides different types of functions, including scalar functions, table-valued functions, and system functions.

Example:

CREATE FUNCTION CalculateTotalPrice
(@UnitPrice decimal(18,2), @Quantity int)
RETURNS decimal(18,2)
AS
BEGIN
RETURN @UnitPrice * @Quantity;
END;

6. Triggers:

Triggers are special types of stored procedures that automatically execute in response to specific events on tables, such as insert, update, or delete operations. They are useful for enforcing business rules, data validation, and maintaining data integrity.

Example:

CREATE TRIGGER PreventDuplicateEmail
ON Customers
AFTER INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM inserted i
INNER JOIN Customers c ON i.Email = c.Email
WHERE i.CustomerID <> c.CustomerID)
BEGIN
RAISERROR ('Email already exists!', 16, 1);
ROLLBACK TRANSACTION;
END;

These are just a few common database objects related to the Microsoft Azure Data Fundamentals exam. Understanding these objects and their usage is essential for successfully working with Azure database services. Happy learning!