QUESTION 21
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows Server 2000 and all client
computers run Windows 2000 Professional. The Certkiller .com network contains a
SQL Server 2000 database server named Certkiller -DB01 that hosts a database
named CK_Projects. The CK_Projects database contains a table named Documents
that contains an XML column named DocLocation. Certkiller .com users complain
that queries against the DocLocation column are processed very slowly.
You want to improve the performance of queries that are run against the
DocLocation column by creating an index on the column.
What should you do?
A. Create a primary index on the primary key of the Documents table and secondary
index on the DocLocation column.
B. Create a clustered index on the primary key of the Documents table and secondary
index on the DocLocation column.
C. Create a nonclustered index on the primary key of the Documents table and primary
index on the DocLocation column.
D. Create a clustered index on the primary key of the Documents table and primary index on the DocLocation column.
Answer: D
Explanation: You can create XML on XML data type columns to index all tags,
values and paths in the column. This will improve query performance. The first
XML index must be a primary index and the Documents table must have a clustered index on the primary key. A clustered index determines the physical ordering of the rows in a table and is required as the primary key is used to correlate XML index rows with the rows in the table.
Incorrect Answers:
A: You cannot create a unique index, clustered index or nonclustered index but not
a primary index on a non-XML column. You must create a clustered index on the
primary key as the primary key is used to correlate XML index rows with the rows
in the table. Also, the first XML index must be a primary index. Secondary indexes
can be created once the primary index has been created.
B: The first XML index must be a primary index. Secondary indexes can be created
once the primary index has been created.
C: You must create a clustered index on the primary key as the primary key is used
to correlate XML index rows with the rows in the table. A clustered index determines
the physical ordering of the rows in a table while a nonclustered index creates a logical ordering.
network consists of a single Active Directory domain named Certkiller .com. All
servers on the Certkiller .com network run Windows Server 2000 and all client
computers run Windows 2000 Professional. The Certkiller .com network contains a
SQL Server 2000 database server named Certkiller -DB01 that hosts a database
named CK_Projects. The CK_Projects database contains a table named Documents
that contains an XML column named DocLocation. Certkiller .com users complain
that queries against the DocLocation column are processed very slowly.
You want to improve the performance of queries that are run against the
DocLocation column by creating an index on the column.
What should you do?
A. Create a primary index on the primary key of the Documents table and secondary
index on the DocLocation column.
B. Create a clustered index on the primary key of the Documents table and secondary
index on the DocLocation column.
C. Create a nonclustered index on the primary key of the Documents table and primary
index on the DocLocation column.
D. Create a clustered index on the primary key of the Documents table and primary index on the DocLocation column.
Answer: D
Explanation: You can create XML on XML data type columns to index all tags,
values and paths in the column. This will improve query performance. The first
XML index must be a primary index and the Documents table must have a clustered index on the primary key. A clustered index determines the physical ordering of the rows in a table and is required as the primary key is used to correlate XML index rows with the rows in the table.
Incorrect Answers:
A: You cannot create a unique index, clustered index or nonclustered index but not
a primary index on a non-XML column. You must create a clustered index on the
primary key as the primary key is used to correlate XML index rows with the rows
in the table. Also, the first XML index must be a primary index. Secondary indexes
can be created once the primary index has been created.
B: The first XML index must be a primary index. Secondary indexes can be created
once the primary index has been created.
C: You must create a clustered index on the primary key as the primary key is used
to correlate XML index rows with the rows in the table. A clustered index determines
the physical ordering of the rows in a table while a nonclustered index creates a logical ordering.
QUESTION 22
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Manufacturing
that stores data from the Manufacturing department and a database named
CK_Sales that stores sales data for the company.
Due to the growth of the CK_Sales database, Certkiller -DB01 is running low on
free disk space. It is anticipated that the CK_Sales database will grow even further
over the next few months. You add a second SQL Server 2000 database server
named Certkiller -DB02 to the Certkiller .com network. You want to move the
CK_Sales database to Certkiller -DB02. You need to accomplish task in minimum
time.
What should you do?
A. A Full backup of the CK_Sales database and its transaction logs must be performed.
Restore the Full backup to Certkiller -DB02.
Restore the transaction logs to Certkiller -DB02.
B. Detach the CK_Sales database on Certkiller -DB01 by executing the sp_detach_db
stored procedure.
Copy the data and log files for CK_Sales to Certkiller -DB02.
Attach the CK_Sales database to Certkiller -DB02 by executing the sp_attach_db
stored procedure.
Restore the Full backup to Certkiller -DB02.
Restore the transaction logs to Certkiller -DB02.
B. Detach the CK_Sales database on Certkiller -DB01 by executing the sp_detach_db
stored procedure.
Copy the data and log files for CK_Sales to Certkiller -DB02.
Attach the CK_Sales database to Certkiller -DB02 by executing the sp_attach_db
stored procedure.
C. Stop the SQL Server service on Certkiller -DB01.
Detach the CK Sales database on Certkiller -DB01 by executing the sp_detach_db
stored procedure.
Copy the data and log files for CK_Sales to Certkiller -DB02.
Attach the CK_Sales database to Certkiller -DB02 by executing the sp_attach_db
stored procedure.
D. A new database named CK_Sales on Certkiller -DB02 must be created.
Copy data from the CK_Sales database on Certkiller -DB01 to the new CK_Sales
database on Certkiller -DB02 using the SQL Server Import and Export Wizard.
Answer: B
Explanation: The fastest method of moving a database from one server to another is
to detach the database and attach it to the destination server. This can be
accomplished by running the sp_detach_db stored procedure to detach the database
on source server, copying the data and log files for the database to the destination
server, and then running the sp_attach_db stored procedure to attach the database
to the destination server.
to detach the database and attach it to the destination server. This can be
accomplished by running the sp_detach_db stored procedure to detach the database
on source server, copying the data and log files for the database to the destination
server, and then running the sp_attach_db stored procedure to attach the database
to the destination server.
Incorrect answers:
A: The Use the detach and attach method option does not allow active connections to
the database while it is being moved and thus will not allow the database to be queried
while it is being moved.
C: You can move the database by restoring a backup of the database to the destination
server but it is quicker to detach and attach the database.
D: The SQL Server Import and Export Wizard can be used to move a database but it is
quicker to detach and attach the database.
A: The Use the detach and attach method option does not allow active connections to
the database while it is being moved and thus will not allow the database to be queried
while it is being moved.
C: You can move the database by restoring a backup of the database to the destination
server but it is quicker to detach and attach the database.
D: The SQL Server Import and Export Wizard can be used to move a database but it is
quicker to detach and attach the database.
QUESTION 23
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 B01.
Certkiller -DB01 hosts a database named CK_Products that contains 60 lookup
tables. The lookup tables should remain static at all times and users should not be
able to alter the static data. You discover that some of the data in the lookup tables
have been altered. You need to ensure that users cannot change any of the data in
the lookup tables.
What should you do?
A. Create a new filegroup and move the lookup tables to the new filegroup.
Enable the Read-Only option on the filegroup.
B. A view of the lookup tables should be created.
Allow users to access the lookup tables through the view only.
C. Stored procedures for modifying data in the lookup tables should be created.
Allow users to modify data through the stored procedures only.
D. A new database role should be created and all users should be added to the new role.
Grant SELECT permissions to the new role.
Enable the Read-Only option on the filegroup.
B. A view of the lookup tables should be created.
Allow users to access the lookup tables through the view only.
C. Stored procedures for modifying data in the lookup tables should be created.
Allow users to modify data through the stored procedures only.
D. A new database role should be created and all users should be added to the new role.
Grant SELECT permissions to the new role.
Answer: A
Explanation:
You can prevent users form modifying data in the lookup tables by moving the
tables to a separate filegroup and making the file group read-only.
You can prevent users form modifying data in the lookup tables by moving the
tables to a separate filegroup and making the file group read-only.
Incorrect answers:
B: Views are used to limit the part of the database that the users can see. It does not
prevent them from modifying the data in the underlying table.
C: Users should not be able to modify the data in the lookup tables. Allowing them to
modify data through the stored procedures only would not meet this requirement.
D: This option will require considerable administrative effort as there are 60 look
up tables. You will need to grant SELECT permissions for each of these tables. Less
effort is required when using a read-only filegroup.
B: Views are used to limit the part of the database that the users can see. It does not
prevent them from modifying the data in the underlying table.
C: Users should not be able to modify the data in the lookup tables. Allowing them to
modify data through the stored procedures only would not meet this requirement.
D: This option will require considerable administrative effort as there are 60 look
up tables. You will need to grant SELECT permissions for each of these tables. Less
effort is required when using a read-only filegroup.
QUESTION 24
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores
data sales data for the company. The following exhibit illustrates the database
properties of the CK_Sales database.
Exhibit:
CK_Sales contains two data files named CK_Sales1 and CK_Sales2. Both are
located on drive E. Drive E has 50 MB of free space available. The Sales department
members gain access to the database via a custom application that allows them to
create new tables and indexes. Thus all user tables and their indexes were created
by the Sales department members using this custom application only. The custom
application does not make provision for users to specify a filegroup on which to
place the objects that are creating.
You received a report from the Sales department members that they are unable to
create new objects. You need to address this issue and thus need to provide
additional disk space to enable users to continue working. You want to address this
issue using the least amount of administrative effort.
What should you do?
A. Automatic file growth should be disabled for the Primary filegroup.
B. Additional transaction log files should be created on drive E.
C. Additional files should be created in the Primary filegroup on drive E.
D. Secondary should be configured as the default filegroup.
E. The maximum allowed size for the secondary file should be reduced on the Properties sheet for the database.
Answer: D
Explanation: In the event of no filegroup explicitly specified as the default filegroup, then Primary will become the default filegroup resulting in all new tables and indexes that are created to be placed in the default filegroup. The custom application does not allow for users to specify a filegroup. Thus one can conclude that the reason why the sales Department members cannot create new objects must be that the Primary filegroup is nearly full. It is also mentioned in the question that all user tables and indexes have been created using the custom application only. Thus no objects should currently reside on Secondary, and thus its file would be empty. To remedy the situation with the least amount of effort should then be to specify Secondary as the default filegroup. This should allow users to create new tables and indexes which will then be assigned to Secondary.
Incorrect answers:
A: .Disabling automatic file growth for Primary filegroup will not provide additional
disk space.
B: .Creating additional log files will result in more than the required administrative effort
to remedy the situation.
C: This situation can be remedied by creating an additional file in the Primary
filegroup on drive E. But drive E only has 50 MB of available free space.
E: You cannot reduce the maximum allowed size for the secondary file on the
Properties sheet for the database.
QUESTION 25
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01. Certkiller -DB01 hosts a database named CK_Sales that stores
sales and product data. The Simple Recovery Model is implemented for the
CK_Sales database. A Full backup of the CK_Sales database is performed every
Saturday at 8:00 P.M.
The sales data is updated frequently throughout the day. Over the last six months
the CK_Sales database has grown considerably. At present the Full backup requires
6 hours to complete. You are concerned that data loss may occur should
Certkiller -DB01 suffer a hard disk failure. You decide to implement additional
backups of the CK_Sales database on a daily basis. However, the backup may only
run between midnight and 4:00 A.M. on week nights.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)
A. Schedule a Full backup of the CK_Sales database at 12:00 P.M. every week night.
B. Schedule a differential backup of the CK_Sales database at 12:00 P.M. every week
night.
C. Create two filegroups. Backup a different filegroup every other week night.
D. Backup the transaction log every week night.
Answer: C, D
Explanation: Creating two filegroups and backing up a different filegroup every
other week night will allow you to backup the database within the given time frame
during the week.
You should also backup the transaction logs every night to ensure full
recoverability as only one filegroup is backed up each night.
Incorrect answers:
A: A Full backup requires six hours to complete but the backup can only run from
12:00 P.M. until 4:00 P.M. during the week. Thus there is insufficient time to complete a Full backup during the week.
B: A differential backup will backup all data that has changed since the last Full backup. Towards the end of the week the differential backup may take quite some depending on the number of changes made since the last Full backup and may require more than 4 hours to complete.
QUESTION 26
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The
following exhibit illustrates the database properties of the CK_Sales database:
Exhibit:
All Certkiller .com users interact with the database via a custom database application that enables certain Sales department members the ability to create new tables and indexes on those tables. Unfortunately the custom application does not make allowance for users to specify a filegroup in which to place new objects. To compound problems, you notice that drive E is running out of space. To this end you
need to mitigate this problem so as to ensure that users will be able to continue
creating tables and indexes in the database.
What should you do?
A. Drive E should be compressed.
B. Secondary should be specified as the default filegroup.
C. Additional files should be created in Secondary on drive E.
D. Primary filegroup's primary data file requires shrinking.
Additional data files should be created in Primary filegroup on Drive E
Answer: B
Explanation: Since the Sales Department members use a custom application to access
SQL Server 2000 and create new objects it stands to reason that all new objects are
automatically placed in the default filegroup. Originally SQL Server designates the
Primary filegroup as the default filegroup. It is also mentioned in the question and
on the exhibit that another filegroup exists on drive E. as an administrator you can
specify another filegroup as the default filegroup. Thus if you specify Secondary as the default filegroup, then new objects will be created on that filegroup, which should contain sufficient free space because all new objects to date had been creates only by using the custom application.
Incorrect answers:
A: SQL Server data files cannot reside on compressed drives.
C: The question pertinently states that only the custom application is used to create
new objects. Thus it does not matter how many files were created in Secondary or
how much free space were available on drive E. the custom application automatically
places new objects on the default filegroup which is currently Primary.
D: The growth on the primary data file is not currently restricted. Thus the growth
of the primary data file is the most likely reason which drive E is running out of
space. Hence there is no free disk space that could be returned to the operating
system by shrinking. And thus you also cannot create additional data files.
QUESTION 27
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server, all client computers run
Windows 2000 Professional and all database servers run SQL Server 2000. The Certkiller .com network contains a SQL Server 2000 database server named
Certkiller -DB01 that runs on a Windows 2000 Server computer.
Certkiller -DB01 hosts a database named CK_Staff. The Full Recovery Model is
implemented for the CK_Staff database.
Certkiller .com acquires another company named TestLabs.com. You import staff
data from the new company into the CK_Staff database. Soon after ward you notice
that Certkiller -DB01 is performing quite poorly. You discover that the
transaction logs for the CK_Staff database has increased by almost five times in size
and has consumed most of the free disk space. To this end you need to recover disk
space as soon as possible.
What should you do?
A. The transaction log files should be backed up.
B. Switch to the Simple Recovery Model.
C. The DBCC SHRINKFILE Transact-SQL statement should be executed.
D. The transaction log files should be truncated.
Answer: C
Explanation: TheDBCC SHRINKFILE statement is used to reduce the log files to a
specified size.
Incorrect answers:
A: Backing up the log file clears the log file but does not reduce the physical size of the transaction log file.
B: Switching to the Simple Recovery Model will not reduce the physical size of the
transaction log file.
D: Truncation reduces the logical size of the transaction log file but it does not reduce the physical size of the transaction log file.
QUESTION 28
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows NT Server 4.0 and all database servers run
SQL Server 2000. The Certkiller .com network contains a SQL Server 2000 database
server named Certkiller -DB01 that runs on a Windows 2000 Server computer.
Certkiller -DB01 hosts a database named CK_Customers. The CK_Customers
database has almost completely filled the available space. An original size of 3 GB
had been allocated when the data file was created, and this data file has since grown
to 10 GB.
You this need to create another database, but you cannot add any new disks to the
computer. There is no free space on the other drives. Thus you archive a portion of
the older data to a decision-support system (DSS) database. Thereafter you remove
the archived data from CK_Customers. However, you find that you cannot create
the new database since the size of the data file has not decreased. You need to ensure that you have enough disk space to create another database.
What should you do?
A. The DBCC SHRINKFILE (DataFile, 2000) statement should be executed.
B. The Read-Only option should be enabled on the CK_Customers database.
C. The Transaction Log should be truncated.
D. The Space available parameter should be set to 1 GB on CK_Customers database.
Answer: A
Explanation: The DBCC SHRINKFILE (DataFile, 2000) statement causes free
space to be relocated to the end of the file and then reclaimed. This free space can
then be returned to the operating system.
The remaining file size should then be approximately 2,000 MB which will accommodate
the creation of a new database.
Incorrect answers:
B: Enabling the Read-Only option will permit users to query the database; it does
not prevent them from manipulating data. Thus there would be no gain in space to
create a new database.
C: Truncating the Transaction Log removes committed transaction from the log,
but it does not reclaim free space that is available in the log. You need to shrink the log. However, this could result in poor database performance.
D: No gains in Space available parameter appears on the General tab in the
database Properties sheet. This parameter cannot be configured.
QUESTION 29
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a SQL Server 2000 database
server named Certkiller -DB01 that runs on a Windows 2000 Server computer.
Certkiller -DB01 hosts a database named CK_Customers.
Certkiller -DB01 has its operating system located on drive C, its program files
located on drive D and its only data file and the transaction log file located on drive E. Drive E has only 250 MB of free space remaining and Drive F has 8 GB of free
space.
You received instruction to load 1 GB of new data into the existing tables in
CK_Customers. CK_Customers is configured for the Bulk-Logged Recovery Model.
You need to make use of the BULK INSERT statement to insert the new data into
the existing tables on CK_Customers. You thus need to prepare the database to
accommodate the new data since there is only 250 MB of free space on the drive on
which the data file and transaction log file is located.
What should you do?
A. You need to allow unrestricted growth for the data file.
B. A new transactional log file should be created on drive F.
C. A new data file should be created in the Primary filegroup on drive F.
D. A new filegroup should be created on drive F.
Answer: C
Explanation: It is mentioned that there are not enough free space on Drive D to
accommodate the new data that must be loaded into the tables on CK_Customers.
Thus you need to create a new data file in the Primary filegroup on drive F since
drive F contains 8 GB of free space. Space for new data is automatically allocated
proportionately to the amount of free space available from both files.
Incorrect answers:
A: Allowing unrestricted growth for the primary file would not be an effective solution as drive E does not contain sufficient free space.
B: It is mentioned in the question that CK_Customers is configured for the
Bulk-Logged Recovery Model. This means that the bulk load operation will not be
fully logged and the transaction log will not require much space for growth.
Creating a new transactional log file on drive F will not accommodate new data.
Data files and transaction log files cannot be places on a compressed drive or a network drive.
D: Adding a new filegroup will not have any objects in it and the question states
pertinently that the new data should be loaded into the existing tables on
CK_Customers. These existing tables are located in the primary file in the Primary
filegroup.
QUESTION 30
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows Server 2000 and all database servers run SQL
Server 2000. The Certkiller .com network contains a SQL Server 2000 database
server named Certkiller -DB01 that runs on a Windows 2000 Server computer.
Certkiller -DB01 hosts a database named CK_Customers. The CK_Customers
Categories table is illustrated in the following exhibit:
Exhibit:
A Certkiller .com user named Mia Hamm is responsible for updating information on
this table. She needs to delete the 'pizza' value in the CatName column since this
item is no longer on the menu. Certkiller .com is replacing pizza with pasta which is
currently being tested. Mia Hamm intends making use of the following statement to
carry out her task:
UPDATE Categories SET CatName = NULL
Unfortunately she finds that the statement fails. You need to enable Mia Hamm to
delete the 'pizza' value in the CatName column.
What should you do?
A. An AFTER DELETE trigger must be created on the CatName column.
B. The Mia Hamm user account should be granted permission to execute the CREATE
PROCEDURE statement. Instruct Mia Hamm to create a stored procedure to replace the value in the CategoryName column with a NULL value.
C. Modify the table to allow nulls in the CatName column.
D. A PRIMARY KEY constraint should be defined on the CatName column.
Answer: C
Explanation: a Null value is not the equivalent of zero, an empty string of a string of zero length; instead it indicates that a particular value is unknown. In the exhibit you can see that the CatName column is not configured to allow null values. If MiaHamm is to be permitted to delete the current value without having to specify a new value, you should change the definition of the CatName column to allow null values by executing the following statement:
ALTER TABLE Categories ALTER COLUMN CatName nvarchar (15) NULL
Incorrect answers:
A: Creating an AFTER DELETE trigger will result in the trigger only being fired after a user successfully executed a delete statement. But this is not possible at the moment
since the CatName column does not allow for null values.
B: This option will not work because the CatName column currently does not allow
null values and any stored procedure that attempted to replace the current value with a null value would thus fail.
D: A PRIMARY KEY constraint enforces entity integrity in a table. Though it can
be created in a table, it will not work in this case since the primary key is defined on the CatID column. And also the column that is defined as a primary key cannot accept null values.
0 comments:
Post a Comment