Scrigroup - Documente si articole

Username / Parola inexistente      

Home Documente Upload Resurse Alte limbi doc  


AccessAdobe photoshopAlgoritmiAutocadBaze de dateCC sharp
CalculatoareCorel drawDot netExcelFox proFrontpageHardware
HtmlInternetJavaLinuxMatlabMs dosPascal
PhpPower pointRetele calculatoareSqlTutorialsWebdesignWindows
WordXml

AspAutocadCDot netExcelFox proHtmlJava
LinuxMathcadPhotoshopPhpSqlVisual studioWindowsXml

Cheet Sheets: Administering Microsoft SQL 2000 (70-228)

sql

+ Font mai mare | - Font mai mic



DOCUMENTE SIMILARE

Trimite pe Messenger
Cheet Sheets: Administering Microsoft SQL 2000 (70-228)


Cheet Sheets: Administering Microsoft SQL 2000

Word 97 Edition




**The following questions have appeared on actual exams. Situational details (ie, names, locations, and quantities) have been changed to avoid copyright infringement. Memorize as many of these questions as possible in the 5 nights prior to your exam. Some questions will refer to exhibits that are not available. Be sure to memorize every detail of these questions…we have given you enough information that they are easily recognizable on the actual exam. If you cannot memorize all these questions, memorize them from end to beginning—the higher numbered questions are the most recent. Good luck. –Keen Interactive

  1. You are the administrator of a SQL Server 2000 computer. Your company uses the server to store service contract information for its customers. You are also the administrator of an Oracle relational database management system (ROEMS) server. This server is used to store your company's financial information. The financial information is updated frequently throughout the day. You need to create a series of reports that combine the service contract information and the financial information. These reports will be updated several times a day. You want to create reports on the SQL Server computer by using the minimum amount of disk space. What should you do?

A.          Set up SQL server replication to replicate the data from the Oracle server to the SQL server computer.

B.          Set up the Oracle server as a linked server.

Create a view that joins the service contract information and the financial information.

C.          Set up a data transformation services (DTS) package that imports and transforms the database from the Oracle server to the SQL server computer. Use SQL Server Agent to execute the DTS package throughout the day as needed.

D.          Set up an Microsoft ActiveX script that connects to the Oracle server and imports the financial information into an SQL Server temporary table.

Create a view that joins the service contract information and the temporary table.

  1. You are the administrator of two SQL Server computers. One server is named SQL7, and other is named SQL2000. SQL7 is running SQL server 7.0 and SQL2000 is running SQL server 2000. The net-libraries on SQL2000 are configured as shown in the exhibit (exhibit not available). SQL7 is configured so that it has the Named Pipes, TCP/IP, NWLink, IPX/SPX and Multiprotocol net libraries. SQL2000 and SQL7 exchange confidential company information. You need to ensure that unauthorized users cannot access this information. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          On SQL2000, enable the Multiprotocol net library.

B.          On SQL2000, select the ‘Force protocol encryption’ check box.

C.          On SQL7, select the ‘Force protocol encryption’ check box.

D.          On SQL2000, install a secure sockets layer (SSL) encryption certificate.

E.           On SQL2000 and SQL7, enable multipoint encryption.

  1. You are the administrator of Microsoft Windows 2000 computer. You are preparing to install SQL Server 2000 on the computer. Your company contains a variety of client computers that will connect to the SQL server 2000 computer by using a specific net-library, as shown in the following table.

Client computer

Net-Library

Microsoft Windows 98

Named Pipes

Novell NetWare

IPX/SPX

Apple Macintosh

TCP/IP

You need to allow the client computers to connect to the SQL Server computer. You also want to minimize the number of configuration changes required on the client computers. Which three actions should you take? (Each correct answer presents part of the solution. Choose three)

A.          Install SQL server 2000 as a named instance.

B.          Install SQL server 2000 as the default instance.

C.          Configure the new instance for Mixed Mode Authentication.

D.          Configure the new instance for Windows authentication.

E.           Configure the server to use the named piped, IPX/SPX, and TCP/IP Net-Libraries.

F.           Configure the server to use the Multiprotocol Net-Library.

  1. You are the administrator of a SQL server 2000 computer. The server contains a database named inventory. Developers at your company upgrade an inventory tracking application. Users report that when they insert new information in the Locations table, the upgrade application returns the following error message:

‘String or binary data would be truncated. The statement has been terminated.’

When you use SQL Profiler to trace the activity of the application, you receive the results as shown below:

Event Class Test Date Application Name

SQL:BatchCompleted Set implicit_transactions off MS SQLEM

SQL:BatchCompleted Use inventory MS SQLEM

Audit Login Network protocol : LPC set quoted Visual Basic

SQL:BatchCompleted Insert into locations Visual Basic

You examine the design of the locations table as shown in the table design below:

Table Schema

Customers  Orders Employees

CustomerID  OrderID EmployeeID

CompanyName CustomerID LastName

ContactName  EmployeeID FirstName

ContactTitle  OrderDate Title

Address  RequiredDate TitleofCourtsey

City  ShippedDate BirthDate

Region  ShipVia HireDate

PostalCode  Freight Address

Country  ShipName City

Phone  ShipAddress Region

Fax  ShipCity PostalCode

ShipRegion Country

Suppliers  Products Order Details

SupplierID  ProductID OrderID

CompanyName ProductName ProductID

ContactName  SupplierID UnitPrice

ContactTitle  CategoryID Quantity

Address  QuantityPerUnit Discount

City  UnitPrice

Region  UnitsInStock

PostalCode  UnitsOnOrder

Country  ReorderLevel

Phone  Discontinued

Fax

HomePage

Categories

CategoryID

CategoryName

Description

Picture

You need to configure the database to support both versions of the application without affecting server performance. What should you do? 

A.          Alter the data type of the description field to varchar(50).

B.          Alter the data type of the Special field to varchar(50).

C.          Alter the data type of the CubicFeet field to float.

D.          In the locations table, create an INSTEAD OF trigger that truncates the description field at 10 characters when the record is updated.

E.           In the locations table, create an INSTEAD OF trigger that truncates the Special field at 10 characters when the record is updated.

  1. You are the administrator of an SQL Server 2000 computer. You configure a set of alerts on the server to notify you whenever certain operations fail or encounter errors. The notifications are sent to your Microsoft Windows 2000 Professional computer by using the ‘net send’ command. You successfully test the alerts. After several days, however, you stop receiving notifications when operations fail or encounter an error. You verify that the SQLServerAgent service is started. You need to make sure that you continue to receive alert notifications. What should you do?

A.          Stop and restart the SQLServerAgent service.

B.          Clear the application log on the SQL server computer.

C.          Clear the system log on your Windows 2000 Professional computer.

D.          Install the SQL Server 2000 administrative tools on your Windows 2000 Professional computer.

You are the administrator of a SQL Server 2000 computer that contains a database. Users report that queries to this database respond slowly. You use System Monitor to examine the subsystems on your server and receive the results shown in the exhibit (in the exhibit, the % processor time remains at 100 all the time). You need to modify the server to accelerate query response time. What should you do?

A.          Increase the amount of RAM.

B.          Upgrade to a faster disk subsystem.

C.          Add a faster network adapter.

D.          Add an additional processor.

  1. You are the administrator of an SQL Server 2000 computer. You create a job that performs several maintenance tasks on the server’s databases. You want the job to run whenever the server’s processor utilization fails below 5 percent. You create a new schedule for the job and specify the ‘start whenever the CPU(s) become idle’ option. After several days, you notice that the job has never executed, although the server’s processor utilization has fallen below 5 percent several times. What should you do?

A.          Modify SQL Server Agent properties and specify a smaller idle time.

B.          Modify SQL Server Agent properties and specify a larger idle time.

C.          Write a stored procedure that executes the job whenever the GGIDLE system variable is less than 5.

D.          Write a stored procedure that executes the job whenever the GGIDLE system variable is greater than 1.

  1. You are the administrator of a SQL Server 2000 computer. Your company modifies a sales application it uses so that it can access data from a SQL Server database rather than a Microsoft Access database. You configure the new SQL server database. The tables are configured as shown in the table schema exhibit (exhibit not available). Users report that queries to this database respond slowly. You use System Manager to analyze database activity and receive the results shown in the exhibit (exhibit not available). You need to modify the database to accelerate query response time. What should you do?

A.          Place primary keys and foreign key indexes on the table.

B.          Remove all indexes from the tables.

C.          Replace the table joins with correlated subqueries.

D.          Use a server-side cursor to retrieve data.

  1. You are the administrator of a Microsoft Windows 2000 Advanced Server computer. The server is also running SQL Server 2000 and contains a database named sales, as shown in the exhibit below:


The sales database is configured by using the default options and increases by 50MB per day. You want an operator to be notified automatically before the database runs out of disk space. You also want to minimize the amount of administrative overhead necessary for this database. What should you do?

A.          Configure System Monitor to log an alert if the hard disk database contains less than 600MB of disk space. Configure SQL Server Agent to send an e-mail message notifying the operator of the System Monitor alert.

B.          Create a SQL Server Agent job that uses the sp_spaceused stored procedure each morning. Place the results of the stored procedure in the e-mail message and set SQL mail to send the results to the operator.

C.          Set the Maxsize property of sales_data.mdb to 7.5GB and the Maxsize property of log file to 1.5GB. Configure SQL Server Agent to send an e-mail message to an operator when the data file and the transaction log have reached these limits.

D.          Use Microsoft Windows Explorer to verify the available space on the hard disk each morning. Execute the sp_spaceused stored procedure each morning.

  1. You are the administrator of a Microsoft Windows NT Server 4.0 computer. The server is the PDC in your domain and also runs SQL Server 2000. The server has four processors and 1GB of RAM. Your network consists of 600 client computers that are running Microsoft Windows Millennium edition. Users report that when they log on in the morning their computers respond slowly and occasionally return error messages. You use System Monitor to monitor processor performance and RAM utilization. You discover that the %Total Time and %User Time counters average 90% and the %privileged time counter averages 20 percent. The available Mbytes counter averages 600MB. You need to improve server performance for user logons. What should you do?

A.          Increase the physical RAM on the computer to 2GB.

B.          Increase the virtual RAM on the computer to 4GB.

C.          Configure SQL server to use only processors 1, 2, and 3.

D.          Configure SQL server to use only processors 0, 1 and 2.

  1. You are the administrator of an SQL Server 2000 computer. Each night, you need to send a copy of the server’s MSDB database as an e-mail message to the administrator of another SQL server computer.

You create a job that contains the following steps:

o        Detach the MSDB database.

o        Send the database to the administrator in an e-mail message.

o        Re-attach the MSDB database.

You test the job, and it fails on the first step. You need to ensure that the MSDB database is mailed to the administrator every night. What should you do?

A.          Ensure that the SQLServerAgent service is running under a user account that has database owner access to the MSDB database.

B.          Delete the first and last steps in the job.

C.          Configure the job to back up the MSDB database to a temporary file. Send the file to the administrator in an e-mail message.

D.          Insert a new job step before the first step. Configure the new step to terminate all processes that use the MSDB database.

  1. You are the administrator of a SQL Server 2000 computer. The server contains two databases. A client/server application accesses one of the databases. A different client/server application accesses the other database. Each application uses several stored procedures to query and update its database.

Users report that both applications respond slowly. You use SQL Profiler to monitor activity on the server. You receive results of the trace as shown below:

Event Class Test Date

SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN

SQL:BatchCompleted Update Customer Set CustomerZip = ……

SQL:BatchCompleted Update Products Set SKU = ‘23232323…..

SQL:BatchCompleted If @@TRANCOUNT > 0 COMMIT TRAN

SQL:BatchCompleted Update Customers Set CustomerZip=

You need to improve the performance of the applications. What should you do? 

A.          Modify the applications so that they use views to query data.

B.          Modify the stored procedures so that they select data into temporary tables.

C.          Re-create the stored procedures so that they do not contain the WITH RECOMPILE option.

D.          Disable the ‘Autoupdate statistics’ option in both databases.

  1. You are the administrator of a database that contains 64 lookup tables. These tables store static data that should not change. However, users report that some of this data is being changed. You need to prevent users from modifying the data. You want to minimize changes to your security model and to your database applications. How should you modify the database?

A.          Create a filegroup named LOOKUP. Move the lookup tables to this filegroup. Select the read-only check box for the filegroup.

B.          Create a database role named datamodifier. Grant SELECT permissions to the datamodifier role. Add all users to the role.

C.          Deny INSERT, UPDATE, and DELETE permissions for all users. Create stored procedures that modify data in all tables except lookup tables. Require users to modify data through these stored procedures.

D.          Create a view of the lookup tables. Use the view to allow users access to the lookup tables.

  1. You are the administrator of an SQL Server 2000 computer. The server contains a database named Inventory. The database has a parts table that has a field named InStock. When parts are shipped, a table named PartsShipped is updated. When parts are received, a table names PartsReceived is updated. The relationship of these tables is shown below:


You want the database to update the Instock field automatically. What should you do?

A.          Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the parts table.

B.          Create a user-defined function that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables.

C.          Use a view that creates InStock as a part of an aggregate query.

D.          Create stored procedures for modifying the PartsReceived and the PartsShipped tables that also modify the InStock field in the Parts table. Use these procedures exclusively when modifying data in the PartsReceived and the PartsShipped tables.

  1. You are the administrator of a SQL Server 2000 computer. You are creating a database named RetailImport to use as an intermediate data store for a data warehouse. Each night you must import daily sales data into the database from SQL Server 2000 computers in 120 locations. After the data is moved into the data warehouse, the tables are truncated. The database schema is shown below:


You want to configure the data import processor so that you minimize the time needed to import the sales data and administer the database. What should you do?

A.          Use the simple recovery model and the FOR LOAD option to create the database. Create a data transformation services package that uses the BULK INSERT statement to copy the sales data.

B.          Index the foreign key fields in the child tables.

C.          Create a data transformation services (DTS) package that uses a Data Driven Query task to copy the sales data.

D.          Collect the sales data by using a distributed transaction that inserts the data from all 120 retail locations into the RetailImport database in a single transaction.

E.           On the servers in the retail locations, create stored procedures that submit the updates to the RetailImport database each night as SQLXML updategrams.

  1. You are the administrator of a SQL Server 2000 computer. You have two new hard disks on which you will create a database named Inventory. You want to insert, update, and delete data as quickly as possible.

Which two actions should you take? (Each correct answer presents parts of the solution. Choose two)

A.          Configure the hard disks as two mirrored NTFS volumes.

B.          Configure the hard disks as one mirrored NTFS volume.

C.          Configure the hard disks as two independent NTFS volumes.

D.          Configure the hard disks as one extended NTFS volumes.

E.           Place inventory_data.mdf on the first volume and inventory_log.idf on the second volume.

F.           Place inventory_data.mdf on the first volume and inventory_data2.ndf and inventory_log.idf on the second volume.

G.          Place inventory_data.mdf and inventory_log.idf on the same volume.

  1. You are the administrator of a SQL Server 2000 database. You import a table of geographic information from a Microsoft Access database into a SQL Server 2000 database. The table has 12,000 rows. Each row averages 5,000 bytes. The table contains lookup data that does not change. You want to minimize the size of the data file and the time required to back up the data. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Create a 60-MB data file named geography.ndf

B.          Create a 95-MB data file named geography.ndf

C.          Create a 60-MB data file named geography.mdf

D.          Create a 95-MB data file named geography.mdf

E.           Place the table in the PRIMARY firegroup.

F.           Place the table in a new firegroup named LOCATION.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named sales. You need to change the way customer IDs appear in the Customers table. The database schema is shown below:


You need to automate the process of updating the primary key tools. You also want to minimize record locks and administration within the database during the update process. What should you do?

A.          Add an ON UPDATE CASCADE constraint to the CustomerID field in the Customers table. Modify the values in the CustomerID field in the Customers table.

B.          Create a duplicate record that has a new CustomerID value. Update the foreign key fields in the invoices, contacts, and quotes tables with the new value.

C.          Disable the FOREIGN KEY constraints. Within a transaction, modify the values in the CustomerID field in the Customers table and all related foreign key values in the invoices, contacts, and quotes tables. Re-enable the FOREIGN KEY constraints after the keys are changed.

D.          Create a data transformation services package. Use the package to transform the CustomerID value and the values of the related foreign keys in the invoices, contacts, and quotes tables.

  1. You are the administrator of two SQL Server 2000 computers. One of these servers contains a 4-GB database named Marketing. You want to remove the Marketing database from one server and add it to the other as quickly as possible. What should you do?

A.          Detach the database from the original server by using the sp_detach_db stored procedure. Copy the database and the transaction log files to the new server, and attach them by using the sp_attach_db stored procedure.

B.          Use the DTS export wizard to transfer all database objects from the original server to the new server. Drop the database from the original server.

C.          Run a full backup of the database on the original server. Create a new database named Marketing on the new server. Restore the backup in the new Marketing database. Drop the database from the original server.

D.          Shut down the original server. Copy the database and the transaction log files to the new server. Use the DISK INIT and DISK REFIT statements to attach the data file to the new server. Drop the database from the original server.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database that stores financial data. You want to use data transformation services packages to import numeric data from other SQL server computers. The precision and scale values of this data are not defined consistently on the other servers. You want to prevent any loss of data during the import operations. What should you do?

A.          Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the source tables. Change the data types so that they will use the lowest precision and scale values of the data that will be transferred.

B.          Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the destination tables. Change the data types to reflect the highest precision and scale values involved in data transfer.

C.          Set a flag on each DTS transformation to require an exact match between source and destination columns.

D.          Set the maximum error count for each DTS transformation task equal to the number of rows of data you are importing. Use an exception file to store any rows of data that generate errors.

