MSCE Interview Questions and Answers

QUESTION 41
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 from the Certkiller .com network CIO to
create a SQL Server 2000 database for the Human Resource department which will
be used to store information about employees and involvements with projects.
Below is a fragment of the database schema:



Certkiller .com has each employee belonging to a specific department participating in
one project for which that department is responsible. Certkiller .com has additionally
assigned each department a unique alphabetic ID. Certkiller .com management has
recently decided to reorganization involving changing each departments ID.
Certkiller .com wants to have all the new IDs to be different from the original
department IDs.
What should you do? (Choose all that apply.)

A. You should specify ON UPDATE CASCADE in the REFERENCES clause in the
Projects table definition.
B. The values in the DepartmentID column in the Departments table should be updated.
C. You should specify ON UPDATE CASCADE in the REFERENCES clause in the
Employees table definition.
D. You should create a FOR UPDATE trigger on the Employee table.
E. You should create a FOR UPDATE trigger on the Projects table.
F. You should create a FOR UPDATE trigger on the Departments table.

Answer: A, B, C

Explanation: In the scenario you should always keep in mind that SQL Server 200
supports cascading referential integrity in the REFERENCE clause of a foreign key
definition which you can specify actions that the SQL Server will perform when the
specific values are referenced primary or candidate key column change or rows are
deleted.

Incorrect Answers:

D, E, F: In the scenario you should not consider using these options because if you
created the FOR UPDATE triggers you also have to create AFTER UPDATE
triggers. If you attempted to update the DepartmentID column the UPDATE statement
would not succeed because the existing FOREIGN KEY constraint on the column.


QUESTION 42
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 a SQL Server 200 database
that will be used to store business information. A table named Test will be used to
store sales information and each sale will be uniquely identified by a specific SaleNo column. You are required to ensure the uniqueness of the values that will be entered
in the SalseNo column.
What should you do?

A. You should define a PRIMARY KEY constraint on the SaleNo column.
B. You should specify the unique identifier data type for the SaleNo column.
C. You should specify ROWGUIDCOL property for the SaleNo column.
D. You should specify an IDENTITY property for the SaleNo column.

Answer: A

Explanation: In the scenario there are several methods which could be used to
ensure the uniqueness of the values in the SaleNo column. The method used in the
answer defines a primary key for the table by specifying a PRIMARY KEY
constraint in a CREATE TABLE or ALTER TABLE statement.

Incorrect Answers:

B: This option should not be considered for use in the scenario because you are able
to include multiple columns of the uniqueidentifier data type.
C: This option should not be used by itself in the scenario because the
ROWGUIDCOL property by itself will not guarantee the uniqueness of the values in a
column.
D: This option should not be used by itself in the scenario as there will be no guarantee that the values in the column will be unique.


QUESTION 43
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.
Certkiller .com is about to start a car valet service for their customers. You have
recently received instruction from the Certkiller .com network CIO to implement a
SQL Server 2000 database that will be used to store the information of the valets.
The valets will be listed as a primary driver for some cars and may be listed a
secondary driver for other cars. Each car will always have one primary driver but
there can be zero or more secondary drivers for each vehicle.
You are required to create the appropriate tables to store the information about the
cars and drivers without introducing redundant data into the database. You are
additionally required to select which script to use.
What should you do?

A. CREATE TABLE Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID) )
CREATE TABLR Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10),
CarID int FOREIGN KEY REFERENCES Cars (CarID) )
B. CREATE TABLR Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10),
PrimaryDriver bit)
CREATE TABLE Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID) )
C. CREATE TABLE CarDriver
(CarID int PRIMARY KEY,
DriverID int PRIMARY KEY,
PrimaryDriver bit)
CREATE TABLR Cars
(CarID int FOREIGN KEY REFERENCES CarDriver (CarID),
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10) )
CREATE TABLE Drivers
(DriverID int FOREIFN KEY REFERENCES CarDriver (DriverID),
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10) )
D. CREATE TABLR Cars
(CarID int PRIMARY KEY,
Make nvarchar (20),
Model nvarchar (20),
LicensePlate nvarchar (10) )
CREATE TABLE Drivers
(DriverID int PRIMARY KEY,
FirstName nvarchar (20),
LastName nvarchar (20),
Phone nvarchar (10) )
CREATE TABLE CarDriver
(CarID int FOREIGN KEY REFERENCES Cars (CarID),
DriverID int FOREIGN KEY REFERENCES Drivers (DriverID),
PrimaryDriver bit,
PRIMARY KEY (CarID, DriverID) )

Answer: D

Explanation: In the scenario you should remember that online transaction
processing (OLTP) systems are often designated according to normalization rules
where each piece of information is stored only in one table avoiding redundant data.

Incorrect Answers:

A: This option should not be used in the scenario because each of the two tables are
referencing the other table and should not be done in the scenario.
B: This option should not be used in the scenario because the FOREIGN KEY
constraints are defined on the wrong tables in the scenario.
C: This option should not be used in the scenario as this will configure one driver to be associated with each car and that should not be done.


