APPENDIX B1 – Further Topics of Interest

This is a beginner’s book on SQL. Therefore, the scope and focus is limited to foundational topics. However, if you finished this book and are interested in expanding your database and SQL repertoire, here are some suggested topics you can explore and research:

You will probably encounter dozens of other topics, especially as you explore the nuances of different database platforms. But these should provide plenty of leads to expand your database knowledge beyond this book.

APPENDIX B2 – Improving Performance with Indexes

As your database grows, the performance can start to slow down with SELECT queries. The machine has to process each record to find ones that match your WHERE condition, and obviously having more records will slow this process down.

A common way to improve performance significantly is to use indexes, a mechanism that enables faster lookups in a way very similar to an index in a book. An index speeds up SELECT performance, but it slows down INSERT, UPDATE, and DELETE statements. It will also make the database file larger. These are factors you have to balance in your decision to use them. You should not think about creating indexes when you first design a database. Do it later, when you find you are having performance issues.

You specify an index on one or more columns, and you want these columns to be the ones you frequently qualify on. For example, if you frequently query the PRODUCT table and use a WHERE statement on the price column, you can apply an index on that column as shown here:

CREATE INDEX price_index ON PRODUCT(price);

We name the index price_index and we apply it on the PRODUCT table, and in parentheses we specify it on the price column. SQLite will keep a map of which records have which price values. This will significantly speed up performance when we qualify on price. But obviously, when we modify records it has to update this index, so this overhead will slow down INSERT, UPDATE, and DELETE operations.

You will notice in the SQLiteStudio database navigator that the table contains all the index objects you have created (see Figure B-1).

Figure B-1. The price_index was added to the PRODUCT table’s indexes

You can also create a UNIQUE index for a column that never has duplicate values, and SQLite will make special optimizations for that case:

CREATE UNIQUE INDEX name_index ON CUSTOMER(name);

In addition, you can use composite indexes if two or more fields are frequently qualified together, but that is beyond the scope of this book.

To remove an index, just run a DROP INDEX statement on the index’s name:

DROP INDEX price_index;

Again, indexes should only be used for very large tables that have noticeable performance issues with SELECT statements. You should avoid using indexes on small tables as the overhead will actually slow performance down (meaning this example was demonstrational, not something you should actually do to the PRODUCT table). You should also avoid using indexes on tables that update heavily and frequently.

Appendix B3 – Transactions

There may be situations where you will want to execute multiple INSERT, UPDATE, or DELETE statements as a batch, but you want all of them to complete successfully, and if one fails you want all of them to fail. This is known as atomicity, which means these actions must all happen successfully or none of them happen at all.

A good example where this kind of behavior is needed is financial transactions, like bank account transfers or payment services like PayPal. When you take money from one account and put it in another, you have to make sure both operations happen successfully.

Take these two INSERT statements that move $187.56 from one account and put it in another:

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (1563,-187.56);

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (3067,187.56);

What happens if the first INSERT succeeds but the second one fails? Well, that $187.56 has effectively disappeared. You have two upset customers and a possible auditing mess. So how do you ensure that in the event of failure, that money returns back to the customer giving it and everything is restored to the way it was?

The answer is to leverage a transaction. With a transaction you can make this transfer atomic and do a ROLLBACK if anything fails and a COMMIT if everything succeeds.

First, call the BEGIN or BEGIN TRANSACTION command (these are the same command):

BEGIN TRANSACTION;

Now any INSERT, UPDATE, and DELETE statements will be recorded so they can be undone if necessary. Perform the two INSERTs. The actions will be performed while being recorded in “transaction mode”:

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (1563,-187.56);

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (3067,187.56);

If everything goes well and no errors occurred, you can call COMMIT or its alias, END TRANSACTION, to finalize the INSERTs. The transfer has then happened successfully.

Now let’s start another transaction so we can do another transfer:

BEGIN TRANSACTION;

However, this time we are going to break it. Say we do another transfer between these two accounts:

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (1563,-121.36);

INSERT INTO ACCOUNT_ACTIVITY (ACCOUNT_ID,AMOUNT) VALUES (121.36);

The first statement will succeed, but the second SQL statement was messed up and will error out. It is missing the ACCOUNT_ID value, and now we have $121.36 in limbo.

Fortunately, we are in “transaction mode.” We can basically hit a rewind button and call ROLLBACK. This will undo everything since the last COMMIT or BEGIN TRANSACTION we called. The first INSERT will be undone and the $121.36 will be back in the 1563 account.

In the event that a database connection goes down, a bad SQL statement is composed, or a validation rule train-wrecks a series of updates, transactions are a way to ensure data does not get corrupted in your database. You will especially want to use them with automated processes or large jobs requiring you INSERT, UPDATE, and DELETE a high volume of fragile records.