E.           Write Microsoft ActiveX scripts for each DTS transformation. Use the scripts to recast data types to the destinations precision and scale values.

  1. You are the administrator of a SQL Server 2000 computer. Each evening after business hours, you perform database maintenance tasks. You create a data transformation services package to populate the data warehouse. MAPI is not installed on the server. You want to schedule the DTS package to run each night. You want to attend to other duties while the DTS package is executing. You also want to be notified if the DTS package fails. What should you do?

A.          Select the ‘DTS packages log package execution to SQL Server’ check box. Create a SQL Server event alert to notify you if the package fails.

B.          Select the ‘DTS packages write completion status to event log’ check box. Create a SQL Server event alert to notify you if the package fails.

C.          Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the send mail task to notify you if the package fails.

D.          Configure the DTS package to include an on failure precedence constraint and a send mail task. Configure the execute process task to execute a not send command that will notify you if the package fails.

  1. You are the administrator of a SQL Server 2000 computer. Your company purchased an accounting application from a vendor. The application stores its data in a database named Accounting on the server. The tables in this database contain columns that function as primary keys, but PRIMARY KEY and FOREIGN KEY constraints are not used.

You need to replicate data from this database to another SQL Server computer. This server will use the replicated data to generate reports. Most reports will run each month, but the accounting department needs to have the ability to run reports at any time. Reports should be accurate through the last full working day.

You cannot make any changes to the database, but you need to implement replication. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Implement merge replication.

B.          Implement snapshot replication.

C.          Implement transactional replication.

D.          Schedule replication to run continuously.

E.           Schedule replication to run during off-peak hours.

  1. You are the administrator of a SQL Server 2000 computer named FABSQL2K01. You create a data transformation services package that contains definitions for two transform data tasks. The tasks get data from two text files named customers and sales. The DTS package is configured as shown below:


You do not want the DTS package to add any data to the server unless both transform data tasks complete successfully. You select the ‘Use transactions’ check box and the ‘Join transaction of present’ check box of both transform data tasks. You them select the ‘Fail package on step failure’ check box of both transform data tasks. You want to use the DTS package to import data from the text files to the server. You want to ensure that changes are committed if all imported data is successfully transformed. What should you do?

A.          Select the ‘Commit on successful completion’ check box of the DTS package.

B.          Select the ‘Commit transaction on successful completion of this step’ check box of the customers transform data task.

C.          Select the ‘Commit transaction on successful completion of this step’ check box of the sales transform data task.

D.          Select the ‘Commit transaction on successful completion of this step’ check box of both transform data tasks.

  1. You are the administrator of a SQL Server 2000 computer. The server is a member of a Windows NT domain and is configured for Windows authentication. The server contains a database that stores contact information for public officials in your region. These officials need to access the database by means of the Internet by using the guest login. However, some users report that they cannot connect to the server by means of the Internet. You need to allow anyone access to the server by means of the Internet. What should you do?

A.          Assign the guest login a blank password.

B.          Delete the guest login and create a new login that is mapped to the guest domain user account.

C.          Create a database user named Anonymous and assign the user the appropriate database permissions.

D.          Configure the server for mixed mode authentication.

  1. You are the administrator of a SQL Server 2000 computer named corpsql. The server is a member of a Microsoft Windows NT domain named CORPDOMAIN. Corpsql is configured for Windows authentication and contains three databases named sales, finance, and research.

Andrea is an administrative assistant in your company. She uses a suite of client/server applications to access all three databases on corpsql. Andrea transfers to a different department within your company and requires access only to the research database. Her former manager requests that you remove Sophia’s access to the sales and finance databases. You need to configure the appropriate permissions for Andrea. Which batch of transact-SQL statements should you execute?

A.          USE finance

GO

EXEC sp_revokedbaccess Andrea

USE sales

GO

EXEC sp_revokedbaccess Andrea

B.          USE finance

GO

EXEC sp_revokedbaccess Andrea ‘db_denydatareader’, ‘Andrea’

USE sales

GO

EXEC sp_revokedbaccess Andrea ‘db_denydatareader’, ‘Andrea’

C.          USE Master

GO

EXEC sp_droplogin ‘CopdomainAndrea’

GO

EXEC sp_droplogin ‘CopdomainAndrea’

D.          USE Finance

GO

EXEC sp_denylogin ‘Andrea’

USE sales

GO

EXEC sp_denylogin ‘Andrea’

You are the Administrator of a SQL Server 2000 computer. The server is configured for Windows authentication. You add an integrated logon for the domain users group. However, 20 of the users in the domain users group should not have access to the server. You need to prevent the unauthorized users from accessing the server. You need to accomplish this goal by making the least number of changes to the server. What should you do?

A.          Add a SQL server login for the unauthorized users, and then deny access to the login.

B.          Add the unauthorized users to a domain user group, add a login for the group and then deny access to the login.

C.          Add a login for each authorized user, and then deny access to each login.

D.          Remove each unauthorized user from the domain users group.

  1. You are the administrator of a SQL Server 2000 computer. Your company has 150 client computers that are running UNIX. This client computer requires access to the server to use a database named Techinfo. The server is configured with the TCP/IP and Named Pipes libraries. You need to configure the server to allow the client computers to use the Techinfo database. You also want to minimize the amount of time it takes to configure the server and allow for the tracking of individual user actions. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Configure Windows authentication.

B.          Configure mixed mode authentication.

C.          Create a SQL Server login for each user.

D.          Create a domain user group login for all of the users.

E.           Create a single SQL server login for all of the users.

F.           Enable the multiprotocol Net-Library.

G.          Disable the Named Pipes Net-library.

  1. You are the administrator of a SQL Server 2000 computer. The server is used to store confidential company information. Company policy requires that every action and change of permission on the server be logged. Policy also requires that the server can run only when logging in enabled. You need to configure the server to comply with this policy. What should you do?

A.          Use SQL Profiler to capture security events and audit events. Make sure that a rollover is enabled.

B.          On the Security tab of the Server Properties dialog box, set the Audit Level option to All.

C.          Configure the server to use Windows authentication. Make sure that Windows security log does not overwrite events.

D.          Set the c2 Audit mode option to 1. Restart the MSSQLServer service.


  1. You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's finance department. The permissions on the financeinfo database are configured as shown below:

User/Role Create Table Create View CreateSP

Accountants Deny Blank Allow

Administrators Allow Blank Blank

Public Blank Blank Blank

AppRole1 Blank Blank Blank

Managers  Allow Allow Allow

Brian Blank Deny Blank

You need to configure the database to allow Brian to add a new view. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Add Brian to the Managers database role.

B.          Add Brian to the Administrators database role.

C.          Revoke CREATE TABLE permissions for Brian.

D.          Revoke CREATE PROCEDURE permissions for Brian.

E.           Revoke CREATE VIEW permissions for Brian.

  1. You are the administrator of a SQL Server 2000 computer. One of the databases on the server contains a table named complaints. This table is used to store information about customer complaints. The customer service representatives in your company add and edit the information in the Complaints table. They work with this table by using a number of client applications, including a Web-based application and a Microsoft Windows 32-bit application.

The customer service manager discovers that some customer complaints are marked as closed before they are resolved to the customer’s satisfaction. You need to notify the customer service manager whenever a compliant is marked as closed. You do not want to make any changes to the client applications. What should you do?

A.          Create an UPDATE trigger that sends an e-mail message.

B.          Create a stored procedure that sends an e-mail message. Use the stored procedures to make changes to the data.

C.          Create a user-defined function that sends an e-mail message. Use the function to make changes to the data.

D.          Create a rule that validates data entry. Bind the rule to the CompliantClosed column.

  1. You are the administrator of a SQL Server 2000 computer. The server is used to store sales information for your company. Your company hires a new sales manager named Paulo. You create a user account named Paulo in your company's Microsoft Windows NT domain, which is named CORPORATE. You create a Windows authenticated login for Paul on the server. The permissions on the sales table are configured as shown below:

User/Role Select Insert Create View CreateSP

Accountants Allow Allow Allow Deny

Administrators Allow Blank Blank Blank

Reporters  Allow Deny Deny Deny

Sales Managers Allow Allow Allow Allow

Salespeople Allow Allow Deny Deny

Guest Blank Blank Blank Blank

Paulo needs permission to view, add, edit, and remove information in the database tables. Which transact-SQL statement should you execute?

A.          EXEC sp_addrolemember ‘SalesManagers’, ‘CorporatePaul’

B.          GRANT ALL ON sales TO ‘CorportaePaul’

C.          EXEC sp_addrolemember ‘SalesManagers’, ‘Paul’

D.          EXEC sp_grantdbaccess ‘CorporatePaulo’, ‘Paul’

EXEC sp_addrolemember ‘SalesManagers’, ‘Paul’

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named FinanceData that is used by a client/server application. Each employee has a SQL server login that the application uses to access the server. Your company hires a new employee named Andrew. You create a SQL server login named Andrew. You also create a database user named Andrew in the database and grant the database user full permissions in the database. Whenever Andrew runs the client/server application, he receives the error message ‘Invalid object name’. Which transact-SQL statement should you execute?
A.          EXEC sp_grantdbaccess ‘Financedata’, ‘Andrew’
GO

Sp_addrolemember ‘db_datareader’, ‘Andrew’

B.          GRANT ALL ON Financedata TO Andrew.

C.          EXEC sp_defaultdb ‘Andrew’, ‘Financedata’

D.          EXEC sp_addlogin ‘Andrew’

  1. You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's sales department.

The permissions on the SalesFigures table are configured as shown below:

User/Role Select Insert Update Delete

Accountants Allow Allow Deny Deny

Managers  Allow Blank Deny Deny

Salespeople Allow Blank Blank Blank

Guest Blank Blank Blank Blank

Lisa is a user in the sales department. She needs to review the data in the SalesFigures table. She also needs to add new items to the table. You need to allow Lisa to perform these tasks without giving her additional permissions in the database. What should you do? (Each correct answer presents part of the solution. Choose all that apply)

A.          Add Lisa to the Managers database role.

B.          Add Lisa to the Salespeople database role.

C.          Grant Lisa INSERT permissions on the Sales Table.

D.          Grant Lisa UPDATE permissions on the Sales Table.

E.           Revoke UPDATE permissions on the table for Lisa.

F.           Revoke DELETE permissions on the table for Lisa.

  1. You are the administrator of three SQL Server 2000 computers at Parker publishing. One server, FLPSQL01, stores other data. You want to be able to use the other two servers, FLPSQL02 and FLPSQL03, to answer queries and run reports. The planned network configuration is shown in the exhibit below:


You want to use the Database Maintenance Plan wizard to configure log shipping from FLPSQL01 to FLPSQL02 and FLPSQL03. You do not want users to add any new data on FLPSQL02 and FLPSQL03, but they must be able to run queries and reports. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Set the database load state for each destination server to No Recovery mode.

B.          Set the database load state for each destination server to Standby mode.

C.          Enable the ‘Allow database to assume primary role’ option for both destination servers.

D.          Enable the ‘Allow database to assume primary role’ option for one destination server and then disable this option for other destination server.

E.           Disable the ‘Allow database to assume primary role’ option for both destination servers.

  1. You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales. Pete reports that he cannot modify data in the database. You use SQL Profiler to capture his activities shown below:

Event Class Test Date

TraceStart

Existing Connections --network protocol:LPC set qualid_identifier on set implicit_transaction off…

Existing Connections --network protocol:LPC set qualid_identifier on set implicit_transaction off…

SQL:Batch Completed set transaction isolation level serializable begin transaction select * from….

You want Pete to be able to modify data. You also want the Transact-SQL statement to execute without compromising data integrity. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two)

A.          Change the isolation level of the database connection.

B.          Add indexes to the product table.

C.          Remove the explicit transaction from the Transact-SQL batch.

D.          Create a stored procedure to execute the Transact-SQL batch.

E.           Add a cascading UPDATE trigger to the Products table.

  1. You are the administrator of two SQL Server 2000 computers. Each server contains data for one of your company's two regional divisions. Company employees currently use a single data entry application on client computers. After the new application is installed, query response times slow. You want to monitor server traffic during business hours to help diagnose this problem. What should you do?

A.          Run SQL Profiler, and create one trace to monitor both servers. Configure the trace to include data from the new data entry application only.

B.          Run two instances of SQL Profiler, and create a trace to monitor each server. Configure the trace to include data from both data entry applications.

C.          Run the sqldrag utility from a command prompt.

Write diagnostic data to a text file.

D.          Execute the sp_monitor stored procedure.

Save the output to a text file.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. Users report that several storage locations in the UnitsStored field contain negative numbers. You examine the database’s table structure. The table properties are configured as shown in the exhibit (exhibit not available).

You correct all the negative number numbers in the table. You must prevent the database from storing negative numbers. You also want to minimize use of server resources and physical I/O. Which transact-SQL statement should you execute?

A.          ALTER TABLE dbo.storagelocations ADD CONSTRAINS

CK_storagelocations_UnitsStored

CHECK (UnitsStored>= 0)

B.          CREATE TRIGGER CK_UnitsStored On StorageLocations

FOR INSERT, UPDATE AS

IF INSERTED, UnitsStored < 0 ROLLBACK TRAN

C.          CREATE TABLE ck_unitsstored As @Units >= 0

GO

Sp_bindrule ‘OK_UnitsStored’,

‘StorageLocations, UnitsStored’

GO

D.          CREATE PROC UpdateUnitsStored

(0StorageLocationID int, @UnitsStored bigint)AS

IF @UnitsStored < 0

DATSERROR (50099, 17)

ELSE

UPDATE storaheLocations
SET UnitsStored = @UnitsStored
WHERE StorageLocationID = @storageLocationID

You are the database administrator of a SQL Server 2000 computer. The server contains your company's Accounts database. Hundreds of users access the database each day. Because you have had power interruptions in the past, you want to perfect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do?

A.          Enable the ‘Torn page detection database’ option for each database.

B.          Disable ‘Write caching’ on all disk controllers.

C.          Ensure that write caching disk controllers have better backups.

D.          Create a database maintenance plan to check database integrity and make repairs each night.

  1. You are the administrator of an SQL Server 2000 computer. The server contains a database named Inventory. Users report that the following query responds slowly:

SELECT parts.SKU AS SKS, parts, inscription AS Part Description), locations, inscription AS location

FROM partslocations INSERT JOIN parts (G PartsLocations.Parts )

You examine the indexes in the PartsLocations table. The indexes are configured as shown below:

index_name  index_description index_keys

1x_PartsLocaion_LocationsID_PK nonclustered, located on PRIMARY Location ID

1x_PartsLocation_PartsID_PK nonclustered, located on PRIMARY PartsID

PK_PartsLocations clustered, unique, primary… PartsID, LocationID

You need to improve the performance of the query. What should you do?

A.          Rebuild the PK_PartsLoactions index.

B.          Rebuild the IX_PartsLoactions_PartsID index.

C.          Rebuild the IX_PartsLoactions_LocationId index.

D.          Create the IX_PartsLocations_PartsID_LocationID index.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. You perform full database backups every two days. You also run regular database consistency checks on the server. The most recent check of the Sales database returns the following message:

HECKDB found 0 allocations errors and 3 consistency errors in table ‘Orders’ (object ID 214575782).

You want to correct the data integrity errors while minimizing the amount of data lost. What should you do?

A.          Disconnect users from the Sales database.

Enable the single user database option.

Execute the DBCC CHECKTABLE statement for the Orders table, and specify the REPAIR_REBUILD option.

B.          Disconnect users from the Sales database.

Enable the ‘dbo use only database’ option.

Execute the DBCC CHECKALLOC statement for the Orders table, and specify the REPAIR_REBUILD option.

C.          Disconnect users from the Sales database.

Execute the RESTORES DATABASE statement for the Sales database.

D.          Execute the DBCC CLEANTABLE statement for the Orders table.

E.           Execute the sp_table_validation stored procedure for the Orders table.

  1. You are the administrator of an SQL Server 2000 computer. The server contains a database named ResearchRecords. This database contains research records and administrative records. The database consumes about 30GB of disk space. The data files are configured as shown below:

Database files

File Name Location Space Allocated (MB) Filegroup

MedicalRecords_data E:Program FilesMicro… 10000 PRIMARY

MedicalRecords_data F:dataMedicalRecords 10000 PRIMARY

MedicalRecords_data G:ProgramFilesMicro… 20000 PRIMARY

You run full database backups each night after business hours. You notice that the backup is not complete by the time the morning shift begins to use the database. You need to minimize the time needed to restore data in the event of a system failure. You also want to reconfigure the database to allow the backups to complete during the evening hours. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Reorganize the data files into two groups.

Place the system tables in the PRIMARY filegroup and the user-defined tables in the other filegroup.

B.          Reorganize the data files into three groups.

Place the system tables and shared objects in the PRIMARY filegroup, the clinical records in a filegroup, and the administrative records in a filegroup, and the administrative records in a filegroup.

C.          Reorganize the data files into three groups.

Place the system, tables in the PRIMARY filegroup, the indexes in a filegroup, and the tables in the other filegroup.

D.          Back up the transaction log each night.

Run a filegroup backup on a different filegroup each night.

E.           Back up the transaction log each night.

Run a filegroup backup on the database each weekend.

F.           Back up the transaction log each night.

Run a differential backup each Sunday, and run a full backup the first day of each month.

You are the administrator of several SQL Server 2000 computers. You want to retrieve information from an archived inventory database. You have a full tape backup of the database. The backup’s header information shows that the backup uses the SQL_Latin1_General_CR437_BIN collation. However, the existing SQL server computers in your office are configured to use the SQL_Latin1_General_CP1_CI_AS collation.

You do not want to join tables in the inventory database with tables in other databases. You need to restore the inventory database to a SQL Server 2000 computer by using the least amount of administrative effort.

What should you do?

A.          Use the rebuildm utility to rebuild the system database on an existing SQL server computer.