QUESTION 44
You work as a database developer at Certkiller .com. You have just finished the
design of a SQL Server 2000 database for Certkiller .com. The database contains a
table named Sales that has 2 million rows, and stores sales information for all
Certkiller .com departments. In the table, each department is identified by the
DepartmentID column. Most queries against the Sales table are utilized to locate
sales for a single department.
You now want to boost the I/O performance of these queries, without affecting the
applications that access the table.
Which of the following will allow you to reach your goal?

A. Create a new table, and move the columns that are most frequently queried to this
table. Retain the DepartmentID column in both tables. Create a view on the original table and on the new table. Add a FOREIGN KEY constraint on the join columns of the new table.
B. Create a new table, and move the columns that are most frequently queried to this
table. Retain the DepartmentID column in both tables. Create a view on the original table and on the new table. Add a CHECK constraint on the DepartmentID columns of both tables.
C. Create one new table for each department, and move the sales information for each
department to that department's table. Add a CHECK constraint on the DepartmentID
columns of the new tables. Create a view on the new tables.
D. Create one new table for each department, and move the sales information for each
department to that department's table. Create a view on the new tables. Add a CHECK
constraint on the DepartmentID column in the view.
E. Create a stored procedure that accepts an input parameter for the department. Use the stored procedure to return results from the Sales table.

Answer: C.

Explanation:
To improve performance, the huge Sales table will be split into several tables. A
horizontal split would be the most beneficial since most queries run are used to find sales for a particular department. In order to keep existing applications functioning, a view will be used which mimics the behavior of the original unsplit Sales table. Deleting the original Sales table and naming the view Sales would allow applications to use sales data in the same way as before.

Incorrect answers:

A: The original table is not needed; in fact, keeping the original table would store the same information in two places, which always is a bad idea in database design.
B: Vertical splitting (column splitting) is not as good as horizontal splitting (row
splitting) in this scenario. We have no indication that some columns are used less
frequently.
D: You can't put a check constraint on a column in a view, only to a column in a table.
E: To increase performance, the sales tables table must be split. Just using a stored
procedure on the existing Sales table will not improve I/O performance.


QUESTION 45
You work as a database developer at Certkiller .com. Certkiller .com recently
acquired a company named SG Publishers. Sales data of SG Publishers is stored in
a SQL Server 2000 database that contains a table named Orders. There is a
clustered index on the Orders table, which is produced by using a customer's name
and the current date.
The Orders table currently has 750,000 rows, and this number of rows will increase
by 5 percent every week. SG Publishers is planning to launch a promotion next
week that will increase the volume of inserts to the Orders table by 50 percent.
Which of the following is the task that you should complete to optimize inserts to the Orders table during the promotion?

A. Create a job that rebuilds the clustered index each night by using the default
FILLFACTOR.
B. Add additional indexes to the Orders table.
C. Partition the Orders table vertically.
D. Rebuild the clustered index with a FILLFACTOR of 50.
E. Execute the UPDATE STATISTICS statement on the Orders table.

Answer: D.

Explanation:
The table's size will increase with 50% during the next week. The database would need to grow and index pages would have to be split. This will degrade performance. It is better to anticipate this growth by rebuilding the data pages, and by rebuilding the clustered index. It is also import to choose an appropriate FILLFACTOR. The default value is 80. By choosing a FILLFACTOR of 50, the pages would be around 75% filled after the promotion and would be allow to grow further.
Note: FILLFACTOR It is seldom necessary to specify a fill factor when you create an index. The option is provided for fine-tuning performance. It is useful when you are creating a new index on a table with existing data, and particularly when you can accurately predict future changes in that data.
FILLFACTOR is specified as a percentage, and it indicates how full SQL Server should
make the leaf level of each index page during index creation. As a table is updated and the index page fills, SQL Server must split the index page to make room for new rows, which is requires time. For update-intensive tables, a properly chosen FILLFACTOR value yields better update performance than an improper FILLFACTOR value. The default FILLFACTOR is usually efficient for most database usage. An explicit
FILLFACTOR setting applies only when the index is first created. SQL Server does not
dynamically keep the specified percentage of empty space in the pages. The default fill factor is 80.

Incorrect answers:

A: Rebuilding the clustered index every night with the default FILLFACTOR requires
more administration. The default FILLFACTOR is 80, and if we assume a few weeks
during which the 50% increase takes place, we see that the data pages would fill up
completely, especially the first day. This would increase index splitting and decrease performance.
B: Adding the correct indexes to a table can be useful for improving the performance of query on a table. Adding indexes, however, could hamper INSERT operations, since they would be added to existing index pages and would reduce the amount of space left in those pages for the indexes of new rows inserted into the table.
Instead, we should anticipate the 50% growth by rebuilding the indexes with a lower
fillfactor.
C: Partitions are used to store and manage pre-calculated aggregations and, sometimes, source data. They also offer flexibility in storing such data in multiple locations and in optimizing its access. This would not, however, anticipate the 50% increase of rows in the table.
E: UPDATE STATISTICS should be re-run when there is significant change in the key
values in the index such as occurs when a large amount of data in an indexed column has been added, changed, or removed, i.e., if the distribution of key values has changed, or the table has been truncated using the TRUNCATE TABLE statement and then
repopulated. In this scenario you are expecting an increase in the volume of data to be inserted into the table. This has not yet occurred, therefore re-running UPDATE
STATISTICS is not required. Furthermore, the UPDATE STATISTICS function is
related to indexing, which is of concern when queries are run against the table. It does not improve INSERT operations.


