Professional SQL Server® 2005 Performance Tuning

11.1. Sample Database

This chapter uses a very simple database called People to demonstrate the concepts. The database contains only four tables:

CREATE TABLE people
(
 personId UNIQUEIDENTIFIER DEFAULT newsequentialid(),
 firstname VARCHAR(80) not null,
 lastname VARCHAR(80) not null,
 dob DATETIME not null,
 dod DATETIME null,

sex CHAR(1) not null
)
go
CREATE TABLE boysnames
 (
 ID INT IDENTITY(0,1) not null,
 [name] VARCHAR(80) not null
 )
go
CREATE TABLE girlsnames
 (
 ID INT IDENTITY(0,1) not null,
 [name] VARCHAR(80) not null
 )
go
CREATE TABLE lastnames
 (
 ID INT IDENTITY(0,1) not null,
 [name] VARCHAR(80) not null
 )
go

The scripts to create the database, tables, and stored procedures can be found at www.wrox.com. If you want to follow the examples you should run createDatabase.sql, createTables.sql, createStoredProcedures.sql, and loadNames.sql before continuing.

The key table is people and you want to insert into it as fast as you can. Each insert into this table has to do lookups in three reference tables and then some calculations to determine the rest of the data to be inserted. The three reference tables are a list of male names, a list of female names, and a list of last names.

For reference, here is a description of the stored procedures that are used:

• usp_namesInsert: Randomly generates names for the three reference tables. Used by the loadNames.sql script, which should be run before starting.

• usp_generateDOB: Generates random birth dates within the last 110 years. Used by usp_peopleInsert ...