Start up

1
Start up

• Log on to the network
• Start Enterprise Manager
• Connect to Reliant\MIS431S05 and your SalesOrders
  database
• (from EM ) Start Query Analyzer
• Start Books Online

2
MIS 431Dr. Steve RossSpring 2006

• CursorsTransactions and Locks

Material for this lecture is drawn from Guerrero
and Rojas, SQL Server 2000 Programming, and the
professors experience.
3
Types of Processing

• Set oriented
• Use a single command to process the entire result
  set
• More efficient
• Requires more programmer sophistication
• Row-by-row
• Use some logical method to move through the
  result set processing one record at a time
• Less efficient
• Easier to program
• Necessary for complex operations

4
Types of Cursors

• Forward-only
• Static
• Dynamic
• Keyset-driven

5
Forward-Only Cursors

• Retrieve results from beginning to end
• Also available FAST_FORWARD

6
Static Cursors

• Complete result set built in TempDB
• Changes (after opening) not reflected in result
  set
• Slower start
• Fast navigation once started

7
Dynamic Cursors

• Result set recreated with each FETCH
• Opens faster
• Slower navigation
• Changes to data will be reflected

8
Keyset-Driven Cursors

• TempDB contains a list of unique values
• Changes to rows not reflected
• Changes to columns are reflected

9
Using Cursors

• DECLARE statement
• Specifies type of cursor
• Query that defines the data
• OPEN statement
• Executes statement to populate (depending on
  type)
• FETCH statement
• Moves to a row and reads data
• CLOSE statement
• Closes cursor but leaves it available
• DEALLOCATE statement
• Drops the cursor

10
Declaring and Opening Cursors

• DECLARE cursor_name CURSORGLOBAL or LOCAL
  FORWARD_ONLY or SCROLLSTATIC, DYNAMIC,
  KEYSET, or FAST_FORWARDREAD_ONLY,
  SCROLL_LOCKS, or OPTIMISTICFOR select_statement
• OPEN cursor_name

11
Fetching Rows

• FETCH FROM cursor_name
• FETCH NEXT FROM cursor_name
• FETCH PRIOR FROM cursor_name
• FETCH FIRST FROM cursor_name
• FETCH LAST FROM cursor_name
• FETCH ABSOLUTE n FROM cursor_name
• FETCH RELATIVE n FROM cursor_name
• _at__at_FETCH_STATUS function to determine success
• FETCH FROM cursor_name INTO _at_variable

12
Cursor Variables

• CURSOR is a data type
• DECLARE _at_variable CURSOR
• SET _at_variable CURSOR optionsFOR
  select_statement
• OPEN _at_variable
• FETCH NEXT FROM _at_variable
• CLOSE _at_variable
• DEALLOCATE _at_variable

13
The ACID Test

• Atomicity
• The whole transaction or none of it
• Consistency
• a.k.a. Integrity (see chapter 7)
• Isolation
• Insulated from other operations
• Durability
• Changes are permanent

14
Transactions

• Ensure that the boundaries of the transaction are
  known
• Three types
• Auto commit any statement that modifies data
• Explicit specifically declared
• Implicit applies to a connection
• A Transact-SQL batch is not a transaction unless
  stated explicitly.

15
Explicit Transactions

• BEGIN TRAN
• Marks the start of statements that must be
  executed or aborted
• COMMIT TRAN
• Saves all changes to data
• ROLLBACK TRAN
• Reverses all changes to data

16
Concurrency Problems

• Lost updates
• Avoid by writing atomic UPDATE statements
• Uncommitted dependency (dirty read)
• Use READ COMMITTED isolation (default)
• Inconsistent analysis (nonrepeatable read)
• Use REPEATABLE READ isolation
• Phantom reads
• Use SERIALIZABLE isolation

17
Locks

• Types of locks
• Shared
• Exclusive
• Update
• Intent
• Schema
• Key-range

18
Next Lecture

• Transferring Data and Linking Servers