Creating a SQL Server Database

Ok, enough with that stuff…let’s create our first SQL Server 2000 database! Start by opening Enterprise manager and expand the tree view until you see the “Databases” folder of your server.

Right Click the Databases folder and select “New Database…”

In the Name textbox enter “FirstDB”

One thing to note on this screen is the ability to set the collation for this database other than the server default. If you don’t remember what the collation is, go back and look over my “Basic Installations” article. For this example we will leave it as the server default.

Click the “Data Files” tab.

The top portion of the Data Files tab shows you all the data files that makeup this database. As you can see Enterprise Manager has already added the Primary Data file for us. The logical file name is FirstDB_Data and the physical file is named FirstDB_Data.MDF located in the Data folder that was specified when we installed SQL Server. The logical file name is the name that SQL Server uses internally to reference the file, whereas the physical file name is the name of the operating system file.

In addition to the logical file name and physical name/location, we also can set the Initial size for each file. If you know you will be loading data into your database and have a general idea of its size you can set this value to avoid automatic file growth (more on this in a second). The last column allows the selection and creation of file groups.

The lower half of the Data Files tab allows you to set the Automatic file growth for each file that makes up the database. When a database has used all available space in a data file, you can have SQL Server automatically expand the file as needed. The File Growth option allows you to set how much SQL Server will expand the data file. You can select a fixed amount in megabytes or enter a percentage of the current file size to grow the file by. You can also set a maximum file size for the data file. Once a file reaches its maximum size, no more automatic file growth will take place.

As a general rule, it is best to have files expand as few times as possible because expanding files causes a performance hit to SQL Server.

Click the “Transaction Log” tab.

The options on the Transaction Log tab are vary similar to the Data Files tab with the only exception being the lacking of a File Group option. Filegroups are only used for data files, not log files. All other options on the Transaction Log tab are the same as the Data Files tab.

Click OK to except the defaults.

Our new database now appears in the Databases folder. If you go exploring into the database we have just created you will notice it looks a lot like the model database. This is because all new databases we create are a copy of the model database. All the system objects and other objects we create in the model database are added automatically to all the new databases we create.

Another way to create a database is by using the Create Database Wizard. To start the Create Database Wizard, in Enterprise Manager Select “Wizards…” from the “Tools” menu.

Then expand “Database”, select “Create Database Wizard”, and click OK.

The wizard then walks you through the steps to create a database.