Configure all the databases on that server to use the SQL_Latin1_General_CR437_BIN collation.

Restore the inventory database to the server.

B.          Restore the inventory database to an existing SQL server computer. Accept the SQL_Latin1_General_CR437_BIN collation for that database.

C.          Install a new named instance of SQL Server 2000 on an existing SQL Server 2000 computer.

Configure the named instance to use the SQL_Latin1_General_CR437_BIN collation.

Restore the inventory database to the named instance.

D. Install SQL Server 2000 on a new computer.

Configure the new server to use the SQL_Latin1_General_CR437_BIN collation.

Restore the inventory database to the new server.

You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales. The database is configured as shown below:


In the last six months, the database has grown by 3 GB. Users report that query response time has slowed. The options set the database are shown in the database options exhibit. Click the exhibit button (exhibit not available). You want to accelerate query response time. What should you do?

A.          Update the database statistics.

B.          Add indexes to the foreign key fields.

C.          Truncate the transaction log.

D.          Run the Database Maintenance Plan Wizard.

E.           Drop primary keys from all tables.

You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales that has two database files and one transaction log file. Each data file is located on its own hard disk and exists in its own filegroup. You perform full database, differential, and transaction log backups on a regular basis. All backups made during a single week are striped across three disk backup devices. A portion of the header information for the current week’s backups is shown in the following table:

Backup Name Backup Type BackupFinishDate

sales_db_10000625 1 2000-06-25 11:57:04.000

sales_t1_10000626_1 2 2000-06-26 11:04:12.000

sales_t1_10000626_2 2 2000-06-26 15:04:23.000

sales_df_20000426 5 2000-06-26 21:15:41.000

On June 28, 2000, at 1:47 P.M, the hard disk that contains the PRIMARY filegroup fails. You want to recover as much data as possible. What should you do? (Each correct answer presents part of solution. Choose two)

A.          Backup the transaction log by using the NO_LOG option.

B.          Backup the transaction log by using the NO_TRUNCATE option.

C.          Restore the most recent full database backup.

D.          Restore the most recent differential backup.

E.           Restore all differential backups in sequence.

F.           Restore all transaction log backups in sequence.

G.          Restore all transaction logs once the most recent differential backup.

  1. You are the database administrator for a legal services company. Employees enter data 24 hours a day into a SQL server 2000 database. These employees report slower response times when new account information is gathered from branch offices and added to the database. You currently use the following BULK INSERT statement to and the account information:

BULK INSERT finance.dbo.customers
FROM ‘dibulkaccts143_10141000.txt’

WITH DATAFILETYPE = ‘char’,

FIELD/TERMINATOR = ‘t’,

ROWTERMINATOR = ‘n,’

TABLOCK

You want to ensure that response times do not slow when new account information is added to the database. What should you do?

A.          Drop the indexes for the Customers table before the data load, and then re-create the indexes after the data load is complete.

B.          Remove the TABLOCK option from the BULK INSERT statement.

C.          Add the BATCHSIZE option to the BULK INSERT statement and then set the option equal to 10 percent of the number of rows to be loaded.

D.          Add the ROWS_PER_BATCH option to the BULK INSERT statement and then set the option equal to 10 percent of the number of rows to be loaded.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named sales. Users report that they cannot add new data to the database. You examine the properties of the database. The database is configured as shown in the sales properties exhibit (exhibit not available). You examine drive E. The hard disk is configured as shown in the local disk properties exhibit (exhibit not available). You want the users to be able to add data, and you want to minimize administrative overhead. What should you do?

A.          Increase the maximum file size of Sales_Data to 1,500MB.

B.          Use the DBCC SHRINKDATABASE statement.

C.          Set automatic file growth to 10 percent.

D.          Create another data file named Sales_Data2 in a new SECONDARY filegroup.

  1. You are the administrator of a SQL server 2000 computer that contains a database named Acct. the database contains 1.5 GB of data. The server has one 9-GB hard disk that is configured as shown in the exhibit (exhibit not available). You need to import data into the database without adversely affecting database performance. The data will require an additional 2GB of storage space. What should you do?

A.          Add another data file on drive E, and then add the file to the PRIMARY filegroup.

B.          Move the transaction log file to drive E, and set the file growth of Acc_Data.mdf by selecting the ‘Unrestricted file growth’ option.

C.          Rebuild all clustered indexes so that they have a fill factor of 100.

D.          Compress drive D.

  1. You are the administrator of an SQL server 2000 computer. The server contains a database named MedicalRecords. Users access medical records by using the PatientID field. This field is the clustered primary key for the Patients table. When users try to access medical records, the database responds slowly. You examine the database options as shown in the exhibit (exhibit not available). You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database? 

A.          Create a SQL Server agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly.

B.          Select the ‘Auto update statistics’ check box.

C.          Run the Database Maintenance Plan Wizard, and accept the default settings.

D.          Rebuild the primary key as a non-clustered primary key.

E.           Clear the ‘Auto create statistics’ check box.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Your company uses the database to store the sales department’s responses to requests for price quotations.

Developers in your company create an application used for saving quotations. The application executes the following transact-SQL statement:



UPDATE QuotationSummary

SET CustomerName = ‘Jean-Paul Deloria’

WHERE QuotationID = 12

When the application executes the statement, the developers receives the following error:

Server: Mrh4402, Level 16, Stats 1, Line 1

View or function ‘Quotation/Summary’ is not updateable because it contains aggregates:

You want developers to be able to use this UPDATE statement without generating errors. What should you do?

A.          Create an INSTEAD OF trigger on the view to update the composite tables.

B.          Grant the developers UPDATE permissions on each base table.

C.          Add a cascading update trigger on each base table.

D.          Create a QuotationSummary table and populate the table with data from the base tables.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database that is heavily indexed and that company users query extensively. The database has grown and query response time has slowed. The database is stored in a single data file. You want to accelerate query response time. What should you do?

A.          On a new hard disk, create a new filegroup.

Drop the existing nonclustered indexes, and then re-create them on the new filegroup.

B.          On a new hard disk, add a new file to the PRIMARY filegroup.

Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.

C.          On the existing hard disk, create a new filegroup.

Drop the existing nonclustered indexes, and then re-create them on the new filegroup.

D.          On the existing hard disk, add a new file to the primary filegroup.

Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.

  1. You are the administrator of a SQL Server 2000 computer. You want to set up snapshot replication on the server. The server will serve as Publisher and Distributor for a minimum of 40 Subscribers. Currently, you want to publish 3GB of data, but the data is expected to grow over time. Subscribers will receive a new snapshot each month. You want to minimize the workload on the Publisher/Distributor. Which two actions should you take to configure snapshot replication? (Each correct answer presents part of the solution. Choose two)

A.          Store the snapshot in the default folder on the Publisher/Distributor.

B.          Store the snapshot in an alternative folder on the Publisher/Distributor.

C.          Store the snapshot in a shared folder on a file server.

D.          Create pull subscriptions.

E.           Create push subscriptions.

  1. You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform adhoc distribution queries against a database that is stored on a SQL Server 2000 computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation. You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead. How should you configure SQL1?

A.          Use the ALTER DATABASE statement to change the collation of the databases on SQL1.

B.          Add SQL2 as remote server.

C.          Add SQL2 as a linked server.

Select the Use Remote Collation check box, and do not specify a collation name.

D.          Add SQL2 as a linked server.

Select the Use Remote Collation check box, and specify a collation name.

Repeat this process once for each database on SQL2.

  1. You are the administrator of a SQL Server 2000 computer in your company's HR department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown in the exhibit (exhibit not available).

You want to create a text file that lists these data columns:

FirstName, LastName, WorkPhone, PositionName, DepartmentName.

You want to create the text file as quickly as possible. You do not expect to recreate this file, and you want to avoid creating new database objects if possible. What should you do?

A.          Use the bcp utility to export data from each table to a separate text file. Use format files to select the appropriate columns. Merge the data from each text file into a single text file.

B.          Create a view that joins data from all three tables include only the columns you want to appear in the text file. Use the bcp utility to export data from the view.

C.          Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a local temporary table. Use the bcp utility to export data from the local temporary table to a text file.

D.          Create a SELECT query that joins the data from the appropriate columns in the three tables. Add an INTO clause to the query to create a global temporary table. Use the bcp utility to export data from the global temporary table to a text file.

  1. You are the administrator of several SQL Server 2000 computers. A data Transformation Services (DTS) package uses native OLE DB providers to transfer data between the servers. Connection details for the servers are specified in .udl files. The .udl files are frequently updated as connection details change.

You want to distribute the DTS package as a file to developers in your company. You want to make sure connection details are available to developers who receive the DTS package. Which two tasks should you perform? (Each correct answer presents part of the solution. Choose two)

A.          Enable the ‘Always read properties from UDL file option’ in the Connection Properties dialog box.

B.          Disable the ‘Always read properties from UDL file option’ in the Connection Properties dialog box.

C.          Delete the .udl files and store connection details in the registry by using system data source names.

D.          Delete the .udl files, and store connection details in the registry by using user data source names.

E.           Make the .udl files available on a network share.

  1. You are the administrator of a SQL Server 2000 computer named DataSvr. The server is a member of a Microsoft Windows NT domain named RESEARCH. Several users need access to the database stored on the server. These users all belong to a domain user group named domain scientists. They also belong to a local user group named Local Techs on the DataSvr computer.

Your company will be hiring 30 new employees who will be added to the domain scientists group by domain administrators. These employees will also need access to DataSvr. You need to allow all of the appropriate users to access DataSvr. You also want to minimize the amount of time you have spend maintaining security on DataSvr. Which Transact-SQL statement would you execute?

A.          EXEC sp_grant login BUILTINLocal Techs

B.          EXEC sp_grant login ‘Local Techs’

C.          EXEC sp_grant login [DatasvrDomain Scientists]

D.          EXEC sp_grant login [ResearchDomain scientists]

  1. On the server, you create a new database named EmployeeData. The EmployeeData database will store confidential information about company employees. You need to allow only authenticated users to access the EmployeeData database. Which Transact SQL statement should you execute?

A.          EXEC sp_revokelogin ‘guest’

B.          EXEC sp_droplogin ‘guest’

C.          EXEC sp_revokedbaccess ‘guest’

D.          EXEC sp_addrolereader ‘db_denydatareader’, ‘guest’

  1. You are the administrator of a SQL Server 2000 computer. You configure SQL Server to perform auditing. Audit logs are saved in the C:MSSQLAudit folder. The server functions normally for several days, but then the MSSQL Server Service shuts down without warning.

You cannot restart the service. You need to restart the MSSQL Server service. What should you do?

A.          Set the SQLServerAgent service’s startup method to disabled.

Start the MSSQLServer service.

B.          Start the MSSQLServer service manually by using the –s startup option.

C.          Start the MSSQLServer service manually by using the –m startup option.

D.          Delete the audit logs from the C:MSSQLAudit folder.

Start the MSSQLServer service.

  1. You are the administrator of a SQL Server 2000 computer. The server contains confidential information about contracts on which your company has placed bids. Company policy requires that bid information be removed permanently from the database one year after the bid closes. You need to comply with this policy and minimize server overhead. What should you do?

A.          Create a data Transformation Services (DTS) package to remove bids that have a closing date older than one year.

B.          Create a trigger to delete any bids that have a closing data older than one year.

Bind the trigger to the bids table for all INSERT, UPDATE, and DELETE events.

C.          Create a stored procedure to delete any bids that have a closing date older than one year.

Use SQL server agent to schedule the stored procedure to run every night.

D.          Create a view that contains a WHERE clause to exclude bids that have a closing date older than one year.

  1. You are the administrator of a SQL Server 2000 computer. The server is a member of a Microsoft Windows NT domain named CORP. The server is configured for Windows Authentication. Several users can connect to the server by using SQL Query Analyzer. These users belong to a domain user group named DataUsers, and the server contains a login for CORPDataUsers.

Your company purchases a client/server application that implements its own user security and will access the server by using a login named Csapp1. You create a domain user account named Csapp1 and add it to the DataUsers domain user group. However, when you try to run the client/server application, you receive the error message, “The required SQL Server database cannot be accessed.” You need to ensure that the application runs properly. What should you do?

A.          Remove the Csapp1 domain user account from the DataUsers domain user group. Create a login for CORPCsapp1.

B.          Configure the server for Mixed Mode authentication. Create a login named Csapp1, and configure it to access the application’s database.

C.          Create a local user account on the server, and name it Csapp1. Add a login for BUILTINC sapp1.

D.          Configure an application role in the application’s database, and name the role Csapp1. Set the application role password so that it is the same as the Csapp1 user account password.

  1. You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's accounting department.

Database Role Select Insert Update Delete

Accountants  Allow Allow Deny Deny

Brian Deny Blank Blank Blank

Managers  Allow Blank Allow Allow

Salespeople Allow Blank Blank Blank

Brian is a member of the Managers and Salespeople database roles. While working in the table, he reports that he can edit and remove information. However, he cannot view any of the information in the table.

You need to allow Brian to view the information in the Accounting table. Which Transact-SQL statement should you execute?

A.          GRANT PRIVILEGES ON Accounting TO Brian

B.          GRANT SELECT ON Accounting TO Brian WITH GRANT OPTION

C.          REVOKE GRANT OPTION FOR SELECT ON Accounting TO Brian

D.          REVOKE SELECT OR Accounting FROM Brian

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database is configured as shown below:


Users frequently need details about parts. Users search for data by using the following query:

SELECT Parts.SKU, Locations.Description, Manufacturer.Name, PartsLocations.Qty, PartsLocations.LastInventoried

FROM Parts

INNER JOINT Manufacturer ON Parts.ManufacturerID= Manufacturer.ManufacturerID

INNER JOINT Locations ON Parts.LocationID= Loactions.LocationID

WHERE SKU

You need to accelerate query response time. What should you do?

A.          Create a parameterised stored procedure to retrieve the data.

B.          Create a denormalized table that is maintained by triggers.

C.          Use ad hoc queries to retrieve the data.

D.          Create a nonclustered index on the primary key of each table.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. The database tables are configured as shown below:

Column Name Data Type Length Allow Nulls

PatientID int 4

Title char 4 Yes

FirstName char 25

LastName char 35

Address varchar 50

Address2  varchar 50 Yes

City char 25

State  char 2

Zip  char 10

Phone char 15 Yes

The existing PatientID field is an identity field. According to a new government regulation, the patient IDs must include a three-character prefix before the numeric portion of the ID. The patient’s home address defines the prefix. You want to comply with the regulation while minimizing changes to the database. Which two actions should you take? (Each correct answer presents part of the solution. Choose Two)

A.          Drop the FOREIGN KEY constraints. In each table, change the data type of the PatientID field to char.

B.          Add an on update constraint to the patients table, and link it to the PatientID field. Update the field in the Patients table.

C.          In the Patients table, add a new field named StatePatientID that has a data type of char. Populate the field with the prefix and the existing PatientID.

D.          In each table, update the PatientID field.

Re-create the FOREIGN KEY constraints.

E.           In the Patients table, update the PatientID field.

F.           Create a trigger that populates the StatePatientID field when a new patient is created.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. This database contains clinical records and administrative records. The database consumes about 30 GB of disk space. The data files are configured as shown in the exhibit (exhibit not available). You run full database backups each night after business hours. You notice that the backup is not complete by the time the morning shift begins to use the database. You need to minimize the time needed to restore data in the event of a system failure. You also want to reconfigure the database to allow the backups to complete during the evening hours. Which two actions should you take? (Each correct answer presents part of the solution. Choose two)

A.          Reorganize the data files into two filegroups.

Place the system tables in the PRIMARY filegroup and the user-defined tables in the other filegroup.

B.          Reorganize the data files into three filegroups.

Place the system tables and shared objects in the PRIMARY filegroup, the clinical records in a filegroup, and the administrative records in a filegroup.

C.          Reorganize the data into three filegroups.

Place the system tables in the PRIMARY filegroup, the indexes in a filegroup, and the tables in a filegroup.

D.          Back up the transaction log each night.

Run a filegroup backup on a different filegroup each night.

E.           Back up the transaction log each night.

Run a full backup of the database each weekend.

F.           Back up the transaction log each night.

Run the differential backup each Sunday, and run a full backup the first day of each month.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Accounting. The data files are configured as shown below:

File Name Location Disk Space (MB) Filegroup

Account_data E:Program Files 8000 Primary

The database has grown to 7 GB. The server has no more available disk space. You need to make more disk space available in the accounting database without affecting system performance. What should you do?

A.          Compress drive E.

B.          Add a new hard disk to the server.

Create a new file on the new hard disk as part of the PRIMARY filegroup.

C.          Add a new hard disk to the server.

Create a new file on the new hard disk as part of a new filegroup.

D.          Map a network drive to the server.

Create a new data file on the network drive as part of the PRIMARY filegroup.

E.           Map a network drive on the server.

Create a new data file on the network drive as part of a new filegroup.

  1. You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales. The database stored information that employees use to analyze sales data. You configure the database by using the default options. You create the database tables as shown below:


You import data as a batch process each evening. Users report that the database responds slowly when they run the following query:

SELECT Customers.CustomerName, Invoices.InvoiceID, Invoices.InvoiceDate, OrderDetail.Quantity, Products.Description, Products.SKU, SalesPersons.FirstName, SalesPerson.LastName

FROM Invoices

INNER JOIN Customers ON Invoices.Customer ID = Customer.CustomerID

INNER JOIN OrderDetail ON Invoices.Invoice ID = OrderDetail.InvoiceID

INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID

INNNER JOIN SalesPerson ON Invoices.SalesPersonID = SalesPerson.SalesPersonID

You want to improve the performance of this query. What should you do?

A.          Create indexes on the foreign key fields.

B.          Create a view to provide the data that the query will use.

