IB Computer Science Topic A3.3.3: Using SQL to Update Data in a Database Notes

INSERT INTO Adds Rows Without Changing the Table Structure

1. INSERT INTO adds a new record to a table that already exists, naming the columns first and then the values in the same order.
2. Any column defined as NOT NULL must be given a value, or the database rejects the whole statement.
3. Inserting a duplicate primary key, or a foreign key with no matching row in the parent table, also fails.

UPDATE SET Changes Values in Rows That Are Already There

1. UPDATE names the table, SET gives each column its new value, and WHERE decides which rows are affected.
2. Several columns can be set in one statement by separating the assignments with commas.
3. The new value can be calculated from the old one, as in SET ItemCost = ItemCost * 1.1.

DELETE Removes Whole Rows From a Table

1. DELETE FROM removes entire records, not individual field values.
2. To clear one field but keep the row, use UPDATE to set that column to NULL instead.
3. A delete can be refused if another table holds a foreign key pointing at the row you are trying to remove.

Forgetting WHERE Is the Costliest Mistake in SQL

1. UPDATE and DELETE apply to every row in the table when no WHERE clause is given.
2. DELETE FROM Customer; empties the whole table, and the database does not ask you to confirm.
3. There is no syntax error to catch this, because a statement with no WHERE is perfectly valid SQL.

DELETE, TRUNCATE and DROP Remove Different Amounts

Updating an Indexed Column Costs More Than Updating an Ordinary One

1. An index is a separate sorted structure that lets the database find rows without scanning the whole table.
2. That speed comes at a price on writes: every INSERT, UPDATE and DELETE has to change the index as well as the table.
3. A table with five indexes therefore does six pieces of work for every row inserted.
4. Updating a column that is not indexed is cheaper, because only the row itself changes.

Heavy Modification Fragments an Index, so It Needs Rebuilding or Reorganizing

1. After many changes the index entries no longer sit in a tidy physical order, which is called fragmentation.
2. A fragmented index still returns correct results, but each lookup touches more of the disk than it needs to.
3. Rebuilding
   1. Discards the old index and builds a new one from scratch.
   2. It removes fragmentation completely but is slow and can lock the table while it runs.
4. Reorganizing
   1. Tidies the existing index in place, moving entries back into order.
   2. It is faster and less disruptive, but it does not clear heavy fragmentation as thoroughly.

Reducing the Cost of Large Data Modifications

1. Batch the work by updating a few thousand rows at a time rather than millions in one statement, so the index update is spread out.
2. Drop and recreate an index around a very large one-off load, which is often quicker than maintaining it row by row.
3. Monitor fragmentation with the database system's own tools instead of rebuilding on a fixed schedule whether it is needed or not.
4. Index only the columns that queries actually filter or sort on, since every extra index taxes every write.