Chapter 6 PROGRAMMING SQL SERVER 2000 381 APPLY
Chapter 6 PROGRAMMING SQL SERVER 2000 381 APPLY YOUR KNOWLEDGE 11. Go back to the Logins container and delete Doug by right-clicking his login and choosing Delete. Notice that the error message says it will also remove all the database users for you. Click OK. 6.3 Monitoring Contention In this exercise, you ll see how to use SQL Server Enterprise Manager to monitor what locks are present on a SQL Server. Estimated Time: 5 minutes 1. Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Management container, then open the Current Activity container. Click on the Locks / Process ID container. 2. Click on the various connections, which are referenced here as Process ID s, or SPID s. You ll see what objects a specific connection has open. 3. Click on the Locks / Object container. Click on the various objects listed to find out what users are accessing the objects and what kind of locks they have. See whether you can find a user with a lock on both the master.dbo.spt_values table and a table called ##lockinfo in TempDB. This is your SQL Server Enterprise Manager session and the locks that it is using to find information to display for you. Review Questions 1. Explain the hierarchy of a script, a batch, and a transaction. 2. Why is locking important to provide concurrency in a database management system? 3. Why should most SELECT statements be done outside a transaction? 4. When should you use a cursor? 5. What can you do to avoid deadlocking in a database? 6. Explain the difference between a static and a dynamic cursor. 7. Explain the difference between a FORWARD_ONLY and SCROLLABLE cursor. Exam Questions 1. Eric is a database developer at the Acme Widget corporation. He s working on setting up a new product application. Eric runs the following two queries: SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO When Eric runs the batch, he receives an error message stating that the table Sales1 was not found and figures out he should have been updating the table Products. He also notices that the UPDATE statement didn t run. How could the batch be rewritten so the update statement runs? A. GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO B. SELECT * FROM Sales1 UPDATE Products SET price= price * 2 AS .INDEPENDENT GO
For high quality website hosting services please check java web hosting website.