C.          Create a stored procedure that executes the query.

D.          Create cascading UPDATE and DELET triggers to the table.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. You have recently added a hard disk to the server and configured the database.

The database is configured as shown in the database schema below:


Each primary key is clustered. All foreign keys contain nonclustered indexes. You want to use the new hard disk to make the database respond more quickly to queries.

What should you do?

A.          Re-create the Notes table on the SECONDARY filegroup.

B.          Re-create the nonclustered indexes on the SEONDARY filegroup.

C.          Re-create the Patients and Doctors tables and their indexes on the SECONDARY filegroup.

D.          Re-create the Cases and Notes tables and their indexes on the SECONDARY filegroup.

  1. You are the administrator of a SQL Server 2000 computer at a bank’s headquarters. Customers can access their accounts from headquarters or from branch offices. The server at headquarters maintains a master copy of all account data for all customers. When a customer deposits or withdraws funds at a branch office, the customer’s new balance must be reflected on the computers at that office and at headquarters. You want to minimize latency between the headquarters and branch offices. You want to copy account data between the server at headquarters and the servers at the branch offices. What should you do?

A.          Implement merge replication. Use the DATETIME (Later Wins) conflict resolver. Schedule replication for off-peak hours.

B.          Implement transactional replication. Allow immediate-updating subscribers. Schedule replication to run continuously.

C.          Create a data transformation services package on the server at headquarters. Configure the DTS package to import new account data from each of the servers at the branch offices and write it to all other servers. Schedule the DTS package to run during off-peak hours.

D.          Add all branch office servers to the headquarters server as linked servers. Create a job on the headquarters server. Configure the job to query each linked server for new account data and write this data to the other servers. Schedule the job to run every three minutes.

E.           Configure log shipping between the headquarters server and the branch office servers. Configure the headquarters server as the primary server and the branch office servers as secondary servers. Schedule log backups and log shipping to run every 10 minutes.

  1. You are the administrator of a SQL Server 2000 computer. You are creating a data transformation services package. As the first step in this process, you need to load data from text files into a database table. These text files contain data on new stores that join your franchise. The text files list the data columns in the following format:

StoreID, storeName, address, city, state, postalCode, managerID, storeTypeID, facilityID.

The destination table is configured as shown in the exhibit (exhibit not available). You want to load the data into the table as quickly as possible. What should you do?

A.          Use a Bulk Insert Task to read the data into a temporary table. Use an Execute SQL task to import the appropriate data into the destination table.

B.          Create and edit a format file to select the columns you want to import. Use a bulk insert task, and then specify the format file to import the appropriate data into the destination table.

C.          Use a transform data task to import the data. Use Microsoft ActiveX transformation scripts to write the appropriate data to the appropriate columns in the destination table.

D.          Create and edit a format file to select the columns you want to import. Use a transform data task, and then specify the format file to import the appropriate data into the destination table.

  1. You are the administrator of an SQL Server computer. The server is running SQL Server 6.5 and SQL server 7.0. You install a named instance of SQL Server 2000, and then run the SQL Server Upgrade Wizard. On the database selection screen, some of the SQL server 6.5 databases are not listed. You cancel the SQL Server Upgrade Wizard. You need to ensure that the SQL Server 6.5 databases are listed in the wizard. What should you do?

A.          Uninstall SQL Server 7.0, and then rerun the SQL Server Upgrade Wizard.

B.          Run the Microsoft SQL Server-switch application, and then rerun the SQL Server Upgrade Wizard.

C.          Create a data transformation services package that imports the databases from SQL server 6.5 to SQL server 2000, and then execute the package.

D.          Uninstall SQL Server 2000, and then reinstall SQL Server 2000 as the default instance.

  1. You are the administrator of a new Microsoft Windows 2000 Server computer named CPSRV1. CPSRV1 is a member server in your company's Windows NT 4.0 domain, which is named CPDOMAIN. A separate Windows NT domain named CPUSERS contains all of your company's user accounts. A trust relationship exists between the CPDOMAIN domain and the CPUSERS domain. You are installing SQL Server 2000 on CPSRV1. You specify a service account for each SQL Server service as shown in the following table:

Service Service Account Account Type

MSSQLServer SPUSERSsqlsvr Domain administrator

SQLServerAgent CPUSERSsqlagent Domain administrator

After the installation is complete, the MSSQLServer service and the SQLServerAgent service do not start. You need to configure the services to start without changing any security settings in either domain.

What should you do?

A.          Configure the services to run under the local system account

B.          Add CPSRV1 to the CPUSERS domain

C.          Add CPUSERSsqlsvr and CPUSERSsqlagent to the local administrators group on CPSRV1

D.          Add CPUSERSsqlsvr and CPUSERSsqlagent to the CPUSERSDomain Admins groups.

E.           Add CPUSERSsqlsvr and CPUSERSsqlagent to the CPUSERSDomain users groups.

  1. You are the administrator of 12 SQL Server 2000 computers. These servers are located in company offices throughout the world. You configure a multiserver job on the server in your company's New York City office. You configure this server as a master server, and designate the remaining 11 servers as target servers.

After the job completes successfully, you decide to set up a new SQL Server 2000 computer as the master server. The new server will be named JobMaster and the other 12 servers will be target servers. You need to make the New York City server a target server instead of a master server. What should you do?

A.          On the New York City server, defect all 11 target servers.

On the JobMaster server, enlist the 12 servers as target servers.

B.          On the JobMaster server, enlist all other servers as target servers.

C.          On the New York City server enlist the JobMaster server as a target server.

Transfer the multiserver jobs from the New York City server to the JobMaster server.

D.          On the JobMaster server, enlist the New York City server as a target server.

On the JobMaster server, enlist the other 11 servers as target servers.

On the JobMaster server, defect the New York City server.

  1. You are the administrator of 20 SQL Server 2000 computers. The servers are located in 10 company offices around the world. You work in the New York City office. You need to configure several dozen alert responses on each server. In the future, you will need to modify these alert responses periodically. You want to reduce the amount of time you spend managing alert responses. You also want to manage the alert responses from the New York City office. What should you do?

A.          Use SQL Enterprise Manager to manage the alert responses on all company servers.

B.          Configure alert forwarding on the remote servers.

C.          Write Transact-SQL scripts that can be used to set up alert responses on the remote servers.

D.          Write stored procedures that can be used to set up alert responses on the remote servers.

  1. You are the administrator of a SQL Server 2000 computer that contains a database. Users report that the database responds slowly when they insert data. You use System Monitor to examine the server’s performance and receive the results shown below:

Memory

Pages/sec 244.

PhysicalDisk _total

%Disk Time 240.

Processor _total

% Processor Time 2,000

You need to modify the server to improve its performance. What should you do?

A.          Install a larger hard disk.

B.          Increase the amount of RAM.

C.          Increase the size of the virtual memory paging file.

D.          Add an additional processor.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Customers, which is used by a custom client/server application. The database contains more than 1 million rows. Users report that the database responds slowly when they search for customers by using the application. Users search for customers by scrolling through an alphabetical list of customer names.

You use SQL profiles to capture information about how the application uses the database. You receive results as shown below:

Event Class Text Data

SQL: Batch completed Select * from customers

You need to improve the application’s performance. What should you do?

A.          Create additional non-clustered indexes on the CustomerInformation table.

B.          Rewrite the application so that it uses a user-defined function to return the customer list.

C.          Rewrite the application so that it uses a query with a WHERE clause to search for customer names.

D.          Rewrite the application so that is uses a stored procedure to return the customer list.

You are the administrator of a Microsoft Windows 2000 computer. The computer is also running SQL Server 2000 and contains a database named Sales. The sales department of your company uses a custom application that has just been upgraded.

A user at your company named Pedro reports that when he attempts to run the weekly analysis report, he receives the following error message:

“Server.Msg3704, Level 16, State 1 , Line 1 User dos not have permission to perform this operation on table ‘Report’.”

You log on to Pedro’s computer, run the program, and receive no error message. You use SQL Profiler to trace database activity while you run the report. You receive the results shown below:

Event Class Text Data

SQL:Batch Completed Drop table report