QUESTION 46
You work as a database developer at Certkiller .com. Certkiller .com is a company that generates an online telephone directory, so you create a table named
PhoneNumbers as shown in the exhibit below.



Subsequent to loading 100,000 names into the table, you create indexes by using the
script below:
ALTER TABLE [dbo]. [PhoneNumbers] WITH NOCHECK ADD
CONSTRAINT[PK_PhoneNumbers]PRIMARY KEY CLUSTERED (
[FirstName],
[LastName],
) ON [PRIMARY]
GO
CREATE UNIQUE INDEX
[IX_PhoneNumbers] ON [dbo].[PhoneNumbers](
[PhoneNumberID]
) ON [PRIMARY]
GO
While testing the performance of the database, you detect that queries such as
"Return all names and phone numbers for persons who live in a certain city and
whose last name begins with W", take a long time to execute.
Which two of the following options make up the solution for improving the
processing performance of these types of queries? (Choose two)

A. Change the PRIMARY KEY constraint to use the LastName column followed by the
FirstName column.
B. Add a nonclustered index on the City column.
C. Add a nonclustered index on the AreaCode, Exchange, and Number columns.
D. Remove the unique index from the PhoneNumberID column.
E. Change the PRIMARY KEY constraints to nonclustered indexes.
F. Execute on UPDATE STATISTICS FULLSCAN ALL statements in SQL Query
Analyzer.

Answer: A, B.

Explanation:
The query searches the City column and then references the LastName column for values
that begin with 'W', thus the City column should be indexed. In addition, changing the PRIMARY KEY constraint, which is used to identify rows in a column, to use the
LastName column before the FirstName column would improve query time performance,
as the FirstName column does not have to be queried. Instead, the rows where the
FirstName column are in the same row as the rows in the LastName column that match
the search criteria only need to be returned. The code for this search would be something like:
SELECT FirstName, LastName, PhoneNumberID
FROM PhoneNumbers
WHERE City=[certain city]
AND LastName LIKE 'W%'


Incorrect answers:

C: The AreaCode, Exchange, and Number columns are not referenced in by the query,
thus the presence or lack of indexes on these columns would not affect query time
performance.
D: The PhoneNumberID column is not referenced by the query, thus the presence or
lack of indexes on this column would not affect query time performance.
E: Generally clustered indexes are faster that non-clustered indexes. The only reason to remove the clustered index on the primary key would be with the idea of using the clustered index on another column. There is no such option in this scenario, which makes the change from a clustered index to a non-clustered index pointless.
Note:
In a nonclustered index the data is stored in one place, and the index in another, with pointers that link the index to the storage location of the data. The items in the index are stored in the order of the index key values, but the information in the table is stored in a different order. SQL Server 2000 searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. Thus, nonclustered indexes should be used on columns that contain a large number of distinct values, and in queries that do not
return large result sets, and on columns that are frequently involved in search conditions of a query that return exact matches. A clustered index alters the physical storage order of the data in the table; as a result, a table can contain only one clustered index. However, the index can comprise multiple columns. A clustered index is particularly efficient on columns that are often searched for ranges of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. If a column is frequently used to sort the data retrieved from a table, it can be advantageous to cluster the table on that column to save the cost of a sort each time the column is queried.
F: SQL Server keeps statistics about the distribution of the key values in each index and uses these statistics to determine which index(es) to use in query processing. Users can create statistics on non-indexed columns by using the CREATE STATISTICS statement.
However, query optimization depends on the accuracy of the distribution of the index.
UPDATE STATISTICS should be re-run when there is significant change in the key
values in the index on that index as occurs when a large amount of data in an indexed
column has been added, changed, or removed, i.e., if the distribution of key values has changed, or the table has been truncated using the TRUNCATE TABLE statement and
then repopulated. The STATS_DATE function can be used to check when the statistics
were last updated. In this scenario large amounts of data have not been added, changed, or removed in the indexed column.


QUESTION 47
You work as a database developer at Certkiller .com. You have recently completed
the design of Certkiller .com's SQL Server 2000 database, which contains a table
named Products and a table named Companies.
You have received instruction from the CIO to insert new product information from
a linked server into the Products table. The Products table currently has a
FOREIGN KEY constraint that references the Companies table. There is also an
UPDATE trigger defined on the Products table.
Which of the following is the option that you should make use of to load the data as
quickly as possible?

