Creating a Database
enough with that stuff...let's create our first database! Start by opening
Enterprise manager and expand the tree view until you see the "Databases" folder
of your server.
Click to Enlarge
Click the Databases folder and select "New Database..."
Name textbox enter "FirstDB"
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.
"Data Files" tab.
portion of the Data Files tab shows you all the data files that make up 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.
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.
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.
general rule, it is best to have files expand as few times as possible because
expanding files causes a performance hit to SQL Server.
"Transaction Log" tab.
options on the Transaction Log tab are very 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.
to accept the defaults.
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.
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"
expand "Database", select "Create Database Wizard", and click OK.
wizard then walks you through the steps to create a database.
again, that will finish it up for this week. Next week we will spend some more
time looking at database management.
As always, if you have any technical questions please post them on the SQL message board.Please send any non-technical questions, comments, and feedback to my email. I hope you are finding this
series to be a useful one, and I'm looking forward to your feedback.
See All Articles by Columnist Michael Aubert