Relational Databases for Agile Developers

Sample Data and Scripts for Microsoft SQL Server

If you have been following the examples in the book, you may have created the tables in the Claims database already. This page takes you through that exercise and also provides data for these tables and shows you how to load this data. If you do this, you will be able to run most of the queries in the book, and hopefully some of your own, with reasonable volumes of generated sample data.

You will need to download the scripts and data files specific to Microsoft SQL Server running on Microsoft Windows. There are a number of differences between this environment and the PostgreSQL environment used by default in the book. The individual links are below, on this page, but you will probably find it easier to download the zip file that contains all of these.

The data files have Windows line ends (carriage return, linefeed) rather than UNIX line ends (linefeed only).
There is no boolean data-type in Microsoft SQl Server. We use the bit data-type, with 0 = false, 1 = true and null = null.
The timestamp data-type means something quite different in SQL Server; it is an integer, not a date and/or time at all. We use datetime2. datetime would also be possible, but datetime2 has greater precision. We probably don't care about millionths of a second, but the data we have on PostgreSQL does have this level of precision and datetime2 is compatible with that.
The Microsoft SQL Server bcp scripts are quite different from the PostgreSQL copy scripts, and run at the Windows command prompt rather than inside the SQL Server command line (sqlcmd).

We start with Iteration 1 of the Claims database, as described in in:

Chapter 3 - An Agile Database - Step by Step
Chapter 4 - Database Migrations in SQL
Chapter 12 - Database Migrations That Preserve data

You can create the Iteration 1 tables, either using Ruby on Rails (scaffold_iteration_1.sh) or SQL (migration_001.sql), as described in the book.

If you are using the SQL migration (as most people will) then you need to download the scripts for the table definitions too:

Here's the example using the SQL migration (substitute your server connection for "localhost\MSSQLSERVER01":

      >sqlcmd -S localhost\MSSQLSERVER01
      1> create database claims;
      2> go      
      1> use claims;
      2> go
      Changed database context to 'claims'.      
      1> :r migration_001.sql
      2> go
      1> select table_name, table_catalog from information_schema.tables where table_type = 'base table';
      2> go
      table_name  table_catalog
      ----------  -------------
      person      claims
      policy      claims
      vehicle     claims
      claim       claims
      claimant    claims
      witness     claims
      driver      claims
      passenger   claims
      damage      claims
      
      (9 rows affected)

Now we can load some data into these tables. First download each of the data files:

Table	Download link
claim	claim.csv
claimant	claimant.csv
damage	damage.csv
driver	driver.csv
passenger	passenger.csv
person	person.csv
policy	policy.csv
vehicle	vehicle.csv
witness	witness.csv

Now download the script load_data_iteration_1.txt. This file contains bcp commands to load the data. You will need to edit the server name and database name in each one to match your set-up. Exit from sqlcmd and at the Windows command prompt change directory to the place where you downloaded the files. Then run the commands in load_data_iteration_1.txt:


bcp claims.dbo.person    in person.csv    -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000

12538 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 141    Average : (88921.98 rows per sec.)

bcp claims.dbo.policy    in policy.csv    -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000

4739 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 31     Average : (152870.97 rows per sec.)

bcp claims.dbo.vehicle   in vehicle.csv   -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000

14355 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 94     Average : (152712.77 rows per sec.)

bcp claims.dbo.claim     in claim.csv     -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000

6726 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 109    Average : (61706.42 rows per sec.)

bcp claims.dbo.claimant  in claimant.csv  -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000
1000 rows sent to SQL Server. Total sent: 15000
1000 rows sent to SQL Server. Total sent: 16000

16211 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 109    Average : (148724.77 rows per sec.)

bcp claims.dbo.witness   in witness.csv   -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000

3499 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 31     Average : (112870.97 rows per sec.)

bcp claims.dbo.driver    in driver.csv    -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000

6093 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 31     Average : (196548.39 rows per sec.)

bcp claims.dbo.passenger in passenger.csv -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...

572 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 16     Average : (35750.00 rows per sec.)

bcp claims.dbo.damage    in damage.csv    -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

There is no data for the table called damage, hence the zero result for the last copy.

We can check what we have loaded using row_counts_iteration_1.sql:

      >sqlcmd -S localhost\MSSQLSERVER01
      1> use claims;
      2> go
      Changed database context to 'claims'.
      1> :r row_counts_iteration_1.sql
      2> go
        table_name   row_count
        ------------ ----------
        claim              6726
        claimant          16211
        damage                0
        driver             6093
        passenger           572
        person            12538
        policy             4739
        vehicle           14355
        witness            3499

        (9 rows affected)

If you want to get to the Iteration 2 database and then the Iteration 3 database, the best way is to run the migrations described in Chapter 12 - Database Migrations That Preserve Data.

The migration from Iteration 1 to Iteration 2 creates the incident table and moves data to it from the claims table, creating the necessary links on the way. It also upgrades the vehicle table. The incident table is populated by the migration, but the extra columns in the vehicle table are new, so there is no data to migrate.

Download the migration script migration_002_preserve_data.sql, and the SQL scripts:

Now run the migration as shown below. The caution is good advice, but you can ignore it in this case; we want to alter some tables and we have tested that these changes do what we want.

1> :r migration_002_preserve_data.sql
2> go
Caution: Changing any part of an object name could break scripts and stored procedures.

(6302 rows affected)

(6726 rows affected)
test_result
-------------------------------------
Success. Claim counts OK

(1 rows affected)
test_result
---------------------------------------
Success. Incident counts OK

(1 rows affected)
test_result
-----------------------------------------
Success. No data lost in migration

(1 rows affected)
test_result
-----------------------------------------------------
Success. No spurious data in migration

