70-461 Study Notes

RHONEDOGRHONEDOG Member Posts: 24 ■□□□□□□□□□
Hi All,

I'm studying for 70-461 (exam date Nov 7). I have a pretty decent background in SQL (8-10 years, but mostly only ad hoc queries), so my study method has been taking the measureup practice exam and delving further into anything I get wrong.

Repetition helps me, so I thought I'd type out my flash cards here for anyone else who might enjoy.

Create Database Objects:
UPDATE(): used in trigger to determine if query updated a column value
COLUMNS_UPDATED(): bit mask indicating which columns were updated
sp_rename: used to rename column in a table

syntax for creating foreign key on existing table
ALTER TABLE *table name*
ADD CONSTRAINT *ConstraintName*
FOREIGN KEY ( *ColumnName* )
REFERENCES *tablename* ( *columnname* )
ON DELETE *what do if parent is deleted - Cascade, set null...*

SEQUENCE object: increment values across multiple tables
IDENTITY object: increment values in one table

Query optimizer hints
NOEXPAND: force query to use indexes built on view
FORCE_SCAN: ignore indexes

Work with Data
XML index - must be primary key
spec xml - Document - only one top level element
Content - none or >1 top level elements -> is default if not specified

XML - cannot be converted to text/ntext (use varchar(max) or nvarchar(max)

Synonyms are not schema-bound

PERCENTILE_DISC(): specific percentile of values across a set of rows. Always returns value existing in set. Use 0.5 to find median
PERCENTILE_CONT(): same as above, but value may or may not exist in set
PERCENT_RANK(): Relative rank of a row
CUME_DIST(): position of row relative to other rows

Modify Data
EXECUTE AS -> can only be used with users, not roles

SCOPE_IDENTITY -> last identity inserted in current scope

@IDENTITY -> last identity returned for any table

EXTERNAL NAME Assembly.Class.Method (these go alphabetically!) -> use .net framework code from assembly

INSERT -> INTO is optional
column list also optional, except when IDENTITY_INSERT is on

OUTPUT $action -> only delete and insert (automatically toss out anything involving update, it doesn't exist)

Troubleshoot and Optimize
-static: displays result set as was when cursor opened
-keyset driven: membership & order of rows fixed, but other changes visible
-dynamic: change on each fetch
-Forward only: fetches rows serially from start to end

Error Handling:
THROW: display custom error (not in sys.messages)
CATCH: captures errors from TRY block
RAISERROR: display error found in sys.messages
ERROR_MESSAGE: function used to display message of error in catch block

When scalar udf is used in select statement, it is executed once for every row in filter condition, like row-based processing. Causes performance penalty

sp_create_plan_guide: use to optimize query when you cannot alter query
OBJECT: inside sp, trigger, function
SQL: inside batch file or stand alone statement
TEMPLATE: plan guide changes whether forced or simple parameterization used

Query optimizer join operations
MERGE - both join fields are in same sorted order (have keys)
HASH - fields in join not in same sorted order
NESTED LOOP - one table has more records than other - inefficient on tables with large numbers of records

Query hints:
OPTIMIZE FOR UNKNOWN - only statistics used, prevents query plan that is good for some values, bad for others
ROBUST PLAN - only plans that support maximum row size
KEEP FIXED PLAN - no recompile even if statistics of underlying tables change

SAVE TRAN - no change
ROLLBACK TRAN *savepoint_name* - no change
ROLLBACK TRAN - set counter at 0


  • Options
    Chev ChelliosChev Chellios Member Posts: 343 ■■■□□□□□□□
    Hey, how did you get on with the exam? *Sorry just saw your other post, congratulations! *Thanks for the above its useful. I'm a database noob in all honesty but company wants me to learn it next year so best get learning. Takes me away from my usual server and networking job but change is good, as is learning new stuff!
  • Options
    RHONEDOGRHONEDOG Member Posts: 24 ■□□□□□□□□□
    Hi Chev!

    I passed 461, then later 462, and I'm scheduled to write 463 next Thursday.

    Best of luck in your studying!

  • Options
    Chev ChelliosChev Chellios Member Posts: 343 ■■■□□□□□□□
    Hi Rhonedog,
    Awesome, congrats on the passes!
    I will let you know how it goes, feel out of my depth though as always avoided DB stuff and SQL as wasn't part of my remit.
    Should be good to learn some new things though, how did you find the journey?

    Cheers icon_thumright.gif
  • Options
    RHONEDOGRHONEDOG Member Posts: 24 ■□□□□□□□□□
    I have a fir bit of SQL in my background, so it's been relatively okay. I am scheduled for 463 next Thursday - I guess we'll see then!
  • Options
    Chev ChelliosChev Chellios Member Posts: 343 ■■■□□□□□□□
    Well good luck for it anyway, sounds like you know your stuff and will be fine though!
  • Options
    SoftwareGuySoftwareGuy Member Posts: 12 ■□□□□□□□□□
    How does UCertify do for studies? Debating paying the $700 for the study mats of all 3 exams
  • Options
    RHONEDOGRHONEDOG Member Posts: 24 ■□□□□□□□□□
    Honestly, this is the first I'm hearing of them. You know how you learn best - make your decision from that :)
Sign In or Register to comment.