A. Use the ALTER TABLE statement and the ON UPDATE clause to modify the
Products table.
B. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to
modify the Products table.
C. Use the ALTER TABLE statement and the DISABLE TRIGGER ALL option to
modify the Companies table.
D. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to
modify the Companies table.
E. Use the ALTER TABLE statement and the NOCHECK CONSTRAINT option to
modify the Products table.

Answer: E.

Explanation:
New products from a linked server must be inserted as quickly as possible on Certkiller 's database. The products should be inserted into the Products table, however there is a foreign key constraint from the Products table to the Companies table. This foreign key constraint will force overhead during the insertion of the new rows. By modifying the Products table by using the ALTER TABLE statement and the NOCHECK CONSTRAINT option, the data would be loaded much more quickly.
The idea is:
1. Remove the check constraints
2. Load the data
3. Add the CHECK constraints again.

Note 1:
A drawback of disabling the foreign key constraint is that product rows with no corresponding company row in the Companies table could be entered. However it seems safe to assume that the new products all belong to already existing companies.
Note 2:
The ALTER TABLE statement is used to modify a table definition by altering, adding,
or dropping columns and constraints, or by disabling or enabling constraints and triggers.
ALTER TABLE acquires a schema modify lock on the table to ensure no other
connections reference the table during the modification. The modifications made to the table are logged and are fully recoverable. Modifications that affect all the rows in very large tables, such as dropping a column or adding a NOT NULL column with a default, can take a long time to complete and generate many log records.
The WITH NOCHECK clause specifies that the data in the table being updated must not
be validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint.
If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK
is assumed for re-enabled constraints. The WITH CHECK and WITH NOCHECK
clauses cannot be used for PRIMARY KEY and UNIQUE constraints.
The WITH NOCHECK clause can be used if you do not want to verify new CHECK or
FOREIGN KEY constraints against existing data. This, however, is not recommended as
any constraint violations suppressed by the WITH NOCHECK clause when the
constraint is added may cause future updates to fail if they update rows that contain data that does not comply with the constraint but had been added while CHECK constraints had been suppressed by the WITH NOCHECK clause.

Incorrect answers:

A: If a row on the table that is being altered is updated through the ALTER TABLE
statement has a referential relationship with a row on another table, the ON UPDATE
clause can be used specify what action should occur to a row on the other table. The
default is NO ACTION. This will raise an error when the row in the referential relation is updated through the ALTER TABLE statement, and the update action on the row in the table that is being altered will be rolled back. If CASCADE is specified, the row in the other table that has a referential relation with a row that is updated through the ALTER TABLE statement. The CASCADE action ON UPDATE cannot be defined if an INSTEAD OF trigger ON UPDATE already exists on the referenced table. As this solution does not specify what action to take, the update will revert to the default NO ACTION, and as the Products table has a FOREIGN KEY constraint that references the Companies table, this will raise an error, and the update action performed by the ALTER TABLE statement on the row on the Products the references a row on Companies table will be rolled back.
B: Disabling the triggers on the products could improve the performance somewhat, but
there is only an UPDATE trigger on the Products and we are going to insert new data.
The update trigger would not be used anyway, so disabling triggers would do no good.
C: The DISABLE TRIGGER ALL clause specifies that all triggers on a table being altered must be disabled. When a trigger is disabled it is still defined for the table; however, when INSERT, UPDATE, or DELETE statements are executed against the table, the actions in the trigger are not performed until the trigger is re-enabled. Furthermore, as it is the Products table that data is being inserted into, the triggers on the Products table and not the triggers on the Companies table should be disabled.
D: As data is to be added to the Products table and not the Companies table, the ALTER TABLE statement will not be run against the Companies table.
The WITH NOCHECK clause specifies that the data in the table being updated must not
be validated against a newly added or re-enabled FOREIGN KEY or CHECK constraint.
If not specified, WITH CHECK is assumed for new constraints, and WITH NOCHECK
is assumed for re-enabled constraints. The WITH CHECK and WITH NOCHECK
clauses cannot be used for PRIMARY KEY and UNIQUE constraints.
The WITH NOCHECK clause can be used you do not want to verify new CHECK or
FOREIGN KEY constraints against existing data.


QUESTION 48
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 from the Certkiller .com network CIO to
create an indexed view in a SQL Server 2000 database. During the course of the day
you create a function that references the view. The function definition you created
includes the SHEMABINDING option.
You have recently successfully tested the function and implemented the view in a
production database residing on another SQL Server computer. You are now no
longer required to view the database on your computer. You are required to select
which of the following should be done to delete the view from your computer.
What should you do? (Choose TWO.)

A. The SCHEMABINDING option should be removed from the function definition.
B. The function should be dropped that references the view.
C. You should do nothing as you can delete the file without performing additional
actions.
D. The table should be dropped that references the view.

Answer: A, B

