MSCE Interview Questions and Answers

QUESTION 71
You work as the network 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 2000 Server
and all client computers run Windows 2000 Professional.
You have recently received instruction to implement SQL Server 2000 on the
network. You are required to monitor how well the system is functioning. You have
recently come to believe that a certain application is causing deadlocks and thus
deteriorating the overall performance of the database. You received additional
instruction to choose which of the tools you are required to use to determine the
cause of the deadlocks.
What should you do?

A. You should use the Performance Monitor.
B. You should use the SQL Server Agent.
C. The sp_monitor system stored procedure should be used.
D. You should use the SQL Profiler.

Answer: D

Explanation: You should remember that SQL server comes shipped with several tools to enable performance monitoring and troubleshooting. You additionally have
the ability to choose to monitor only deadlocks that are associated with a specific
database by providing a Database ID event to be used as a filter.

Incorrect Answers:
A: You should remember that Performance Monitor cannot determine the processes of
objects that are involved in particular deadlock situations.
B: You should not consider using this tool as it is unable to identify the cause of a
deadlock it only enables scheduling of recurring activities and notification for problems associated with SQL 2000.
C: This option cannot be used because it only provides general statistical information and cannot obtain information about deadlocks.


QUESTION 72
You work as the network 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 2000 Server
and all client computers run Windows 2000 Professional.
The Certkiller .com network has recently implemented SQL 200 Server Enterprise
Edition. Certkiller .com stores its business information in several SQL 2000
databases which reside on a Windows 2000 Server computer. Certkiller .com has
users in the marketing and sales departments who execute queries against the
databases to retrieve data analysis. Certkiller .com members of the Data Entry
department update the customer information and members of the accounting
department update financial information.
You are responsible for administering these databases. You are required to create a
sample of user activity in the database in order to optimize indexes, queries a stored procedures. The server is very busy and the monitoring should not overload the
server.
What should you do?

A. Activity should be monitored periodically through the Current Activity windows in
Enterprise Manager.
B. SQL Profiler should be run on the SQL Server production computer and relay the
trace on a Windows 98 computer located on the network.
C. SQL Profiler should be run on another computer to capture a trace with typical
activity and save the trace data into a table in a database.
D. SQL Profiler should be run on another computer to capture a trace with typical
activity and save the trace data into a file.

Answer: D

Explanation: In the scenario you should remember that SQL Profiler enables you to
capture database activity in real time and replay the events to simulate actual
database activity.

Incorrect Answers:
A: This option should not be used in the scenario as you would only be allowed to view information about current processes only.
B: This option should not be used in the scenario because Windows 98 does not support
an installation of SQL Server Enterprise Edition.
C: This option should not be used in the scenario because this places additional strain and might overload the server.


QUESTION 73
You work as the network 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 2000 Server
and all client computers run Windows 2000 Professional.
You have recently received instruction to start developing an application. When you
test your application it appears to be not responding. You later attempt to run the
application at different times and notice that it appears the stop responding at
approximately the same times.
You have reason to believe that the application is stopping due to locking problems.
You are required to determine the source or sources of the locking problems to
repair your application.
What should you do?

A. You should use the KILL command.
B. You should call the sp_droplock stored procedure.
C. You should use the System Monitor.
D. You should call the sp_lock stored procedure.

Answer: D

Explanation: In the scenario you should remember that locking is a mechanism that
SQL Server 2000 initiates by default to prevent concurrency problems. Executing
the sp_lock stored procedure to view the information about locks sis the proper
option in the scenario.

Incorrect Answers:
A: This option should not be used in the scenario as it will end the application causing the problems but will not identify the where the locks are.
B: This option can not be considered in the scenario for usage as there is no sp_droplock command in SQL Server 2000 only a sp_lock command.
C: This option should not be used in the scenario because the System monitor will not be able to identify where the locks are occurring in your application.


QUESTION 74
You work as the network 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 2000 Server
and all client computers run Windows 2000 Professional.
A Certkiller .com network employee named Rory Allen has recently reported that he
USE CK_Tracking
DROP TABLE Suppliers
CREATE TABLE Suppliers
(
SupplierID int IDENTITY,
CompanyName nvarchar (50),
Phone nchar (10),
Address nvarchar (50),
City nvarchar (50)
)
Rory Allen reports that the error message states that his user account does not have
the necessary permissions. Rory Allen however states that he is capable of creating
several other tables in the CK_Tracking database before he ran this script.
You are required to enable Rory Allen the capability to perform his task whilst
using the least administrative effort. Rory Allen should not be granted excessive
permissions in SQL Server.
What should you do?

A. Rory Allen's user account should be added to the db_accessadmin fixed database role.
B. Rory Allen's user account should be granted the DROP TABLE permission.
C. Rory Allen's user account should be granted the CREATE TABLE permission.
D. Rory Allen's user account should be added to the db_dlladmin fixed database role.

