How To Write Good SQL - Part 1

Always try to affect the maxiumum number of rows with the minimum number of transactions

Let's say you want to create and populate a simple table containing the phonetic alphabet. Let's create a table to hold this information. In practice phonetic_id would be an auto increment column. 

            
  1. create table phonetic (
  2.     phonetic_id int not null primary key,
  3.     phonetic varchar(10) not null unique
  4. );

You could produce 26 insert statements like this: 

            
  1. insert phonetic(phonetic_id, phonetic) values (1, 'Alfa');
  2. insert phonetic(phonetic_id, phonetic) values (2, 'Bravo');
  3. insert phonetic(phonetic_id, phonetic) values (3, 'Charlie');
  4. insert phonetic(phonetic_id, phonetic) values (4, 'Delta');
  5. insert phonetic(phonetic_id, phonetic) values (5, 'Echo');
  6. insert phonetic(phonetic_id, phonetic) values (6, 'Foxtrot');
  7. --
  8. insert phonetic(phonetic_id, phonetic) values (23, 'Whiskey');
  9. insert phonetic(phonetic_id, phonetic) values (24, 'Xray');
  10. insert phonetic(phonetic_id, phonetic) values (25, 'Yankee');
  11. insert phonetic(phonetic_id, phonetic) values (26, 'Zulu');

This is 26 separate transactions and take 0.345 secs to execute. If one of them fails the table is left in an inconsistent state. A better approach is to create a select statement using union all. That way all 26 rows are inserted as a single transaction (atomicity). Either the statement succeeds and all rows are populated or it fails and the table remains empty.

I've used union all as opposed to union as I know the dataset to be inserted contains unique values and it performs better not having to look for duplicates. 

            
  1. insert phonetic (phonetic_id, phonetic)
  2. select 1, 'Alfa' union all
  3. select 2, 'Bravo' union all
  4. select 3, 'Charlie' union all
  5. select 4, 'Delta' union all
  6. select 5, 'Echo' union all
  7. select 6, 'Foxtrot' union all
  8. select 7, 'Golf' union all
  9. select 8, 'Hotel' union all
  10. select 9, 'India' union all
  11. select 10, 'Juliett' union all
  12. select 11, 'Kilo' union all
  13. select 12, 'Lima' union all
  14. select 13, 'Mike' union all
  15. select 14, 'November' union all
  16. select 15, 'Oscar' union all
  17. select 16, 'Papa' union all
  18. select 17, 'Quebec' union all
  19. select 18, 'Romeo' union all
  20. select 19, 'Sierra' union all
  21. select 20, 'Tango' union all
  22. select 21, 'Uniform' union all
  23. select 22, 'Victor' union all
  24. select 23, 'Whiskey' union all
  25. select 24, 'Xray' union all
  26. select 25, 'Yankee' union all
  27. select 26, 'Zulu';

Took 0.032 sec, ten times faster than individual insert statements.