Explanation: In the scenario you should remember that the SCHEMABINDING
option can be used to prevent objects that are referenced from being changed by
using ALTER and DROP statements. You should also remember that it as a
requirement for all indexed views to include the SCHEMABINDING option.

Incorrect Answers:

C, D: This option will not allow you to delete the table because the
SHEMABINDING option defined in the definition is used to deny users from
changing properties by using ALTER and DROP statements in the scenario, All
indexed views require the SCHEMABINDING option.


QUESTION 49
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 from the Certkiller .com network CIO to
create separate databases on two SQL Server 2000 computers that will be used to
store sales information. The databases used on the network are named TestSales
and BillSales residing respectively one each on the servers named
Certkiller -SR01 and Certkiller -SR02. The two database have an identical
schema. You are required to create a view that will facilitate the creation of reports
about both sales by selecting which statement to use.
What should you do?

A. CREATE VIEW SalesReport AS
SELECT OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
JOIN Certkiller -SR02.BillSales.dbo.Orders
ON Equipment.Computers = Equipment.Office
ORDER BY OrderDate
B. CREATE VIEW SalesReport AS
SLECT OrderID, OrderDate, Amount FROM Orders
WHERE Equipment = 'Test' OR Equipment = 'Bill'
ORDER BY OrderDate
C. CREATE VIEW SalesReport AS
SELECT Equipment = 'Test', OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
JOIN Equipment = 'Bill' OrderID, OrderDate, Amount
FROM Certkiller -SR02.BillSales.dbo.Orders
ORDER BY OrderDate
D. CREATE VIEW SalesReport AS
SELECT Equipment = 'Test', OrderID, OrderDate, Amount
FROM Certkiller -SR01.TestSales.dbo.Orders
UNION ALL Select 'Bill', OrderID, OrderDate, Amount
FROM Certkiller -SR02.BillSales.dbo.Orders

Answer: D

Explanation: In the scenario you should always keep in mind that a view is a
SELECT statement stored in a database which you can query and update tables by
querying and updating views that are derived from the tables in question.

Incorrect Answers:

A, B, C: This statements should not be used in the scenario because they have various
irregularities that do not agree with what you are required to perform. The only common reason that this options share and why they should not be used is the use of the ORDER BY clause which is only allowed in a view definition if a TOP clause is specified.


QUESTION 50
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 from the Certkiller .com network CIO to
implement a SQL Server 2000 patients database for the company. You created the
table named Patients which is shown below:



Certkiller .com has let you configure all the customers to be uniquely identified by
the first name and last name. The Certkiller .com network users will be able to issue
queries against this table which will return names addresses and telephone numbers
of customers last names starting with a specific letter in a specific city. You are
required to create indexes to optimize the performance of such queries.
What should you do? (Choose TWO.)

A. You should create a nonclustered index on the City column.
B. You should create a clustered index on the FirstName and LastName columns.
C. You should create a nonclustered index on the Address column.
D. You should create a nonclustered index on the Phone column.
E. You should create a nonclustered index on the State column.

Answer: A, B
Explanation: You should always remember that the FirstName and LastName are
primary key columns which are by default a unique clustered index on those
columns. Listing the last name first in the table is a brilliant move which helps the
implementation.

Incorrect Answers:

C, D, E: In the scenario you should always remember that it is not required for you to create indexes in the Phone Address or Region columns in the scenario. This option would only be effective if you wanted to return information for customers with specific phone numbers with a specific area code.


QUESTION 51
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 from the Certkiller .com network CIO to plan
the physical implementation of a database that will be used to record credit card
transactions. Certkiller .com expects that several of the tables would be large housing millions of rows. You will be required to create a clustered index on a primary key of each table and non clustered indexes on all columns expected to be frequently used.
The server that will be used to host the database has several hardware-based RAID
devices each with a separate controller. You decided to create a data file in a
separate filegroup on each RAID device and want to place tables and indexes on the
filegroups to optimize database performance.
What should you do?

A. You should place nonclustered indexes for the frequently joined tables on separate file groups.
B. You should create more files in each filegroup and place frequently used tables on a separate file.
C. You should place frequently used tables on a different filgroup than its clustered
index.
D. You should make an alphabetical list of all the tables in the database and place each table and its indexes on a separate filegroup in a round-robin fashion.

Answer: A

Explanation: In the scenario you should always remember in order to optimize the
performance of queries and transactions against a database that it is recommended
you distribute frequently used tables amongst many physical disks or RAID devices
as possible. Making this configuration ensures that physical I/O among multiple
devices which enables access to several tables or indexes in parallel.

Incorrect Answers:
B: There is no need to consider this option in the scenario because SQL Server
automatically distributes the data evenly amongst multiple files that belong to the same filegroup.
C: This option should not be considered in the scenario because a clustered index is
inseparable from its base table and cannot be placed on a different filegroup than the table.
D: This option should not be used in the scenario as there is no guarantee that the
frequently used tables will be placed on separate disks as this will only degrade the
performance.