Answer: D

Explanation: In the scenario you should remember that by granting Rory Allen the
db_dlladmin fixed database role that you will enable him to issue data definition
language (DLL) statements without granting other users the permissions.

Incorrect Answers:
A: This option should not be used in the scenario as this would enable Rory Allen the
capability to delete user accounts in SQL which is not what should happen.
B: This option should not be used in the scenario because this permission is owned by the creator of the table and cannot be explicitly granted to any user.
C: This option should not be used in the scenario as it states that Rory Allen was capable of creating several other tables before using the script.
receives an error message each time when attempting to run the SQL script below:


QUESTION 75
You work as the database administrator at Certkiller .com. All servers on the
Certkiller .com network run Windows 2000 Server and all database servers run SQL
Server 2000. The Certkiller .com network contains a database server named
Certkiller -DB01 B01. Certkiller -DB01 hosts a database named CK_Sales that
stores sales data for the company.
Certkiller .com recently acquired another company named Testlabs.com. Users in
TestLabs.com also access the CK_Sales database on Certkiller -DB01. A
Certkiller .com user reports that she often receives an error message when she
accesses the CK_Sales database. You discover that an errant process is causing the
error message. You need to terminate the errant process while minimizing the
impact on users that are currently connected to the database. You identify the
session ID (SPID) of the errant process as SPID 34.
What should you do next?

A. Run the KILL SPID 34 Transact-SQL statement.
B. Stop and restart the SQL Server Agent service.
C. Locate the errant process in task manager and end the process tree.
D. Run the KILL 34 Transact-SQL statement.

Answer: D

Explanation: The errant process can be terminated by using the KILL statement
and specifying the session ID as in KILL 34.

Incorrect answers:
A: The KILL statement does not use the SPID clause, only the SPID number.
B: Stopping and restarting the SQL Server Agent service will affect all users that are connected to the database.
C: You cannot use Task Manager to terminate a SQL Server process. Task Manager
terminates all processes associated with an application.


QUESTION 76
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Windows 2000 domain named Certkiller .com. All servers
on the Certkiller .com network run Windows 2000 Server 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_Sales. The CK_Sales database stores sales data for the company.
Certkiller .com has several Sales Representatives that use portable client computers
when they visit prospective clients. You enable these users to access the CK_Sales
database creating an Internet Information Services (IIS) virtual directory on a Web
server named Certkiller -SR04. A new Certkiller .com security policy requires that
remote users run only pre-defined queries against the CK_Sales database. You need
to ensure that the Sales Representatives comply with the new security policy.
What should you do?

A. Enable only URL queries on the IIS virtual directory.
B. Enable only template queries on the IIS virtual directory.
C. Enable only XPath queries on the IIS virtual directory.
D. Configure the portable computers to use only the Named Pipes Net-Library.
E. Configure the portable computers to use only the Shared Memory Net-Library.

Answer: B

Explanation: Template queries allow users to specify a pre-defined query and run it
against a database from the URL. The pre-defined queries are contained in XML files.

Incorrect Answers:
A: URL queries allow users to construct a query and run it against a database from the URL. You should therefore use template queries instead.
C: XPath queries allow users to run XPath queries against annotated mapping schemas. It does not allow them to query the database.
D, E: No Net-Libraries are used to access a database through an IIS virtual directory.


QUESTION 77
You work as the database administrator at Certkiller .com. The Certkiller .com has its
headquarters in Washington and a branch office in Chicago. All servers on the
Certkiller .com network run Windows 2000 Server and all client computers run
either Windows 2000 Professional or UNIX. All the UNIX users are located in
Chicago. The Certkiller .com network contains a Web server named
Certkiller -SR01 and a SQL Server 2000 database server named
Certkiller -DB01. Both Certkiller -SR01 and Certkiller -DB01 are located at
headquarters. Certkiller -DB01 hosts a database named CK_Data that stores
business information for the company.
You need to provide Certkiller .com users in the Chicago office with access to the
CK_Data database. You create an Internet Information Services (IIS) virtual
directory for the CK_Data database on Certkiller -SR01. You need to configure
the IIS virtual directory and ensure that all Certkiller .com users in the Chicago
office can access the CK_Data database across the Internet. You want to accomplish
this task using the least amount of administrative effort.
What should you do? (Each correct answer presents part of the solution. Choose
TWO.)

A. Enable Basic Authentication on the IIS virtual directory.
B. Enable Windows Integrated Authentication on the IIS virtual directory.
C. Enable Mixed Mode Authentication on the IIS virtual directory.
D. Create SQL Server logins for all users.
E. Create SQL Server logins for all UNIX users.
F. Create a SQL Server login for all UNIX users.

