The Databases node(right-click menu option New Database)—This is a visual interface for more experienced users that combines all the Database Wizard steps on one screen. It also enables you to make use of the more advanced option settings, such as collation order.
Direct Transact SQL commands— This method is for advanced users and those creating databases through a command-line connection; it will be covered in Session 9, once you’ve mastered Transact-SQL basics.
These three methods are essentially the same, as behind the scenes your Database Wizard builds and executes the T-SQL statements that create your database.
There are at least two major components of each database: the data component (which includes database objects like rules, roles, stored procedures, and so on) and the transaction-log component. As I mentioned before, in SQL Server 2000 a database is physically stored in separate non-shared files — one (or more) for the data (default extension .mdf), and one (or more) for the transaction log (default extension .ldf). The database can span several files; by convention, the extension of the primary file is .mdf and that of each secondary data file is .ndf. These files are exactly the same as any Windows file and are treated as such by the operating system.
You can give your database file any extension you want, though adhering to conventional standards reduces confusion and makes file management easier.
Let’s create a database using the Database Wizard interface. You can access it
from the Enterprise Manager Tools➪Wizards menu.
1. Select Create Database Wizard and click OK. The first screen explains the
steps you’re going to take to create your database. Click OK.
2. The second screen prompts you to specify a name for your database as
well as a location for the files for your primary database file and transac- tion log. Click Next to continue.
If you leave the default Unrestricted file growth option selected, SQL Server will enlarge your database file as necessary automati- cally. It’s a good idea to stick with the default values until you have a little more experience in creating databases, but keep in mind that unrestricted file growth can affect your system’s performance. Tip Tip Note Saturday Morning 74
3. The next screen prompts you to name your database file. By default SQL
Server will name your database file <your database name>_Data.mdf and
will name your transaction-log file <your database name>_Log.ldf. You
can change the name of the file to whatever you want. By default, also, the initial size of these files will be 1MB; change this value to something in line with your expected database size.
If you want all new databases in your SQL Server System to pos- sess certain properties (such as initial size or data type), you may specify these properties in your Model database and every custom database you subsequently create will inherit it. (After you are through with the database-creation process, try modify- ing your Model database, setting its database size to 10MB and its log to 2MB and recreating your custom database, to see how this affects the new database properties.)
4. Now it is time to specify some properties of your yet-to-be-created data-
base (see Figure 7-1). I recommend leaving the Automatically grow the database files option selected. The last option on this screen deals with the file-growth restriction: If you choose to restrict file growth, the file will grow only until it reaches a specified limit. This is an important option because unrestricted file growth can potentially chew up all your hard-drive space. Click Next.
One of my pet peeves is the option to increase file size by per- cents. Doing this can result in your running out of space, as you have no control over the number of megabytes allocated once the maximum capacity is reached. If your database occupies 1GB, a 10 percent increase means an increase of 100MB to accommo- date only 1MB of new data. Specifying growth in megabytes gives you greater control over space usage. It is also a good idea to specify the maximum possible size for the transaction log so it does not fill up quickly.
5. Essentially, you repeat the same operations for your new database trans-
action log: choosing the name and initial size. In selecting the initial size (and in using the file-growth option on the next screen) the rule of thumb is to keep log size at approximately 25 percent of data-file size. Click Next.
6. The same considerations for Step 4 apply to the option selection for this
step. Click Next.
Tip Tip
Session 7—Creating and Using a Custom Database 75
P art II — Satur day Morning Session 7
Figure 7-1
Set your database properties.
7. Now you see a summary screen displaying all the options you selected in
the previous steps. It’s a good idea to go through this summary and make sure that the options specified are the ones you want. You can still go back and change them or cancel the whole operation.
After you click Finish, SQL Server will create your database, which you will be able to see on your Enterprise Manager console under the Databases node. If you used the wizard interface, SQL Server will prompt you to create a database mainte- nance plan; while it is a very good idea to have one, the topic of maintenance plans is beyond the scope of this session and will be covered in Sessions 21, 26, and 27.
Most of the time SQL Server will succeed. If SQL Server is unable to create your database it will display an error message and an error code you can use to find a more detailed explanation in Books Online (for example, SQL Server displays the error message “There is not enough disk space” and the error code 112 if you attempt to create a 100GB database on a 20GB hard drive).
Note
Saturday Morning
If you followed me through this session you should have a brand new database showing under the Databases node on your Enterprise Manager console. Let’s take a closer look at all the objects that make up your database.
You can create a database simply by issuing a Transact-SQL com- mand from the SQL Query Analyzer window: create database <database_name>. The size and all the properties will be the same as those of the Model database, and the default file names will be the same as those that appear in Step 3 of the Create Database Wizard: <database_name>_Data.mdf and
<database_name>_Log.ldf. The files will be physically located in
the default directory of your SQL Server installation under that name.
At this point all you have is an empty shell ready to be filled with your data- base content. It contains 19 system tables, roles, and user(s) inherited from the Model database. I will show you how to add your own tables, views, rules, and stored procedures in Sessions 9, 11, and 16.