Database Reference
In-Depth Information
Creating Database with Your Own Settings
Sometimes you want to have control over creating a database so you can control things such as the
maximum size a database can grow to, the amount of growth after the initial amount is consumed, or
even the location/folder of your data and log files. Table 3-4 shows the arguments you can use.
Table 3-2.
Create Database Arguments
Argument Name
Description
Name
This is the logical name of the database file, by which it can be referred to in your SQL
Server system.
This is the physical path where the database files (
.mdf
and
.ldf
) are going to be
stored.
Filename
Size
This is the initial size you want to create your database with. The size can be in
kilobytes, megabytes, gigabytes, or terabytes. If you don't specify any unit by default,
it is megabytes. For example, Size = 1 is the same as specifying Size= 1MB.
One limitation of the Size argument is that it has to be a minimum of 4MB initially so
it can accommodate the model database's settings. If you specify a size less than
4MB, then you will receive an error when executing the
create database
statement.
MaxSize
This is the maximum size limit that the database you are creating can grow to. Hence,
if you omit this argument, then you are letting SQL Server continue growing your
database as long as there is a disk space, in other words, until the disk is full. The
MaxSize setting can be in kilobytes, megabytes, gigabytes, or terabytes; the default is
megabytes.
FileGrowth
This is the size your database will grow to whenever new space is required to
accommodate incoming data. As you recall, Size specifies only the initial allocation of
space to begin with. But after that, your database grows dynamically as and when on
demand. This can be specified in percentage or in kilobytes, megabytes, gigabytes, or
terabytes. The default is megabytes. Also, it's worth knowing that if you don't specify
this argument at all, then the default FileGrowth setting is 1MB.
Now let's try the arguments discussed in Table 3-2 in a SQL query syntax to create a database with
your argument values. Follow these steps:
1.
Make sure your New Query pane shows the master database just below the
Save icon on the toolbar.
2.
Go to the query pane, and to make sure you are in the master database, type
the following:
use master
3.
Select the statement, and click Execute or press F5; you should see the
message “Command(s) completed successfully.”
