- Creating the Database
- Defining Database Options
- The Transaction Log
- Attaching to an Existing Database
- Summary
- Q&A
- Workshop
- Activities
This chapter is from the book
This chapter is from the book
This chapter is from the book
Defining Database Options
In the previous section, you created a new SQL Server database. You accepted all the default options available on the General page of the New Database dialog box. Many important options are available on the General page. They include the Logical Name, File Type, Filegroup, Initial Size, Autogrowth, Path, and File Name (see Figure 3.5).
)
FIGURE 3.5 Several important features are available on the General page of the New Database dialog box.
The logical name is the name that SQL Server will use to refer to the database. It is also the name you will use to refer to the database when writing programming code that accesses it.
The File Type is Data or Log. As its name implies, SQL Server stores data in data files. The file type of Log indicates that the file is a transaction log file.
The initial size is very important. You use it to designate the amount of space you will allocate initially to the database.
Related to the initial size is the Autogrowth option. When you click the build button (ellipsis) to the right of the currently selected Autogrowth option, the Change Autogrowth dialog box appears (see Figure 3.6).
)
FIGURE 3.6 The Change Autogrowth dialog box enables you to designate options that affect how the database file grows.
The first question is whether you want to support autogrowth at all. Some database designers initially make their databases larger than they ever think they should be and then set autogrowth to false. They want an error to occur so that they will be notified when the database exceeds the allocated size. The idea is that they want to check things out to make sure everything is okay before allowing the database to grow to a larger size.
The second question is whether you want to grow the file in percentage or in megabytes. For example, you can opt to grow the file 10% at a time. This means that if the database reaches the limit of 5,000 megabytes, then 10% growth would grow the file by 500 megabytes. If instead the file growth were fixed at 1,000 megabytes, the file would grow by that amount regardless of the original size of the file.
The final question is whether you want to restrict the amount of growth that occurs. If you opt to restrict file growth, you designate the restriction in megabytes. Like the Support Autogrowth feature, when you restrict the file size, you essentially assert that you want to be notified if the file exceeds that size. With unrestricted file size, the only limit to file size is the amount of available disk space on the server.
File Groups
One great feature of SQL Server is that you can span a database’s objects over several files, all located on separate devices. We refer to this as a file group. By creating a file group, you improve the performance of the database because multiple hardware devices can access the data simultaneously.