📘 Views

### What are Views in SQL? A **view** is a virtual table created from the result set of an SQL query. It provides a way to simplify complex queries, control access to data, and encapsulate business logic.

💡 Key Characteristics of Views

• Views are virtual tables that don't store data physically
• They provide a simplified interface to underlying data
• Changes made to the base tables reflect in views automatically
• Can be used to restrict access to sensitive columns or rows

💡 Creating Views

To create a view, use the `CREATE VIEW` statement. The query inside the view can be as simple or complex as needed.

CREATE VIEW customer_orders AS
SELECT o.order_id, c.customer_name, o.order_date, SUM(ol.quantity * ol.unit_price) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_lines ol ON o.order_id = ol.order_id
GROUP BY o.order_id, c.customer_name, o.order_date;

💡 Updating Views

Views can be updated using the `ALTER VIEW` statement. Note that not all queries are updatable (known as **updatable views**). For a view to be updatable, it must meet certain criteria such as referencing only one base table or meeting INSTEAD OF trigger requirements.

ALTER VIEW customer_orders AS
SELECT o.order_id, c.customer_name, o.order_date,
CONCAT('$', SUM(ol.quantity * ol.unit_price)) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_lines ol ON o.order_id = ol.order_id
GROUP BY o.order_id, c.customer_name, o.order_date;

💡 Materialized Views

A **materialized view** is a copy of the data in a table or another materialized view. Unlike standard views, which are computed each time they're queried, materialized views store their data physically.

CREATE MATERIALIZED VIEW mv_product_sales AS
SELECT p.product_name, SUM(s.quantity) AS total_sold,
SUM(s.quantity * s.price) AS revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_name;

💡 Best Practices for Views

• Keep view definitions simple and focused
• Use meaningful names for views
• Document your views with comments
• Test complex queries before creating a view
• Refresh materialized views regularly to maintain accuracy

❌ Common Mistakes to Avoid

• Don't create overly complex views that are hard to maintain
• Avoid using views for performance optimization without testing
• Don't use views when direct table access is more efficient
• Be cautious with circular references between views

📘 Indexes

### What are Indexes? Indexes are database objects that improve query performance by providing faster access to data. They work similarly to book indexes, allowing the database engine to quickly locate specific information without scanning the entire table.

💡 Key Concepts

• Indexes can significantly improve query performance for large datasets
• There are different types of indexes (B-tree, hash, bitmap)
• Too many indexes can negatively impact write operations and storage
• Index tuning is crucial for optimal database performance

✅ Common Index Types

### B-tree Indexes The most common type of index that organizes data in a balanced tree structure. They are ideal for range queries and support order operations.

CREATE INDEX idx_customers_name ON customers(name);

### Hash Indexes Used for equality searches where the hash value is calculated from the data and stored in an array. They are faster for exact match queries but do not support range operations.

CREATE INDEX idx_hash ON transactions USING HASH (transaction_id);

### Bitmap Indexes Used for columns with a limited number of possible values. They store rows as bits and are memory efficient but not suitable for high-cardinality data.

CREATE INDEX idx_bitmap ON orders USING BITMAP (status);

💡 Covering Indexes

A covering index includes all the columns needed for a query, eliminating the need to access the table. This can drastically improve performance.

// Query without covering index
SELECT name FROM employees WHERE department = 'Engineering';

// With covering index
CREATE INDEX idx_covering ON employees(department) INCLUDE (name);

❌ Common Mistakes to Avoid

• Over-indexing your tables
• Not considering index selectivity
• Using indexes on frequently updated columns
• Ignoring the impact of clustered vs non-clustered indexes

✅ Best Practices for Index Tuning

• Analyze query execution plans to identify missing indexes
• Monitor index usage and drop unused ones
• Use composite indexes when multiple columns are frequently queried together
• Consider the data distribution and cardinality of your tables

💡 Practical Applications

Proper index management is critical for high-performance applications. Consider the following scenarios: - E-commerce platforms with frequent product searches - Financial systems requiring fast transaction lookups - Large-scale data warehouses with complex analytical queries

