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 2005, 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.
Database Files and Filegroups
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.
- 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 require multiple secondary data files, or they may use secondary files on separate disk drives to spread the data across multiple disks.
- Transaction logs
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 KB.
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.
File Initialization
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 2005, data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations. For more information, see Database File Initialization.
Database Snapshots
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.
Removable Databases
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.
To create a database
- CREATE DATABASE (Transact-SQL)
- How to: Create a Database (SQL Server Management Studio)
- How to: Create a Database Snapshot (Transact-SQL)
See Also
Concepts
Changing the Database Owner
Understanding Files and Filegroups
Other Resources
Implementing Databases
sys.databases (Transact-SQL)
sys.database_files (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.filegroups (Transact-SQL)