Question With Answer : 70-229 Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition
21. You are database developer for your company's SQL Server 2000 database named Sales. The company has several custom Web-based applications that retrieve data from the Sales database.
Some of these applications use the EXECUTE statement to allow users to issue administrator ad hoc queries. As the use of the Web-based applications increases, queries are taking longer to execute.
You want to discover which applications are sending a high number of these queries to the database server.
What should you do?
A. Use SQL profiler to capture the RPC:Completed event. Group the trace by the HostName data column
B. Use SQL profiler to capture the SQL:StmtCompleted event. Group the trace by the ApplicationName data column
C. Use system monitor to monitor the SQLServer:Database counter. Select all counters for the sales database
D. Use system monitor to monitor the SQLServer:General Statistics counter. Select all counters for the sales database
Ans: B
22. You are a database developer for a multinational corporation. The Company has a centralized online transaction processing database located on a SQL Server 2000 computer.
This database has a table named Sales, which contains consolidated sales information from the Company's offices.
During the last year, more than 150,000 rows have been added to the sales table. Users of the database report that performance during the course of the year has steadily decreased.
You need to improve the performance of queries against the Sales table.
In the SQL Query Analyzer, which script should you execute?
A. EXEC sp_updatestatics 'resample'
B. CREATE STATISTICS Sales WITH FULLSCAN
C. Sp_autostats 'Sales'
D. UPDATE STATISTICS Sales WITH FULLSCAN ALL
Ans: A
WARNING: This was wrong in the orig razor, D looked:
UPDATE STATISTIC_Sales WITH FULLSCAN, ALL
But the real D was that you see up there, with the missing comma as syntax error making D incorrect!
23. You are a database developer for your company's SQL Server 2000 online transaction processing database. You have written several stored procedures that will produce critical sales reports.
The stored procedures access existing tables, which are indexed.
Before you put the stored procedures in the production environment, you want to ensure optimal performance of the new stored procedures. You also want to ensure that daily operations in the database are not adversely affected.
What should you do?
A. Create a covering index for each query contained in the stored procedures.
B. For each query in the stored procedures, create an index that includes each column contained in the WHERE clause
C. Use output from the Index Tuning Wizard to identify whether indexes should be added.
D. Create statistics on all columns in the SELECT and WHERE clauses of each query
Ans: C
24. You are a database developer for an insurance company. You are informed that database operations such as selects, inserts, and updates, are taking much longer than they were when the database was created a year ago.
The previous developer added necessary indexes on the tables when the database was created. Since that time, additional tables and stored procedures have been added to the database. In addition, many of the queries are no longer used.
You want to improve the response time of the database operations as quickly as possible.
What should you do?
A. Execute the DBCC UPDATEUSAGE statement against the database to update the sysindexes system table
B. Execute the DBCC SHOW_STATISTICS statement to find high-density indexes. Drop the high-density indexes.
C. Run the Index Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.
D. Use SQL profiler to find table scans. Add indexes to tables that were found to have table scans.
Ans: C
WARNING: In the orig razor the C option was:
C Run the Index Drop Tuning Wizard against a workload file to suggest indexes to create and drop the suggested indexes.
But there is no such thing as Index Drop Tuning Wizard.
25. You are a database developer for a sporting goods company. The company has one main office and many regional offices across the United States. A 56-Kbps frame relay network connects the offices.
Each office has a SQL Server 2000 database that contains information about the company's products. The main office SQL Server database is used to process incremental updates to the regional office databases.
Transactional replication is used to perform these updates.
Each quarter, you create a new snapshot file. You use this snapshot file to replace the regional office databases with the latest product information. This snapshot file is now more than 800MB in size.
You need to apply the snapshot file to the regional office databases. You want to accomplish this by using the least amount of expense.
What should you do?
A. Use transactional replication. Mark all subscriptions for reinitialization.
B. Copy the snapshot to an NTFS compressed share. Create a CD from that share. Distribute the CD to the regional office.
C. Create a compressed snapshot in the default snapshot folder on the Distributor. Create a CD from that folder. Distribute the CD to the regional offices.
D. Create a compressed snapshot in the alternative snapshot folder on the network. Create a CD from that folder. Distribute the CD to the regional offices.
E. Create a compressed snapshot in an alternative snapshot folder on the network. Use FTP to distribute the snapshot to the regional offices.
Ans: D
26. You are a database developer for your Company's SQL Server 2000 database. This database contains a table named Sales, which has 2 million rows. The sales table contains sales information for all departments in the company.
Each department is identified in the table by the DepartmentID column. Most queries against the table are used to find sales for a single department.
You want to increase the I/O performance of these queries. However, you do not want to affect the applications that access the table.
What should you do?
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 from the sales table.
Ans: C
27. You are a database developer for your Company's SQL Server 2000 database. You are deleting objects in the database that are no longer used. You are unable to drop the 1997 Sales view.
After investigation, you find that the view has the following characteristics:
· There is a clustered index on the view.
· The sales database role has permissions on the view.
· The view uses the WITH SCHEMABINDING option.
· A schema-bound inline function references the view.
· An INSTEAD OF trigger is defined on the view.
What should you do before you can 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.
Ans: D
28. You are a database developer for Proseware Inc. You are creating a database named Human Resources for the company. This database will contain all employee records and demographics information.
The company has 2,000 employees and experiences a yearly turnover rate of about 2 percent. When employees leave the company, all of their records must be retained for auditing purposes.
Employee demographics information changes at a yearly rate of about 9 percent. You do not need to maintain a history of demographics changes.
The schema for the human resources database is shown in the human resources schema exhibit, and the scripts that will be used to create the indexes are shown in the Index Scripts:
ALTER TABLE [dbo].[Employee] WITH NOCHECK ADD
CONSTRAINT [pk_Employee] PRIMARY KEY CLUSTERED
([EmployeeID])
WITH FILLFACTOR = 90
GO
ALTER TABLE [dbo].[EmployeeDemographics] WITH NOCHECK ADD
CONSTRAINT [dbo].[EmployeeDemographics] PRIMARY KEY CLUSTERED
([EmployeeID])
WITH FILLFACTOR = 90
GO
You want to conserve disk space and minimize the number of times that expansion of the database files needs to occur. All varchar columns are 50 percent full.
Which two parameters should you specify for the CREATE DATABASE statement? (Each correct answer presents part of the solution. Choose two)
A. SIZE = 1GB
B. SIZE = 1MB
C. SIZE= 2048KB
D. FILEGROWTH = 20
E. FILEGROWTH = 5%
F. FILEGROWTH = 0
Ans: C,E
29. You are a database developer for your company's SQL Server 2000 database. This database contains a table named products and a table named companies.
You want to insert new product information from a linked server into the products table. The products table has a FOREIGN KEY constraint that references the companies table. An UPDATE trigger is defined on the products table.
You want to load the data as quickly as possible.
What should you do?
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.
Ans:E
30. You are the database developer for your company's Accounting database. The database contains a table named Employees.
Tom is a member of the Accounting department. Tom's database user account has been denied SELECT permissions on the salary and BonusPercentage columns of the Employees table. Tom has been granted SELECT permissions on all other columns in the table.
Tom now requires access to all the data in the Employees table.
What should you do?
A. Revoke SELECT permissions on the Salary and BonusPercentage columns of the employees table for Tom's database user account.
B. Add Tom to the db_datareader database role.
C. Add Tom to the db_accessadmin database role.
D. Grant SELECT permissions on the Salary and BonusPercentage columns of the Employees table for Tom's database user account.
Ans:D