QUESTION 52
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 received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information.
Certkiller .com makes use of a table that is updated when ever the wind tunnel
becomes empty by adding event parameters to the Detail table which is shown



The duration of each testing session will vary depending on the technology tested.
The wind tunnel will be pressurized about 100 times a day after which maintenance
of the tested equipment should be performed. The information of the Detail table
will be summarized and archived into a History table. Every time testing resumes
the Detail table should be truncated, the Detail table should not contain more than
100 rows at any time.
What should you do?

A. You should create a rule
B. You should create a column-level CHECK constraint
C. You should create a table-level CHECK constraint
D. You should create a trigger

Answer: D

Explanation: In the scenario you should remember that you are able to control the
number of rows in the table by creating an INSTEAD OF INSERT trigger which
will fire and use the COUNT (*) function to calculate the rows in the required table.

Incorrect Answers:
A: This option should not be used in the scenario because rules provide backward
compatibility by offering a subset of the functionality of CHECK constraints and can not be used to determine the number of rows.
B, C: This option should not be used in the scenario as this would require and additional row to be created on the table and the table should not have more than 10 rows at any given time.


QUESTION 53
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. Certkiller .com has its
headquarters in Chicago and branch office in Miami.
You have received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information. During the
course of the business day you receive additional instruction to create a view in a
SQL Server 2000 database. You want to create indexes on the view to improve the
performance.
You have successfully created the views but when you attempt to implement the
same view in the branch office only the view is created and not the indexes on the
view. You are required to determine the cause of the problem.
What could the cause be?

A. Tables are referenced by the view by using two-part names rather than four-part
names.
B. In the branch office the ANSI_NULLS option was set to on when the view was
created.
C. The SCHEMABINDING option is included in the view.
D. The Standard Edition of SQL Server 2000 is used at the branch office.

Answer: D

Explanation: In the scenario you should remember that a view is a SELECT
statement that is stored in a database and is dynamically executed when the view is
referenced in a query or another view.

Incorrect Answers:

A: This option is not what is wrong in the scenario because all referenced tables must be referenced by using two-part qualified names in the scenario.
B, C: This option is not what is wrong tin the scenario because this is one of the
requirements that should be met to be able to create indexed views.


QUESTION 54
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 received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information. A network
user named Rory Allen has recently decided to leave the company, she created a
table named BillAccount in a SQL Server 2000 database and loaded some
information into it before leaving. You are a member of the db_dlladmin database
role. You want to drop the BillAccounts table and issue the statement below:
DROP TABLE BillAccounts You receive the following message:
Server: Msg 3701, Level 11, State 5, Linr1
Cannot drop the table 'BillAccounts', because it does not exist in the system
catalog You are required to drop the table.
What should you do?

A. A system administrator should be asked to grant you the DROP TABLE permission in
the database.
B. A member of the db_owner database role should be asked to delete the table.
C. A member of the sysadmin server role should be asked to delete the table.
D. You use a two-part qualified name for the table in the DROP TABLE statement.

Answer: D

Explanation: In the scenario you should keep in mind that SQL Server 200 database
objects names can be preceded by up to three qualifiers. The database assumes that
the name of the current database owner and the user names in the current database
is mapped to the login of the current user.

Incorrect Answers:

A: This option should not be used in the scenario because the database assumes that the name of the current database owner and the user names in the current database is mapped to the login of the current user.
B, C: This option should not be considered in the scenario because the object
created by members of the roles in question belongs to dbo.


QUESTION 55
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 received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information. The
Certkiller .com network users frequently query a table named Parts which is used to
store the information about prototype modifications shown below:



The network users typically use queries which return the Part and price. You are
required to optimize the performance of such queries by adding or modifying
indexes. The table currently has a clustered index on the PartID column and a
nonclusterd index on the SKU column.
What should you do?

A. You should create a clusterd index on the Description column.
B. You should create a nonclustered index on the LocationID column.
C. The clusterd index on the PartID column should be changed to nonclustered.
D. The nonclusters index on the SKU column should be changed to include the
LocationID column.

Answer: D

Explanation: You should remember that indexes can be used to improve the
performance of such particular queries in the scenario. By creating a composite
nonclustered index of the two columns would likely improve query performance.

Incorrect Answers:
A, C: This option should not be used in the scenario as you would gain no performance
increase by making the changes specified in the answer.
B: There is no need for you to cerate a nonclustered index on the LocationID column
as its is not used to search for rows that meet the specified criteria.


QUESTION 56
You work as a database developer at Certkiller .com. You have recently finished the
design of Certkiller .com's SQL Server 2000 database.
You are now in the process of deleting objects in the database that is not being used
anymore. You then find that you are unable to drop the 1997Sales view. After a
brief investigation, you discover that the view has the following characteristics:
1. There is a clustered index on the view.
2. The sales database role has permissions on the view.
3. The view uses the WITH SCHEMABINDING option.
4. A schema-bound inline function references the view.
5. An INSTEAD OF trigger is defined on the view.
Of the following options, which is the one that you should perform before you will
be able to drop the view?

