Creating a Database

The easiest way to create a database is to use Object Explorer in SQL Server Management Studio, which provides a graphical front end to the Transact-SQL commands and stored procedures that actually create the database and set its properties. Figure 4-1 shows the New Database dialog box, which represents the Transact-SQL CREATE DATABASE command for creating a new user database. Only someone with the appropriate permissions can create a database, either through Object Explorer or by using the CREATE DATABASE command. This includes anyone in the sysadmin role, anyone who has been granted CONTROL or ALTER permission on the server, and any user who has been granted CREATE DATABASE permission by someone with the sysadmin or dbcreator role.

Figure 4-1. The New Database dialog box, where you can create a new database

When you create a new database, SQL Server copies the model database. If you have an object that you want created in every subsequent user database, you should create that object in model first. You can also use model to set default database options in all subsequently created databases. The model database includes 47 objects41 system tables and 6 objects used for SQL Server Notification Services and Service Broker. You can see these objects by selecting from the system table called sys.objects. However, if you run the procedure sp_help in the model database, it will list 1788 objects. It turns out that most of these objects are not really stored in the model database but are accessible through it. In Chapter 6, I'll tell you what the other kinds of objects are and how you can tell whether an object is really stored in a particular database. Most of the objects you see in model will show up when you run sp_help in any database, but your user databases will probably have more objects added to this list. The contents of model are just the starting point.

A new user database must be 1 MB in size or larger, and the primary data file size must be at least as large as the primary data file of the model database. (The model database only has one file and cannot be altered to add more. So the size of the primary data file and the size of the database are basically the same for model.) Almost all the possible arguments to the CREATE DATABASE command have default values, so 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 namesnewdb and newdb_logare 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).

The SQL Server login account that created the database is known as the database owner, and that information is stored with the information about the database properties in the master database. A database can have only one actual owner, who always corresponds to a login name. Any login that uses any database has a user name in that database, which might be the same name as the login name but doesn't have to be. The login that is the owner of a database always has the special user name dbo when using the database it owns. I'll discuss database users later in this chapter when I tell you about the basics of database security. 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 1 MB. 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, SQL Server creates a log file with a size that is 25 percent of the sum of the sizes of all data files.

If the MAXSIZE clause isn't specified for the files, the file will grow until the disk is full. (In other words, the file size is considered unlimited.) You can specify the values for SIZE, MAXSIZE, and FILEGROWTH in units of terabyte, gigabyte (GB), MB (the default), or KB. You can also specify the FILEGROWTH property as a percentage. A value of 0 for FILEGROWTH indicates no growth. If no FILEGROWTH value is specified, the default growth increment for data files is 1 MB. This is a change from SQL Server 2000, where the default growth increment for data files was 10 percent. In SQL Server 2005, the log file FILEGROWTH default is 10 percent, the same as it was in SQL Server 2000.

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.1\mssql\data\archdat1.mdf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20), ( NAME = Arch2, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\archdat2.ndf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20) LOG ON ( NAME = Archlog1, FILENAME =     'c:\program files\microsoft sql server\mssql.1\mssql\data\archlog1.ldf', SIZE = 100MB, MAXSIZE = 200, FILEGROWTH = 20);