MSCE Interview Questions and Answers

QUESTION 81
You work as the database administrator at Certkiller .com. The Certkiller .com has its headquarters in Washington. 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. Certkiller -DB01 hosts a database named CK_Inventory that
stores product information for the company. The CK_Products database is used
mainly for querying purposes.

Certkiller .com opens a new office in San Francisco. A 512 Kbps WAN link is
established between the San Francisco office and headquarters. A CK_Products
database has been created on a SQL Server 2000 computer named
Certkiller -DB02 in the San Francisco office. You need to copy the CK_Products
database to the San Francisco office. You are not required to synchronize the source
and destination databases after the data has been copied. You want to copy the
database using the least amount of administrative effort.
What should you do?

A. Run a distributed query against the database in San Francisco.
B. Use the BULK INSERT statement to copy the database.
C. Detach the database and attach it at the San Francisco office.
D. Use the DTS Import/Export Wizard to copy the database.
E. Use the bcp utility.

Answer: D

Explanation: You can use the DTS Import/Export Wizard to copy data from one
database to another by specifying a source and target location.

Incorrect Answers:
A: You could populate the database in the San Francisco office by using distributed
queries but this would require that you create and configure linked servers in SQL Server 2000. This would require more addminstrative effort than using the DTS Import/Export Wizard.
B, E: The BULK INSERT statement and the bcp utility can only be used to import data
from a flat file. It cannot be used to copy a database.
C: Detaching and attaching a database effectively moves the database from one location to the other; however, you need to copy the database, not move it.

QUESTION 82
You work as the database administrator at Certkiller .com. The Certkiller .com
network consists of a single Windows 2000 domain named Certkiller .com. All client
computers run Windows 2000 Professional. The Certkiller .com network contains an
Oracle 9i database server named Certkiller -DB01 that hosts a database named
CK_Data. The CK_Data database consists of five comma-delimited files.
Certkiller .com wants to migrate the CK_Data database to SQL Server 2000. You
install a new SQL Server 2000 database server named Certkiller -DB02. You want
to create a new database named CK_Data and all the required tables on
Certkiller -DB02. You must import the data into the CK_Data database on
Certkiller -DB02. You want to accomplish this task as quickly as possible.
What should you do?

A. Use the bcp command to import the required data.
B. Use a BULK INSERT statement to import the required data.
C. Use a SELECT statement with an INTO clause to import the required data.
D. Use the Data Transformation Services (DTS) to import the required data.

Answer: B

Explanation: The bcp command and the BULK INSERT statement can be used to
import data from a text file into a SQL Server 2000 database; however, the BULK
INSERT statement provides the best performance.

Incorrect Answers:
A: The bcp command can be used to import data from a text file into a SQL Server 2000
database; however, the BULK INSERT statement provides the best performance.
C: The SELECT statement can be used to retrieve data from a SQL Server 2000
database. It cannot be used to retrieve data from a text file.
D: Data Transformation Services (DTS) can be used to import data from a single text file but BULK INSERT statement provides the best performance when data from multiple
text files must be imported.

QUESTION 83
You work as the database administrator at hospital named 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.
Certkiller -DB01 hosts a database named CK_Patients that stores patient
information for the hospital. The CK_Patients database contains a table named
PatientsInsurance. The definition for the PatientsInsurance table is show in the
following exhibit.
Exhibit:



You need to export data from the PatientsInsurance table to a text file. When you
export the data, you must ensure that the data from the Expiration column in the
PatientsInsurance table is presented as a character string in the standard USA
format MM/dd/yyyy.
What should you do?

A. Use the GETDATE function.
B. Create a Data Transformation Services (DTS) package.
C. Use the @@DATEFIRST session option.
D. Use the bcp utility with a format file.

Answer: B

Explanation: A Data Transformation Services (DTS) package
transform data by specifying a Transform Data task. This
the format of the data in the Expiration column.

Incorrect Answers:
A: The GETDATE function returns the current date and 2000 format. It does not change the format of datetime
C: The @@DATEFIRST session option is used to map does not change the format of datetime data.
D: The bcp command can be used to transfer data to and bcp utility uses a format file to change the columns in not allow you to change the format of the data itself.


QUESTION 84
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. Certkiller -DB01 hosts two
databases named CK_Sales and CK_Products. The CK_Sales and CK_Products
databases are interrelated.

You need to transform the data in the CK_Sales and CK_Products databases. You
create a Data Transformation Services (DTS) package with two steps. The first step
specifies a Transform Data task for the CK_Sales database and the second step
specifies a Transform Data task for the CK_Products database. To ensure that the
data in the two databases remain consistent, you need to ensure that the DTS
package can be rolled back if either of the tasks fails.
What should you do?

A. Select the Rollback transaction on failure check box for the DTS package.
B. Select the Commit transaction on successful completion of this step check box for the DTS package.
C. Select the Commit transaction on successful package completion check box for the
DTS package.
D. Select the Rollback transaction on failure check box for each step in the DTS package.

Answer: C
Explanation: You should select the Commit transaction on successful package completion check box and Fail package on first error check box for the DTS
package. This will ensure that data is not committed unless all steps in the DTS
package completes successfully.

