Creating a Database | Inside Microsoft SQL Server 2000

The easiest way to create a database is to use SQL Server Enterprise Manager, which provides a graphical front end to Transact-SQL commands and stored procedures that actually create the database and set its properties. Figure 5-1 shows the SQL Server Enterprise Manager Database Properties dialog box, which represents the Transact-SQL CREATE DATABASE command for creating a new user database. Only someone with the sysadmin role or a user who's been granted CREATE DATABASE permission by someone with the sysadmin role can issue the CREATE DATABASE command.

When you create a new user database, SQL Server copies the model database, which—as you just learned—is simply a template database. For example, if you have an object that you would like created in every subsequent user database, create that object in model first. (You can also use model to set default database options in all subsequently created databases.) The model database also includes 19 system tables and 2 system views, which means that every new database also includes these 21 system objects. SQL Server uses these objects for the definition and maintenance of each database. The two system views are provided for backward compatibility and have no current functionality. They mimic information that was available in earlier versions of SQL Server.

click to view at full size.

Figure 5-1. The Database Properties dialog box, which creates a new database called newdb.

The system objects have names starting with sys. If you haven't added any other objects to model, these 21 system objects will be the entire contents of a newly created database. Every Transact-SQL command or system stored procedure that creates, alters, or drops a SQL Server object will result in entries being made to system tables.

WARNING
Do not directly modify the system tables. You might render your database unusable by doing so. Direct modification is prevented by default: a system administrator must take deliberate action via the sp_configure stored procedure to allow system tables to be modified directly. I'll discuss system tables in more detail in Chapter 6.

A new user database must be 1 MB or greater in size, and the primary data file size must be at least as large as the primary data file of the model database. Almost all the possible arguments to the CREATE DATABASE command have default values so that it's possible to create a database using a simple form of CREATE DATABASE, such as this:

 CREATE DATABASE newdb

This command creates the newdb database, with a default size, on two files whose logical names—newdb and newdb_log—are derived from the name of the database. The corresponding physical files, newdb.mdf and newdb_log.ldf, are created in the default data directory (as determined at the time SQL Server was installed).

NOTE
If you use Enterprise Manager to create a database called newdb, the default logical and physical names will be different than if you use the CREATE DATABASE command. Enterprise Manager will give the data file the logical name of newdb_Data (instead of just newdb), and the physical file will have the name newdb_data.mdf.

The SQL Server login account that created the database is known as the database owner, and has the user name DBO when using this database. The default size of the data file is the size of the primary data file of the model database, and the default size of the log file is half a megabyte. Whether the database name, newdb, is case sensitive depends on the sort order you chose during setup. If you accepted the default, the name is case insensitive. (Note that the actual command CREATE DATABASE is case insensitive, regardless of the case sensitivity chosen for data.)

Other default property values apply to the new database and its files. For example, if the LOG ON clause is not specified but data files are specified, a log file is automatically created with a size that is 25 percent of the sum of the sizes of all data files.

For the files, if the MAXSIZE clause isn't specified, the file will grow until the disk is full. (In other words, the file size is considered unlimited.) The values supplied for SIZE, MAXSIZE, and FILEGROWTH can be specified in units of TB, GB, MB (the default), or KB. The FILEGROWTH property can also be specified as a percentage. A value of 0 for FILEGROWTH indicates no growth. If no FILEGROWTH value is specified at all, the default value is 10 percent, and the minimum value is 64 KB.

A CREATE DATABASE Example

The following is a complete example of the CREATE DATABASE command, specifying three files and all the properties of each file:

 CREATE DATABASE Archive ON PRIMARY ( NAME = Arch1, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archdat2.ndf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = Archlog1, FILENAME = 'c:\program files\microsoft sql server\mssql\data\archlog1.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20)