| |
|
|
|
Da Notes -
70-029 - R
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
|
|
|
RAISERROR
|
Error message management. Returns errors to apps or to the SQL Server and Win NT application error log.
SP_ADDMESSAGE can be used to add custom error messages to the SYSMESSAGES table in the MASTER database.
|
READ
|
READCOMMITTED: shared locks are held while the data is modified. Avoids dirty reads, but data can be changed before the transaction has completed which can result in non-repeatable reads or phantom data
READUNCOMMITTED: isolation level 0. No shared locks. Dirty reads, nonrepeatable reads and phantom data can occur.
|
Recommended Practices
|
Save statements as scripts
Format T-SQL for legibility
Choose a naming convention
Comment scripts thoroughly
USE ANSI SQL 92 syntax when possible
Place database and log on separate physical disks
Disable write caching on disks
Specify maximum size when you use automatic file growth
Backup master database after you create, modify or delete a database
Use constraints because they are ANSI compliant.
Use trigger when additional functionality is required
Choose appropriate columns to index
Use the fillfactor and pad_index options to optimize performance
Use drop_existing option to rebuild indexes
Exec dbcc showcontig to measure fragmentation
Avoid optimizer hints
Join tables on primary and foreign keys
Reference all columns of composite primary key in the ON clause when composite key relates to a table
Limit the number of tables in a join
Set the select into / bulkcopy option on to create permanent tables with the select into statement
Use sub-queries to break down a complex query
Use table name aliases to correlate sub-queries
Use the insert … select statement to add rows from other sources to an existing table.
Index frequently aggregated columns
Avoid using aggregate functions with null values
Use the order by clause to guarantee a sort order
Use the rollup operator whenever possible
Use the cube and rollup operators when you expect to process rows programmatically
Avoid using the compute or compute by clause
Keep transactions short
Design transactions to minimize deadlocks
Use SQL server default for locking
Be careful when you use locking options
Use linked servers only for frequent remote data access
Use ad hoc queries for infrequent remote data access
To execute either remote stored procedures or distributed queries, set up linked servers
Avoid setting up duplicate login accounts on different servers
Use a standard naming convention
Dbo should own all views
Verify object dependencies before you drop objects
Never delete the entries in the syscomments table
Avoid creating views based on views
Include error-handling techniques
Design each stored proc to accomplish a single task
Perform error checking before you begin transactions
Use the same connection setting for all stored procs
Use WITH ENCRYPTION to hide the text of stored procs
Use triggers only when necessary
Keep definitions simple as possible
Include recursion termination check in recursive triggers
Minimize use of ROLLBACK in triggers
Use DISTINCT clause to eliminate duplicate rows in the result set
Improve readability of a result set by changing column or by using littorals.
Always include a WHERE clause with the DELETE and UPDATE statements
Use cursors for row level processing or when you cannot use set level processing
Use read only, forward only and one row at a time cursors
Test and compare row-level and set-level processing
|
Redundant
Data
|
Data that is irrelevant to a specific project.
Data that has multiple copies stored in the database.
Data that is derived from existing data.
|
Referential Integrity
|
see integrity
|
Replication
|
Review books on-line for various replication strategies.
You will need to study this extensively for the Administration test.
Merge replication - info needed in multiple locations and multiple locations updating. No timestamp columns.
Transactional - info needed immediately at other location - less than 30 minutes (usually)
Snapshot - info needed once in awhile
|
ROLLUP operator
|
Select id, orderid, sum(qty) from orderhist group by id, orderid with rollup order by id, orderid.
Use to summarize the group values.
|
ROWLOCK
|
Use row-level locks rather than use the coarser-grained page- and table-level locks.
|
|
|
|