Incorrect Answers:
A: The Rollback transaction on failure option can only be specified for each step, not for the whole package.
B: The Commit transaction on successful completion of this step option commits data on the successful competition of each step.
D: The Rollback transaction on failure option for each step will only rollback the
transaction if an error is encountered in that step. It will not roll back the transaction for the other step.


QUESTION 85
You work as a 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 two SQL Server
2000 database servers named Certkiller -DB01 and Certkiller -DB02.
Certkiller -DB01 hosts a database named CK_Sales that is used for online
transaction processing (OLTP). Certkiller -DB02 hosts a database named
CK_Data that is used for online analytical processing (OLAP).
You need to export the data in the CK_Sales database to the CK_Data database at
the end of every working week. You create a Data Transformation Services (DTS)
package and store it on Certkiller -DB01. You want the package to execute
automatically every Saturday at 5:00 P.M.
What should you do?

A. Create a new job in SQL Server Enterprise Manager on Certkiller -DB02.
Create a CmdExec job step that runs the dtsrun utility. Schedule the job to run every Saturday at 5:00 P.M.
B. Create a new job in SQL Server Enterprise Manager on Certkiller -DB02.
Create a Transact-SQL job step to query data from the CK_Sales database.
Create a second Transact-SQL job step to launch the DTS package.
Schedule the job to run every Saturday at 5:00 P.M.
C. Move the DTS package to Certkiller -DB02.
Create a new job in SQL Server Enterprise Manager on Certkiller -DB02.
Create a CmdExec job step that runs the dtsrun utility.
Schedule the job to run every Saturday at 5:00 P.M.
D. Create a new job in SQL Server Enterprise Manager on Certkiller -DB01.
Create a Transact-SQL job step to launch the DTS package.
Schedule the job to run every Saturday at 5:00 P.M.

Answer: A

Explanation: Transfers can be defined and stored in a DTS package, which can then
be run from a command line using the dtsrun utility (DTSRUN.EXE). CmdExec job
steps are operating system commands or executable programs ending with .bat,
.cmd, .com, or .exe. In this scenario we create a job consisting of a single cmdexec job step which runs the DTS package using the dtsrun utility.

Incorrect answers:

B: Only one job, not two, has to be created at the OLTP database server.
C: The DTS export package should be run at the OLTP database server, not at the OLAP
database server. The DTS package is exported from the OLTP server to the OLAP server.
D: The job has to be created at the OLTP database server.

QUESTION 86
You work as a 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. Certkiller .com currently
uses an ODBC-compliant proprietary Relational Database Management System
(RDBMS) to store business information.
You have been instructed to migrate some of the databases in the proprietary
RDBMS to SQL Server 2000. You need to ensure that Certkiller .com users can
access data in the RDBMS databases during the migration process. You deploy SQL
Server client tools on all Certkiller .com client computers. You want to ensure that
Certkiller .com users can access data in both database systems by running ad hoc
queries against SQL Server.
What should you do?

A. Create a linked server and a data source name (DSN) for the proprietary RDBMS
system.
B. Create a linked server and use the OLE DB Provider for SQL as a data source.
C. Create a local portioned view of the databases on the RDBMS system in SQL Server.
D. Create stored procedures in SQL Server 2000 to query the RDBMS system.

Answer: A

Explanation: To run distributed queries with a proprietary database system, you
must create a linked server definition for the proprietary database system. As the
proprietary database system is ODBC-compliant, you can create a data source
name (DSN) for the proprietary database system that can be used to identify the
proprietary database system.

Incorrect Answers:

B: The OLE DB Provider for SQL data source can be used for SQL Server 6.5 and later
databases. It cannot be used for proprietary RDBMS systems.
C: A partitioned view must be derived from the same SQL Server instance. It cannot be
created on remote databases.
D: Stored procedures can be run against linked server. However, the Certkiller .com users must be able to use ad hoc queries rather than stored procedures.

QUESTION 87
You work as a 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. Certkiller .com currently
uses an ODBC-compliant third-party Relational Database Management System
(RDBMS) to store business information.
You implement SQL Server 2000 database server named Certkiller -DB02 on the
Certkiller .com network. You need to transfer data from the RDBMS databases to a
SQL Server 2000 database on Certkiller -DB02. You create a Data
Transformation Services (DTS) package to transfer the data and store the DTS
package on Certkiller -DB02. However, when you attempt to execute the DTS
package from a client computer named Certkiller -WS228, the package fails. You
need to ensure that you can execute the DTS package from Certkiller -WS228.
What should you do?

A. Install the OLE DB provider for the third-party RDBMS database on Certkiller -WS228.
B. Add your user account to the bulkadmin fixed server role on Certkiller -DB02.
C. Install the OLE DB provider for the third-party RDBMS database on the RDBMS
database server.
D. Use a SQL Server login to access Certkiller-DB02.

Answer: A

Explanation: The OLE DB Provider is required to communicate wit the
ODBC-compliant RDBMS database. The OLE DB Provider must be installed on the
client computer from which you want to execute the DTS package.

