Creating a Database

To create a database determine the name of the database, its owner (the user who creates the database), its size, and the files and filegroups used to store it.

Before creating a database, consider that:

• Permission to create a database defaults to members of the sysadmin and dbcreator fixed server roles, although permissions can be granted to other users.
  
  
• The user who creates the database becomes the owner of the database.
  
  
• A maximum of 32,767 databases can be created on a server.
  
  
• The name of the database must follow the rules for identifiers.

Three types of files are used to store a database:

• Primary files

  These files contain the startup information for the database. The primary files are also used to store data. Every database has one primary file.

• Secondary files

  These files hold all the data that does not fit in the primary data file. Databases do not need secondary data files if the primary file is large enough to hold all the data in the database. Some databases may be large enough to need multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.

• Transaction log

  These files hold the log information used to recover the database. There must be at least one transaction log file for each database, although there may be more than one. The minimum size for a log file is 512 kilobytes (KB).

  Important  Microsoft® SQL Server™ 2000 data and transaction log files must not be placed on compressed file systems or a remote network drive, such as a shared network directory.

When a database is created, all the files that comprise the database are filled with zeros to overwrite any existing data left on the disk by previously deleted files. Although this means that the files take longer to create, this action prevents the operating system from having to fill the files with zeros when data is written to the files for the first time during usual database operations. This improves the performance of day-to-day operations.

It is recommended that you specify a maximum size to which the file is permitted to grow. This prevents the file from growing, as data is added, until disk space is exhausted. To specify a maximum size for the file, use the MAXSIZE parameter of the CREATE DATABASE statement or the Restrict filegrowth (MB) option when using the  Properties dialog box in SQL Server Enterprise Manager to create the database.

After you create a database, it is recommended that you create a backup of the master database.

To create a database

Transact-SQL