Da Notes - 70-028 - D

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: 680, Questions: 52,  Time: 90 Minutes

Da Notes Index 

[ 70-028 - B ] [ 70-028 - C ] [ 70-028 - D ] [ 70-028 - F ] [ 70-028 - H ] [ 70-028 - I ] [ 70-028 - L ] [ 70-028 - M ] [ 70-028 - N ] [ 70-028 - P ] [ 70-028 - R ] [ 70-028 - S ] [ 70-028 - U ] [ 70-028 - W ]

Database Objects

Check constraint prevents invalid data in columns, cannot assign a value like a default

Data type column data values = binary, character, Unicode, date/time

Default definition if no column value supplied this will use the default

Index fast access for data retrieval

Rules Specific column values, cannot assign a value

Stored procedure collection of TSQL that execute together

Table collection of rows

Trigger special stored procedure - executed when a user modifies data in a table

Unique constraint prevents non unique values from being entered

View look at data from one or more tables 

Databases: System

Master:  Information about logins, stored procedures, pointer to primary data file for every database.

Model:  Provides a template or prototype for new databases. Contains the system tables, which belong in every database. Items that are to appear in all new databases should be placed here. The size is approx. 1.5 MB after installation.

Tempdb: Provides storage for temporary tables and other temporary storage needs such as intermediate results of GROUP BY, ORDER BY, DISTINCT and cursors. The size is approx. 2.5 MB after installation. 

Msdb:  Information about jobs, alerts, events and replication. Also history of all backup/restore operations. The size is approx. 8.5 MB after installation. If this is deleted, it will eliminate most of the functionality of the SQL Server agent.

Distribution: Stores history and transaction data used in replication. Size is set by wizards.

Pubs/Northwind:  Template databases for learning (pubs ~2MB, northwind ~8.5 MB).

Databases: System Tables - Master

sysdatabases: Databases on SQL Server

Sysdevices:  Available database and disk devices

Sysxlogins:  User accounts

Sysmessages: System error messages

Sysservers: Remote servers

Sysmessages: Error messages

Databases: System Tables - Msdb

sysoperators: Administrative personnel information, including email addresses and pager numbers

Sysalerts: User-defined alerts

Backupfiles:  List of backupfiles

Backups: List of backups

Sysjobs: Jobs

Data Loading

BCP - Copies data between SQL Server and a data file in a user-specified format. 

INSERT INTO - Causes the row data values to be inserted as one or more rows into the named table

BULK INSERT - Table must exist before executing BULK INSERT

DTS - Data Transformation Services - Designer makes it easy to import, export, and transform heterogeneous data. Exam questions weighted towards this answer.