Incorrect Answers:

B: The bulkadmin role allows you to run BULK INSERT statements. It will not assist in
running a DTS package.
C: The OLE DB Provider must be installed on the client computer from which you want
to execute the DTS package.
D: A SQL Server login is required for users that do not have Windows-based client
computers. However, all client computers on the network run Windows 2000 Professional.

QUESTION 88
You work as a 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. Certkiller -DB01 hosts a database
named CK_Sales that stores sales data for the company. The tables in the CK Sales
database are shown in the database diagram.



A PRIMARY KEY constraint has been defined on the InvoiceID column of the
Invoices table. The InvoiceID column was defined as the integer datatype and
specified as the IDENTITY (6,6) property. Certkiller .com users are allowed to
manually insert invoices with IDs that are not multiples of 6 between the existing
invoices. You need to identify the invoices that have been inserted manually.
What should you do?

A. Run the DBCC CHECKINDENT (Invoices, RESEED, 6) statement.
B. Run the SET IDENTITY_INSERT Invoices ON (InvoiceID) VALUES (6) statement.
C. Run the following statement:
SELECT *
FROM Invoices
WHERE
CONVERT (NUMERIC, InvoiceID)/6 <> FLOOR
(CONVERT (NUMERIC, InvoiceID)/6)
D. Run the following statement:
SELECT *
FROM Invoices
WHERE InvoiceID/6 <> FLOOR (InvoiceID/6)

Answer: C

Explanation: You need to identify integer values that are not multiples of six. This
means that you must convert the datatype to numeric so that you can specify
multiples of six as whole numbers. You can then use the FLOOR function to return
the values that are not multiples of six.

Incorrect Answers:

A: The DBCC CHECKINDENT (Invoices, RESEED, 6) statement will set the ID of
the next row inserted in to the table to 6.
B: This is the incorrect usage of the SET IDENTITY_INSERT statement. The SET
IDENTITY_INSERT statement can either be set to YES or NO. When set to YES, it
allows explicit values to be inserted into the identity column of the specified table.
D: This is the incorrect usage of the SET IDENTITY_INSERT statement. The SET
IDENTITY_INSERT statement can either be set to YES or NO. When set to YES, it
allows explicit values to be inserted into the identity column of the specified table.

QUESTION 89
You work as a 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. Certkiller -DB01 hosts a database
named CK_Sales that stores sales data for the company. The tables in the CK Sales
database are shown in the database diagram.



You run daily queries against the CK_Sales database to produce a Stock Level
report. The queries are contained in a stored procedure named Stock_Levels and
are dependant on the OrderDetail and Products tables. The Quantity column in the
OrderDetail table is defined as numeric data. You need to convert the Quantity
column from numeric data to decimal data in a temporary table. You must ensure
that loss of precision or scale does not occur.
What should you do?

A. Perform an explicit conversion by adding a CAST clause to the SELECT statement in
the Stock_Level stored procedure.
B. Perform an explicit conversion by adding a CONVERT clause to the
SELECT statement in the Stock_Level stored procedure.
C. Perform an implicit conversion by running the ALTER TABLE statement with the
ALTER COLUMN clause to change the data type.
D. Add the SET NUMERIC_ROUNDABORT ON statement to the Stock_Level stored
procedure.

Answer: A

Explanation: An explicit CAST clause will prevent loss of precision or scale when
used within the SELECT statement.

Incorrect Answers:

B: The CONVERT clause could cause loss of precision or scale. You should use the
CAST clause. CAST is based on the SQL-92 standard and prevents loss of precision or
scale.
C: The ALTER TABLE statement will not prevent loss of precision or scale.
D: The SET NUMERIC_ROUNDABORT ON statement will set the level of error
reporting that is generated when rounding in an expression causes a loss of precision. It does not prevent loss of precision or scale.


QUESTION 90
You work as a 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. Certkiller -DB01 hosts a database
named CK_Sales that stores sales data for the company. The CK Sales database
contains a table named Customers. The properties page of the Customers table is
shown in the following exhibit.
Exhibit:



You need to ensure that data entered into the CustomerDOB is in the format
mm/dd/yyyy. You want to accomplish this task by performing an implicit
conversion.
What Transact-SQL statement should you run?

A. USE CK_Sales
GO
CONVERT (datetime, (Customers.dbo.CustomerDOB))
GO
B. USE CK_Sales
GO
CAST (datetime, (Customers.dbo.CustomerDOB))
GO
C. USE CK_Sales
GO
ALTER TABLE Customers
ALTER COLUMN CustomerDOB datetime(mm/dd/yyyy) NOT NULL
GO
D. USE CK_Sales
GO
ALTER TABLE Customers
ALTER COLUMN CustomerDOB datetime NOT NULL
GO

Answer: D

Explanation: You need to use the ALTER TABLE statement with the ALTER
COLUMN to perform an implicit conversion.

Incorrect Answers:

A, B: The CONVERT and CAST statements are used to perform an explicit conversion.
They do not perform implicit conversions.
C: The datetime data type does not take a parameter such as (mm/dd/yyyy).