DATABASE

DATABASE

The creation of a database involves naming the database, and determining the size of the database And the files used to store the database.

A database can be created by specifying options in SQL Server Enterprise Manager or by using the CREATE DATABASE statement. The transaction log is also created while creating a database. Many factors determine the size of a database the number of objects that are part of the database, the number of records, the record length, and the expected number of records in the database.

While creating a database, the size of the data files should be based on the maximum amount of data expected in the database. It is advisable to allow the data files to grow automatically. A maximum data file size is also specified while creating a database. If the maximum size is not specified, a file can continue to grow until it uses all the available space on the disk. The size of an already created file can be increased later. A value of zero for the growth increment indicates no growth.

Syntax

CREATE DATABASE database_name
[ON {[PRIMARY (UAME=logical_file_name,
FILENAME^ 'os_file_name'
[,S\ZE=size]
[,MAXSIZE=max_s/ze]
[,F\LEGROWTH growthJncrement])
[LOG ON
{(UAME=logicaI_file_name,
F\LEHAME='os_file_name'
[MAXS\ZE=max_size]
[,BLEGROW\'H=growth_increment])
}[.......n]
}
[FOR RESTORE]
Creating Databases 4.5

where database_name is the name of the new database. ON specifies the disk files used to store the data portion of the database (data files).
PRIMARY specifies the associated list that defines the files in the primary filegroup. NAN\E=logical_file_name specifies the logical name for the file.
FiLENAME=os_/7/e_/7ame specifies the operating-system file name for the file.
SIZE-s/ze specifies the initial size of the file defined in the list.
MAXSIZE=max_s/ze specifies the maximum size to which the file defined in the list can F!LEGROWTH=gfrowt/7_/V?cremenf specifies the growth increment of the file defined in the
Example
CREATE DATABASE Employee ON
( NAME = employee_dat, FILENAME = 'c:\SQL\employee.mdf, SIZE =12,
MAXSIZE = 100, FILEGROWTH - 2 ) LOG ON
( NAME = 'employee_log', FILENAME = 'c:\SQL\employee.ldf, SIZE - 4 MB, MAXSIZE = 50 MB,
FILEGROWTH = 2 MB )
creates a database Employee with one data file and one log file.

Modifying a Database

The changes that can be made to an existing database are:

The data and log files can be added or removed.
The database can be made to expand or shrink.
Filegroups can be added to the database.
The configuration settings for the database can be changed.