MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 4 (Applying Additional Query Techniques)

• Implement subqueries
  
  • Subquery Fundamentals
  • Subquery Rules
  • Qualifying Column Names in Subqueries
  • Subquery Types
  • Subqueries with Aliases
  • Subqueries with IN
  • Subqueries with NOT IN
  • Subqueries in UPDATE, DELETE, and INSERT Statements
  • Subqueries with Comparison Operators
  • Comparison Operators Modified by ANY, SOME, or ALL
  • Subqueries with EXISTS
  • Subqueries with NOT EXISTS
  • Subqueries Used in Place of an Expression
  • Multiple levels of nesting
  • Correlated Subqueries
  • Correlated Subqueries with Aliases
  • Correlated Subqueries with Comparison Operators
  • Correlated Subqueries in a HAVING Clause
• Implement CTE (common table expression) queries
  
  • Using common table expressions
  • WITH common_table_expression (Transact-SQL)
  • Recursive Queries Using Common Table Expressions
• Apply ranking functions
  
  • Ranking Functions
  • RANK
  • DENSE_RANK
  • NTILE
  • ROW_NUMBER
  • OVER Clause (Transact-SQL)
• Control execution plans
  
  • Hints (Transact-SQL)
  • Query Hints
  • Join Hints (Transact-SQL)
  • max degree of parallelism Option
  • Optimizing Queries in Deployed Applications by Using Plan Guides
  • Forced Parameterization
  • simple parameterization
  • Specifying Query Plans with Plan Forcing
  • Using the INDEX and FORCESEEK Query Hints in Plan Guides
  • sp_create_plan_guide (Transact-SQL)
  • sp_control_plan_guide (Transact-SQL)
  • Table Hints (Transact-SQL)
  • Using the FORCESEEK Table Hint
  • Resolving Indexes on Views
  • Locking Hints
• Manage international considerations
  
  • Collation and International Terminology
  • Collation
  • Locale
  • Unicode
  • Code page
  • Data types
  • Sort order
  • Collation Precedence (Transact-SQL)
  • Windows Collation Sorting Styles
  • Using SQL Server Collations
  • SERVERPROPERTY (Transact-SQL)
  • COLLATIONPROPERTY
  • Selecting a SQL Server Collation
  • Setting Client Code Pages
  • SQL Server Collation Name (Transact-SQL)
  • COLLATE (Transact-SQL)
  • Windows Collation Name
  • Getting Information About Collations
  • Collations and SQL Server Client Applications
  • Collation in BACKUP and RESTORE Operations
  • Setting and Changing Collations
  • Selecting Collations
  • Collation Types
  • Setting and Changing the Server Collation
  • Setting and Changing the Database Collation
  • Setting and Changing the Column Collation
  • Setting Expression Collation
  • Identifier Collation
  • Copying Data Between Different Collations