Apply Your Knowledge

Exercises

6.1 Writing a Batch That Uses a Cursor

This exercise demonstrates how to write a script in SQL Server, and also demonstrates how cursors can be used to access data.

Estimated time: 5 minutes

Open SQL Server Query Analyzer. Log in to the database you want to work with.

Type in the following text:

 DECLARE SI_Cursor CURSOR FORWARD_ONLY STATIC FOR SELECT DISTINCT id, name FROM sysindexes DECLARE @IndexName sysname,         @ID int OPEN SI_Cursor FETCH NEXT from SI_Cursor INTO @ID, @IndexName WHILE @@FETCH_STATUS = 0 BEGIN         print 'Table ' + object_name(@id) + ' has an index named ' + @IndexName + '.'         FETCH NEXT FROM SI_Cursor INTO @ID, @IndexName END CLOSE SI_Cursor DEALLOCATE SI_Cursor

Run the query by clicking the Play button.

6.2 Creating and Managing a Login

In this exercise, you'll see how to create and manage a login in SQL Server Enterprise Manager.

Estimated Time: 15 minutes

Open SQL Server Enterprise Manager, connect to your SQL Server, and open the Security container. Click on the Logins container.
Right-click in the list of logins and choose New Login. The SQL Server Login PropertiesNew Login dialog box should appear.
Enter the name Doug for the login.
Choose SQL Server Authentication. This sets up an account that doesn't require Windows authentication. Type in a password for Doug.
Choose a default database for Doug. This should be the database he will use most often. Choose the Pubs database.
On the Server Roles tab, verify that nothing is checked. This is the list of server-fixed roles that are available.
Click on Database Access. On this tab, you grant Doug access to the databases he's going to be able to use. Grant Doug access to the Pubs database by clicking in the empty check box to the left of the database name.
After you choose Pubs , you're given the option of adding Doug to several database roles. Add Doug to the db_datareader role. This enables him to read data, but he can't change any data, and can't execute any stored procedures.
Click OK to create the user . Notice that Doug now shows up in the Login list.
Open the Databases container, open the Pubs database, and click on the Users container. Notice that Doug is a user in the database. That's what you did in step 7.
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

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.
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.
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.
A1:	A script contains one or more batches. A batch contains zero or more statements. A transaction contains zero or more statements. A batch has no relation to a transaction.
2:	Why is locking important to provide concurrency in a database management system?
A2:	Locking is important because it enables multiple users to access the database at the same time without having the users tromp on each other's data. Without locking, users could not manipulate data without partially overwriting each other.
3:	Why should most `SELECT` statements be done outside a transaction?
A3:	Most `SELECT` statements should be done outside a transaction to keep the transactions shorter. To make transactions run quickly and reduce lock contention, you need to make sure the statements between `BEGIN TRANS` and `COMMIT TRANS` are the ones that need to be there. The exception is when you need to pull the most recent value possible from a table, and there is a chance it may change from outside the transaction to inside the transaction.
4:	When should you use a cursor?
A4:	Generally, you shouldn't use a cursor. But you will have to when the operation you want to perform on each row is dependent upon the operations you've performed on previous rows. For example, if you want to write an algorithm to find a statistical median, you have to use a cursor. Statistical mean, however, is a built-in function, and you can get a statistical `MODE` with a `count(*)` and `GROUP BY` .
5:	What can you do to avoid deadlocking in a database?
A5:	You can do two common things to reduce deadlocking in a database. First, always access objects in the same order. It doesn't matter what orderalphabetical order is fineit just has to be the same order all the time. Second, keep transactions as short as possible to reduce lock contention. Generally, anything that you can do to reduce lock contention also reduces deadlocking.
6:	Explain the difference between a static and a dynamic cursor.
A6:	Static cursors are created from a copy of the data set, and do not reflect changes made to the underlying data after the initial instantiation, which occurs at the `OPEN` statement. Dynamic cursors always reflect changes in the underlying data.
7:	Explain the difference between a `FORWARD_ONLY` and `SCROLLABLE` cursor.
A7:	A `FORWARD_ONLY` cursor can only be used with `FETCH NEXT` ; it cannot be used with `FETCH FIRST` , `FETCH LAST` , and so on. It also cannot go backward through the rowset.

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? GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2 GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2 AS INDEPENDENT GO SELECT * FROM Sales1 GO UPDATE Products SET price= price * 2 GO SELECT * FROM Sales1 UPDATE Products SET price= price * 2
A1:	C. The `GO` delimiter separates one batch from another. The syntax error in the batch prevented the entire batch from running, so to make the `UPDATE` statement run, put it into a different batch. For more on this, refer to the section titled "Scripts, Batches, and Transactions".
2:	You are a database administrator at a small web development shop in south central Wisconsin. Your boss is an idiot. He has been trying to write SQL Scripts again. He's given you this script to run, but you want to make sure it doesn't block all the users out of the server and set all the pricing information to zero, like last time. So, given the following script: DECLARE @Var int SET @var = 1 GO WHILE @Var < 11 BEGIN PRINT @Var * 2 SET @Var= @Var + 1 END What will be printed on the output screen? A line of zeros. A line of ones. The multiples of two. An error will occur.
A2:	D. An error will occur because the variable `@Var` does not exist when the second batch of the script runs. See the section, "Variable Scope" to find out why.
3:	You are a SQL Developer working on an Internet application in SQL Server 2000. You need to write a batch that prints the first ten multiples of 5. Which of the following gets the job done? DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar 5 SET @MyVar = @MyVar +1 END DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 10 BEGIN PRINT @MyVar 5 SET @MyVar = @MyVar +1 END DECLARE @MyVar int SET @MyVar =1 WHILE @MyVar < 11 BEGIN PRINT @MyVar 5 SET @MyVar = 5 END DECLARE @MyVar int SET @MyVar =1 GO WHILE @MyVar < 11 BEGIN PRINT @MyVar 5 SET @MyVar = @MyVar +1 END
A3:	A. Batch A works correctly and prints the multiples of 5 from 5 to 50. Batch B prints the multiples of 5 from 5 to 45, so it prints only nine of them. Batch C is an infinite loop because it doesn't increment its loop counter, and Batch D has a `GO` in the middle of it, so the variables aren't defined when they're being used. Check out the section "WHILE Loops" to see why it works that way.
4:	When using a cursor, which of the following statements cause the data to be returned to the batch? `DECLARE` `OPEN` `RETRIEVE` `FETCH`
A4:	D. The `FETCH` statement is used to retrieve data from a cursor. `DECLARE` and `OPEN` initialize the cursor, and `RETRIEVE` isn't a command. Cursors are covered in the section titled "Managing Result Sets by using Cursors and Transact -SQL."
5:	Paul has a 5-batch script, and is creating a T-SQL cursor in batch one. He wants to use the cursor in batches two, three, and four. What must Paul do to access the cursor from any batch? Use the `DECLARE CURSOR` statement in every batch and then re-populate it. Creating this type of cursor is not possible in SQL Server 2000. Create the cursor using the `GLOBAL` keyword. Create the cursor using the `PUBLIC` keyword.
A5:	C. Global cursors are available to later batches from the same connection. By the way, there is no such thing as a public cursor. This is a good example of an exam question because none of the answers look quite right and you'll spend way too much time trying to figure it out. Remember, the instructions are to pick the best answer. There's some information on cursor scope in the section "Managing Result Sets by Using Cursors and Transact-SQL" that may help if you had trouble with this question.
6:	Bob has just finished using his cursor and will not need it for the rest of the time he is connected. What can Bob use to fully release all system resources held up by the cursor? Run `DBCC_CURSOR_PERFORMANCE` . Run the `DEALLOCATE` command. Run the `CLOSE CURSOR` command. Run the `ALL SYSTEMS` statement with the `CURSOR` argument.
A6:	B. The `DEALLOCATE` command releases all the resources used by a cursor. This information is covered in the "Managing Result Sets by Using Cursors and Transact-SQL" section.
7:	Paul needs to create a cursor that is sensitive to data updates and deletes. He knows that the number of rows in the query he's using should be around 50. Paul subsequently uses the `@@CURSOR_ROWS` global variable to check for the number of rows in the cursor, which apparently should be around 50. To his surprise, he notices another value. What is this value? `1` - `1` `NULL`
A7:	C. The value of the `@@CURSOR_ROWS` global variable is `1` only when the cursor is declared as dynamic, and Paul's cursor was declared as dynamic. Information about using global variables is located in the "Global Variables" section, and how cursor status works is in the section titled "Managing Result Sets by Using Cursors and Transact-SQL."
8:	Stan runs a query and receives an error message with a severity level of 17. How serious is this error? The error was not that serious; the user should rerun the query. The query contained one or more typographical errors. The query was severe and most probably caused by a fault in hardware or software. The severity level has nothing to do with how serious the error was.
A8:	C. The severity levels from 17 to 19 designate hardware and/or software problems. Subsequent processing may be stopped . For more information, check out the section titled "Raising Errors."
9:	What is the value of the `@@TRANCOUNT` function when this code is finished executing? declare @Counter int begin transaction update mytable set value = 42 save transaction Point1 while @Counter < 19 begin begin transaction insert into MyTable values (2, 3, 42, 'hello') set @Counter = 1 commit transaction end rollback Point1 begin transaction Point2 insert into mytable (2, 3, 42, 'goodbye') rollback `-1` `1` `2`
A9:	B. The value of the `@@TRANCOUNT` variable increases by one when a `BEGIN TRAN` statement is encountered , and decreases to 0 when the `ROLLBACK` happens. The last transaction statement run was a `ROLLBACK` , which leaves `@@TRANCOUNT` set to 0. For more fun with transactions and using `@@TRANCOUNT` , see the section, "Managing Control of Flow with Transactions."
10:	Carl needs to write a SQL Script that will change everyone's pay status from part-time to full-time . Which of the following is the best way to accomplish the task? declare cursor FixStatus for SELECT ID, Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, @Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Part' where current of FixStatus fetch next from FixStatus into @ID, @Status end declare cursor FixStatus for SELECT ID, Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, @Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Part' where current of FixStatus fetch next from FixStatus into @ID, @Status end close FixStatus Deallocate FixStatus begin transaction declare cursor FixStatus for SELECT ID, Status from employees for update of status declare @ID int, @Status varchar(4) fetch next from FixStatus into @ID, @Status while @@FETCH_STATUS = 0 begin update Employees set Status = 'Part' where current of FixStatus fetch next from FixStatus into @ID, @Status end close FixStatus Deallocate FixStatus commit transaction update employee set status = 'full'
A10:	D. To some extent, all the answers work. The only differences between the first three are minor tweaks to the cursor operation. The best answer is to not use a cursor, though, which is usually the right answer anyway. "Managing Data Manipulation Using Transactions" is the section that covered this particular question.
11:	What is the value of `@@TRANCOUNT` after this code is run? begin transaction OuterTransaction insert into MyTable (2, 3, 19, 'hello') save transaction innertransaction insert into MyTable (2, 3, 37, 'goodbye') rollback transaction innertransaction `-1` `1` `2`
A11:	C. The rollback, in this case, rolls back to the savepoint, so it leaves the transaction count alone with just the initial `BEGIN TRANSACTION` having taken effect, so the answer is `1` . If you need some more information, see "Managing Data Manipulation Using Transactions."
12:	You are a database administrator at a small corporation. You've just been asked to run a stored procedure, but you're wary because the last time someone did this, it caused massive lock contention and forced a system shutdown. What is printed by the following stored procedure? begin transaction declare @foo int select @foo = id from mytable print @foo rollback transaction Nothing. There are no database changes to the database, so this causes an error. It prints the value of `@Foo` . It prints the value of `@Foo` twice because of the rollback.
A12:	C. It will print the value of `@Foo` . Rollbacks do not change how print statements work. A print happens immediately. Also, it's not an error to have an empty transaction or a transaction that just doesn't do any database updates. It's unwise, but not an error. The "Managing Data Manipulation Using Transactions" section covers what you need to know for this question.
13:	You're an administrator of a SQL Server 2000 server. You're hearing complaints from users about their applications hanging, occasionally receiving error messages. You've found a couple of batches that are being run at the time that the users are getting errors. Here are the batches: Batch 1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Declare @ProductID int Select @ProductID = ProdCode From Product where ProductName like 'Deluxe Widget' Update SalesTracker Set ProdCode = @ProductID where SalesID = 19 Commit Transaction Batch 2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION Declare @SalesID int Select @SalesID from SalesTracker where ProductCode = 42 Update Product Set SalesCode = @SalesID where ProductCode = 42 Commit Transaction What's a likely cause of the problem? Lock contention in the `Product` table. Lock contention in the `SalesTracker` table. A deadlock between the `Product` table and the `SalesTracker` table. All of the above.
A13:	D. Any and all of the options could cause the symptoms described. A deadlock is probably the best explanation, but with that high a transaction isolation level, you're going to have lock contention in any busy database. The section on "Concurrency and Locking" discusses deadlocks and isolation levels.