Bigger sample databases

I like Northwind and Pubs. They're cute.
But, if you're trying to train on issues surrounding performance testing,
having a much larger database would be advantageous.
Is there any resources to get larger sample databases from?
Thanks!http://www.microsoft.com/downloads/details.aspx?familyid=487C9C23-2356-436E-94A8-2BFB66F0ABDC
For performance testing there is no substitute for using data that matches
or approximates your own production data. Test data generation ought to be
part of your project plan for new projects.
--
David Portas
SQL Server MVP
--|||David,
Thanks, this is just what I was looking for! Since I'm still working on
an MCDBA cert, I don't really have any "production data" I can use yet. But,
this Adventure Works DB looks like what I needed.
I guess why I need it is that as I train, I want to compare various
options. For instance, which is faster, using subqueries or joins between
tables in T-SQL statements? When you only have 16 records in a table, it's a
little difficult to do "real world analysis" to answer performance questions
like that.
Thanks again!
"David Portas" wrote:
> http://www.microsoft.com/downloads/details.aspx?familyid=487C9C23-2356-436E-94A8-2BFB66F0ABDC
> For performance testing there is no substitute for using data that matches
> or approximates your own production data. Test data generation ought to be
> part of your project plan for new projects.
> --
> David Portas
> SQL Server MVP
> --
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01FD_01C58875.18FCF7B0
Content-Type: text/plain;
format=flowed;
charset="Utf-8";
reply-type=original
Content-Transfer-Encoding: 7bit
Joel wrote:
> I like Northwind and Pubs. They're cute.
> But, if you're trying to train on issues surrounding performance
> testing, having a much larger database would be advantageous.
> Is there any resources to get larger sample databases from?
> Thanks!
BigNW Script attached. Scott's web site is down. See file for comments
related to new database size and file / backup locations.
David Gugick
Quest Software
www.imceda.com
www.quest.com
--=_NextPart_000_01FD_01C58875.18FCF7B0
Content-Type: text/plain;
format=flowed;
name="Create BigNW Script.sql";
reply-type=original
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="Create BigNW Script.sql"
set quoted_identifier off
go
--begin pgp signed message--
/*
*************************************************************************=******
* l i c e n s e *
*************************************************************************=******
(c) copyright 1998-2000, scott mauvais
the software is provided "as-is" and without warranty of any kind, =expressed,
implied or otherwise, including and without limitation, any warranty of
merchantability or fitness for a particular purpose. in no event shall =the
author be liable for any special, incidental, indirect or consequential
damages whatsoever (including, without limitation, damages for loss of
profits,business interruption, loss of information, or any other loss),
whether or not advised of the possibility of damage, and on any theory =of
liability, arisingout of or in connection with the use or inability to =use
this software.
this is an unsupported script. that said, i appreciate hearing about =bugs so i
can address them in future releases. if you have questions or wish to =report a
bug, please send e-mail to sm@.mauvais.com with the word bignw in the
subject. while i can't respond to all messages i will do my best to help =out.
updates, if any, will be posted to =http://www.mauvais.com/downloads/bignw.htm
*************************************************************************=******
* r e l e a s e h i s t o r y *
*************************************************************************=******
0.80 11/14/1999 scott mauvais created.
0.90 02/22/1999 scott mauvais o use temp tables
o add Orders and [Order Details] tables
1.00 03/15/1999 scott mauvais o drop indices/contraints
o store variables in temp table
o configure growth factor
o update db options
2.00 01/23/2000 scott mauvais o clean up formatting and add comments =for
o parallel query article for ziff =davis.
2.10 02/24/2000 scott mauvais o dump/load Northwind rather than =require dts
o test for temp tables before drop
o allow change of source db
o add more robust comments for bignw =article
for ziff davis
*************************************************************************=******
* n o t e s *
*************************************************************************=******
purpose: creates a large copy of the Northwind database for =testing.
input params: none
output params: none
returns: none
usage: execute in isql
called by: interactive user
calls: none
ddl/dml: several. creates new database
local vars: @.sdbname
@.ssourcedb
@.sbackupname
@.sdatapath
@.slogpath
@.sbackuppath
@.sdbsize
@.ssql
@.lhowbig
files created: bignw.mdf in path specified by @.sdatapath
bignw_log.ldf in path specified by @.sdatapath
[backup file] in path specified by @.sbackuppath
files deleted: all files listed in files created
*************************************************************************=******
* c o n f i g u r a t i o n *
*************************************************************************=******
search for "todo:" (no quotes) for areas you need to change to match =your
system. below is a quick overview.
first, you must have the Northwind sample database installed. if you do =not
it installed on your system you can create by running the instnwnd.sql =script
located in the mssql7\install directory.
i called my copy bignw. if you used a different name you will need to =change
the change the name in two places (lines 79 and 276). they are both =marked
with todo:
finally, you need to specify how large you want to make the new =database. you
do this by chaning the @.lhowbig variable (around line 155) which is
marked with a todo: by default, this script increases the customer, =Orders,
and Order Details tables by a factor of 100 and will require around 60 m
if you received or downloaded this from an untrusted source, please =verify
the pgp signature. my finger print is
80a6 3e8b 7a39 4264 e576 2ce5 85e5 7ea2
*************************************************************************=******
*/
set nocount on
declare @.sdbname sysname
declare @.ssourcedb sysname
declare @.sbackupname sysname
declare @.sdatapath varchar(255)
declare @.slogpath varchar(255)
declare @.sbackuppath varchar(255)
declare @.sdbsize varchar(8)
declare @.ssql varchar(2047)
declare @.lhowbig int
-- todo: change these variables
select @.sdbname =3D 'bignw' -- change this to the =name of
-- of the database you =want to
-- create. if you change =this
-- the default 'bignw' =you will
-- also have to change =line 276
select @.lhowbig =3D 100 -- change this to your =desired
-- growth factor. a =factor of 100
-- requires =approximatelym 60 m.
select @.ssourcedb =3D 'Northwind' -- name of source =database.
-- this must have the =same
-- structure of the =standard
-- 'Northwind' database.
select @.sbackupname =3D 'Northwindtobignw' -- *** do not ** use the =name
-- of an existing backup =device
-- because the script =will drop
-- it!
-- name of backup device =the
-- script will drop and =recreate
-- to store its backup =of
-- Northwind.
select @.sdatapath =3D 'c:\program files\microsoft sql =server\mssql$sp2_650Patch\data\' -- 'c:\mssql7\data\' -- path where you =want to
-- place the data file =for
-- bignw
select @.slogpath =3D 'c:\program files\microsoft sql =server\mssql$sp2_650Patch\data\' -- 'c:\mssql7\data\' -- path where you =want to
-- place the log for =bignw
select @.sbackuppath =3D 'c:\program files\microsoft sql =server\mssql$sp2_650Patch\backup\' -- 'c:\mssql7\backup\' -- path your =backup location.
-- the script makes a =copy of
-- Northwind and will =place it
-- here.
select @.sdbsize =3D '60' -- size in mb to use =when
-- the new database.
/*
/////////////////////////////////////////////////////////////////////////=//////
make sure temp tables deleted
/////////////////////////////////////////////////////////////////////////=//////
*/
if object_id('tempdb..#bignw_vars') is not null
drop table #bignw_vars
if object_id('tempdb..#bignw_orddetorig') is not null
drop table #bignw_orddetorig
if object_id('tempdb..#bignw_orddet') is not null
drop table #bignw_orddet
if object_id('tempdb..#bignw_ord') is not null
drop table #bignw_ord
if object_id('tempdb..#bignw_cust') is not null
drop table #bignw_cust
-- save db name and how big so we can use it later
create table #bignw_vars (dbname sysname, howbig int)
insert into #bignw_vars (dbname, howbig) values (@.sdbname, @.lhowbig)
/*
/////////////////////////////////////////////////////////////////////////=//////
create database
/////////////////////////////////////////////////////////////////////////=//////
*/
-- drop the database if it already exists
-- notice that i am dynmaically building the sql statement and use exec =to
-- avoid an 226
use tempdb
select @.ssql =3D "if exists (select * from master..sysdatabases
where name =3D '" + @.sdbname + "' )
exec (""drop database " + @.sdbname + """) "
exec (@.ssql )
-- start off with just 3mb because that is all that Northwind uses and =the
-- load will make the new database the same size as the old one. we =will
-- expand it later.
select @.ssql =3D "
create database " + @.sdbname + "
on (
name =3D 'bignw_data',
filename =3D '" + @.sdatapath + @.sdbname + "_data.mdf',
size =3D 3mb
)
log on (
name =3D 'bignw_log',
filename =3D '" + @.slogpath + @.sdbname + "_log.ldf',
size =3D 5mb
)"
exec (@.ssql)
-- drop dump device if it exists
select @.ssql =3D "if exists (select * from master.dbo.sysdevices
where name =3D '" + @.sbackupname + "'
and status =3D 16)
exec (""sp_dropdevice '" + @.sbackupname + "'"")"
exec (@.ssql)
-- add dump device
select @.ssql =3D "sp_addumpdevice 'disk', '" + @.sbackupname + "',
'" + @.sbackuppath + @.sbackupname + =".bak'"
exec (@.ssql)
-- dump Northwind to new device
select @.ssql =3D "dump database " + @.ssourcedb + " to " + @.sbackupname + =" with init"
exec (@.ssql)
-- restore the original Northwind
select @.ssql =3D
"load database " + @.sdbname + " from disk =3D
'" + @.sbackuppath + @.sbackupname + ".bak' with replace,
move '" + @.ssourcedb + "_log' to '" + @.sdatapath + =@.sdbname + "_log.ldf',
move '" + @.ssourcedb + "' to '" + @.sdatapath + =@.sdbname + ".mdf'"
exec (@.ssql)
-- set the db options to maximize performance as we insert lots of data
select @.ssql =3D 'sp_dboption "' + @.sdbname + '",
"auto update statistics", "false" '
exec (@.ssql)
select @.ssql =3D 'sp_dboption "' + @.sdbname + '",
"select into/bulkcopy", "false" '
exec (@.ssql)
select @.ssql =3D 'sp_dboption "' + @.sdbname + '",
"trunc. log on chkpt", ="false" '
exec (@.ssql)
-- alter database to increase to desired size.
-- notice that the file name is back to Northwind because of the =restore.
select @.ssql =3D "alter database " + @.sdbname + " modify file
(name =3D " + @.ssourcedb + ", size =3D " + =@.sdbsize + ")"
exec (@.ssql)
go
/*
/////////////////////////////////////////////////////////////////////////=//////
make bignw the current db
/////////////////////////////////////////////////////////////////////////=//////
*/
-- todo:
-- if you changed default the name of the database this script creates =you
-- must update the line to use that database. make sure the name here =matches
-- the on on line 79
use bignw
/*
/////////////////////////////////////////////////////////////////////////=//////
drop indices and constraints
/////////////////////////////////////////////////////////////////////////=//////
*/
-- Order Details
drop index [Order Details].OrderID
drop index [Order Details].OrdersOrder_Details
drop index [Order Details].ProductID
drop index [Order Details].ProductsOrder_Details
alter table [Order Details] drop constraint PK_Order_Details
alter table [Order Details] drop constraint FK_Order_Details_Orders
alter table [Order Details] drop constraint FK_Order_Details_Products
-- Orders
drop index Orders.CustomerID
drop index Orders.CustomersOrders
drop index Orders.EmployeeID
drop index Orders.EmployeesOrders
drop index Orders.OrderDate
drop index Orders.ShippedDate
drop index Orders.ShippersOrders
drop index Orders.ShipPostalCode
alter table Orders drop constraint PK_Orders
alter table Orders drop constraint FK_Orders_Customers
alter table Orders drop constraint FK_Orders_Employees
alter table Orders drop constraint FK_Orders_Shippers
--custmercustomerdemo
alter table CustomerCustomerDemo
drop constraint FK_CustomerCustomerDemo_Customers
alter table CustomerCustomerDemo
drop constraint PK_CustomerCustomerDemo
-- Customers
alter table Customers drop constraint PK_Customers
drop index Customers.City
drop index Customers.CompanyName
drop index Customers.PostalCode
drop index Customers.Region
/*
/////////////////////////////////////////////////////////////////////////=//////
alter tables
/////////////////////////////////////////////////////////////////////////=//////
*/
-- Customers
if exists (select * from syscolumns w
where name =3D 'cycle' and id =3D =object_id('Customers'))
alter table Customers drop column cycle
alter table Customers add cycle int
alter table Customers alter column CustomerID varchar(11) not null
--CustomerCustomerDemo
alter table CustomerCustomerDemo alter column CustomerID varchar(11) =not null
-- Orders
if exists (select * from syscolumns
where name =3D 'cycle' and id =3D =object_id('Orders'))
alter table Orders drop column cycle
alter table Orders add cycle int
alter table Orders alter column CustomerID varchar(11) not null
-- Order Details
if exists (select * from syscolumns
where name =3D 'cycle' and id =3D object_id('Order =Details'))
alter table [Order Details] drop column cycle
alter table [Order Details] add cycle int
go
/*
/////////////////////////////////////////////////////////////////////////=//////
increase size of tables
/////////////////////////////////////////////////////////////////////////=//////
*/
declare @.i int
declare @.lhowbig int
select @.lhowbig =3D howbig from #bignw_vars
-- start counter off at zero
select @.i =3D 0
delete from Customers where cycle is not null
select * into #bignw_cust from Customers
update #bignw_cust set cycle =3D @.i
while @.i < @.lhowbig
begin
select @.i =3D @.i + 1
update #bignw_cust set CustomerID =3D left(CustomerID, 5)
+ cast(@.i as varchar(5)), cycle ==3D @.i
insert Customers select * from #bignw_cust
print 'customer passes complete: ' + cast(@.i as varchar(5))
end
-- Orders
select @.i =3D 0
delete from Orders where cycle is not null
select CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, =ShipVia,
Freight, ShipName, ShipAddress, ShipCity, ShipRegion, =ShipPostalCode,
ShipCountry, cycle
into #bignw_ord from Orders
update #bignw_ord set cycle =3D @.i
while @.i < @.lhowbig
begin
select @.i =3D @.i + 1
update #bignw_ord set CustomerID =3D left(CustomerID, 5)
+ cast(@.i as varchar(5)), cycle ==3D @.i
insert Orders select * from #bignw_ord
print 'Orders passes complete: ' + cast(@.i as varchar(5))
end
-- Order Details
select @.i =3D 0
delete from [Order Details] where cycle is not null
-- make two copies of Order Details
-- one, orddet, that we will update and insert back into
-- the original table. we will use the other, orddetorig,
-- to refresh orddet after each cycle
select * into #bignw_orddet from [Order Details]
select * into #bignw_orddetorig from [Order Details]
update #bignw_orddet set cycle =3D @.i
while @.i < @.lhowbig
begin
select @.i =3D @.i + 1
-- refresh orddet temp table with original [Order Details]
-- table. we need to do this because we need the original values in
-- the OrderID column
truncate table #bignw_orddet
insert into #bignw_orddet select * from #bignw_orddetorig where =cycle is null
-- 830 Orders
update #bignw_orddet set OrderID =3D (OrderID + (830 * @.i)), cycle ==3D @.i
insert [Order Details] select * from #bignw_orddet
print 'Order Details passes complete: ' + cast(@.i as varchar(5))
-- if you are short on disk space, you can issue a
-- checkpoint here to truncate the log
--checkpoint
end
/*
/////////////////////////////////////////////////////////////////////////=//////
re-create indices and contraints
/////////////////////////////////////////////////////////////////////////=//////
*/
-- customer
alter table Customers with check add constraint PK_Customers
primary key clustered (CustomerID) on [default]
create index City on Customers (City) on [default]
create index CompanyName on Customers (CompanyName) on [default]
create index PostalCode on Customers (PostalCode) on [default]
create index Region on Customers (Region) on [default]
-- Orders
alter table Orders with check add constraint PK_Orders
primary key clustered (OrderID) on =[default]
create index CustomerID on Orders (CustomerID) on =[default]
create index CustomersOrders on Orders (CustomerID) on =[default]
create index EmployeeID on Orders (EmployeeID) on =[default]
create index EmployeesOrders on Orders (EmployeeID) on =[default]
create index OrderDate on Orders (OrderDate) on =[default]
create index ShippedDate on Orders (ShippedDate) on =[default]
create index ShippersOrders on Orders (ShipVia) on =[default]
create index ShipPostalCode on Orders (ShipPostalCode) on =[default]
alter table Orders with check add constraint FK_Orders_Customers
foreign key (CustomerID) references Customers (CustomerID)
alter table Orders with check add constraint FK_Orders_Employees
foreign key (EmployeeID) references Employees (EmployeeID)
alter table Orders with check add constraint FK_Orders_Shippers
foreign key (ShipVia) references Shippers (ShipperID)
-- Order Details
alter table [Order Details] with check add constraint PK_Order_Details
primary key clustered (OrderID, =ProductID)
on [default]
create index OrderID on [Order Details] (OrderID) on =[default]
create index OrdersOrder_Details on [Order Details] (OrderID) on =[default]
create index ProductID on [Order Details] (ProductID) on =[default]
create index ProductsOrder_Details on [Order Details] (ProductID) on =[default]
alter table [Order Details] with check add constraint =FK_Order_Details_Orders
foreign key (OrderID) references Orders (OrderID)
alter table [Order Details] with check add constraint =FK_Order_Details_Products
foreign key (ProductID) references Products (ProductID)
-- CustomerCustomerDemo
alter table CustomerCustomerDemo with check add constraint =PK_CustomerCustomerDemo
primary key nonclustered (CustomerID, CustomerTypeID) on =[default]
alter table CustomerCustomerDemo add constraint =FK_CustomerCustomerDemo_Customers
foreign key (CustomerID) references Customers (CustomerID)
/*
/////////////////////////////////////////////////////////////////////////=//////
reset db options and clean up
/////////////////////////////////////////////////////////////////////////=//////
*/
declare @.sdbname sysname
declare @.ssql varchar(255)
-- retrieve database name from temp table
select @.sdbname =3D dbname from #bignw_vars
select @.ssql =3D 'sp_dboption "'+ @.sdbname + '", "auto update =statistics", "true" '
exec (@.ssql)
select @.ssql =3D 'sp_dboption "'+ @.sdbname + '", "select =into/bulkcopy", "true" '
exec (@.ssql)
select @.ssql =3D 'sp_dboption "'+ @.sdbname + '", "trunc. log on chkpt", = "true" '
exec (@.ssql)
exec ('sp_updatestats')
-- drop temp tables
if object_id('tempdb..#bignw_vars') is not null
drop table #bignw_vars
if object_id('tempdb..#bignw_orddetorig') is not null
drop table #bignw_orddetorig
if object_id('tempdb..#bignw_orddet') is not null
drop table #bignw_orddet
if object_id('tempdb..#bignw_ord') is not null
drop table #bignw_ord
if object_id('tempdb..#bignw_cust') is not null
drop table #bignw_cust
-- return a to a "safe" location
use tempdb
--begin pgp signature--
/*
iqcvawubolbdevzh9pyexhmzaqgscwp9fonrtcqzpcjitqgsolhrmzm6gyfen8hp
s3xbqybxn4yybe7djecestq7tyxagle3m7whjmkg3oxino8iqm77ngmgun2h26q1
mpn1spcnps1sptblzwhneitf7wu5slyev/dfj2npghxfegfxx5wmdnssujrgxmei
lel9dwlyoam=3D
=3Doss8
*/
--end pgp signature--
--=_NextPart_000_01FD_01C58875.18FCF7B0--|||I get to this newsgroup by browsing to:
http://msdn.microsoft.com/newsgroups/default.aspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.server&fltr=&mid=336fd7f5-5023-4c73-b24e-2e34cd6ed6f7&tid=974e2c9a-2bc0-4dcc-b6c6-099a858cd211
...and find this post currently on page 4. I don't see anything for
attachments, just the text you wrote. How do I get the attachment?
Thanks!
"David Gugick" wrote:
> Joel wrote:
> > I like Northwind and Pubs. They're cute.
> >
> > But, if you're trying to train on issues surrounding performance
> > testing, having a much larger database would be advantageous.
> >
> > Is there any resources to get larger sample databases from?
> >
> > Thanks!
> BigNW Script attached. Scott's web site is down. See file for comments
> related to new database size and file / backup locations.
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest|||Joel wrote:
> I get to this newsgroup by browsing to:
> http://msdn.microsoft.com/newsgroups/default.aspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.server&fltr=&mid=336fd7f5-5023-4c73-b24e-2e34cd6ed6f7&tid=974e2c9a-2bc0-4dcc-b6c6-099a858cd211
> ...and find this post currently on page 4. I don't see anything for
> attachments, just the text you wrote. How do I get the attachment?
>
Use a real news reader :-)
I see it from Outlook Express... The ng server name is
msnews.microsoft.com
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||David,
Well, our company uses a standard desktop configuration, that doesn't
have Outlook or Outlook Express. There are no Exchange servers, as they use
Lotus Notes / Domino as their e-mail platform (Grrrr...). They seem to have
Internet news groups in Notes blocked as well (Grrrr....).
Is there any other way to get the script you're talking about? A web or
ftp site perhaps?
Thanks again...
"David Gugick" wrote:
> Joel wrote:
> > I get to this newsgroup by browsing to:
> >
> > http://msdn.microsoft.com/newsgroups/default.aspx?pg=4&lang=en&cr=US&guid=&sloc=en-us&dg=microsoft.public.sqlserver.server&fltr=&mid=336fd7f5-5023-4c73-b24e-2e34cd6ed6f7&tid=974e2c9a-2bc0-4dcc-b6c6-099a858cd211
> >
> > ...and find this post currently on page 4. I don't see anything for
> > attachments, just the text you wrote. How do I get the attachment?
> >
> Use a real news reader :-)
> I see it from Outlook Express... The ng server name is
> msnews.microsoft.com
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>|||Joel wrote:
> David,
> Well, our company uses a standard desktop configuration, that
> doesn't have Outlook or Outlook Express. There are no Exchange
> servers, as they use Lotus Notes / Domino as their e-mail platform
> (Grrrr...). They seem to have Internet news groups in Notes blocked
> as well (Grrrr....).
>
Send me an email. Remove the nospam from the email address.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Joel wrote:
> Is there any other way to get the script you're talking about?
> A web or ftp site perhaps?
Use Google Groups as a newsreader:
http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/7cbe2b7e84054e82
Razvan|||David,
Razvan Socol's post about using the Google news groups helped me. I
was able to get the script from your previous post.
Thanks for your help and your patience!
Joel
David Gugick wrote:
> Joel wrote:
> > David,
> > Well, our company uses a standard desktop configuration, that
> > doesn't have Outlook or Outlook Express. There are no Exchange
> > servers, as they use Lotus Notes / Domino as their e-mail platform
> > (Grrrr...). They seem to have Internet news groups in Notes blocked
> > as well (Grrrr....).
> >
> Send me an email. Remove the nospam from the email address.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com