📘 Stored Procedures & Functions

💡 Introduction to Stored Procedures & Functions

**Stored procedures** and **functions** are essential components of SQL that allow you to create reusable logic. They help improve performance, reduce network traffic, and provide a layer of security by encapsulating business logic within the database.

💡 What are Stored Procedures?

A **stored procedure** is a named collection of SQL statements that performs a specific task. It can take parameters, perform operations, and return results. Stored procedures are compiled once and executed multiple times, making them more efficient than ad-hoc queries.

CREATE PROCEDURE GetCustomerOrders
(
    @CustomerId INT
)
AS
BEGIN
    SELECT OrderId, ProductName, Quantity, OrderDate
    FROM Orders
    WHERE CustomerId = @CustomerId;
END;

💡 What are Functions?

A **function** is similar to a stored procedure but returns a single value or table. It can be used in SQL statements wherever an expression is allowed. Functions are often used for calculations, data transformations, and encapsulating complex logic.

CREATE FUNCTION CalculateTotalOrderValue
(
    @OrderId INT
)
RETURNS MONEY
AS
BEGIN
    DECLARE @TotalValue MONEY;
    SELECT @TotalValue = SUM(Quantity * UnitPrice)
    FROM OrderDetails
    WHERE OrderId = @OrderId;
    RETURN @TotalValue;
END;

💡 Key Differences between Stored Procedures and Functions

• **Stored Procedures** return multiple result sets or no results, while **functions** must return a single value or table.
• Functions can be used in SQL statements like SELECT, WHERE, and JOIN clauses, whereas stored procedures cannot.
• Functions have stricter error handling requirements as they are expected to always return a value.

💡 Parameters in Stored Procedures & Functions

Parameters allow you to pass values into stored procedures and functions. They can be of different types including input, output, and return parameters.

CREATE PROCEDURE CalculateOrderTotal
(
    @OrderId INT,
    @TotalAmount MONEY OUTPUT
)
AS
BEGIN
    SELECT @TotalAmount = SUM(Quantity * UnitPrice)
    FROM OrderDetails
    WHERE OrderId = @OrderId;
END;

💡 Control Flow in Stored Procedures & Functions

Like any programming language, SQL stored procedures and functions support control flow statements such as IF-ELSE, WHILE loops, and CASE statements.

IF (EXISTS(SELECT 1 FROM Customers WHERE CustomerId = @CustomerId))
BEGIN
    SELECT * FROM Customers WHERE CustomerId = @CustomerId;
END
ELSE
BEGIN
    RAISERROR('Customer not found!', 16, 1);
END;

💡 Error Handling in Stored Procedures & Functions

Proper error handling is critical to ensure your stored procedures and functions are robust and maintainable. Use TRY-CATCH blocks and RAISERROR statements for effective exception management.

BEGIN TRY
    -- Some risky operation here
    IF (@Divisor = 0)
        SET @Result = NULL;
END TRY
BEGIN CATCH
    IF (ERROR_NUMBER() = 8134) -- Division by zero error
        SET @ErrorMessage = 'Cannot divide by zero!';
    ELSE
        SET @ErrorMessage = ERROR_MESSAGE();
    RAISERROR(@ErrorMessage, 16, 1);
END CATCH;

✅ Best Practices for Stored Procedures & Functions

• Use meaningful names that describe the purpose of the procedure or function.
• Document your code with comments and descriptions using EXECUTE sp_addextendedproperty.
• Avoid SELECT * - always specify the columns you need.
• Use proper error handling and logging.
• Test thoroughly before deploying to production.

❌ Common Mistakes to Avoid

• Don't use SELECT * in stored procedures - it can lead to performance issues and unnecessary data transfer.
• Avoid using Cursors unless absolutely necessary - they can negatively impact performance.
• Don't hardcode values - use parameters or configuration tables instead.
• Don't ignore error handling - always plan for potential failures.