A. Drop the clustered index on the view.
B. Remove all permissions from the view.
C. Remove the WITH SCHEMABINDING option from the view.
D. Remove the WITH SCHEMABINDING option from the function that is referencing
the view.
E. Disable the INSTEAD OF trigger on the view.

Answer: D.

Explanation: The CREATE FUNCTION supports a SCHEMABINDING clause
that binds a function to the schema of an object, such as a table, a view, or another
user-defined function that it references. An attempt to alter or drop any object
referenced by a schema-bound function will fail. The ALTER FUNCTION can be
used to remove the SCHEMABINDING clause by redefining the function without
specifying the WITH SCHEMABINDING clauses.

Incorrect answers:

A: A clustered index on the view would not prevent it from being deleted.
Note: A clustered index alters the physical storage order of the data in the table. A table can contain only one clustered index and can comprise multiple columns. A clustered index is particularly efficient on columns that are often searched for a range of values. After the row with the first value is found using the clustered index, rows with subsequent indexed values are guaranteed to be physically adjacent. If a column is frequently used to sort the data retrieved from a table, it can be advantageous to cluster the table on that column to save the cost of a sort each time the column is queried. When an index is no longer needed, it can be deleted from a database and the storage space it uses can be reclaimed. Only the owner of a table can delete indexes on that table, and the owner of the table cannot transfer this permission to other users. However, members of the db_owner and db_ddladmin fixed database roles and sysadmin fixed server role can drop any database
object by explicitly specifying the owner of the object in the DROP VIEW statement.
Furthermore, indexes created on any views or tables are automatically deleted when the view or table is dropped, and since the DROP VIEW statement can be executed against indexed views, indexes do not prevent the dropping of a view.
B: A view can be deleted if it is no longer needed, or if the view definition and the
permissions associated with it need to be cleared. When a view is deleted, the tables and the data upon which it is based are not affected. Thus, the permissions that pertain to a view do not prevent the view from being dropped.
C: The SCHEMABINDING clause is supported by the CREATE VIEW statement and
binds the view to a schema. Views or tables participating in a view created with the
schema binding clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Dropping the SCHEMABINDING on the view would
make it possible to drop those other views or tables. This SCHEMABINDING does not,
however, prevent the view itself from being dropped.
E: Disabling a trigger on the view would not affect the possibility of dropping the view.
Note: Triggers are a special type of stored procedure that execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view. INSTEAD OF triggers allow views that are not updateable to support updates. A view comprising multiple base tables must use an INSTEAD OF trigger to support inserts, updates and deletes that reference data in the tables. A trigger can be deleted when it is no longer needed. When a trigger is deleted, the table and the data upon which it is based are not affected. While permissions to delete a trigger default to the owner of the table or view upon which the trigger is defined, deleting the table or view will automatically delete any triggers on that table or view.


QUESTION 57
You work as a database developer at Certkiller .com. Certkiller .com has recently
acquired a company named Test Labs, Inc. You have received instruction from the
CIO to design a SQL Server 2000 database for Test Labs, Inc. that will be
distributed with an application to numerous companies.
You have created a number of stored procedures in the database that contains
confidential information, and you now want to prevent the other companies from
viewing.
Which of the following will allow you to achieve this objective?

A. Remove the text of the stored procedures from the syscomments system table.
B. Encrypt the text of the stored procedures.
C. Deny SELECT permissions on the syscomments system table to the public role.
D. Deny SELECT permissions on the sysobjects system table to the public role.

Answer: B.

Explanation:
The WITH ENCRYPTION clause can be used to ensure that the stored procedure's
definition cannot be viewed by other users. The procedure definition is then stored in an unreadable form. When a stored procedure is encrypted, its definition cannot be
decrypted and cannot be viewed by anyone, including the owner of the stored procedure
or the system administrator.

Incorrect answers:

A: The syscomments system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. The text column
contains the SQL definition statements, which are limited to a size of 4 MB. This table is stored in each database. None of the entries in syscomments should be deleted, as the corresponding stored procedure will not function properly when its entry in syscomments is manually removed or modified. To hide or encrypt stored procedure definitions, use CREATE PROCEDURE (or ALTER PROCEDURE) with the ENCRYPTION keyword.
C: The syscomments system table contains entries for each view, rule, default, trigger, CHECK constraint, DEFAULT constraint, and stored procedure. Denying SELECT
permission for the public role would not hide the procedure definition from users.
D: The sysobjects system table contains one row for each object (constraint, default, log, rule, stored procedure, and so on) created within a database. Denying SELECT permission for the public role would not hide the procedure definition from users.


QUESTION 58
You work as a database developer at Certkiller .com. The local bank recently hired
Certkiller .com to design a SQL Server 2000 database that will be used to store sales
data.
You have just completed the design, and now you want to create an indexed view in
this database. You make use of the script shown in the exhibit below to accomplish
this.



