Da Notes - 70-029 - I

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 

[ 70-029 - A ] [ 70-029 - B ] [ 70-029 - C ] [ 70-029 - D ] [ 70-029 - E ] [ 70-029 - F ] [ 70-029 - H ] [ 70-029 - I ] [ 70-029 - J ] [ 70-029 - K ] [ 70-029 - L ] [ 70-029 - M ] [ 70-029 - N ] [ 70-029 - O ] [ 70-029 - P ] [ 70-029 - Q ] [ 70-029 - R ] [ 70-029 - S ] [ 70-029 - T ] [ 70-029 - U ] [ 70-029 - V ] [ 70-029 - W ]

IDENTITY NOT FOR REPLICATION

IDENTITY NOT FOR REPLICATION PRIMARY KEY:

When a replication agent replicates a row to a Subscriber, the identity value is not changed when the row is inserted in the Subscriber's table. 

INDEXES  

Index Analysis
Set SHOWPLAN_ALL ON - indicates if index used, displays summary info about a query.
SET STATISTICS IO ON - indicates i/o used to return the result set, display logical & physical I/O info
Graphical showplan in SQL Query Analyzer

Index fill factor
Applied only when created or rebuilt. It is not dynamically allocated.
0 percent = default - light mds - mixed OLAP Service and OLTP)
1-99 percent = moderate to heavy modifications - mixed or OLTP
100 percent = optimized for Decision Support Systems or read only databases (no mods)

Index Guidelines
· Choose the appropriate columns to Index 
· Columns to Index: primary and foreign keys, frequently used in search range, frequently sorted 
· Columns NOT to Index: seldom used in queries, few unique values, type = bit, text or image 
· Create clustered before non-clustered
· Only 1 clustered index on a table - pick the most frequently used column in your select statement order by clauses.
· Non-clustered indexes usually on columns in your select statement where clauses.
· Create composite indexes
· Create multiple indexes for a table that is read frequently
· Use the index tuning wizard
· Avoid optimizer hints
· DBCC SHOWCONTIG to measure fragmentation
· DROP_EXISTING to rebuild indexes
· Use FILLFACTOR and PAD_INDEX to optimize performance

Index Types
Clustered - leaf level is data page. Data and values are physically stored in ascending order.

Non Clustered - SQL Server default
· Automatically rebuilt when Existing clustered index is dropped (in 6.5 this would also drop the non CI) or Clustered index is created

DROP_EXISTING option is used to change which columns define the clustered index

Non Clustered Heap - row identifiers in the index pages that point to rows in the data pages

Non-Clustered on Clustered - clustering key in index page that point to the clustered index, which stores data location info. 

Information schema   

SQL Server metadata

Information_schema. tables - select * from INFORMATION_SCHEMA.TABLES - list of tables in database 

Information_schema. columns - select * from INFORMATION_SCHEMA.COLUMNS order by TABLE_NAME, COLUMN_NAME - list of columns in database

Information_schema. table_privileges - select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES order by TABLE_NAME - list of security info in database

Inner Joins    

see joins 

Integrity   

Declarative 
Criteria defined in object definitions
Enforced by SQL Server automatically
Implement by using constraints, defaults and rules

Domain = columns
Data values in each column must be valid.
Foreign Keys, Defaults, Check constraints. Not Null

Procedural
Criteria defined in scripts
Enforced by scripts
Implement by using triggers and stored procedures

Summary

Integrity Type Defines Options

Domain Column FK, default, check, NOT Null

Entity Row PK, unique, identity

Referential Across 2 tables FK, check 

User Column and Table level Constraints in CREATE TABLE, Stored procs, triggers