(1 rows affected)

The working table that we used to migrate the claim and incident data (old_claim) must now be dropped. It is very important to get rid of working tables when we just finished with them and checked the results. This is the time when we know what happened and we know that we can drop this table. In a week's time we will not be sure and we won't bother to check it all again. This is how databases get clogged with junk.

1> drop table old_claim;
2> go

sqlcmd does not give any confirmation, but we can easily check that the table is no longer there.

Download the script to create a table to hold the extra data for the new columns in the vehicle_table: vehicle_make_model_year_first_licensed.sql, and run it:

1> :r vehicle_make_model_year_first_licensed.sql
2> go

To populate the extra columns in the vehicle table:

Download the file of extra data: vehicle_make_model_year_first_licensed.csv

At the Windows command prompt, change directory to the place where you downloaded the csv file. Then edit this command for your server name and database name and run it:

           bcp claims.dbo.vehicle_make_model_year_first_licensed in vehicle_make_model_year_first_licensed.csv -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

The results should look like this:

    bcp claims.dbo.vehicle_make_model_year_first_licensed in vehicle_make_model_year_first_licensed.csv -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
1000 rows sent to SQL Server. Total sent: 3000
1000 rows sent to SQL Server. Total sent: 4000
1000 rows sent to SQL Server. Total sent: 5000
1000 rows sent to SQL Server. Total sent: 6000
1000 rows sent to SQL Server. Total sent: 7000
1000 rows sent to SQL Server. Total sent: 8000
1000 rows sent to SQL Server. Total sent: 9000
1000 rows sent to SQL Server. Total sent: 10000
1000 rows sent to SQL Server. Total sent: 11000
1000 rows sent to SQL Server. Total sent: 12000
1000 rows sent to SQL Server. Total sent: 13000
1000 rows sent to SQL Server. Total sent: 14000

14355 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 78     Average : (184038.47 rows per sec.)

Download the script: populate_new_columns_in_vehicle.sql

Here's what happens, with a select of the first 5 rows to show that we did add the make, model and year_first_licensed, and give them values:

      1> :r populate_new_columns_in_vehicle.sql
      2> go
      (14355 rows affected)

Check the first few rows:

      select top 5 
          id, 
          policy_id, 
          license_number, 
          state_where_licensed, 
          make, 
          model, 
          year_first_licensed 
      from 
          vehicle;

The results should show make, model and year_first_licensed. (You may not get the same rows that are shown here. This is OK.)

       id policy_id  license_number state_where_licensed make       model            year_first_licensed 
       -- ---------- -------------- -------------------- ---------- ---------------- ------------------- 
        1          2 EUKOUU         OH                   GMC        Sierra                          2007 
        2          3 P279SHK        MO                   Cadillac   ELR                             2015 
        3          4 IWD NOV        GA                   Cadillac   Escalade                        2009 
        4          8 JBR QJR        AL                   Ram        ProMaster City                  2007 
        5          9 C480TOW        DE                   Lexus      RX                              2006 
        
       (5 rows affected)

The working table that we used to copy in the new data (vehicle_make_model_year_first_licensed) was dropped by this script, but sqlcmd does not confirm that. It is very important to get rid of working tables when we just finished with them and checked the results. This is the time when we know what happened and we know that we can drop this table. In a week's time we will not be sure and we won't bother to check it all again. This is how databases get clogged with junk. (I've said it before, and I'll say it again...)

migration_003.sql converts the database from Iteration 2 to Iteration 3 by creating the claim_head and claim_transaction tables. Download this file and the SQL scripts it uses:

and run it as before:

      1> :r migration_003.sql
      2> go
      1> select table_name,table_catalog from information_schema.tables where table_type = 'base table';
      2> go
      table_name           table_catalog
      -------------------- -------------
      person               claims
      policy               claims
      vehicle              claims
      claimant             claims
      witness              claims
      driver               claims
      passenger            claims
      damage               claims
      incident             claims
      claim                claims
      claim_head           claims
      claim_transaction    claims

      (12 rows affected)

Neither of these tables have data that can be migrated from earlier iterations. You can download the data: claim_head.csv and claim_transaction.csv, and the load script: load_data_iteration_3.txt. You will need to edit the server name and database name in the bcp commands, as before. Now run the bcp commands:

      bcp claims.dbo.claim_head in claim_head.csv -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T

      Starting copy...

      23 rows copied.
      Network packet size (bytes): 4096
      Clock Time (ms.) Total     : 16     Average : (1437.50 rows per sec.)

      bcp claims.dbo.claim_transaction in claim_transaction.csv -t , -c -F 2 -E -S localhost\MSSQLSERVER01 -T
      Starting copy...
      1000 rows sent to SQL Server. Total sent: 1000
      1000 rows sent to SQL Server. Total sent: 2000
      1000 rows sent to SQL Server. Total sent: 3000
      [...]
      1000 rows sent to SQL Server. Total sent: 168000
      1000 rows sent to SQL Server. Total sent: 169000
      1000 rows sent to SQL Server. Total sent: 170000

      170127 rows copied.
      Network packet size (bytes): 4096
      Clock Time (ms.) Total     : 1000   Average : (170127.00 rows per sec.)

Let's get a summary of what we have now loaded. Download row_counts_iteration_3.sql and run it:

      1> :r row_counts_iteration_3.sql
      2> go
      table_name        row_count
      ----------------- ---------
      person                12538
      policy                 4739
      vehicle               14355
      claim                  6726
      claimant              16211
      witness                3499
      driver                 6093
      passenger               572
      damage                    0
      claim_head               23
      claim_transaction    170127

      (11 rows affected)