MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 2 (Implementing Programming Objects)

• Create and alter stored procedures
  
  • Understanding Stored Procedures
    
    • Stored Procedure Basics
    • Types of Stored Procedures
  • Designing Stored Procedures (Database Engine)
  • Implementing Stored Procedures
    
    • Creating Stored Procedures (Database Engine)
    • Creating CLR Stored Procedures
    • Extended Stored Procedures
    • Modifying Stored Procedures
    • Recompiling Stored Procedures
  • CREATE PROCEDURE (Transact-SQL)
• Table-Valued Parameters(TVPs)
  
  • Table-Valued Parameters (Database Engine)
• EXECUTE AS
  
  • EXECUTE AS (Transact-SQL)
  • EXECUTE AS Clause (Transact-SQL)
  • Understanding Context Switching
  • Understanding Execution Context
  • Understanding Impersonation
  • Tutorial: Ownership Chains and Context Switching
• RECOMPILE
  
  • Recompiling Stored Procedures
  • Query Hints (Transact-SQL)
  • Optimizing Queries in Deployed Applications by Using Plan Guides
    
    • Understanding Plan Guides
    • Designing and Implementing Plan Guides
    • Specifying Query Parameterization Behavior by Using Plan Guides
    • Designing Plan Guides for Parameterized Queries
    • Using the INDEX and FORCESEEK Query Hints in Plan Guides
• Parameter direction (output)
  
  • Specifying Parameters
    
    • Specifying a Parameter Name
    • Specifying a Parameter Data Type
    • Specifying the Direction of a Parameter
    • Returning Data by Using OUTPUT Parameters
    • Returning Data by Using a Return Code
    • Using the cursor Data Type in an OUTPUT Parameterr
• WITH ENCRYPTION
  
  • Look at CREATE PROCEDURE (Transact-SQL)
• Manage permissions (GRANT, DENY, REVOKE)
  
  • Permissions (Database Engine)
  • GRANT Object Permissions (Transact-SQL)
  • DENY Object Permissions (Transact-SQL)
  • REVOKE Object Permissions (Transact-SQL)
• Create and alter user-defined functions (UDFs)
  
  • Understanding User-defined Functions
  • User-Defined Function Basics
  • Types of Functions
  • Designing User-Defined Functions
  • User-Defined Function Design Guidelines
  • Table-Valued User-Defined Functions
  • Inline User-Defined Functions
  • Deterministic and Nondeterministic Functions
  • Rewriting Stored Procedures as Functions
  • Implementing User-defined Functions
  • Creating User-Defined Functions (Database Engine)
  • Creating User-Defined Aggregates
  • Creating CLR Functions
• Create and alter DML triggers
  
  • Understanding DML Triggers
  • Types of DML Triggers
  • Triggers Compared to Constraints
  • Designing DML Triggers
  • Implementing DML Triggers
• INSERT
  
  • CREATE TRIGGER (Transact-SQL)
• DELETE
  
  • CREATE TRIGGER (Transact-SQL)
• INSTEAD OF
  
  • Designing INSTEAD OF Triggers
  • CREATE TRIGGER (Transact-SQL)
  • INSTEAD OF INSERT Triggers
  • INSTEAD OF UPDATE Triggers
  • INSTEAD OF DELETE Triggers
  • Expressions and Computed Columns in INSTEAD OF Triggers
  • Using text, ntext, and image Data in INSTEAD OF Triggers
• Create and alter DDL triggers
  
  • Understanding DDL Triggers
  • Designing DDL Triggers
  • Implementing DDL Triggers
  • Getting Information About DDL Triggers
  • Understanding DDL Triggers vs... DML Triggers
  • CREATE TRIGGER (Transact-SQL)
  • DROP TRIGGER (Transact-SQL)
  • ALTER TRIGGER (Transact-SQL)
  • sys.sql_expression_dependencies (Transact-SQL)
  • sys.dm_sql_referenced_entities (Transact-SQL)
  • sys.dm_sql_referencing_entities (Transact-SQL)
• Enabling/disabling
  
  • DISABLE TRIGGER (Transact-SQL)
  • ENABLE TRIGGER (Transact-SQL)
• Return event data
  
  • EVENTDATA (Transact-SQL)
  • Using the EVENTDATA Function
• Create and deploy CLR-based objects
  
  • Assemblies
  • Creating CLR Functions
  • Creating CLR Stored Procedures
  • Programming CLR Triggers
  • Creating User-Defined Aggregates
  • Working with CLR User-defined Type
• Permission sets (SAFE, UNSAFE, EXTERNAL_ACCESS)
  
  • CREATE ASSEMBLY (Transact-SQL)
  • Designing Assemblies
  • Host Protection Attributes and CLR Integration Programming
  • CLR Integration Code Access Security
• SET TRUSTWORTHY
  
  • TRUSTWORTHY Database property
• Implement error handling
  
  • Understanding Database Engine Errors
  • Database Engine Error Severities
• TRY/CATCH
  
  • TRY...CATCH (Transact-SQL)
  • Using TRY...CATCH in Transact-SQL
• RAISERROR
  
  • Using RAISERROR
  • RAISERROR (Transact-SQL)
• Retrieving error information
  
  • Retrieving Error Information in Transact-SQL
• Custom Error Messages
  
  • Using PRINT
  • PRINT (Transact-SQL)
• @@ERROR
  
  • Using @@ERROR
  • @@ERROR (Transact-SQL)
  \
• Manage transactions
  
  • Transactions
  • Controlling Transactions (Database Engine)
  • Explicit Transactions
  • Implicit Transactions
  • Autocommit Transactions
  • Distributed Transactions (Database Engine)
• BEGIN TRANSACTION
  
  • Explicit Transactions
  • BEGIN TRANSACTION (Transact-SQL)
• COMMIT
  
  • Explicit Transactions
  • COMMIT TRANSACTION (Transact-SQL)
  • COMMIT WORK (Transact-SQL)
• ROLLBACK
  
  • Explicit Transactions
  • ROLLBACK TRANSACTION (Transact-SQL)
  • ROLLBACK WORK (Transact-SQL)
• SET TRANSACTION ISOLATION LEVEL
  
  • Customizing Transaction Isolation Level
  • Isolation Levels in the Database Engine
  • Adjusting Transaction Isolation Levels
  • SET TRANSACTION ISOLATION LEVEL