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
