Large sample database
-
For an article I want to write I need a large sample database. A bit like Northwind but with many records. I could have based in on Northwind but I'd like to be able to use it (for comparison) on any database and not being in violation of Microsoft licenses. I therefore wrote a script that create a few tables from scratch and a lot of data into it. If you're mad enough to try it (you got the source below). This is pretty much textbook to speak to database beginners but hopefully with enough relationship to make interesting queries. It will create 5 classic tables (record count in the box):
+- Client -+ +--Order----+ | 100,000 |<---| 500,000 | +----------+ +-----------+ | ^ | | v | +- Occupation-+ | | 330 | | +-------------+ | | | |+- Product -+ +--OrderLine--+
| 1,554 |<----------| 3,137,991 |
+-----------+ +-------------+I hope you appeciate this fantastic Ascii-Art I am trying to have a shortish script (a few kilobytes) that can quickly generate enough data to show the impact of indexes. The script takes 6 minutes to run on my laptop I worked hard on faking meaningful data. This is one the first client looks like:
ClientId 1
FirstName: Jimmy
MiddleName: Lawrence
LastName: Mcpherson
Gender: M
DateOfBirth: 24:28.8
CreditRisk: 2
XCode: AZ3 1KN
OccupationId: 228
TelephoneNumber: (123) 822 - 3268
Street1: 468 Old Barnes Street
Street2: 2nd Floor
City: Sacramento
ZipCode: 83319
Longitude: 38.46202086
Latitude: 120.999543
Notes: In aute sunt et ea sed ex tempor. Et ex non eu anim ad. Id velit aliqua. Elit id do qui quis. Sed fugiat ex. Sunt ipsum ut eu ad sint. Enim magna animus lorem eu eu culpa. Sint nostrud irure ipsum amet amet. Sed do mollit irure nisi. Elit ut sint est ea culpa. Sit aute enim culpa deserunt ad ea. Eu velit et ex minim ad. Eu do enim non ad ad. Exclamo amet est. Nulla enim ex. Do ad ea ullamco ex. Magna excepteur ipsum ad non sunt eu non. Animus ut elit. Ut et est aute. Do cillum non ad sunt do enim. Ex non ea ea. Et id id sint duis. In in et. Id duis ipsum. Aute velit ut. Amet enim velit elit. Id occaecat ea. Sunt minim exclamo. Do ad et id id ipsum lorem. Qui qui do sunt ad. In exclamo do. Ipsum ad sed. Et ex ea. Et ullamco -
For an article I want to write I need a large sample database. A bit like Northwind but with many records. I could have based in on Northwind but I'd like to be able to use it (for comparison) on any database and not being in violation of Microsoft licenses. I therefore wrote a script that create a few tables from scratch and a lot of data into it. If you're mad enough to try it (you got the source below). This is pretty much textbook to speak to database beginners but hopefully with enough relationship to make interesting queries. It will create 5 classic tables (record count in the box):
+- Client -+ +--Order----+ | 100,000 |<---| 500,000 | +----------+ +-----------+ | ^ | | v | +- Occupation-+ | | 330 | | +-------------+ | | | |+- Product -+ +--OrderLine--+
| 1,554 |<----------| 3,137,991 |
+-----------+ +-------------+I hope you appeciate this fantastic Ascii-Art I am trying to have a shortish script (a few kilobytes) that can quickly generate enough data to show the impact of indexes. The script takes 6 minutes to run on my laptop I worked hard on faking meaningful data. This is one the first client looks like:
ClientId 1
FirstName: Jimmy
MiddleName: Lawrence
LastName: Mcpherson
Gender: M
DateOfBirth: 24:28.8
CreditRisk: 2
XCode: AZ3 1KN
OccupationId: 228
TelephoneNumber: (123) 822 - 3268
Street1: 468 Old Barnes Street
Street2: 2nd Floor
City: Sacramento
ZipCode: 83319
Longitude: 38.46202086
Latitude: 120.999543
Notes: In aute sunt et ea sed ex tempor. Et ex non eu anim ad. Id velit aliqua. Elit id do qui quis. Sed fugiat ex. Sunt ipsum ut eu ad sint. Enim magna animus lorem eu eu culpa. Sint nostrud irure ipsum amet amet. Sed do mollit irure nisi. Elit ut sint est ea culpa. Sit aute enim culpa deserunt ad ea. Eu velit et ex minim ad. Eu do enim non ad ad. Exclamo amet est. Nulla enim ex. Do ad ea ullamco ex. Magna excepteur ipsum ad non sunt eu non. Animus ut elit. Ut et est aute. Do cillum non ad sunt do enim. Ex non ea ea. Et id id sint duis. In in et. Id duis ipsum. Aute velit ut. Amet enim velit elit. Id occaecat ea. Sunt minim exclamo. Do ad et id id ipsum lorem. Qui qui do sunt ad. In exclamo do. Ipsum ad sed. Et ex ea. Et ullamcoTry Google for a large test database. I have seen various ones over the years but cannot remember where.