Da Notes - 70-029 - C

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 ]

CAST and CONVERT

Integers converted to character 

CAST is SQL-92 compliant, does not support styles.

CAST(expression AS data_type)

Convert has more options - CONVERT (data_type[(length)], expression [, style])

CLUSTERED INDEX 

see INDEX 

COMPUTE and COMPUTE by clauses 

 Select id, orderid, qty from orderhist order by id, orderid compute sum(qty)

Select id, orderid, qty from orderhist order by id, orderid compute sum(qty) by id compute sum(qty) 

NOT RECOMMENDED FOR USE IN GENERAL - Generates extra summary rows of data - useful for viewing (not ANSI standard). 

CONTAINS and FREETEXT 

CONTAINS and FREETEXT predicates are typically used in the WHERE clause of SELECT statements.

FREETEXT(column | *, 'freetextstring')
CONTAINS(column | * '' can use 'searchword' OR 'searchword'.

CONTAINSTABLE and FREETEXTTABLE functions can only be use in the FROM clause.

CORRELATED SUBQUERIES 

Inner query relies on info from outer query.

Use alias to distinguish tables

Consider using JOINS for faster performance.

Create database  

You must know all the parameters of this T-SQL command.

Create procedure 

 You must know this T-SQL command.

Create table 

 You must know all the parameters of this T-SQL command.

When creating many tables for your database, create the child table, child table primary key, parent table, parent table primary key and then the parent table foreign key to the child table.

Cross Joins 

see joins

Cursor  

in real life, it is best to avoid using this method - temp tables may be faster. A database object used by applications to manipulate data by rows instead of by sets. Using cursors, multiple operations can be performed row by row against a result set with or without returning to the original table.

Methods of calling cursors
TSQL
API - OLE DB, ODBC

Caching Cursors - Client API only, Server ASI & T-SQL

Types of cursors
Forward only
Static
Dynamic
Keyset driven

T-SQL cursor syntax
Declare cursor 
Open
Fetch 
Close
Deallocate