Answer: C, E.
Explanation:
C: We must decide the initial size and the file growth of the database. We calculate the specifics for this
database and then add the size of the model database. All new databases are based on the model database.
The model database has an initial size of 1MB. A quick estimate of the user data of this table (see Note
below) is that less than 1MB is needed, so a total size of 2 MB is sufficient.
070 - 229
E: As the company experiences a yearly turnover of about 2% of its employee size and because data must be on
employees who have left the company must be maintained for auditing purposes, a FILEGROWTH of 5%
should be an adequate rate of growth of the datebase.
Note, estimate size of user data:
EmployeeDemographics table
We estimate the size of one row:
Varchar columns: total of 160. They are less than 50% full. So about 80 bytes would be needed
Rest of columns. An estimate. Less than 30 bytes.
We conclude: one row 110 bytes.
Employeetable
Varchar columns: total of 150. They are less than 50% full. So about 75 bytes would be needed
Rest of columns. An estimate. Around 30 bytes.
We conclude: one row 105 bytes.
Both tables.
There is a one-to-one relationship between the tables so we can simply add the estimates of the tables.
Both tables: 225 bytes
All rows (2000 rows): 2000*125 bytes: 250,000 bytes
Data pages only fills to 90%, due to fillfactor. So we actually need approx 278, 000 bytes (250,000/0,9).
If we add the size of the model database (1 MB) we see that 2MB (or 2048KB) would be more than enough and
a good value for the default file size of the database.
Incorrect answers:
A: 1 GB would be too much. Only 2MB is needed.
B: The model database has a size of at least 1MB. A default file size of 1MB would be too small.
D: FILEGROWTH specifies the growth increment of the file size and cannot exceed the MAXSIZE setting.
If the FILEGROWTH value is not represented as a percentage, then the value represents a number of
MB. A FILEGROWTH of 20 thus represents a FILEGROWTH of 20 MB.
F: FILEGROWTH specifies the growth increment of the file size and cannot exceed the MAXSIZE setting.
If the FILEGROWTH value is not represented as a percentage, then the value represents a number of
MB. SQL Server does not increment a file in units smaller than 1 MB. Furthermore, a SQL Server does
not permit a FILEGROWTH of 0% or 0 MB.