Answer: A, D

Explanation: To configure database access across the Internet using the least
amount of effort, you should select the Use Basic Authentication (Clear Text) to
SQL Server account option on the Security tab of the IIS Virtual Directory
Management for SQL Server utility. This requires that you create SQL Server
logins for all users in the Chicago office.

Incorrect Answers:
B: You cannot use Windows Authentication as UNIX users will not have Windows accounts.
C: SQL Server 2000 supports Mixed Mode Authentication but you need to configure
Internet access to the database in the IIS virtual directory. IIS does not support Mixed Mode Authentication.
E, F: The IIS virtual directory allows only one authentication scheme - Basic
Authentication to SQL Server login or Windows Integrated Authentication. This means
that all users that require database access through the Internet must have the same type of account.


QUESTION 78
You work as the database administrator at Certkiller .com. The Certkiller .com
network contains an Oracle database server named Certkiller -DB01 and a
Windows 2000 member server named Certkiller -SR01. Certkiller -DB01
contains a database named CK_Dir that has a single table named Contacts. The
Contacts table is used to store business contacts for the company. The Oracle client
software is installed on Certkiller -SR01.
You install SQL Server 2000 on Certkiller -SR01 and create a new database
named CK_Data with a Contacts table. You are required to import the data in the
Contacts table in the CK_Dir database to the Contacts table in the CK_Data
database. Your solution must provide the maximum performance.
What should you do?

A. Use the bcp utility.
B. Use the BULK INSERT statement.
C. Use a SELECT ... INTO statement.
D. Create a Data Transformation Services (DTS) package.

Answer: D

Explanation:
The Data Transformation Services (DTS) is used to import data from various data
sources, including other databases and text files.

Incorrect Answers:
A, B: The bcp utility and the BULK INSERT statement can only be used to import
data from a flat file. They cannot be used to import data from another database.
C: The SELECT ... INTO statement can only be used in the current database. It also
creates the table into which the data is imported. It cannot be used to import data from another database and cannot be used to import data into an existing table.


QUESTION 79
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Windows 2000 domain named Certkiller .com. All servers
on the Certkiller .com network run Windows 2000 Server 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_Data.
You install a new SQL Server 2000 database server named Certkiller -DB02. You
create a new database named CK_Sales on Certkiller -DB02. You must populate
the CK_Sales database with data from several tables in the CK_Data database.
You want to use the bcp command line utility to export the data to a text file. You
want to accomplish this task as quickly as possible.
What should you do?

A. Run the bcp command against each table that contains the required data.
B. Run the bcp command against each column that contains the required data.
C. Create a view based on the table columns that contain the required data and run the bcp command against resulting view.
D. Run a query to return the required data and run the bcp command against the query's return set.

Answer: D

Explanation: The bcp command can be run against a table, a view, or a query.
Running the bcp utility against the result set of a query will require the least
amount of time.

Incorrect Answers:
A: You can run the bcp command against a table but you would need to run it against
each table that contains the required data. You should first create a query that returns the required data and run the bcp command against the result set from the query.
B: The bcp command can be run against a table, a view, or a query. It cannot be run
against a column.
C: You can run the bcp command against a table view but it would require less time
to first create a query that returns the required data and run the bcp command
against the result set from the query.

QUESTION 80
You work as the database administrator at Certkiller .com. The Certkiller .com has its headquarters in Washington and a branch office in Chicago. All servers on the
Certkiller .com network run Windows 2000 Server and all client computers run
Windows 2000 Professional. The Certkiller .com network contains a SQL Server
2000 database server named Certkiller -DB01 that is located in Chicago.
Certkiller -DB01 hosts a database named CK_Sales that stores sales information
for the company.
Certkiller .com opens a new office in Atlanta. The Atlanta office will house
Certkiller .com's Sales department. The Atlanta office has an Internet connection but
no direct connection exists between Atlanta and the other offices. You need to move
the CK_Sales database to the Atlanta office.
What should you do?

A. Use the Database Copy Wizard to move the database.
B. Use the BULK INSERT statement to move the database.
C. Detach the database and attach it at the Atlanta office.
D. Create a Data Transformation Services (DTS) package.

Answer: C

Explanation: As there is no connectivity between the source and the destination
database servers, you must detach the database and attach it at the destination.

Incorrect Answers:
A: The Database Copy Wizard to move the database from one location to another.
However, the Database Copy Wizard uses a Data Transformation Services (DTS)
package to perform the operation and DTS requires connectivity between the two data
sources.
B: The BULK INSERT statement can only be used to import data from a flat file. It
cannot be used to move a database.
D: The Data Transformation Services (DTS) is used to import data from various data
sources, including other databases and text files; however, the DTS requires connectivity between the two data sources.