The on Clause

Use the on clause to specify where to store a database and how much space, in megabytes, to allocate for the database.

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.

Note: 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 2²³MB.