SQL:Batch Completed Drop table report (Productname C…

You want to allow Pedro to run the report. You also want to minimize administrative overhead. What should you do?

A.          Add Pedro’s login to the db_ddladmin role in the Sales database.

B.          Create an application role for the sales department’s application. Grant the role database owner rights.

C.          Add Pedro to the db_datawriter role in the Sales database.

D.          Add Pedro to the local administrators group on the server.

  1. You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Your company deploys an upgraded version of a customer relationship management application, which accesses the sales database.

The sales database contains a table named Customer, which stores information about your company’s customers. Users report that they cannot delete information in the CustomerAddress2 column. The Customer table is configured as shown below:

Table Design

Name Customers

Owner dbo

CreateDate 8/25/2000 10:42:06 a.m

Filegroup  PRIMARY

Rows 1


Columns

Key ID Name Data Type Size Nulls

CustomerID int 4

CustomerName varchar 50

CustomerAddress varchar 50

CustomerAddress2 varchar 50

CustomerCity varchar 50 Y

CustomerState char 2

CustomerZip char 10

You use SQL Profiler to capture the activity of the application and receive the results shown below:

Event Class Text Data Application Name

Audit Login ----Nework protocol---- Visual Basic

SQL:Batch Completed Update Customer se… Visual Basic

You want to configure the Customer table to support the upgraded application. What should you do?

A.          Alter the table to allow null values in the CustomerAddress2 column.

B.          Alter the data type of the CustomerAddress2 column to sql_varient.

C.          Create an UPDATE trigger that changes a null value in the CustomerAddress2 column to an empty string.

D.          Use an INSTEAD OF trigger to change the value of the CustomerAddress2 column to an empty string whenever the column’s value is null.

  1. You are the administrator of a SQL Server 2000 computer. You import a large amount of data into the contracts database. After the import, you perform a full database backup. You examine the hard disk and discover that the log file has grown from 200MB to 800MB. You want to recover disk space as quickly as possible without losing backup information. Which two Transact-SQL statements should you execute? (Each correct answer present s part of the solution. Choose two)

A.          BACKUP LOG Contracts WITH no_log

B.          BACKUP LOG Contracts WITH truncate_only

C.          BACKUP LOG Contracts TO disk= ‘E: SQLBackupContracts_Log.bkp’

D.          ALTER DATABASE Contracts

(Name= Contract_Log.ldf,

FILENAME= ‘E: DataContracts_ldf’

SIZE= 200)

E.           DBCC ShrinkDatabase (Contracts, 200)

F.           DBCC ShrinkFile (Contracts_Log, 200)

You are the administrator of two Microsoft Windows 2000 Advanced Server computers. On these servers, you are configuring a database that will store accounting information for your company. The data must be available at all times. Interruptions in data connectivity should not last longer than five minutes. Any changes to the database should not require you to reconfigure the client computers. How should you configure the database?

A.          Configure the database on the two servers as a SQL Server 2000 cluster.

B.          Configure the database on one server, and then configure a standby database on the second server.

C.          Configure the database on each server.

Use Microsoft Distributed Transaction Coordinator to keep the two servers perfectly synchronized.

D.          Configure the database as a federated database, and then partition half the data on each server.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Contracts. The server is configured as shown below:


The database files in Contracts are configured as shown below:

File Name Location Initial Size (MB) File group

Contracts_data E:datacontracts_data 2000 PRIMARY

Contracts_data2 E:datacontracts_data2 2000 SECONDARY

The database developers have been creating new tables and indexes without specifying a filegroup. The primary filegroup is reaching its maximum capacity. You want the developers to continue adding new objects, but you do not want then to change the way they create objects. You do not want to run out of disk space. You also want to minimize the time it takes to administer the database. What should you do?

A.          Back up the existing objects on the PRIMARY filegroup. Drop them from the database. Re-create them on the SECONDARY filegroup.

B.          Set the file growth on the PRIMARY filegroup to UNLIMITED.

C.          Set the PRIMARY filegroup so that it is read-only.

D.          Set the SECONDARY filegroup as the default filegroup.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database will store sales transactions. Retail locations must be able to add transactions to the database 24 hours a day, every day. The server is configured as shown below:




You need to configure the data files and transaction log for the Sales database. What should you do?

A.          Place the transaction log on physical disk 0 and the data file on the RAID-5 disk array.

B.          Place the transaction log on the RAID-5 disk array and the data file on physical disk 0.

C.          Place the transaction log and the data file on physical disk 0.

D.          Place the transaction log and the data file on the RAID-5 disk array.

You are the administrator of an SQL Server 2000 computer. The server contains a database named Acct. You purge the Acct database of old records and perform a full backup. The database now uses 4 GB of space. The database files are configured as shown below:

File Name Location Space Allocated Filegroup

Acct_Data C:dataacct_data.mdf 4000 PRIMARY

Acct_Data1 D:dataAcct_Data1_D… 4000 PRIMARY

Acct_Data2 E:dataAcct_Data2_D… 4000 PRIMARY

The server has two hard disks that are configured as shown below:


The 3 GB transaction log file for the Acct database is stored on drive C. You need to make room for a new database that has a 3 GB data file and a 1 GB transaction log file. You want to optimize database performance on both databases. You also want to minimize administrative overhead. What should you do?

A.          Shrink the empty data file on drive E.

Place the new data file and the new log file on drive E.

B.          Shrink the empty data file on drive E.

Backup and shrink the log file on drive C to 2 GB.
Place the new data file on drive E and the new log file on drive C.

C.          Shrink the data file on drive D to 1 GB, and then shrink the data file on drive E to 2 GB.

Place the new data file on drive D and the new log file on drive E.

D.          Shrink and delete the data file on drive C, and then shrink the database files on drives D and E so that they are both 2 GB.

Place the new data file and the new log file on drive C.

You are the administrator of a SQL Server 2000 computer. The server contains your company's Accounts database. Hundreds of users access the database each day. Because you have had power interruptions in the past, you want to protect the physical integrity of the Accounts database. You do not want to slow down server operations. What should you do?

A.          Enable the ‘torn page detection’ database option for each database.

B.          Disable write caching on all disk controllers.

C.          Ensure that the write caching disk controllers have battery backups.

D.          Create a database maintenance plan to check database integrity and make repairs each night.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Gus reports that he cannot modify data in the database. You use SQL Profiler to capture his activities as shown below:

EventClass TextData

TraceStart

ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off…

ExistingConnection -- network protocol: LPC set quoted identifier on set implicit transactions off…

SQL:BatchCompleted set transaction isolation level serializable begin transaction select * from…

You want Gus to be able to modify data. You also want the Transact-SQL statement to execute without compromising data integrity. What are two possible ways to achieve this goal? (Each correct answer represents a complete solution. Choose two.)

A.          Change the isolation level of the database connection.

B.          Add indexes to the Sales table.

C.          Remove the explicit transaction from the Transact-SQL batch.

D.          Create a stored procedure to execute the Transact-SQL batch.

E.           Add a cascading UPDATE trigger to the Products table.

You are the database administrator for a retail company. The company owns 250 stores. Every month, each store submits approximately 3,000 sales records, which are loaded into a SQL Server 2000 database at corporate headquarters. A Data Transformation Services (DTS) package transforms the sales records as they are loaded. The package writes the transformed sales records to the Sales table, which has a column for integer primary key values. The IDENTITY property automatically assigns a key value to each transformed sales record.

After loading this month's sales data, you discover that a portion of the data contains errors. You stop loading data, identify the problem records, and delete those records from the database. You want to reuse the key values that were assigned to the records that you deleted. You want to assign the deleted key values to the next sales records you load. You also want to disrupt users' work as little as possible. What should you do?

A.          Export all records from the Sales table to a temporary table.
Truncate the Sales table, and then reload the records from the temporary table.

B.          Export all records from the Sales table to a text file.
Drop the Sales table, and then reload the records from the text file.

C.          Use the DBCC CHECKIDENT statement to reseed the Sales table's IDENTITY property.

D.          Set the Sales table's IDENTITY_INSERT property to ON.
Add new sales records that have the desired key values.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database contains a table that is used to store information about equipment scheduling. The EquipmentSchedules table is configured as shown below:

Column_name Type Computed Length Prec Scale Nullable

EquipmentID int no 4 10 0 no

CustomerID  int no 4 10 0 no

Location varchar no 50 yes

StartDate datetime no 8 no

EndDate datetime no 8 no

Rate money no 8 19 4 no

Identity Seed Increment

EquipmentID 1 1

RowGuidCol

No rowguidcoi column defined

Data_located_on_filegroup

PRIMARY

Index_name index_description index_keys

PK_EquipimentSchedules clusterd, unique, primary… EquipmentID

Contraint_type contraint_name delete_action update_action

PRIMARY KEY (clustered) PK_EquipmentSchedules na na

Users report that some equipment schedules have an end date that is earlier than the start date. You need to ensure that the start date is always earlier than or equal to the end date. You also want to minimize physical I/O. You do not want users to change the Transact-SQL statements they use to modify data within the database. What should you do?

A.          Create a constraint that compares the start date to the end date.

B.          Create a trigger that compares the start date to the end date.

C.          Create a rule that compares the start date to the end date.

D.          Create a stored procedure that tests the start and end dates before inserting the row into the database.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. Users report that several storage locations in the UnitsStored field contain negative numbers. You examine the database's table structure. The table properties are configured as shown below:

Key ID Name Data Type Size

Y SotrageLocationID int 4

LocationDescription varchar 50

PartID int 4

UnitsStored bigint 8

DateStored datetime 8

TagID int 4

You correct all the negative numbers in the table. You must prevent the database from storing negative numbers. You also want to minimize use of server resources and physical I/O. Which Transact-SQL statement should you execute?

A.          ALTER TABLE dbo.StorageLocations ADD CONSTRAINT
CK_StorageLocations_UnitsStored
CHECK (UnitsStored >= 0)

B.          CREATE TRIGGER CK_UnitsStored On StorageLocations
FOR INSERT, UPDATE AS
IF INSERTED.UnitsStored < 0 ROLLBACK TRAN

C.          CREATE RULE CK_UnitsStored As @Units >= 0
GO
sp_bindrule 'CK_UnitsStored'
'StorageLocations.UnitsStored'
    GO

D.          CREATE PROC UpdateUnitsStored
(@StorageLocationID int, @UnitsStored bigint) AS
IF @UnitsStored < 0
RAISERROR (50099, 17)
ELSE
UPDATE StorageLocations
SET UnitsStored = @UnitsStored
WHERE StorageLocationID = @StorageLocationID

You are the administrator of a SQL Server 2000 computer. You have two new hard disks on which you will create a database named Inventory. You want to insert, update, and delete data as quickly as possible. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Configure the hard disks as two mirrored NTFS volumes.

B.          Configure the hard disks as one mirrored NTFS volume.

C.          Configure the hard disks as two independent NTFS volumes.

D.          Configure the hard disks as one extended NTFS volume.

E.           Place Inventory_data.mdf on the first volume and Inventory_log.ldf on the second volume.

F.           Place Inventory_data.mdf and Inventory_log.ldf on the first volume and Inventory_data2.ndf and Inventory_log2.ldf on the second volume

G.          Place Inventory_data.mdf and Inventory_log.ldf on the same volume.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. The database has a Parts table that has a field named InStock. When the parts have shipped, a table named PartsShipped is updated. When the parts are received, a table named PartsReceived is updated. The relationship of these tables is shown below:


You want the database to update the InStock field automatically. What should you do?

A.          Add triggers to the PartsShipped and the PartsReceived tables that update the InStock field in the Parts table.

B.          Create a user-defined function that calculates current inventory by running aggregate queries on the PartsShipped and PartsReceived tables.

C.          Use a view that creates an InStock field as part of an aggregate query.

D.          Create stored procedures for modifying the PartsShipped and PartsReceived tables that also modify the InStock field in the Parts table.
Use these procedures exclusively when modifying data in the PartsShipped and PartsReceived tables.

You are the administrator of a SQL Server 2000 computer. The server is configured as shown below:


You need to create a new database named Inventory. Employees in your company will use the Inventory database to track inventory data. Users will require immediate responses to queries that help them locate where parts are stored. The tables in the database will be configured as shown below:


The database will consume 14 GB of disk space. You must configure the data files and transaction log to accelerate query response time. Which two courses of action should you take? (Each correct answer represents part of the solution. Choose two.)

A.          On drive C, create a transaction log.
On drive D, create a data file in the PRIMARY filegroup.
On drive E, create a data file in the SECONDARY filegroup.

B.          On each drive, create a transaction log.
On each drive, create a data file in the PRIMARY filegroup.

C.          On drive D, create a transaction log.
On drive E, create a data file in the PRIMARY filegroup.

D.          On the PRIMARY filegroup, create all tables and all indexes.

E.           On the PRIMARY filegroup, create all tables. On the SECONDARY filegroup, create all indexes.

F.           On the PRIMARY filegroup, create the Parts table and its indexes.
On the SECONDARY filegroup, create all other tables and their indexes.

You are the administrator of a database that contains 64 lookup tables. These tables store static data that should not change. However, users report that some of this data is being changed. You need to prevent users from modifying the data. You want to minimize changes to your security model and to your database applications. How should you modify the database?

A.          Create a filegroup named LOOKUP.
Move the lookup tables to this filegroup.
Select the read only check box for the filegroup.

B.          Create a database role named DataModifier.
Grant the SELECT permissions to the DataModifier role.
Add all users to the role.

C.          Deny INSERT, UPDATE and DELETE permissions for all users.
Create stored procedures that modify data in all tables except lookup tables.
Require users to modify data through these stored procedures.

D.          Create a view of the lookup tables.
Use the view to allow users access to the lookup tables.

You are the administrator of a SQL Server 2000 computer. One of these servers contains a 4-GB database named Marketing. You want to remove the Marketing database from one server and add it to the other as quickly as possible. What should you do?

A.          Detach the database from the original server by using the sp_detach_db stored procedure.
Copy the database and the transaction log files to the new server, and attach them by using the sp_attach_db stored procedure.

B.          Use the DTS Export Wizard to transfer all database objects from the original server to the new server.
Drop the existing database from the original server.

C.          Run a full backup of the database on the original server.
Create a new database named Marketing on the new server.
Restore the backup in the new Marketing database.
Drop the database from the original server.

D.          Shut down the original server.
Copy the database and the transaction log files to the new server.
Use the DISK INIT and DISK REFIT statements to attach the data file to the new server.
Drop the database from the original server.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. In this database, the Parts table has a primary key that is used to identify each part stored in the company's warehouse. Each part has a unique UPC code that your company's accounting department uses to identify it. The database is configured as shown below:


You want to maintain the referential integrity between the Parts table and the OrderDetails table. You want to minimize the amount of physical I/O that is used within the database. Which two Transact-SQL statements should you execute? (Each correct answer represents part of the solution. Choose two.)

A.          CREATE UNIQUE INDEX IX_UPC On Parts(UPC)

B.          CREATE UNIQUE INDEX IX_UPC On OrderDetails(UPC)

C.          CREATE TRIGGER UPCRI On OrderDetails
FOR INSERT, UPDATE As
If Not Exists (Select UPC From Parts
Where Parts.UPC = inserted.UPC) BEGIN
    ROLLBACK TRAN
END

D.          CREATE TRIGGER UPCRI On Parts
FOR INSERT, UPDATE As
If Not Exists (Select UPC From Parts
Where OrderDetails.UPC = inserted.UPC) BEGIN
    ROLLBACK TRAN
END



E.           ALTER TABLE dbo.OrderDetails ADD CONSTRAINT
FK_OrderDetails_Parts FOREIGN KEY(UPC)
REFERENCES dbo.Parts(UPC)

F.           ALTER TABLE dbo.Parts ADD CONSTRAINT
FK_Parts_OrderDetails FOREIGN KEY (UPC)
REFERENCES dbo.Parts(UPC)

You are the administrator of a SQL Server 2000 computer named SQL1. You want to perform ad hoc distributed queries against a database that is stored on a SQL Server 2000 computer named SQL2. SQL2 contains several databases, and each of these databases uses a different collation. You want to ensure that comparisons in distributed queries are evaluated correctly. You also want to minimize administrative overhead. How should you configure SQL1?

A.          Use the ALTER DATABASE statement to change the collation of the database SQL1.

B.          Add SQL2 as a remote server.

C.          Add SQL2 as a linked server.
Select the Use Remote Collation check box, and do not specify a collation name.

D.          Add SQL2 as a linked server.
Select the Use Remote Collation check box, and specify a collation name.
Repeat this process once for each database on SQL2.

You are the administrator of a SQL Server 2000 computer. The server contains seven databases that provide data to several company client/server applications. A different user maintains each database. You need to configure server and database permissions so that each user who maintains a database has full permissions on that database. What should you do?

A.          Create a domain user group for all users.
Add a login for the domain user group.
Add the login to the sysadmin server role.

B.          Create a domain user group for all users.
Add a login for the domain user group.
Map the login to a database user in each database.
Add all database users to the db_owner database role in each database.

C.          Create a login for each user's domain user account.
Map each login to a database user in the appropriate databases.
Add the database users to the db_owner database role in each database.

D.          Create a login for each user's domain user account.
Add the logins to the dbcreator server role.

You are the administrator of a SQL Server 2000 computer. The server contains a Data Transformation Services (DTS) package that queries multiple databases and writes the results to a text file. You run this package by using a Microsoft Windows batch file. The batch file uses the dtsrun utility to execute the DTS package.

You want to ensure that connection properties, such as login names and passwords, cannot be read or modified by users. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Save the DTS package so that it has an owner password.

B.          Save the DTS package so that it has an user password.

C.          Encrypt the DTS package details in the command line of the dtsrun utility.

D.          Store the DTS package in the Meta Data Services repository.

E.           Store the DTS package as a Microsoft Visual Basic file.

You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's finance department. The permissions on the FinanceData table are configured as shown below:

Users/Database Role Select Insert Update Delete

Accountants Grant Grant Grant Deny

Administrators Grant Grant Grant Grant

Reporters Grant Deny Deny Deny

Sales Managers Grant Blank Blank Grant

Guest Blank Blank Blank Blank

Ryan is a member of the Accountants, Administrators, and public groups.

He reports that he is unable to delete information from the FinanceData table. You need to allow him to delete information from the table. Which Transact-SQL statement should you execute?

A.          EXEC sp_droprolemember 'Accountants', 'Ryan'

B.          EXEC sp_droprolemember 'Administrators', 'Ryan'

C.          EXEC sp_addrolemember 'db_datawriter', 'Ryan'

D.          EXEC sp_addrolemember 'db_owner', 'Ryan'

You are the administrator of a SQL Server 2000 computer. Peter is an employee in your company's marketing department. Peter has a SQL Server login named Peter, and he has access to a database named MarketingData.

Peter has been assigned to a research project and needs to view and edit information stored in a database named ResearchData. The database is contained on the same server as the MarketingData database.

You need to configure the appropriate permissions for Peter on the ResearchData database. Which Transact-SQL statement should you execute?

A.          GRANT ALL ON ResearchData TO 'Peter'

B.          GRANT SELECT ON ResearchData TO 'Peter'
GRANT INSERT ON ResearchData TO 'Peter'
GRANT UPDATE ON ResearchData TO 'Peter'

C.          EXEC sp_addrolemember 'db_datareader','Peter'
EXEC sp_addrolemember 'db_datawriter','Peter'

D.          EXEC sp_grantdbaccess 'Peter',' PeterU'
GO
EXEC sp_addrolemember 'db_datareader',' PeterU'
EXEC sp_addrolemember 'db_datawriter',' PeterU'

You are the administrator of two Microsoft Windows 2000 computers. One computer is running Internet Information Services (IIS), and the other is running SQL Server 2000. Company partners need to connect by means of the Internet and query data stored on the SQL Server computer. Some of these partners have computers that do not use Microsoft operating systems or Web browsers.

You need to configure the IIS and SQL Server 2000 computers to allow access to data by means of the IIS virtual directory. IIS uses Basic Authentication to allow access to the virtual directory. Only company partners should have access to the SQL Server computer by means of the Internet. The partners should be allowed to make ad hoc queries. You want to be able to audit successful and failed logins to the SQL Server computer. You want to allow the partners access to the SQL Server computer while keeping your security scheme as simple as possible. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Configure the IIS virtual directory to allow URL queries.

B.          Configure the IIS virtual directory to allow template queries.

C.          Create a new Windows user account.
Create a corresponding Windows Authenticated login on the SQL Server computer. Configure the IIS virtual directory to always use this login when connecting to the SQL Server computer.

D.          Create a Windows Authenticated login on the SQL Server computer for the IIS Internet Guest Account.
Configure the IIS virtual directory to always use the Internet Guest Account when connecting to the SQL Server computer.

E.           Create a Windows Authenticated login for each company partner on the SQL Server computer.
Configure the IIS virtual directory to use Windows Integrated Authentication when connecting to the SQL Server computer.

You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's sales department. The permissions on the SalesFigures table are configured as shown below:

Users/Database Role Select Insert Update Delete

Accountants Grant Grant Deny Deny

Managers Grant Blank Grant Grant

Salespeople Grant Blank Blank Blank

Guest Blank Blank Blank Blank

Lilly is a user in the sales department. She needs to review the data in the SalesFigures table. She also needs to add new items to the table. You need to allow Lilly to perform these tasks without giving her additional permissions to the database. What should you do? (Each correct answer represents part of the solution. Choose all that apply.)

A.          Add Lilly to the Managers database role.

B.          Add Lilly to the Salespeople database role.

C.          Grant Lilly INSERT permissions on the table.

D.          Grant Lilly UPDATE permissions on the table.

E.           Revoke UPDATE permissions on the table for Lilly.

F.           Revoke DELETE permissions on the table for Lilly.

You are the administrator of a SQL Server 2000 computer. The server will be used to contain data from your company's accounting, engineering, and production departments. Each department is contained in a Microsoft Windows domain users group. The groups are named Accounting, Engineering, and Production. Members of each department need to read and modify their data. However, users in one department should not have access to data in other departments. You want to configure the database so that it meets your company's security requirements. You also want to minimize administration time and the consumption of server resources. Which three actions should you take? (Each correct answer represents part of the solution. Choose three.)

A.          Create a database for each department.

B.          Create a database for all departments.

C.          Create a Windows-Authenticated login for each department.

D.          Create a named instance of SQL Server 2000 for each department.

E.           Map each domain users group to the processadmin role.

F.           Map each domain users group to the db_datareader and db_datawriter database roles.

G.          Grant each domain users group SELECT, INSERT, UPDATE, and DELETE permissions on the database objects that are used by that department.

You are the administrator of a SQL Server 2000 computer. The server contains a database named EmployeeData. Your company's human resources department uses this database, which contains several tables as shown in the following table.

Table name Used for

Employees Employee names, addresses, departments, and base salaries

Bonuses Bonuses that have been paid to employees

Awards Awards that have been presented to employees

The human resources department wants to make employee names, addresses, and awards available to anyone who has permission to access the server. However, the human resources department wants to allow only users in the human resources department to access salary and bonus information. You need to allow company users to access only the appropriate employee information. What should you do?

A.          Create a trigger on the Salary column of the Employees table.

B.          Create a stored procedure that retrieves all of the data from the Employees and Awards tables, inserts the data into a temporary table, and then grants the current user SELECT permissions on the temporary table.
Grant the Guest database user EXECUTE permissions on the stored procedure.

C.          Move the sensitive information out of the Employees table into a new table.
Grant the Guest database user SELECT permission on the Employees table.

D.          Create a view that contains the name, address, and award information.
Grant the Guest database user SELECT permissions on the view.

You are the administrator of a SQL Server 2000 computer. The server is used to store sales and product information for your company. A client/server application accesses the server by activating an application role named SalesAppRole. Several users report that they are unable to use the client/server application. You test the application and discover that it cannot log on to the server. You examine your security audit logs, which contain the information below:

EventClass TextData LoginName

Audit Object Permission Select * FROM [customers] Martha

Audit Object Permission Select [productname] * [productname] FR… Martha

Audit Object Permission Byron

Audit Object Permission -- sp_Mshasdbaccess select name as ‘… Byron

Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Martha

Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Martha

Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron

Audit App Role Change Pa… Byron

Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron

Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron

Audit Object Permission -- ‘sp_approlepassword’ was found in… Byron

Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Maria

Audit Object Permission UPDATE [Customers] SET [CompanyName] =… Maria

Audit Object Permission -- ‘sp_setapprole’ was found in the t… Byron

You need to enable the application to log on to the server. What should you do?

A.          Configure the database permissions to deny Martha access to the database.

B.          Reset the password on the SalesAppRole application role.

C.          Reset the permissions on the SalesAppRole application role.

D.          Add the SalesAppRole application role to the db_owner database role.

E.           Remove Byron’s database user account.

F.           Enable encryption on the password for the SalesAppRole application role.

You are the administrator of a SQL Server 2000 computer. The server is used to store confidential company information. Company policy requires that every action and change of permission on the server be logged. Policy also requires that the server can run only when logging is enabled. You need to configure the server to comply with this policy. What should you do?

A.          Use SQL Profiler to capture security events and audit events.
Make sure that file rollover is enabled.

B.          On the Security tab of the Server Properties dialog box, set the audit level to All.

C.          Configure the server to use Windows Authentication.
Make sure the Windows security log does not overwrite events.

D.          Set the c2 audit mode option to 1.
Restart the MSSQLServer service.

You are the administrator of a SQL Server 2000 computer. The server is a member of a Windows NT domain and is configured for Windows Authentication. The server contains a database that stores contact information for public officials in your region. These officials need to access the database by means of the Internet by using the Guest login. However, some users report that they cannot connect to the server by means of the Internet. You need to allow anyone access to the server by means of the Internet. What should you do?

A.          Assign the Guest login a blank password.

B.          Delete the Guest login and create a new login that is mapped to the Guest domain user account.

C.          Create a database user named Anonymous and assign the user the appropriate database permissions.

D.          Configure the server for Mixed Mode authentication.

You are the administrator of a SQL Server 2000 computer at a doctor's office. The server contains a database named Medical. The database stores patient and treatment information and is configured as shown below:


The office accountant needs access to the total number of treatments that have been performed in the office. The accountant should not have access to any other patient data and should not be able to change any information in the database. The accountant has a SQL Server Authenticated login and database user account named AccountUser. You need to make the appropriate information available to the accountant, while protecting the other information in the database. Which batch of Transact-SQL statements should you execute?

A.          CREATE VIEW TotalTreatments AS
SELECT COUNT(PatientID) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser

B.          CREATE VIEW TotalTreatments AS
SELECT COUNT(*) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser

C.          CREATE PROCEDURE TotalTreatments AS
SELECT COUNT(*) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser

D.          CREATE PROCEDURE TotalTreatments AS
SELECT COUNT(PatientID) AS Total FROM dbo.Patients
GO
GRANT ALL ON TotalTreatments TO AccountUser

You are the administrator of a SQL Server 2000 computer. One of the databases on the server contains a table named SalesData. This table stores information about sales orders that is used to create sales reports and to calculate commissions for sales personnel. The permissions on the SalesData table are configured as shown below:

Users/Database Role Select Insert Update Delete

Reporters Grant Blank Blank Blank

SalesManagers Grant Grant Grant Grant

Salespeople Blank Grant Blank Blank

Guest Blank Blank Blank Blank

Sales managers monitor the entries made by sales personnel and correct any errors. Because monitoring is time-consuming, the sales department will hire several assistants. The sales managers will make necessary corrections on hard copy, and then the assistants will enter the changes in the table. However, the assistants must not be allowed to delete any data from the table. You need to configure permissions on the table to support these requirements. You also want to reduce the amount of time needed to manage these permissions. What should you do?

A.          Create a database user account for each assistant.
Add each database user account to the SalesManagers database role.
Revoke DELETE permissions for the SalesManagers database role.

B.          Create a single database user account for all assistants.
Add the database user account to the SalesManagers database role.
Revoke DELETE permissions for the SalesManagers database role.

C.          Create a database user account for each assistant.
Create a database role named SalesAssistants. Grant SELECT, UPDATE, and INSERT permissions to the SalesAssistants database role.
Add each database user account to the SalesAssistants database role.

D.          Create a database user account for each assistant.
Create a database role named SalesAssistants. Add the role to the SalesManagers database role.
Add each database user account to the SalesAssistants database role.
Revoke DELETE permissions for the SalesAssistants database role.

You are the administrator of a SQL Server 2000 computer. The server belongs to a Microsoft Windows NT domain named DOMAIN1. The server contains a database named SalesData. Julia is a sales representative who uses the database. You create a login named DOMAIN1Julia and manually configure the permissions necessary for her to perform her job.

Julia is temporarily reassigned to the marketing department. During her assignment in the marketing department Julia must not be able to access the server. However, you must restore her access when she returns to the sales department. You need to configure the appropriate database permissions for Julia. Which Transact-SQL statement should you execute?

A.          REVOKE ALL TO SalesData FROM 'Julia'

B.          EXEC sp_revokeaccess 'Julia'

C.          EXEC sp_denylogin 'Julia'

D.          EXEC sp_droplogin 'Julia'

You are the administrator of a SQL Server 2000 computer named ProdSql1. The server is a member of a Microsoft Windows NT domain named CORP. ProdSql1 contains a database named Finance. Your company purchases a client/server application that will use a database named Marketing. This database will be stored on ProdSql1. Maria, Bruno, and Stefan will use the new application. These users belong to a domain user group named Marketers. In the future, several additional users who will also use the new application will be added to the Marketers group. The logins on ProdSql1 are configured as shown below:

Name Type Server Access

BUILTINAdministrators Windows Permit

Sa Standard Permit

CORPMartha Windows Permit

CORPByron Windows Permit

CORPSal Windows Permit

Marketers Standard Permit

You need to allow Martha, Byron, and Sal access to the Marketing database as quickly as possible. Which batch of Transact-SQL statements should you execute?

A.          USE Marketing
GO
EXEC sp_grantdbaccess 'CORPMartha'
EXEC sp_grantdbaccess 'CORPByron'
EXEC sp_grantdbaccess 'CORPSal'

B.          EXEC sp_grantlogin 'CORPMarketers'
GO
USE Marketing
EXEC sp_grantdbaccess 'CORPMarketers'

C.          USE Marketing
GO
EXEC sp_grantdbaccess 'Marketers'

D.          EXEC sp_addlogin 'CORPMarketers'
GO
USE Marketing
EXEC sp_grantdbaccess 'CORPMarketers'

You are the administrator of a SQL Server 2000 computer. Your company has client computers that are running Microsoft Windows 2000 Professional and UNIX. All client computers require access to the server. Users with domain accounts will access the server by means of the company LAN, and external users will access the server by means of the Internet. You need to configure the server so that all authorized users can connect to it. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A.          Configure Windows Authentication.

B.          Configure Mixed Mode Authentication.

C.          Create a login for the Domain Users group.

D.          Create a login for the Guest domain user account.

E.           Create a login for each UNIX user, and set a password for each login.

F.           Create a login for each external user, and set a blank password for each login.

G.          Create a login named Guest, and set a blank password for the login.

You are the administrator of a SQL Server 2000 computer. One of the databases on the server contains a table named CustomerInfo. This table is used to store information about your company's customers. You want some users to be allowed to modify only the table's Address column. Other users should be allowed to modify only the table's CreditCard column. Neither group of users has permission on the CustomerInfo table. You need to make sure that only authorized users make changes to the Address and CreditCard columns. What should you do?

A.          Create a stored procedure and require users to enter information by means of the stored procedure.

B.          Create a user-defined function and require users to enter information by means of the function.

C.          Create a rule and apply it to the Address and CreditCard columns.

D.          Create a constraint and apply it to the Address and CreditCard columns.

You are the administrator of a SQL Server 2000 computer named DataSvr. The server is a member of a Microsoft Windows NT domain. DataSvr is currently configured for Windows Authentication and contains logins for several domain user groups. The server is configured to use the Named Pipes Net-Library. Your company purchases five UNIX client computers. You want to allow employees who will be using these computers to access DataSvr. What should you do?

A.          Configure DataSvr for Mixed Mode authentication.
Add a login for a domain user group that includes the five UNIX computers.

B.          Configure DataSvr for Mixed Mode authentication.
Add a login for each of the five UNIX users.

C.          Install the TCP/IP Net-Library.
Add the five UNIX users to a domain user group that has access to DataSvr.

D.          Install the TCP/IP Net-Library.
Add a login for each of the five UNIX users.

You are the administrator of an SQL Server 2000 computer named CorpSQL. The server is a member of a Microsoft Windows NT domain named CORPORATE. Franz maintains a local user account on CorpSQL and is a member of the local Power Users group. He requests access to the SQL Server databases that are stored on CorpSQL. You need to allow Franz to access CorpSQL. Which Transact-SQL statement should you execute?

A.          EXEC sp_grantlogin Franz

B.          EXEC sp_grantlogin CORPORATEFranz

C.          EXEC sp_grantlogin Power UsersFranz

D.          EXEC sp_grantlogin CorpSQLFranz

You are the administrator of a SQL Server 2000 computer. The server is a member of a Microsoft Windows NT domain named CORPHQ. Several users need access to the server. These users are members of a domain user group named Receptionists. The Receptionists group also contains users who should not have access to the server. These users are also members of a domain user group named TempEmployees. You need to allow only the appropriate users to access the server. Which Transact-SQL statement should you execute?

A.          EXEC sp_addlogin 'CORPHQReceptionists'
EXEC sp_droplogin 'CORPHQTempEmployees'

B.          EXEC sp_addlogin 'CORPHQTempEmployees'
EXEC sp_addlogin 'CORPHQReceptionists'
REVOKE ALL ON Master FROM CORPHQTempEmployees

C.          EXEC sp_addlogin 'CORPHQReceptionists'
EXEC sp_denylogin 'CORPHQTempEmployees'

D.          EXEC sp_addlogin 'CORPHQTempEmployees'
EXEC sp_addlogin 'CORPHQReceptionists'
GRANT ALL ON Master TO CORPHQReceptionists

You are the administrator of a SQL Server 2000 computer. The server is used to store confidential information about company employees. You suspect that at least one user is accessing the employee information database without company approval.

You believe the access is occurring after business hours. You need to discover whether users are accessing the database without approval and which users are accessing the data. You also want to minimize any additional overhead on the server during business hours. What should you do?

A.          Use SQL Profiler to capture security events and audit events.

B.          On the Security tab of the Server Properties dialog box, set the audit level option to All.

C.          Configure the server to use Windows Authentication.
Make sure the Windows security log does not overwrite events.

D.          Set the c2 audit mode option to 1.
Restart the MSSQLServer service.

You are the administrator of a SQL Server 2000 computer. The server is used to store information for your company's finance department. The permissions on the FinanceInfo database are configured as shown below:

Users/Database Role Create Table Create View Create SP

Accountants Deny Blank Deny

Administrators Grant Blank Blank

Public Blank Blank Blank

AppRole1 Blank Blank Blank

Managers Grant Grant Grant

Salespeople Blank Blank Blank

Byron Blank Deny Blank

Lilly Grant Blank Blank

Byron is a member of the Accountants database role. He needs to add a new view to the database. You need to configure the database to allow Byron to add a new view. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Add Byron to the Managers database role.

B.          Add Byron to the Administrators database role.

C.          Revoke the CREATE TABLE permissions for Byron.

D.          Revoke the CREATE PROCEDURE permissions for Byron.

E.           Revoke the CREATE VIEW permissions for Byron.

You are the administrator of a SQL Server 2000 computer that contains multiple databases. One of these databases is named CorpSales. Your company hires a new employee named Helene. Part of Helene's job will be to create new tables in the CorpSales database. However she should not have access to the other databases on the server. You create a SQL Server login named Helene. Helene reports that she can log on to the server but is unable to access the CorpSales database. You need to allow Helene access to the CorpSales database. What should you do?

A.          Create a new database user in the CorpSales database.
Map the database user to Helene's login.
Grant CREATE TABLE permissions to the database user.

B.          Create a new database role in the CorpSales database.
Add Helene's login as a member of the role.
Grant CREATE TABLE permissions to the database role.

C.          Change the server's authentication mode to Windows Authentication.

D.          Add Helene's login to the processadmin server role on the server.

You are the administrator of a new multiprocessor SQL Server 2000 computer. You are migrating data from other database management systems to this server. You need to load data from two data files into a new table on the server. The table is empty and has no indexes. You want to complete the data load as quickly as possible. What should you do? (Each correct answer represents part of the solution. Choose all that apply.)

A.          Apply a bulk update lock to the table.

B.          Use separate client connections to load the files into the table simultaneously.

C.          Create a clustered index on the table.

D.          Specify the Full Recovery model for the database.

E.           Specify the Bulk-Logged Recovery model for the database.

You are the administrator of a SQL Server 2000 computer in your company's personnel department. Employee data is stored in a SQL Server 2000 database. A portion of the database schema is shown below:


You want to create a text file that lists these data columns in the following format:

title, firstName, lastName, workPhone, positionName, departmentName.

You want to create the text file as quickly as possible. You do not expect to re-create this file, and you want to avoid creating new database objects if possible. What should you do?

A.          Use the bcp utility to export the data from each table to a separate text file.
Use ‘format files’ to select the appropriate columns.
Merge the data from each text file into a single text file.

B.          Create a view that joins data from all three tables.
Include only the columns you want to appear in the text file.
Use the bcp utility to export the data from the view.

C.          Create a SELECT query that joins the data from the appropriate columns in the three tables.
Add an INTO clause to the query to create a local temporary table.
Use the bcp utility to export the data from the local temporary table to a text file.

D.          Create a SELECT query that joins the data from the appropriate columns in the three tables.
Add an INTO clause to the query to create a global temporary table.
Use the bcp utility to export the data from the global temporary table to a text file.

You are the administrator of an SQL Server 2000 computer. The server contains a database that stores financial data. You want to use Data Transformation Services (DTS) packages to import numeric data from other SQL Server computers. The precision and scale values of this data are not defined consistently on the other servers. You want to prevent any loss of data during the import operations. What should you do?

A.          Use the ALTER COLUMN clause of the ALTER TABLE statement to change data types in the source tables. Change the data types so that they will use the lowest precision and scale values of the data that will be transferred.

B.          Use the ALTER COLUMN clause of the ALTER TABLE statement to change the data types in the destination tables. Change the data types to reflect the highest precision and scale values involved in the data transfer.

C.          Set a flag on each DTS transformation to require an exact match between source and destination columns.

D.          Set the maximum error count for each DTS transformation task equal to the number of rows of data you are importing.
Use an exception file to store any rows of data that generate errors.

E.           Write a Microsoft ActiveX script for each DTS transformation.
Use the script to recast data types to the destination's precision and scale values.

You are the administrator of a SQL Server 2000 computer. You are creating a database named RetailImport to use as an intermediate data store for a data warehouse. Each night you must import daily data into the database from SQL Server 2000 computers in 120 retail locations. After the data is moved into the data warehouse, the tables are truncated.

You want to configure the data import process so that you minimize the time needed to import the sales data and administer the database. What should you do?

A.          Use the Simple Recovery model and the FOR LOAD option to create the database.
Create a Data Transformation Services (DTS) package that uses the BULK INSERT statement to copy the sales data.

B.          Index the foreign key fields in the child tables.

C.          Create a Data Transformation Services (DTS) package that uses a Data Driven Query task to copy the sales data.

D.          Collect the sales data by using a distributed transaction that inserts the data from all 120 retail locations into the RetailImport database as a single transaction.

E.           On the servers in the retail locations, create stored procedures that submit the updates to the RetailImport database each night as SQL-XML updategrams.

You are the database administrator for a financial services company. Employees enter data 24 hours a day into a SQL Server 2000 database. These employees report slower response times when new account information is gathered from branch offices and added to the database. You currently use the following BULK INSERT statement to add the account information.

BULK INSERT finance.dbo.customers
FROM 'd:bulkaccts143_10142000.txt'
WITH DATAFILETYPE = 'char',
     FIELDTERMINATOR = 't',
     ROWTERMINATOR = 'n',
     TABLOCK

You want to ensure that response time does not slow when new account information is added to the database. What should you do?

A.          Drop the indexes on the Customers table before the data load, and then re-create the indexes after the data load is complete.

B.          Remove the TABLOCK option from the BULK INSERT statement.

C.          Add the BATCHSIZE option to the BULK INSERT statement, and then set the option equal to 10 percent of the number of rows to be loaded.

D.          Add the ROWS_PER_BATCH option to the BULK INSERT statement, and then set the option equal to 10 percent of the number of rows to be loaded.

You are the administrator of an SQL Server 2000 computer. You create a Data Transformation Services (DTS) package. The package is stored as a local package on the server. The package exports data from an online transaction processing (OLTP) database system to an online analytical processing (OLAP) database system that is located on a second SQL Server 2000 computer. You want the package to execute automatically each evening at 8:00 P.M. What should you do?

A.          Use SQL Server Enterprise Manager to create a new job
Create a CmdExec job step that runs the dtsrun utility
Schedule the job to run each night at 8:00 P.M.

B.          Use SQL Server Enterprise Manager to create a new job
Create a Transact-SQL job step to query data from the OLTP database
Create a second Transact-SQL job step to launch the DTS package
Schedule the job to run each night at 8:00 P.M.

C.          Create a batch file that runs the dtsrun utility
Use the Microsoft Windows Task Scheduler to run the job each night at 8:00 P.M.
Configure the Windows Task Scheduler to use the local system account

D.          Move the DTS package to the repository
Configure the SQLServerAgent service on the OLAP database server to update the package each night at 8:00 P.M.

You are a database administrator in the Los Angeles branch office of a specialty foods supplier. A mainframe database at the headquarters contains all company data. Each branch office contains a SQL Server 2000 computer that imports regional data from the mainframe database. The server in Los Angeles contains a Data Transformation Services (DTS) package that uses OLE DB to connect to the company's mainframe database. The DTS package extracts and transforms data about buyers and products for that region. The DTS package then writes the data to the SQL Server database in Los Angeles.

You need to configure a SQL Server computer for a new branch office in Sydney. You want to copy the Los Angeles package and modify it so that it writes data to the SQL Server database in Sydney. You use the DTS Designer to modify the DTS package so that it imports regional data from the mainframe database to the server in Sydney. The modified DTS package runs successfully on the server in Los Angeles. You save this DTS package to the server in Sydney, but the server in Sydney cannot connect to the mainframe database. You want to enable the server in Sydney to connect to the mainframe database. What should you do?

A.          Change the connection properties in the DTS package so that the package uses new login credentials to connect to the mainframe database.

B.          Modify the workflow in the DTS package so that the server in Sydney is included.

C.          On the server in Sydney, install an OLE DB provider for the mainframe database.

D.          On the server in Sydney, delete and then re-create the DTS package.

You are the administrator of a SQL Server 2000 computer. The server contains your company's order processing database. Two-hundred operators take orders by telephone 24 hours a day. Three-hundred data entry personnel enter data from orders received by mail. To ensure that order data will not be lost, your company's disaster recovery policy requires that backups be written to tape. Copies of these tapes must be stored at an off-site company location. Orders must be entered into the database before they can be filled. If the server fails, you must be able to recover the order data as quickly as possible. You need to create a backup strategy that meets the company requirements and minimizes server workload. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Perform a combination of full database and filegroup backups.

B.          Perform a combination of full database and file backups.

C.          Perform a combination of full database, differential, and transaction log backups.

D.          Back up the data to a local tape drive.

E.           Back up the data to a network share, and then use enterprise backup software to write the disk backups to tape.

You are the administrator of a SQL Server 2000 computer. The server contains a database named CustomerManagement that tracks customer requests for product information. The CustomerManagement database has two filegroups. The PRIMARY filegroup stores current transactional data, and the SECONDARY filegroup stores historical data. Your backup strategy includes full database and transactional log backups. A portion of the header information for the current week's backups is shown below:

BackupName  BackupType BackupFinishDate


custmgmt_full_20000625 1 2000-06-25 21:57:03.000
custmgmt_tlog_20000626_1 2 2000-06-26 10:34:21.000
custmgmt_tlog_20000626_2 2 2000-06-26 14:36:32.000
custmgmt_tlog_20000626_3 2 2000-06-26 18:42:11.000
custmgmt_tlog_20000627_1 2 2000-06-27 10:33:37.000
custmgmt_tlog_20000627_2 2 2000-06-27 14:35:18.000

On June 27, 2000, at 4.18 PM, an application developer accidentally deletes the current customer requests and immediately notifies you of the problem. You want to recover as much data as you can as quickly as possible to minimize server downtime. What should you do?

A.          Back up the transaction log.
Restore the PRIMARY filegroup to a new location.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.
Import the deleted data from the new location.

B.          Back up the transaction log.
Restore the PRIMARY filegroup to the existing database.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.

C.          Back up the transaction log.
Restore the entire database to a new location.
Restore subsequent transaction logs in sequence.
Use the STOPAT option to restore the final transaction log backup.
Import the deleted data from the new location.

D.          Restore the PRIMARY filegroup to the existing database.
Restore subsequent transaction logs in sequence.

You are the administrator of a SQL Server 2000 computer. The server contains a database that has the torn page detection database option enabled. Backups of the database are made daily. The server loses power for one minute. When power is restored, torn pages are detected. You notice in SQL Server Enterprise Manager that the database is marked suspect. You need to correct the problem. What should you do?

A.          Execute the DBCC CHECKDB statement, and then specify the PHYSICAL_ONLY option.

B.          Execute the DBCC CHECKDB statement, and then specify the REPAIR_REBUILD option.

C.          Execute the sp_resetstatus stored procedure.

D.          Restore the suspect database from backups.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. You perform full database backups every two days. You also run regular database consistency checks on the server. The most recent check of the Sales database returns the following message:

CHECKDB found 0 allocation errors and 9 consistency errors in the table 'Orders'
(object ID 214575782).

You want to correct the data integrity errors while minimizing the amount of data lost. What should you do?

A.          Disconnect users from the Sales database.
Enable the ‘single user’ database option.
Execute the DBCC CHECKTABLE statement for the Orders table, and specify the REPAIR_REBUILD option.

B.          Disconnect users from the Sales database.
Enable the ‘DBO use only’ database option.
Execute the DBCC CHECKTABLE statement for the Orders table, and specify the REPAIR_REBUILD option.

C.          Disconnect users from the Sales database.
Execute the RESTORE DATABASE statement for the Sales database

D.          Execute the DBCC CLEANTABLE statement for the Orders table.

E.           Execute the sp_table_validation stored procedure for the Orders table.

You are the administrator of two SQL Server 2000 computers for an online retailer. One of the SQL Servers records customer buying preferences and demographic data. A DTS package populates a data warehouse with this information and a second SQL server uses Analysis Services to create multidimensional cubes.

The Data Transformation Services (DTS) package is stored in the Meta Data Services repository on the second server. You want to maximize the amount of lineage data that can be recovered if a data file is damaged or lost. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Use the Full Recovery model for the staging database.

B.          Use the Full Recovery model for the msdb database.

C.          Back up the transaction log in the staging database by using the NO_TRUNCATE option.

D.          Back up the transaction log in the msdb database by using the NO_TRUNCATE option.

E.           Back up the multidimensional data cube.

F.           Save the DTS package as a file.

You are the administrator of a SQL Server 2000 computer at your company's warehouse. All product orders are shipped from this warehouse. Orders are received at 30 sales offices. Each sales office offers a range of products specific to its region. Each sales office contains one SQL Server 2000 computer. These servers connect to the warehouse through dial-up connections as needed, typically once a day. Each sales office needs data pertaining only to its region. You need to replicate inventory data from the server at the warehouse to the servers at the sales offices. You want to minimize the amount of time needed to replicate the data. Which three actions should you take? (Each correct answer represents part of the solution. Choose three.)

A.          Create one publication for each Subscriber.

B.          Create one publication for all Subscribers.

C.          Enable horizontal filtering.

D.          Enable vertical filtering.

E.           Use pull subscriptions.

F.           Use push subscriptions.

You are the administrator of a SQL Server 2000 computer. Your company purchases an accounting application from a vendor. The application stores its data in a database named Accounting on the server. The tables in this database contain columns that function as primary keys, but PRIMARY KEY and FOREIGN KEY constraints are not used. You need to replicate data from this database to another SQL Server computer. This server will use the replicated data to generate reports. Most reports will run each month, but the accounting department needs to have the ability to run reports at any time. Reports should be accurate through the last full working day. You cannot make any changes to the database, but you need to implement replication. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Implement merge replication.

B.          Implement snapshot replication.

C.          Implement transactional replication.

D.          Schedule replication to run continuously.

E.           Schedule replication to run during off-peak hours.

You are the administrator of an SQL Server 2000 computer. The server contains a database named Sales. The database is configured as shown below:


In the last six months, the database has grown by 4 GB. Users report that query response time has slowed. The options set up on the database are configured as follows:

Restrict Access Read-only Recovery Model ANSI NULL default Recursive triggers

Unchecked Unchecked Full Unchecked Unchecked

Auto update statistics Torn page detection Auto close Auto shrink

Checked Checked Unchecked Unchecked

Auto create statistics Use quoted identifiers Compatibility Level

Checked Unchecked 80

You want to accelerate query response time. What should you do?

A.          Update the database statistics

B.          Add indexes to the foreign key fields

C.          Truncate the transaction log

D.          Run the Database Maintenance Plan Wizard

E.           Drop primary keys from all tables

You are the administrator of a SQL Server 2000 computer. The server contains a database that is heavily indexed and that the company users query extensively. The database has grown and query response time has slowed. The database is stored in a single data file. You want to accelerate query response time. What should you do?

A.          On a new hard disk, create a new filegroup.
Drop the existing nonclustered indexes, and then re-create them on the new filegroup.

B.          On a new hard disk, add a new file to the PRIMARY filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.

C.          On the existing hard disk, create a new filegroup.
Drop the existing nonclustered indexes, and then re-create them on the new filegroup.

D.          On the existing hard disk, add a new file to the PRIMARY filegroup.
Drop the existing nonclustered indexes, and then re-create them on the PRIMARY filegroup.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database has a group of tables that analysts use to examine sales trends. The database options are set to their default values, and the tables are configured as shown below:


Analysts who use the database report that query performance has become slow. You analyze the clustered primary key on the Invoices table and receive the following results:

DBCC SHOWCONTIG scanning 'Invoices' table
Table: 'Invoices' (21575115); index ID: 1, database ID: 6
TABLE level scan performed.

Pages Scanned..: 200
- Extents Scanned: 50
- Extent Switches: 40
- Avg. Pages per Extent: 4.0
- Scan Density [Best Count:Actual Count].: 60.00% [3:5]
- Logical Scan Fragmentation.: 0.00%
- Extent Scan Fragmentation..: 40.00%
- Avg. Bytes Free per Page: 146.5
- Avg. Page Density (full): 98.19%

DBCC execution completed. If DBCC printed error messages, contact your system
administrator.

You want to improve performance of queries that join tables to the Invoices table. What are three possible Transact-SQL statements you can execute to achieve this goal? (Each correct answer represents a complete solution. Choose three.)

A.          DBCC UPDATEUSEAGE ('Sales','Invoices','PK_Invoices')

B.          CREATE UNIQUE CLUSTERED INDEX PK_Invoices On
Invoices(InvoiceID) WITH DROP_EXISTING

C.          DBCC INDEXDEFRAG ('Sales','Invoices','PK_Invoices')

D.          DBCC DBREINDEX (Sales.dbo.Invoices, 'PK_Invoices')

E.           UPDATE STATISTICS 'Invoices'

F.           DBCC CHECKALLOC (Sales, REPAIR_FAST)

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The Sales database is configured as shown below:


The Sales database stores point-of-sale transaction data. The data is later imported into another database for analysis. Recently, users have reported slow response times whenever they enter point-of-sale information. You examine the indexes on the Invoices table that are configured as shown in the Index Description exhibit.

index_name index_description index_keys

IX Customers PK nonclustered located on PRIMARY CustomerID

IX SalesPerson PK nonclustered located on PRIMARY SalesPersonID

PK Invoices clustered, unique, primary key located on PRIMARY InvoiceID

You want to accelerate database response time when users insert new point-of-sale information. What should you do?

A.          Drop IX_Customers_FK and IX_SalesPerson_FK.

B.          Change IX_Customers_FK to a clustered index.

C.          Change IX_SalesPerson_FK to a clustered index.

D.          Change PK_Invoices to a nonclustered index.

You are the administrator of a SQL Server 2000 database. The database was configured with all default options. You drop indexes to facilitate a large import of data, and rebuild them after the import is complete. Users report that response times from a stored procedure have become slower. In the stored procedure, you examine the following code.



CREATE PROCEDURE GetCustomer
( @CustomerID Int )
AS
SELECT FirstName, LastName, Address1, Address2, City,
State, Zip, MAX(PurchaseDate) AS LastPurchase
FROM Customers C
JOIN Addresses A ON A.AddressID = C.AddressID
JOIN Purchases P ON P.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY FirstName, LastName, Address1, Address2, City, State, Zip

You want to improve the performance of the procedure by using the least amount of administrative effort. What should you do?

A.          Recompile the stored procedure.

B.          Update the database statistics.

C.          Check the consistency of the database and repair errors.

D.          Create a view and modify the stored procedure to use the view.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Inventory. Users report that the following query responds slowly:

Select Parts.SKU AS SKU, Parts.Description as [Part Description],
Locations.Description AS Location
FROM PartsLocations INNER JOIN Parts ON PartsLocations.LocationID = Locations.LocationID AND
Parts.LocationID = Locations.LocationID

You examine the indexes in the PartsLocations table. The indexes are configured as shown below:

Index_name index_description index_keys

IX_PartsLocations_LocationID_PK nonclustered located on PRIMARY LocationID

IX_PartsLocations_PartID_PK nonclustered located on PRIMARY PartID

PK_PartsLocations clustered, unique, primary… PartID, LocationID

You also examine the contiguity of the table and receive the following results:

DBCC SHOWCONTIG scanning 'PartsLocations' table
Table: 'PartsLocations' (1); index ID: 1, database ID: 6
TABLE level scan performed.

- Pages Scanned..: 3000
- Extents Scanned: 750
- Extent Switches: 502
- Avg. Pages per Extent: 4.0
- Scan Density [Best Count:Actual Count].: 33.33% [1:3]
- Logical Scan Fragmentation.: 33.33%
- Extent Scan Fragmentation..: 33.33%
- Avg. Bytes Free per Page: 1166.0
- Avg. Page Density (full): 85.77%

You need to improve the performance of the query. What should you do?

A.          Rebuild the PK_PartsLocations index.

B.          Rebuild the IX_PartsLocations_PartsID index.

C.          Rebuild the IX_PartsLocations_LocationID index.

D.          Rebuild the IX_PartsLocations_PartsID_LocationID index.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. The database is configured as shown below:


Analysts examine customer buying patterns and frequently use the following query:

SELECT Customers.CustomerID,
      Invoices.InvoiceID,
      Invoices.InvoiceDate,
      OrderDetail.Quantity, Products.BKU
FROM  Customers
INNER JOIN Invoices ON Customers.CustomerID = Invoices.CustomerID
INNER JOIN OrderDetail ON Invoices.InvoiceID = OrderDetail.InvoiceID
INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID
WHERE CustomerID = ?

You analyze the indexes in the Invoices table. The indexes are configured as shown below:

Index_name index_description index_keys

IX_Customers_PK nonclustered located on PRIMARY CustomerID

IX_SalesPerson_PK nonclustered located on PRIMARY SalesPersonID

PK_Invoices clustered, unique, primary key located on PRIMARY InvoiceID

You want to optimize the database schema so that it responds quickly to the analysts' queries. Which two actions should you take? (Each correct answer represents part of the solution. Choose two.)

A.          Re-create the PK_Invoices index as a nonclustered index

B.          Re-create the IX_Customers_FK index as a clustered index

C.          Re-create the IX_SalesPerson_FK index as a clustered index

D.          Create a composite index on CustomerID fields and SalesPersonID fields

E.           Create a composite index on InvoiceID fields and CustomerID fields

You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. Users access medical records by using the PatientID field. This field is the clustered primary key for the Patients table. When users try to access medical records, the database responds slowly. The database options are configured as follows:

Restrict Access Read-only Recovery Model ANSI NULL default Recursive triggers

Unchecked Unchecked Full Unchecked Unchecked

Auto update statistics Torn page detection Auto close Auto shrink

Unchecked Checked Unchecked Unchecked

Auto create statistics Use quoted identifiers Compatibility Level

Checked Unchecked 80

You want to accelerate query response time and minimize administrative overhead. How should you reconfigure the database?

A.          Create a SQL Server Agent job to execute the UPDATE STATISTICS statement, and schedule the job to run weekly.

B.          Select the ‘Auto update statistics’ check box.

C.          Run the Database Maintenance Plan Wizard, and accept the default settings.

D.          Rebuild the primary key as a nonclustered primary key.

E.           Clear the Auto create statistics check box.

You are the administrator of a SQL Server 2000 database named Articles. A large city newspaper uses this database to store its archived newspaper articles. Journalists query the database as part of their research. You have created full-text indexes so that users can query the articles quickly. A hardware failure forces you to restore the entire database from backup. After you restore the database, the journalists report that they can no longer run queries without receiving errors. You want to ensure that the journalists can query the database. What should you do?

A.          Create a new full-text catalog for the Articles database

B.          Rebuild the full-text catalog for the Articles database

C.          Create a new full-text index on the table that holds the text for the articles

D.          Repopulate the full-text index on the table that holds the text for the articles

You are the administrator of a SQL Server 2000 computer. The server contains your company's investment-tracking database. Each day, 100 operators make approximately 5,000 changes to customer investments. In addition, daily and monthly reports are created from the investment data. Developers at your company need to optimize the database application. They need a sample of database query activity to discover if they can speed up the transactions. The developers also want to replay the sample on another SQL Server computer. You need to capture the sample, but you want to minimize any increase to the workload of the server. What should you do?

A.          Run SQL Profiler on a client computer.
Configure SQL Profiler to monitor database activity, and log data to a .trc file.

B.          Run SQL Profiler on the server.
Configure SQL Profiler to monitor database activity, and log data to a database table.

C.          Run System Monitor on a client computer.
Configure System Monitor to monitor database activity, and log data to a .blg file.

D.          Start SQL Server from a command prompt.
Specify trace flag 1204 to enable verbose logging.

You are the administrator of a SQL Server 2000 computer. You are configuring a database for an inventory application. The hard drive has two physical hard disks.  Physical disk 0 has two drives, C and D—both 4.5 GB in size. Physical disk 1 has two drives, E and F—both 4.5 GB in size.

The operating system files are located on drive C. Your database will store a maximum of 6 GB of data and requires a maximum of 2 GB of disk space for the transaction log. You want to optimize database performance. What should you do?

A.          Add a 2-GB transaction log to drive D, a 3-GB data file to drive E, and a 3-GB data file to drive F

B.          Add a 1-GB transaction log to drive C, a 1-GB transaction log to drive D, a 3-GB data file to drive E, and a 3-GB data file to drive F

C.          Add a 1-GB transaction log to drive E, a 1-GB transaction log to drive F, a 3-GB data file to drive E, and a 3-GB data file to drive F

D.          Add a 2-GB transaction log to drive F, a 3-GB data file to drive D, and a 3-GB data file to drive E

You are the administrator of an SQL Server 2000 computer. The server contains a database named Inventory. The Inventory database has a table named StorageLocations that stores the location of parts contained in your company's warehouses. The StorageLocations table is configured as shown below:

Column Name Data Type Length

StorageLocationID (key) int 4

LocationDescription varchar 50

PartID int 4

UnitsStored bigint 8

DateStored datetime 8

TagID int 4

The LocationDescription field is usually described with a name 10 to 25 characters in length. The locations never store more than 100,000 units of any given part. You want to modify the table's schema to save space. You cannot lose any existing data. You want to do this by using the minimum amount of administrative time and server resources. Which Transact-SQL statement should you execute?

A.          ALTER TABLE [dbo].[StorageLocations]
ALTER FIELD [UnitsStored] [int] NOT NULL

B.          ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [char] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL

C.          ALTER TABLE [dbo].[StorageLocations]
ALTER FIELD [UnitsStored] [smallint] NOT NULL

D.          ALTER TABLE [dbo].[StorageLocations]
[LocationDescription] [nvarchar] (25) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL

You are the administrator of a SQL Server 2000 computer. The server contains a database that stores inventory data. Another database administrator has created a number of scheduled jobs to maintain the inventory database. Each weeknight the following jobs occur:

A BULK INSERT job imports data at 10:00 P.M.

Indexes are rebuilt at 10:15 P.M.

Data integrity checks are performed at 10:30 P.M.

A differential backup is performed at 10:45 P.M.

A DBCC SHRINKDATABASE job runs at 11:00 P.M.

You notice that the final job often fails and returns the following error message:

'Server Msg 3140, Level 16, State 3. Could not adjust the space allocation for file 'inventory_data'.'

You need to ensure that the final job runs without errors. What should you do?

A.          Create a new DBCC SHRINKFILE job to shrink the inventory_data file. Schedule the new job to run at the same time as the DBCC SHRINKDATABASE job.

B.          Modify the DBCC SHRINKDATABASE job so that it uses the DBCC SHRINKFILE statement to shrink each file individually.

C.          Increase the time between the data integrity checks and the differential backup.

D.          Increase the time between the differential backup and the DBCC SHRINKDATABASE job.

You are the administrator of an SQL Server computer. Users report that the database times out when they attempt to modify data. You use the Current Activity window to examine locks held in the database:

Spid 41: Locked

Spid 42: Blocking

Spid 53: Blocked by 42

You need to discover why users cannot modify data in the database, but you do not want to disrupt normal database activities. What should you do?

A.          Use the spid 42 icon in the Current Activity window to discover which SQL statement is being executed

B.          Use the sp_who stored procedure to discover who is logged in as spid 42

C.          Use SQL Profiler to capture the activity of the user who is logged in as spid 42

D.          Use System Monitor to log the locks that are granted in the database

You are the administrator of a SQL Server 2000 computer. The server contains a database that stores product data for your company. You need to execute an existing stored procedure that examines prices for your company's products and can modify them if necessary. You execute the stored procedure after business hours, but it does not complete. You execute the sp_lock stored procedure and receive the following output:

spid dbid ObjId IndId Type Mode

7 0 0 DB S
7 0 0 DB S
7 0 0 DB S
7 2145623952 1 PAG IS
7 2145623952 0 TAB IS
7 2145623952 1 KEY S
78 7 0 0 DB S
78 7 2145623952 1 PAG IX
78 7 2145623952 0 TAB IX
78 7 2145623952 1 KEY X

You want the stored procedure to complete successfully. What should you do?

A.          Execute the stored procedure, and specify the WITH RECOMPILE option.

B.          Execute the DBCC FREEPROCCACHE statement.

C.          Release the locks that are held by connections 61 and 64.

D.          Release the locks that are held by connections 72 and 78.

You are the administrator of a SQL Server 2000 computer. The server contains a database named Orders, which is used to record customer orders for the products your company sells. Your company's order volume exceeds 1 million orders per day. Each order uses approximately 100 KB of space in the database. Users report that the database responds slowly when they enter new orders. You use SQL Profiler to monitor the activity on the database and receive the data shown below:

StartTime EventClass

Data File Auto Grow

Data File Auto Grow

Data File Auto Grow

Data File Auto Grow

Data File Auto Grow

You need to modify the database to improve performance. What should you do?

A.          Double the size of the data file

B.          Configure the database to automatically grow by 10 percent

C.          Separate the database into two physical files

D.          Increase the size of the transaction log file

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. Users report that the database times out when they attempt to perform queries. You use System Monitor to analyze the server and receive the results shown below:

SQLServer:Access Methods

Full Scans/sec

SQLServer:Latches

Latch Waits/sec

SQLServer:Locks _Total

Lock Requests/sec

SQLServer:SQL Statistics

Batch Requests/sec

You need to discover why database performance is slow. What should you do?

A.          Use the current Activity window to discover who is holding locks in the database.

B.          Use SQL Profiler to discover who is scanning tables in the database.

C.          Measure disk activity by running the diskperf /y command, restarting, then using the % Disk Time counter in System Monitor.

D.          Use the sp_monitor stored procedure to measure the activity of the server.

You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. The data processing department in your company adds new business rules to the database's stored procedures. After the rules are added, users report that the medical records application receives time-out errors. You use System Monitor to analyze the server and receive the results shown below:

SQLServer:Access Methods

Full Scans/sec

Index Searches/sec 5601.116

SQLServer:Cache Manager Adhoc SQL Plans Procedure Plans

Cache Hit Ratio .056

SQLServer:Locks _Total

Lock Requests/sec

SQLServer:SQL Statistics

Batch Requests/sec

SQL Compilations/sec 51.020

SWL Re-Compilations/sec 0.000

You need to tune the stored procedures to improve performance. What should you do?

A.          Add indexes to the fields that are used in the joins.

B.          Remove the WITH COMPILE option from the stored procedure.

C.          Remove the stored procedures and alter the application so that it uses ad hoc queries to obtain data.

D.          Change the transaction isolation level to Repeatable Read.

You are the administrator of a SQL Server 2000 computer. The SQLServerAgent service on the server is configured as shown below:

Service startup account:  System account

Mail profile: Empty

Error log file name: E:Program FilesMicrosoft SQL

Include execution trace messages:  Unchecked

Write OEM file: Unchecked

Net Send Receipt: Empty

You configure two SQL Server Agent jobs to perform maintenance tasks. Each job copies files from the server to a file share on a corporate file server. Both jobs fail when you attempt to execute them. You need both jobs to execute successfully. What should you do?

A.          Ensure that the file copy procedures are in an ActiveScripting job step

B.          Make a member of the serveradmin server role the owner of both jobs

C.          Configure the SQLServerAgent service to use a domain account

D.          Grant the necessary permissions in the msdb database to the job owner's SQL Server login

You are the administrator of a Microsoft Windows NT 4.0 computer that is running SQL Server 2000. The computer contains a database named Sales. The data file and transaction log for the Sales database are located on a 9.1 GB hard disk. Both files are configured for automatic file growth. The data file occupies 7 GB of disk space. You need to calculate when you will need to add additional disk space. What should you do?

A.          Configure System Monitor to log disk space utilization

B.          Configure a database maintenance job to run the DBCC CHECKALLOC statement each night and then to log the to the job history

C.          Configure a SQL Server Agent job to execute the sp_spaceused stored procedure each day and then to store the results of the procedure to a table in the database

D.          Configure a SQL Server Agent job to execute the chkdsk utility each night at midnight and then to send the results of the utility to the default operator in an e-mail message

You are the administrator of an SQL Server 2000 computer. You create a job to perform several database maintenance tasks. This job must run whenever the server is restarted. You use SQL Server Enterprise Manager to create a new schedule entry for the job, and specify that the job should start automatically. You test the job by restarting the server. You check the job's history and discover that the job did not run. You need the job to run automatically when the server restarts. What should you do?

A.          Create a stored procedure that uses sp_start_job to start the job whenever the MSSQLService service starts

B.          Add another schedule entry to the job, specifiy that the job start hourly, and then disable the job

C.          Change the job's category to Database Maintenance

D.          Configure the SQLServerAgent service to start automatically

You are the administrator of eight SQL Server 2000 computers. You configure alerts on each server so that various problem conditions will be reported if they occur. You create 18 operators on one of the servers. You configure these operators by using the e-mail and pager contact information for the employees in your department. You configure the alerts on the server to send e-mail messages and pager messages to the appropriate operators. You need to configure the same 18 operators on the other seven servers. You want to do this with a minimum amount of administrative time. What should you do?

a.           Detach the msdb database from the first server
Copy the database to the other seven servers
Attach the database on all eight servers

b.           Use SQL Server Enterprise Manager on a single client computer to create the operators on each server

c.            Use SQL Server Enterprise Manager to script all of the operators
Use SQL Query Analyzer to run the script on the other seven servers

d.           Use snapshot replication to replicate the msdb database from the first server to the other seven servers

You are the administrator of a SQL Server 2000 computer. The server contains a database named Sales. A company e-commerce application uses the Sales database to store sales transactions. The company Web site has grown in popularity, and database utilization has increased. You need to collect data about the utilization of server resources so that you can provide capacity planning. You want to automate the collection process so that information is gathered as quickly as possible. What should you do?

A.          Configure System Monitor to collect data and store it in a SQL Server table

B.          Create a SQL Server Agent job that executes the sp_statistics stored procedure daily and places the results in a text file

C.          Use SQL Profiler to trace server activity and store the results in SQL Server tables

D.          Configure SQL Server alerts to store information in the Windows application event log

You are the administrator of an SQL Server 2000 computer. You create several jobs on the server and schedule them to run during the evening. You configure an operator on the server and configure the jobs to send an e-mail to this operator if a job fails for any reason. While testing the jobs, you discover that e-mail messages are not being sent to the operator when a job fails. You use SQL Server Enterprise Manager to confirm that SQL Mail is started. You need to ensure that e-mail messages are sent to the operator whenever a job fails. What should you do?

A.          Configure SQLAgentMail to use a valid MAPI profile

B.          Configure SQL Mail to use a mailbox that delegates permissions to the operator

C.          Use the net send notification method to send the operator e-mail messages

D.          Create a job that executes the xp_startmail stored procedure
Configure the job to run whenever SQL Server 2000 starts

You are the administrator of a SQL Server 2000 computer named CorpSQL. You configure several SQL Server Agent jobs to perform automated maintenance tasks on CorpSQL. The jobs execute properly for several weeks, and then stop executing. You discover that the SQLServerAgent service has stopped. You attempt to start the SQLServerAgent service. The service does not start. In the Microsoft Windows application event log you discover the following error message:

'SQLServerAgent could not be started (reason: Unable to connect to server 'CORPSQL', SQLServerAgent cannot start)'.

You verify that the MSSQLServer service is running. You need to start the SQLServerAgent service and execute the maintenance jobs. What should you do?

A.          Restore the msdb database from a backup
Restart the MSSQLServer service, and start the SQLServerAgent service

B.          Restart the server
Start the MSSQLServer service, and start the SQLServerAgent service

C.          Configure the SQLServerAgent service to start by using the local system account

D.          Configure the SQLServerAgent service to start by using the same user account as the MSSQLServer service

You are the administrator of a SQL Server 2000 computer. Each night you need to send a copy of the server's msdb database in an e-mail message to the administrator of another SQL Server computer.

You create a job that contains the following steps:

  • Detach the msdb database
  • Send the database to the administrator in an e-mail message
  • Reattach the msdb database

You test the job, and it fails on the first step. You need to ensure that the msdb database is mailed to the administrator every night. What should you do?

A.          Ensure that the SQLServerAgent service is running under a user account that has database owner access to the msdb database

B.          Delete the first and last steps in the job

C.          Configure the job to back up the msdb database to a temporary file
Send the file to the administrator in an e-mail message

D.          Insert a new job step before the first step
Configure the new step to terminate all processes that use the msdb database

You are the administrator of a SQL Server 2000 computer. The server contains a database named MedicalRecords. Users query the database using the following stored procedure:

CREATE PROC GetManyVisits
(@PatientID INT,
@MadeTable TINYINT OUTPUT)
AS
DECLARE @Visits INT
SET @Visits = (SELECT COUNT(*) FROM Cases
WHERE PatientID = @PatientID)
IF @Visits > 5
BEGIN
SELECT P.PatientName, D.PhysicianName,
V.DateVisited, V.VisitDuration,
INTO ##ClientVisits
FROM Patients P
JOIN Physician D ON P.PhysicianID = D.PhysicianID
JOIN Visits V ON P.PatientID = V.PatientID
WHERE P.PatientID = @PatientID
SET @MadeTable = 1
END
ELSE SET @MadeTable = -1

Users report that queries to the database respond slowly. You use System Monitor to measure the system and discover that SQL Compilations/sec average 50 per minute. You need to modify the server to accelerate query response time. What should you do?

A.          Execute sp_recompile on the stored procedure

B.          Replace the stored procedure with a user-defined function

C.          Convert the stored procedure into two procedures--one procedure to decide if the ##ClientVisits table should be populated and one to populate the ##ClientVisits table

D.          Replace the stored procedure with a temporary stored procedure created by the user

  1. You are the administrator of three SQL Server 2000 computers at Five Lakes Publishing. One server, FLPSQL01, stores order data. You want to be able to use the other two servers, FLPSQL02 and FLPSQL03, to answer queries and run reports. The planned network configuration is shown in the exhibit (exhibit not available).


You want to use the Database Maintenance Plan Wizard to configure log shipping from FLPSQL01 to FLPSQL02 and FLPSQL03. You do not want users to add any new data on FLPSQL02 or FLPSQL03, but they must be able to run queries and reports.

Which two actions should you take?

A.      Set the database load state for each destination server to ‘No recovery’ mode.

B.      Set the database load state for each destination server to ‘Standby’ mode.

C.      Enable the ‘Allow database to assume primary role’ option for both destination servers.

D.      Enable the ‘Allow database to assume primary role option for one destination server,’ and then disable this option for the other destination server.

E.       Disable the ‘Allow database to assume primary role’ option for both destination servers.

You are the administrator of a SQL Server 2000 computer. You configure the SQLServerAgent service as shown below:

Service Startup account

q System Account

q This Account: CORPSQLAgent

Mail Session

Mail Profile

Save copies of the sent items in the Sent folder

Error Log

File Name: E:Program filesMicrosoftSQLService

You configure several SQL Server Agent jobs to perform maintenance tasks. These jobs delete old database records and copy files from the server to file servers on your network. You discover that none of the jobs will execute. You use SQL Server Enterprise Manager to check the jobs and discover that the SQLServerAgent service will not start. You need to allow the SQL Server Agent service to start and execute the jobs. What should you do?

A.      Configure the SQL Server Agent service to start by using the local system account.

B.      Grant the SQLAgent domain user account ‘Log on as a service’ rights.

C.      Add the SQLAgent domain user account as a Microsoft Windows login account on the server.

D.      Configure permissions on the master database to allow full access to the SQLAgent user account.

You are the administrator of a SQL server 2000 computer. The server contains a database named SalesTransactions. The database handles sales transactions for a network of retail outlets. The primary table is shown below:

Column Name Data Type Length Allow Nulls

SalesTransID int 4 blank

CustomerName varchar 50 checked

CustomerAddress varchar 50 checked

CustomerCity varchar 50 checked

CustomerState char 10 checked

CustomerZip char 10 checked

DateSold datetime 8 checked

PaymentMethod char 10 checked

Item1SKU char 10 checked

Item1Qty int 4 checked

Item2SKU char 10 checked

Item2Qty int 4 checked

Item2Price money 8 checked

Item3SKU char 10 checked

Item3Qty int 4 checked

Item3Price money 8 checked

SalesTax money 8 checked

Users report that when they add new sales, their inserts often time out. You examine the indexes on the table. The indexes are shown below:

Index_name index_description

IX_CustomerName nonclustered located on PRIMARY

IX_CustomerState nonclustered located on PRIMARY

IX_CustomerZip nonclustered located on PRIMARY

IX_PaymentMethod nonclustered located on PRIMARY

PK_SalesTransactions_1 nonclustered, unique, primary key located on PRIMARY

You want to decrease the time required to store purchases made at the retail outlets. What are two possible ways to achieve this goal?

A.      Normalize the data structure of the table.

B.      Drop the nonclustered indexes of the table.

C.      Drop the primary key.

D.      Add nonclustered indexes to the item 1SKU, item2SKU, and item3SKU fields in the table.

E.       Re-create the PK_Sales Transactions index as a clustered index.

You are the administrator of a new SQL Server 2000 computer. Your company has client computers that are running Microsoft Windows 2000 Professional. All client computers require access to the server.

According to company policy, passwords must be at least eight characters long and change every 30 days. You need to implement these policies and allow client computers to connect. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

A.      Configure Mixed Mode authentication.

B.      Configure Windows Authentication.

C.      Create a SQL Server login for each user who will access the server.

D.      Create a login for the Domain Users group.

You are the administrator of several SQL Server 2000 computers. You configure these servers to send e-mail messages to SQL Server operators whenever a SQL server agent job fails or encounters an error.

Your company's software developers create a set of stored procedures that send query results in e-mail messages about stored procedures that have not been successful.

By using a MAP1 profile named MS Exchange Settings, you verify that SQLAgentMail is correctly configured and working properly. You need to configure the servers so that the stored procedures function correctly. Which two actions should you take?

A.      Set the security permissions on your e-mail server to permit multiple connections to the SQL Server mailbox.

B.      Set the security permissions on your e-mail sever to permit connections from the MSSQLServer service account.

C.      Configure the MSSQLServer service to use the same service account as the SQLServerAgent service.

D.      Configure the SQLServerAgent service to use the same service account as the MSSQLServer service.

E.       Configure SQL Mail to use the MS Exchange Settings MAOI profile.

F.       Configure SQL Mail to use the Default Settings MAPI profile.

You are the administrator of a new Microsoft Windows 2000 server computer named HQSQL5. HQSQL5 is a member server in your company's Windows NT 4.0 domain, which is named HQMAIN. After installing SQL Server 2000 on HQSQL5, you configure the MSSQLServer service account to use the HQMAINsqladmin user account, which is a member of the local administrators group on HQSQL5.

You can use the HQMAINsqladmin user account to log on the HQSQL5. However, the MSSQLServer service fails to start. You need to start the service. What should you do?

A.      Revoke log on interactivity rights for the HQMAINsqladmin user account.

B.      Add the HQMAINsqladmin user account to the HQMAINDomain Admins group.

C.      Select the Password Never Expires check box for the HQMAINsqladmin user account.

D.      Grant log on as a service rights to the HQMAINsqladmin user account.

You are the database administrator for an accounting company. You are installing SQL Server 2000 on a new Microsoft Windows 2000 Server computer. The computer will run two client/server database applications that your company has purchased. Each application uses a separate database.

During the installation, you specify SQL_Latin1_General_CP1_CI_AI as the default collation for the SQL server computer. After completing the installation, you discover that one of the new applications, named Financials, is sorting information incorrectly.

You contact the application vendor and discover that the Financials application requires the SQL_Latin1_CP437_BIN collation. You need to configure the correct collation for the applications without diminishing the performance of the SQL Server computer.

What should you do?

A.      Rerun the SQL Server Setup and specify the SQL_Latin1_General_CP437_BIN collation.

B.      Run the rebuildm utility and specify the SQL_Latin1_General_CP437_BIN collation.

C.      Use the ALTER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation for the Financials database.

D.      Use the ALTER DATABASE statement to specify the SQL_Latin1_General_CP437_BIN collation for each table in the Financials database.

Answer Key:

B

AB

BCE

A

B

D

A

A

A

D

C

B

A

A

D

CE

AF

A

A

B

B

BE

A

D

A

B

BC

D

AE

A

D

C

BC

BC

AC

D

A

C

A

A

CD

B

B

CD

B

A

B

B

A

A

CD

C

D

AE

D

C

D

C

B

D

D

AD

CD

B

A

B

B

B

D

C

A

B

B

C

A

A

CF

A

D

A

B

C

AC

C

A

A

CE

A

AE

A

A

AE

C

C

AB

A

D

AE

BC

ACF

D

B

D

D

C

C

C

B

BCE

A

B

D

C

A

AE

B

BE

D

B

D

B

C

C

CE

B

D

A

BD

BCE

BE

B

A

BCD

A

A

A

AB

B

B

A

A

A

D

A

D

A

A

D

C

A

D

C

C

A

D

C

A

BC

B

AE

BD

CF

D

C







Politica de confidentialitate



DISTRIBUIE DOCUMENTUL

Comentarii


Vizualizari: 1383
Importanta: rank

Comenteaza documentul:

Te rugam sa te autentifici sau sa iti faci cont pentru a putea comenta

Creaza cont nou

Termeni si conditii de utilizare | Contact
© SCRIGROUP 2021 . All rights reserved

Distribuie URL

Adauga cod HTML in site