Use SQL to Update Data

What is Data Modification in SQL?

SQL allows users to modify database records using the INSERT, UPDATE, and DELETE commands.

These operations can affect the performance of indexed columns and may require index maintenance.

SQL Commands for Updating Data

SQL Command	Purpose	Example Usage
`INSERT INTO`	Inserts new records into a table	`INSERT INTO` Students (Name, Age) `VALUES` ('Alice', 20);
`UPDATE`	Modifies existing records	`UPDATE` Students `SET` Age = 21 `WHERE` Name = 'Alice';
`DELETE`	Removes records from a table	`DELETE FROM` Students `WHERE` Age < 18;

Inserting Data into a Table

The INSERT INTO statement adds new records into a database table.

        INSERT INTO Students (StudentID, Name, Age)

        VALUES (1, 'Alice', 20);

Updating Existing Records

The UPDATE statement modifies existing records based on specified conditions.

        UPDATE Students

        SET Age = 21

        WHERE StudentID = 1;

Deleting Data from a Table

The DELETE statement removes records from a table.

        DELETE FROM Students

        WHERE Age < 18;

Using Indexed Columns

What Is an Index in SQL?

An index in SQL is a special data structure used to improve the speed of data retrieval operations on a table. It acts like an efficient lookup reference, similar to an index in a book.

Why Are Indexes Useful?

Faster Queries: Indexes allow the database to find data more quickly without scanning every row.
Efficient Searches: Indexes are especially useful for columns used in WHERE clauses, JOIN conditions, or ORDER BY operations.
Improved Performance: Queries on large datasets run significantly faster when appropriate indexes exist.

What Are the Downsides?

Slower Updates: Every time indexed data is inserted, updated, or deleted, the index must also be updated.
Extra Storage: Indexes consume additional disk space.
Over-indexing: Having too many indexes can slow down write operations and reduce performance gains.

How Indexes Work (Analogy)

            Imagine you're searching for the topic "Functions" in a textbook:
            
            Without an Index:

            → Flip through every page to find "Functions".
            
            With an Index:

            → Go to the back of the book, look up "Functions", find the page number, and jump straight to it.

SQL indexes work the same way. Instead of scanning all table rows, the database can jump directly to the location of the data using the index - saving time and improving efficiency.

Indexed Columns: Updating an indexed column may slow down performance as indexes need to be adjusted.
Rebuilding Indexes: Large updates may require index reorganisation for efficiency.
Impact on Queries: Well-maintained indexes improve query performance, but unnecessary updates can degrade efficiency.

Rebuilding or Reorganising Indexes

Following large data modifications, indexes might need maintenance.

Rebuild vs Reorganise: What's the Difference?

The decision to Rebuild or Reorganise is usually based on how fragmented the index has become due to frequent INSERT, UPDATE, or DELETE operations.

Reorganising an Index: A lightweight operation that defragments the leaf-level pages of an index. It keeps the index online and doesn't require much additional space. Best used for minor fragmentation (up to ~30%).
Rebuilding an Index: Drops and recreates the index from scratch. It requires more resources but is more thorough. Best used for heavy fragmentation (above ~30%) and gives better performance improvement.

Rebuilding an Index

            ALTER INDEX StudentIndex ON Students REBUILD;
        

Reorganising an Index

            ALTER INDEX StudentIndex ON Students REorganise;
        

Why Are SQL Updates Important?

Data Accuracy: Ensures stored information remains up-to-date.
Efficient Querying: Well-maintained indexes improve performance.
Database Integrity: Prevents outdated or incorrect data from persisting.
Performance Optimisation: Index maintenance prevents slow queries.

Key Takeaways

SQL updates include INSERT, UPDATE, and DELETE to modify records.
Modifying indexed columns can impact performance, requiring index maintenance.
Index rebuilding or reorganisation helps optimise database query efficiency.
Efficient data updates ensure database consistency, accuracy, and performance.