Chapter 6. Programming SQL Server 2000


O BJECTIVES

Manage result sets by using cursors and Transact - SQL . Considerations include locking models and appropriate usage.

  • This objective requires you to understand how cursors work, including how the different types of cursors affect concurrency and performance. This objective also requires you to understand when to use cursors and when to use other statements.

Enforce procedural business logic by using transactions.

  • Design and manage transactions.

  • Manage control of flow.

  • This objective requires you to know how transactions relate to business processes and why transactions are used at all.

Manage data manipulation by using transactions.

  • Implement error handling in transactions.

  • This objective requires you to understand how to manipulate data in a transaction model. This includes explicitly starting, committing, and rolling back transactions, how transactions relate to locking and concurrency issues, and how to raise errors within a transaction.

O UTLINE

Scripts, Batches, and Transactions

Scripts

Batches

Working with Variables

Naming Variables

Declaring Variables

Variable Types

Variable Scope

Setting and Using Variables

Global Variables

Comments

Statement Blocks with BEGIN...END

Conditional Statements and Branching with IF...THEN...ELSE

WHILE loops

Managing Result Sets by Using Cursors and Transact- SQL

Concurrency and Locking

Lock Contention

Deadlocks

Isolation Levels

Lock Granularity

Enforcing Procedural Business Logic Using Transactions

Designing and Managing Transactions

Managing Control of Flow with Transactions

Raising User-Defined Errors

System and User Error Messages

Raising Errors

Managing Data Manipulation Using Transactions

Implementing Security

The SQL Server Login/User Model

Statement Permissions

Object Permissions

User Roles

Application Roles

Apply Your Knowledge

Exercises

Review Questions

Exam Questions

Answers to Review Questions

Answers to Exam Questions

S TUDY S TRATEGIES

  • With SQL Server, the best way to learn is by doing. Write scripts and use the samples in this chapter to get started.

  • Literally hundreds of different functions can be used in a script. Use SQL Server Books Online to learn some of them, paying particular attention to important ones, such as SUBSTRING , CONVERT , and the date and time functions.

  • Pay close attention to the section on transactions. The exam will ask a lot of questions about how transactions roll back, commit, and save.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net