Creating a Database (Database Engine)
To create a database, you have to determine the name of the database, its owner, its size, and the files and filegroups used to store it.
Before creating a database, you should consider the following:
To create a database, you must, at a minimum, have CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permission.
In SQL Server, certain permissions are set on the data and log files of each database. The permissions prevent the files from being accidentally tampered with if they reside in a directory that has open permissions. For more information, see Securing Data and Log Files.
The user who creates the database becomes the owner of the database.
A maximum of 32,767 databases can be created on an instance of SQL Server.
The name of the database must follow the rules specified for Identifiers.
All user-defined objects in the model database are copied to all newly created databases. You can add any objects, such as tables, views, stored procedures, and data types, to the model database to be included in all newly created databases. For more information, see model Database.
Three types of files are used to store a database. These include primary files, secondary files, and transaction logs. Your database must have a primary data file and at least one transaction log file. You can optionally create one or more secondary data files and additional transaction log files.
For more information, see Designing Files and Filegroups.
When you create a database, make the data files as large as possible, based on the maximum amount of data you expect in the database. For more information, see Using Files and Filegroups to Manage Database Growth.
Data and log files are initialized to overwrite any existing data left on the disk from previously deleted files. These files are also initialized and filled with zeros when you perform one of the following operations:
Create a database
Add files to an existing database
Increase the size of an existing file
Restore a database or filegroup
In SQL Server, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. For more information, see Database File Initialization.
You can use the CREATE DATABASE statement to create a read-only static view, called a database snapshot, of an existing database, the source database. A database snapshot is consistent transaction-wise with the source database as it existed at the time of the snapshot's creation. A source database can have multiple snapshots. For more information, see Database Snapshots.
You can create a database for read-only purposes that can then be distributed through removable media such as CD-ROM. This can be useful for distributing large databases that contain history data such as a database that consists of detailed sales data for a particular year. For more information, see Creating a Removable Database.