The optional on clause allows you to specify where to store the database and how much space, in megabytes, to allocate for it. If you use the keyword default, the database is assigned to an available database device in the pool of default database devices indicated in the master database table sysdevices. Use sp_helpdevice to see which devices are in the default list.
A system administrator may have made certain storage
allocations based on performance statistics and other considerations.
Before creating databases, check with a system administrator.
To specify a size of 5MB for a database to be stored in this default location, use on default = size:
use master create database newpubs on default = 5 drop database newpubs use pubs2
To specify a different location for the database, give the logical name of the database device where you want it stored. You can store a database on more than one database device, with different amounts of space on each.
This example creates the newpubs database and allocates 3MB to it on pubsdata and 2MB on newdata:
create database newpubs on pubsdata = 3, newdata = 2
If you omit the on clause and the size, the database is created with 2MB of space from the pool of default database devices indicated in sysdevices.
A database allocation can range in size from 2MB to 223MB.
