Using Sample Data

Once your data model is in a benchmark environment, you need to load sample data to test performance. You can use either of two methods to accomplish this. You can spend money to purchase a good product such as TestBase to load the data. Products like this work wonders on loading any given amount of records in a database. You can also use the free method of generating a CROSS JOIN query, which requires slightly more work, but offers essentially the same result.

Using a CROSS JOIN

As a simple example, I've taken a list of cartoon characters, for which I can create a list of every combination of their first and last names by using a CROSS JOIN. The following query by Neil Boyle (http://www.sqlservercentral.com/columnists/nboyle/) generates a list of the Flintstone cartoon characters:

SELECT * FROM (     SELECT 'Fred' as fName union      SELECT 'Wilma' union     SELECT 'Barney' union     SELECT 'Betty' ) as flintstones_1 CROSS JOIN (     SELECT 'Flintstone' as lName union     SELECT 'Rubble'  ) as flintstones_2

Although some of the data is bogus, the result looks like this:

fName  lName       ------ ----------  Barney Flintstone Betty  Flintstone Fred   Flintstone Wilma  Flintstone Barney Rubble Betty  Rubble Fred   Rubble Wilma  Rubble (8 row(s) affected) 

To create additional combinations, simply add more names to the first or last name list. If you add an additional last name, you have 12 results.

For a more practical example, I can load some sample data into the authors table of the Pubs database. The following query inserts 150 combinations into the authors table:

INSERT authors  SELECT au_id1 + '-' + au_id2 as au_ud,      fName,      lName,     au_id1 + ' 5' + au_id2 as phone,     'Test address for ' + fName + ' ' + lName,      'London',     'UK',     '12345',     1 FROM (     SELECT '009' as au_id1, 'Fred' as fName union      SELECT '010', 'Wilma' union     SELECT '012', 'Barney' union     SELECT '013', 'Betty' union     SELECT '014', 'Al' union     SELECT '015', 'Peggy' union     SELECT '016', 'Frasier' union     SELECT '017', 'Niles' union     SELECT '018', 'Homer' union     SELECT '019', 'Marge' union     SELECT '020', 'Hawkeye' union     SELECT '021', 'Bob' union     SELECT '024', 'Sam' union     SELECT '025', 'Diane' union     SELECT '026', 'Rebecca' ) as test_authors_part_1 CROSS JOIN (SELECT '55-0010' as au_id2, 'Flintstone' as lName union     SELECT '55-0021', 'Rubble' union     SELECT '55-0022', 'Bundy' union     SELECT '55-0023', 'Crane' union     SELECT '55-0024', 'Simpson' union     SELECT '55-0025', 'Pierce' union     SELECT '55-0026', 'Bush' union     SELECT '55-0028', 'Malone' union     SELECT '55-0029', 'Chambers' union     SELECT '55-0030', 'Howe' ) as test_authors_part_2 (1)Finding Duplicate Rows 

When you're trying to debug why records won't load because of a primary key constraint, you have to determine where your data is being duplicated. To do this, you can use a very easy query that uses the HAVING clause. Simply include the columns you'd like to check for duplication in your SELECT and GROUP BY statements, as shown here:

SELECT <columns to check> FROM <table name> GROUP BY <columns to check> HAVING count(*) > 1

To check for duplication in the Headline and Lead columns of the Articles table, use the following syntax:

SELECT Headline, Lead FROM Articles GROUP BY Headline, Lead HAVING count(*) > 1

This returns all the duplicate records in your table, returning each duplicated record only one time. You can also figure out how many times the row has been repeated by using the following syntax:

SELECT Headline, Lead, count(*) as Occurrences FROM Articles GROUP BY Headline, Lead HAVING count(*) > 1

If you want to figure out which records are repeated more than once, increase the number 1 to the number of occurrences that is appropriate.

Note 

The above query works on SELECT statements that don't contain text, ntext, image, or bit columns, since these items can't be grouped.




SQL Server 2000 for Experienced DBAs
ppk on JavaScript, 1/e
ISBN: 72227885
EAN: 2147483647
Year: 2005
Pages: 126

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net