When the index creation fails, you are presented with an error message. You have to
ensure that the error message is eliminated and that the index is created.
Which of the following is a task that you should carry out to achieve this objective?

A. Add an ORDER BY clause to the view.
B. Add a HAVING clause to the view.
C. Change the NUMERIC_ROUNDABORT option to ON.
D. Change the index to a unique, nonclustered index.
E. Add the WITH SCHEMABINDING option to the view.

Answer: E.

Explanation:
To create a clustered indexed on a view, the view must meet a number of requirements.
One of these is that the view must be created with the SCHEMABINDING option.
SCHEMABINDING binds the view to the schema of the underlying base tables.
Reference: BOL, CREATE VIEW

Incorrect answers:

A: Although a view in which the SELECT statement that defines the view can use the
ORDER BY clause, the ORDER BY clause is not required in creating the clustered
index.
B: If GROUP BY is specified in a view, the SELECT list must contain a
COUNT_BIG(*) expression, and the view definition cannot specify the HAVING,
CUBE, or ROLLUP clauses.
C: The NUMERIC_ROUNDABORT option would not prevent the creation of indexes
on the view.
Note: When NUMERIC_ROUNDABORT is set to ON, an error is generated when loss
of precision occurs in an expression. When it is set to OFF, losses of precision do not generate error messages and the result is rounded to the precision of the column or variable storing the result. SET NUMERIC_ROUNDABORT should be set to OFF when
indexes are created or manipulated on computed columns or indexed views to keep the
highest possible precision.
D: A clustered index determines the physical order of data in a table while a
nonclustered index does not. A unique index on a view can be either clustered or
nonclustered. A clustered index gives the best performance. We should therefore try to create a clustered index.


QUESTION 59
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 received instruction from the Certkiller .com network CIO to implement a
SQL Server 200 database that will be used to store business information. The
network users of Certkiller .com access the database using several custom
applications based on stored procedures. Certkiller .com has recently finished several new products.
The results of the new products has resulted in the network users reporting that
operations involving the products table have become slower. The performance of
operations that do not involve the products table has not deteriorated. You are
required to restore performance of the affected operations to their original level.
What should you do?

A. The EXECUTE statement that call the stored procedure in the applications should be
changed to include the RECOMPILE option.
B. The sp_updatestats stored procedure should be executed against the Products table.
C. The UPDATE STATISTICS statement should be issued against the database.
D. The sp-Recompile stored procedure should be executed against the Products table.

Answer: D

Explanation: In the scenario you should remember that SQL Server compiles
execution plans for the stored procedures and queries the first time they are issued.
So making use of this answer will definitely improve the performance of the custom
applications used by the network users.

Incorrect Answers:

A: This option should not be used in the scenario because the performance of the
application will not be improved since the stored procedure would be recompiled every
time that it is called.
B: This option should not be used because this stored procedure executes an UPDATE
STATISTICS statement against each user-defined table in the current database.
C: This option should not be considered for usage in the scenario because you are only able to issue this command against a table not an entire database.


QUESTION 60
You work as a database developer at Certkiller .com. Certkiller .com has a SQL
Server 2000 database that includes information about companies situated in specific
postal codes. The database has table named Company that contains this
information.
Currently, the database contains company data for five different postal codes.
Currently the number of companies in a specific postal code varies from 10 to 5,000,
and more companies and postal codes will be added to the database over time.
You are in the process of creating a query to retrieve information from the
database. You have to accommodate new data by making only minimal
modifications to the database. Also, the performance of your query should not be
influenced by the number of companies returned.
Which of the following is a task that you should execute to create a query that
performs consistently and reduces future maintenance?

A. Create a stored procedure that requires a postal code as a parameter. Include the
WITH RECOMPILE option when the procedure is created.
B. Create one stored procedure for each postal code.
C. Create one view for each postal code.
D. Split the Company table into multiple tables so that each table contains one postal code. Build a partitioned view on the tables so that the data can still be viewed as a single table.

Answer: A.

Explanation:
We need to meet the following requirements:
1. The new data, more companies and postal codes, must be added with minimal changes
to the database.
2. Future maintenance should be minimized.
3. The performance of the query should not be affected by the number of companies
returned.
4. The query should perform consistently.
A single procedure with a postal code parameter meets the first requirement. We only add a single procedure and make no further changes to the existing database.
The second requirement is met since no adjustments have to be made for additional
companies and postal codes.
The WITH RECOMPILE option will force a recompilation of the execution plan every
time the stored procedure is run. This could decrease the performance, especially if the stored procedure is used frequently.
The third requirement is met because the performance of the query is not affected by the companies returned, since the execution is recompiled every time the stored procedure is run.
The fourth requirement, a consistent performance of the query, is also met by the same argument.
Note: Specifying the WITH RECOMPILE option in stored procedure definition indicates
that SQL Server should not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. The use of the WITH RECOMPILE option causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed. This option should only be used when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time