|
| |
| |
|
|
|
Da Notes -
70-029 - T
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
|
|
|
Tables - System
|
Syslogins - master - one row for each login
Sysmessages - master - one row for each system error
Sysdatabases - master - one row for each database
Sysusers - all - one row for each NT user
Sysobjects - all - one row for each object
|
TABLOCK
|
TABLOCK: Use tables lock rather than using finer-grained row- or page-level locks. SQL Server holds this lock until the end of the statement. However, if you also specify HOLDLOCK, the lock is held until the end of the transaction.
TABLOCKX: Use an exclusive lock on a table. This lock prevents others from reading or updating the table and is held until the end of the statement or transaction.
|
TOP n values
|
Lists only the first n rows or n percent of rows. Returns ties if WITH TIES is used - Need an ORDER BY to use WITH TIES. NOT ANSII standard.
|
TRANSFER DATA
|
DTS -SQL Server data and OLE DB (MS Access) and ODBC sources - usually best choice - Allows custom data transformations.
BCP - DOS command line utility in text or native SQL server format
SELECT INTO - Only used when the destination table does not exist. External data using an OLE DB provider but requires a lot of work.
BULK INSERT - import large amounts of text files into SQL Server 7.0 only
|
TRANSACT SQL
|
Data Control language statements - DCL - who can see or modify the data
- Grant, deny, revoke
Data Definition language statements - DDL - create objects - Create, alter, drop
Data manipulation language statements - DML - query and modify data
-Select, insert, update, delete
Additional language elements
local variables - declare, set
operators - math, comparison, strings, logical
functions - rowset - openquery, aggregate - avg, sum, scalar - DB_NAME()
control flow - statement level - begin..end, if..else, while, row level - case
comments - inline using - or block using /* and */
TRANSACT SQL - EXECUTION
Dynamic - execute with string literal
Batches - one or more statements submitted together, go is end of batch. If syntax is wrong on one statement, the batch does NOT execute.
Scripts - .SQL extension - run in Query Analyzer or osql, execute many times
Transactions - processed like a batch, data integrity guaranteed, applied or rolled back together
|
TRANSACTIONS
|
Ensure multiple data changes are processed as a unit: atomicity. E.g. a banking transaction might credit one account and debit another. Both steps must complete together or not at all.
Implicit - each insert, update and delete executes as a transaction.
Explicit - begin transaction, commit transaction, rollback transaction statements. Some SQL statements will not work inside a transaction. E.g. ALTER DATABASE, BACKUP LOG, CREATE DATABASE, DROP DATABASE, RECONFIGURE, RESTORE DATABASE, RESTORE LOG, UPDATE STATISTICS.
Set IMPLICIT_TRANSACTION ON statement - Automatically stars a transaction when you execute certain
SQL statements. Nested transactions not allowed. Must be completed by a COMMIT or ROLLBACK. By default this setting is OFF.
Transaction statements recorded in the log before written to database. Automatic error recovery is provided by SQL Server. Log is used to roll forward all committed transactions and rollback all uncommitted transactions based on where the last checkpoint was taken.
Keep transaction short. Beware of some T-SQL statements, avoid user interaction in transactions
Nested Transactions are allowed but not recommended. @@trancount will tell nesting level
|
TRIGGERS
|
Associated with a specific table
Invoked automatically - Cannot be called directly
Is a transaction - can be rolled back from anywhere within the trigger.
Minimize use of ROLLBACK TRAN within a trigger.
Considerations
Constraints are checked before trigger executes
Triggers are reactive, constraints are proactive
Tables can have multiple triggers for any action (SQL 7.0)
No triggers on temp tables or views or system tables
Triggers should not return result sets
Triggers can handle multi row actions (if coded properly). Use @@rowcount to check rows
Cannot contain these statements:
CREATE, DROP, ALTER, TRUNCATE TABLE, GRANT, REVOKE DENY, UPDATE STATS, RECONFIGURE, LOAD DATABASE / LOG RESTORE DATBASE / LOG
Nested Triggers
Sp_configure to enable or disable nested trigger - default = ON
32 levels deep - if exceeded triggers ends and rollback occurs
Nested triggers do not fire twice in the same trigger when the original trigger table is modified
@@nestlevel function returns the current nesting level
NO fixed order for multiple triggers to be executed. Each trigger should be self contained.
Performance
Triggers work quickly because the inserted and deleted tables are in cache
Execution time determined by: number of tables, number of rows
Actions in triggers are implicitly in a transaction. Rollback will terminate everything from trigger
Uses
Cascade changes to other related tables
Enforce more complex data integrity than a CHECK constraint. Can enforce referential integrity by cascading updates or deletes.
Define custom error messages
Maintain de-normalized data - redundant data and de-normalized data usually need triggers
Compare data before and after - constraints, rules and defaults use standard system errors only
|
|
|
|
|
