70-461 Study Notes
RHONEDOG
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
Cursors:
-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
@TRANCOUNT
BEGIN TRAN - +1
SAVE TRAN - no change
COMMIT TRAN - -1
ROLLBACK TRAN *savepoint_name* - no change
ROLLBACK TRAN - set counter at 0
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
Cursors:
-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
@TRANCOUNT
BEGIN TRAN - +1
SAVE TRAN - no change
COMMIT TRAN - -1
ROLLBACK TRAN *savepoint_name* - no change
ROLLBACK TRAN - set counter at 0
Comments
-
Chev 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! -
RHONEDOG
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!
-Rhonedog -
Chev 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
-
RHONEDOG
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!
-
Chev Chellios
Member Posts: 343 ■■■□□□□□□□
Well good luck for it anyway, sounds like you know your stuff and will be fine though!
-
SoftwareGuy
Member Posts: 12 ■□□□□□□□□□
How does UCertify do for studies? Debating paying the $700 for the study mats of all 3 exams -
RHONEDOG
Member Posts: 24 ■□□□□□□□□□
Honestly, this is the first I'm hearing of them. You know how you learn best - make your decision from that
