| CATEGORII DOCUMENTE |
| Asp | Autocad | C | Dot net | Excel | Fox pro | Html | Java |
| Linux | Mathcad | Photoshop | Php | Sql | Visual studio | Windows | Xml |
DOCUMENTE SIMILARE |
||
|
||
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
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.
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.
|
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)
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.
'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.
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.
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.
B. Remove all indexes from the tables.
C. Replace the table joins with correlated subqueries.
D. Use a server-side cursor to retrieve data.
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.
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.
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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
GO
EXEC sp_revokedbaccess Andrea
USE sales
GO
EXEC sp_revokedbaccess Andrea
GO
EXEC sp_revokedbaccess Andrea 'db_denydatareader', 'Andrea'
USE sales
GO
GO
EXEC sp_droplogin 'CopdomainAndrea'
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?
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.
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.
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.
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.
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.
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?
B. GRANT ALL ON sales TO 'CorportaePaul'
C. EXEC sp_addrolemember 'SalesManagers', 'Paul'
D. EXEC sp_grantdbaccess 'CorporatePaulo', 'Paul'
Sp_addrolemember 'db_datareader', 'Andrew'
B. GRANT ALL ON Financedata TO Andrew.
C. EXEC sp_defaultdb 'Andrew', 'Financedata'
D. EXEC sp_addlogin 'Andrew'
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
B. EXEC sp_grant login 'Local Techs'
C. EXEC sp_grant login [DatasvrDomain Scientists]
D. EXEC sp_grant login [ResearchDomain scientists]
B. EXEC sp_droplogin 'guest'
D. EXEC sp_addrolereader 'db_denydatareader', 'guest'
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.
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.
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.
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?
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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?
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.
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
A.
On the
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
Transfer the multiserver jobs from the
D.
On the JobMaster server, enlist the
On the JobMaster server, enlist the other 11 servers as target servers.
On the JobMaster server, defect the
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.
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.
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.
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.
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:
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
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
You need to configure a SQL Server computer for a
new branch office in
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
C.
On the server in
D.
On the server in
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
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
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 ModeYou 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:
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 GetManyVisitsUsers 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
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 | Termeni si conditii de utilizare |
Vizualizari: 1913
Importanta: 
Termeni si conditii de utilizare | Contact
© SCRIGROUP 2024 . All rights reserved
