|
| |
| |
|
|
|
Da Notes -
70-029 - L
Personal notes on the MCDBA tests. These notes are from the various resources I used to pass the MCDBA exams including the MCDBA sample exams from Transcender. |
Passing Score: 693, Questions: 49, Time: 150 Min.
|
Da Notes Index
|
|
|
LIKE clause
|
% - any string of zero or more characters
_ - any single characters
[] - any single character within range or set
[^] - any single character NOT within range or set
LIKE 'Br%' - every name beginning with Br
LIKE '%een' - every name ending with the letters een
LIKE '%een%' - every name containing the letters een
LIKE '_een' - every four letter name ending in the letters een
LIKE '[CK]%' - every name beginning with the letter C or K
LIKE '[C-K]%' - every name beginning with the letters C through K
LIKE '[C-K]een' - every four letter name beginning with the letters C - K and ending in letters een
LIKE 'M[^c]%' - every name beginning with the letter M, but c is not the second letter
|
Locks
|
Transaction Isolation Level
REPEATABLE READ - no dirty reads or non repeatable reads. Read locks held till transaction ends.
READ COMMITTED - DEFAULT SETTING - no dirty reads, use shared locks
READ UNCOMMITTED - dirty reads ok, no shared locks nor exclusive locks
SERIALIZED - no update or insert until transaction ends. Phantoms cannot occur.
Concurrency problems prevented by locks: lost updates, dirty reads, non repeatable read, phantoms
Item Description
RID Row identifier used to lock a single row in a table
Key Row lock with an index - protect key ranges
Page Data Page or index page - 8KB
Extent Group of pages - used during space allocation
Table Entire table including all data and indexes
Database Entire Database used during a database restore
Table level locking
· Use with caution
· Use optimizer hints in FROM clause of SELECT or UPDATE (e.g. UPDATE load(TABLOCKX HOLDLOCK) - look at transaction isolation levels above))
· Overrides session level locking
Deadlocks
· Use resources in same sequence
· Minimize steps in a transaction
· Avoid queries that perform mass updates
· Minimize indexing
Deadlock 1205 error - application should attempt the transaction again.
Display locking info
· Sp_lock in a SEM query window
· SQL Profiler - traces of lock activity
· WIN NT performance monitor - SQL Service lock manager and locks objects
· Sp_who, syslockinfo table, sysprocesses, sysobjects, systables, and syslogins
|
|
|
|
|
