Fundamentals of Databases: Non-Table Objects

Fundamentals of Databases

• The presentation focuses on non-table objects within Relational Database Management Systems (RDBMS).

Agenda

• The agenda covers key non-table objects:
  • Index
  • View
  • Stored Procedure
  • Trigger

Index

• Indexes are crucial for relational database performance.
• Database administrators often view indexes as the most important tool for improving performance.
• Indexes are data structures that contain copies of data from one or more tables.
• Indexes provide a framework for the DBMS to quickly locate needed information.
• This greatly enhances the speed of SQL queries.
• Indexing can significantly improve the speed of SQL queries.

Why Indexing?

• Indexing is a critical tool for achieving high performance in relational databases.
• Average Search Time (without index): average = (n + 1) / 2
• Maximum search time (without index): maximum = n
• Average Search Time (with index): average = log₂(n) - 1 = 3.5
• Maximum search time (with index): maximum = log₂(n) = 4.5

Index Concepts

• Indexes are created for one or more table columns.
• An index on a primary key (PK) column contains the PK value for each row and its position in the table.
• When queried with a PK, the DBMS locates the value in the index and finds the row's position in the table.
• Without an index, the DBMS performs a table scan to locate the desired row.

Index Concepts (Limitations)

• Not all columns can be indexed.
• Large object data types (e.g., TEXT, ntext, Image, varchar(max), nvarchar(max), varbinary(max)) typically require additional indexing mechanisms.

Index Concepts (Storage Space)

• Creating an index increases database storage space.
• The index contains a copy of some table data.
• Estimating storage requirements: Number of rows in table * Average number of bytes per row for indexed columns.

B-Tree Index

• A balanced-tree (B-tree) is a common database indexing type.
• B-trees use pointers and multiple node levels to quickly locate data.
• The DBMS, when processing a query with an indexed column, begins at the root node, traversing downward to the desired leaf node.

B-tree example

• Diagrams with example of root, intermediary, and leaf nodes.

Clustered Indexes

• In a clustered index, the table's data rows are physically stored in the index's leaf level, sorted.
• Only one clustered index is permitted per table.
• Primary key (PK) columns are good choices.

View

• A view is a virtual or logical table derived from other tables.

• A view simplifies complex queries.

• A view enables computed columns.

• A view provides a security layer by masking sensitive data.

• A view facilitates backward compatibility.

• Cons of a view include performance issues, and dependency on table changes which require changes to the view.

• The syntax for creating and altering views is presented, along with parameters.

View Examples

• Examples of computed columns, views formed from subqueries and views created from another view.

Updatable Views

• For updatable views, the SELECT statements used to define the views are restricted to avoid aggregate functions, DISTINCT, GROUP BY, HAVING, UNION clauses, and subqueries in the SELECT or WHERE clause that reference the FROM tables. Other restrictions apply.

With CHECK OPTION Clause

• The WITH CHECK OPTION clause prevents updates or insertions that are not visible through the view.

View Management

• Commands to show view definition, delete views and change views.
• Listing all views with their updateability details.

Stored Procedure (SP)

• A stored procedure (SP) is a SQL statement collection stored within the database.
• SPs are compiled and stored in memory whenever used, preventing repeated compilation.
• SPs accept input parameters and return output parameters.
• SP syntax is shown.

Stored Procedure Characteristics

• SP characteristics include:
  • stored in memory
  • Parameter types: input and output

Stored Procedure (SP) Syntax

• Syntax for creating and dropping stored procedures.

Stored Procedure vs. SQL Statement

• Comparison table showing differences in execution time between SQL Statements and Stored Procedures.

Types of SP

• SP categories include:
  • System Stored Procedure (names begin with sp_)
  • Local Stored Procedure (defined in a user database)

Executing a Stored Procedure (SP)

• Methods for executing SPs, including by name and by position.
• Utilizing parameters within the execution process.

Pros and Cons

• Advantages of using stored procedures:
  • Improved performance
  • Reduced traffic of data exchange
  • Reusability and transparency
  • Secure data access
• Disadvantages of using stored procedures:
  • CPU usage increased due to overuse of logical operators
  • Debugging and maintenance challenges

Example

• Example code showcasing a stored procedure that counts the number of rows from a table.

Input Parameter

• Example code showing an SP with an input parameter for querying customers in a specific city.

Triggers

• Triggers are special stored procedures that are automatically executed during data modification (INSERT, UPDATE, or DELETE).
• Triggers are table-associated.
• Triggers cannot be invoked directly.

Trigger Syntax

• Basic CREATE TRIGGER syntax is provided.

Simplified Syntax

• A simplified representation of trigger syntax highlights the involved temporary tables holding new or old/updated/deleted records.

Trigger Use Cases

• Potential uses of triggers: maintaining data, ensuring integrity, and implementing constraints.

Trigger Examples

• INSERT, UPDATE, DELETE trigger examples are shown.

Performance Considerations

• Trigger performance is fast due to the use of in-cache temporary tables for Inserted and Deleted data.
• Trigger execution time depends on:
  • Number of referenced tables
  • Number of affected rows.
• Triggers' actions are implicitly handled as a transaction.

Explore the essential non-table objects in Relational Database Management Systems (RDBMS). This quiz covers key concepts such as Index, View, Stored Procedure, and Trigger, emphasizing their significance in optimizing database performance. Test your knowledge on how these components enhance SQL query efficiency.