Creating Databases | Inside Microsoft SQL Server 7.0 (Mps)

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 accomplish those tasks . Figure 5-1 shows SQL Enterprise Manager's Database Properties dialog box, which represents the CREATE DATABASE Transact-SQL command.

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. The command is used to create a new user database.

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 subsequent databases.) In addition to objects you might create in model , 18 system tables and 20 system views are included in model , which means that every new database also includes these 38 system objects. SQL Server uses these objects for the definition and maintenance of each database.

click to view at full size.

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

(Three of the system views are provided for backward compatibility and have no current functionality. They mimic information that was available in earlier versions of SQL Server.) The system objects are the ones whose names start with sys . If you haven't added any other objects to model , these 38 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.

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 testdb

This would create the testdb database, with a default size, on two files whose logical names ” testdb and testdb_log ”are derived from the name of the database. The corresponding physical files would be testdb.mdf and testdb_log.ldf. They would be created in the default data directory, as determined at the time SQL Server was installed.

NOTE
If you use the SQL Server Enterprise Manager to create a database called testdb , the default logical and physical names will be different than if you use the CREATE DATABASE command. The Enterprise Manager will give the data file the logical name of testdb_Data (instead of just test ), and the physical file will have the name testdb_data.mdf.

The SQL Server login account that created the database is known as the database owner, or DBO, and will always have the username 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, testdb , is case sensitive depends on the sort order you choose 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 values apply to the new database and its files. If the LOG ON clause is not specified, but data files are specified, a log file will be 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.) Both SIZE and MAXSIZE can be specified in units of MB (the default) or KB. A value of 0 for FILEGROWTH indicates no growth. The default value is 10 percent, and the minimum value is 64 KB.

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