MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 7 (Gathering Performance Information)

• Capture execution plans
  
  • Displaying Graphical Execution Plans (SQL Server Management Studio)
  • Displaying Execution Plans by Using SQL Server Profiler Event Classes
  • XML Showplans
  • SET SHOWPLAN_XML (Transact-SQL)
  • SET SHOWPLAN_ALL (Transact-SQL)
  • SET SHOWPLAN_TEXT (Transact-SQL)
  • SET STATISTICS XML (Transact-SQL)
  • SET STATISTICS PROFILE (Transact-SQL)
  • SET STATISTICS TIME (Transact-SQL)
  • SET STATISTICS IO (Transact-SQL)
  • Transact-SQL Statements That Produce Showplans
  • SHOWPLAN Permission and Transact-SQL Batches
• Gather trace information by using the SQL Server Profiler
  
  • Introducing SQL Server Profiler
  • SQL Server Profiler Terminology
  • Using SQL Server Profiler
  • SQL Server Profiler Templates
  • Permissions Required to Run SQL Server Profiler
  • Saving Traces and Trace Templates
  • Modifying Trace Templates
  • Starting, Pausing, and Stopping Traces
  • Correlating a Trace with Windows Performance Log Data
  • Viewing and Analyzing Traces with SQL Server Profiler
  • Analyzing Deadlocks with SQL Server Profiler
  • Analyzing Queries with SHOWPLAN Results in SQL Server Profiler
  • Filtering Traces with SQL Server Profiler
  • Replaying Traces
  • Scenarios for Using SQL Server Profiler
  • SQL Server Profiler Reference
  • Starting SQL Server Profiler
• Collect output from the Database Engine Tuning Advisor
  
  • Database Engine Tuning Advisor Features
  • Differences Between Database Engine Tuning Advisor and Index Tuning Wizard
  • Using Database Engine Tuning Advisor
  • Initializing Database Engine Tuning Advisor
  • Starting Database Engine Tuning Advisor
  • Determining Whether Events Can Be Tuned
  • Tuning a Database
  • Available Tuning Options
  • Choosing a Database Engine Tuning Advisor Report
  • Reducing the Production Server Tuning Load
  • Tuning Multiple Databases
  • Using an XML Input File for Tuning
  • Tuning Mode Precedence: When the dta Utility Overrides XML Tuning Input
  • Using Session Monitor to Evaluate Tuning Recommendations
  • How to: Create Workloads
  • How to: Tune a Database
  • How to: View Tuning Output
  • How to: Perform Exploratory Analysis
  • How to: Implement Tuning Recommendations
• Collect information from system metadata
  
  • Dynamic Management Views and Functions
  • Change Data Capture Related Dynamic Management Views
  • Common Language Runtime Related Dynamic Management Views
  • Database Mirroring Related Dynamic Management Views
  • Database Related Dynamic Management Views
  • Execution Related Dynamic Management Views and Functions
  • Full-Text Search Related Dynamic Management Views
  • Index Related Dynamic Management Views and Functions
  • I/O Related Dynamic Management Views and Functions
  • Object Related Dynamic Management Views and Functions
  • Query Notifications Related Dynamic Management Views
  • Replication Related Dynamic Management Views
  • Resource Governor Dynamic Management Views
  • Service Broker Related Dynamic Management Views
  • SQL Server Extended Events Dynamic Management Views
  • SQL Server Operating System Related Dynamic Management Views
  • Transaction Related Dynamic Management Views and Functions
  • Security Related Dynamic Management Views

MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 6 (Working with XML Data)

• Retrieve relational data as XML
  
  • FOR Clause (Transact-SQL)
  • Using FOR XML and OPENXML to Publish and Process XML Data
  • Basic Syntax of the FOR XML Clause
  • Constructing XML Using FOR XML
  • Using RAW Mode
  • Using AUTO Mode
  • Using EXPLICIT Mode
  • Using PATH Mode
  • TYPE Directive in FOR XML Queries
  • Nested FOR XML Queries
  • Adding Namespaces Using WITH XMLNAMESPACES
  • WITH XMLNAMESPACES (Transact-SQL)
  • xml Data Type Methods
• Transform XML data into relational data
  
  • OPENXML
  • Using OPENXML
  • Querying XML Using OPENXML
  • Specifying Metaproperties in OPENXML
  • sp_xml_preparedocument (Transact-SQL)
  • sp_xml_removedocument (Transact-SQL)
• Query XML data
  
  • XQuery Language Reference
  • xml Data Type Methods
  • query() method
  • XQuery Basics
  • XQuery Expressions
  • Modules and Prologs (XQuery)
  • XQuery Functions against the xml Data Type
  • Additional Sample XQueries Against the xml Data Type
  • Examples of Bulk Importing and Exporting XML Documents
  • Sequence and QNames (XQuery)
  • Expression Context and Query Evaluation (XQuery)
  • Atomization (XQuery)
  • Effective Boolean Value (XQuery)
  • Type System (XQuery)
  • Error Handling (XQuery)
  • Comments in XQuery
  • XQuery and Static Typing
  • Primary Expressions (XQuery)
  • Path Expressions (XQuery)
  • Sequence Expressions (XQuery)
  • Arithmetic Expressions (XQuery)
  • Comparison Expressions (XQuery)
  • Logical Expressions (XQuery)
  • XML Construction (XQuery)
  • FLWOR Statement and Iteration (XQuery)
  • Ordered and Unordered Expressions (XQuery)
  • Conditional Expressions (XQuery)
  • Quantified Expressions (XQuery)
  • SequenceType Expressions (XQuery)
  • Validate Expressions (XQuery)
  • Introduction to Using XPath Queries (SQLXML 4.0)
  • Specifying a Location Path (SQLXML 4.0)
  • Sample XPath Queries (SQLXML 4.0)
  • XPath Data Types (SQLXML 4.0)
  • Specifying Axes in XPath Queries (SQLXML 4.0)
  • Specifying Arithmetic Operators in XPath Queries (SQLXML 4.0)
  • Specifying Relational Operators in XPath Queries (SQLXML 4.0)
  • Specifying Explicit Conversion Functions in XPath Queries (SQLXML 4.0)
  • Specifying Boolean Functions in XPath Queries (SQLXML 4.0)
  • Specifying XPath Variables in XPath Queries (SQLXML 4.0)
• Manage XML data
  
  • xml (Transact-SQL)
  • query() Method (xml Data Type)
  • value() Method (xml Data Type)
  • exist() Method (xml Data Type)
  • modify() Method (xml Data Type)
  • nodes() Method (xml Data Type)
  • Binding Relational Data Inside XML Data
  • Guidelines for Using xml Data Type Methods
  • Implementing XML in SQL Server
  • Typed XML Compared to Untyped XML
  • Indexes on XML Data Type Columns
  • Full-Text Index on an XML Column
  • Primary XML Index
  • Secondary XML Indexes
  • Creating XML Indexes
  • Modifying XML Indexes
  • Dropping XML Indexes
  • CREATE XML SCHEMA COLLECTION (Transact-SQL)
  • ALTER XML SCHEMA COLLECTION (Transact-SQL)
  • DDL for Managing XML Schema Collections in the Database
  • Permissions on an XML Schema Collection.
  • Viewing a Stored XML Schema Collection

MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 5 (Working with Additional SQL Server Components)

• Integrate Database Mail.
  
  • Database mail
  • Database Mail Configuration Wizard
  • sysmail_configure_sp
  • How to: Convert Stored Procedures from SQL Mail to Database Mail (Transact-SQL)
  • Database Mail Architecture
  • Planning for Database Mail
  • Database Mail XPs Option
  • Database Mail Configuration Stored Procedures
  • Database Mail Configuration Objects
  • Mail Host Database
  • Database Mail Views (Transact-SQL)
  • Database Mail and SQL Mail Stored Procedures (Transact-SQL)
  • Database Mail Profiles
  • Database Mail Accounts
  • How to: Create Database Mail Accounts (Transact-SQL)
  • How to: Create Database Mail Private Profiles (Transact-SQL)
  • How to: Create Database Mail Public Profiles (Transact-SQL)
• Implement full-text search
  
  • Full-text search
  • Full-Text Search Overview
  • Full-Text Search Architecture
  • Administering Full-Text Search
  • Querying SQL Server Using Full-Text Search
  • Troubleshooting Full-Text Search
  • Full-Text Predicates and Functions Overview
  • Supported Forms of Query Terms (Full-Text Search)
  • Limiting Ranked Result Sets (Full-Text Search)
  • Querying varbinary(max) and xml Columns (Full-Text Search)
  • Querying Multiple Columns (Full-Text Search)
  • Querying Linked Servers (Full-Text Search)
  • Performance Tuning and Optimization of Full-Text Queries
  • How Search Query Results Are Ranked (Full-Text Search)
  • CONTAINS (Transact-SQL)
  • CONTAINSTABLE (Transact-SQL)
  • FREETEXT (Transact-SQL)
  • FREETEXTTABLE (Transact-SQL)
  • Stopwords and Stoplists
  • CREATE FULLTEXT STOPLIST (Transact-SQL)
  • How to: Create a Full-Text Stoplist (SQL Server Management Studio)
  • ALTER FULLTEXT STOPLIST (Transact-SQL)
  • How to: Change the Properties of a Full-Text Stoplist (SQL Server Management Studio)
  • DROP FULLTEXT STOPLIST (Transact-SQL)
  • CREATE FULLTEXT INDEX (Transact-SQL)
  • How to: Start the Full-Text Indexing Wizard (SQL Server Management Studio)
  • ALTER FULLTEXT INDEX (Transact-SQL)
  • How to: View or Change the Properties of a Full-Text Index (SQL Server Management Studio)
  • transform noise words Option
  • sys.fulltext_stoplists (Transact-SQL)
  • sys.fulltext_stopwords (Transact-SQL)
  • sys.dm_fts_parser (Transact-SQL)
  • Configuring Full-Text Linguistic Components
• Implement scripts by using Windows PowerShell and SQL Server Management Objects (SMOs)
  
  • SQL Server PowerShell overview
  • SQL Server PowerShell Help
  • Running SQL Server PowerShell
  • Using the SQL Server PowerShell Provider
  • Using the SQL Server cmdlets
  • Using SQL Server Identifiers in PowerShell
  • Query Expressions and Unique Resource Names
  • How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)
  • Scripting (Database Engine)
  • Using the Invoke-Sqlcmd cmdlet
  • Using the Invoke-PolicyEvaluation cmdlet
  • Microsoft.SqlServer.Management.Smo
  • Microsoft.SqlServer.Management.Smo.Agent
  • Microsoft.SqlServer.Management.Smo.Broker
  • Microsoft.SqlServer.Management.Smo.Mail
  • Microsoft.SqlServer.Management.Smo.Wmi
• Implement Service Broker solutions
  
  • Service Broker Tutorials
  • Benefits of Programming with Service Broker
  • Service Broker Applications
  • Planning for Service Broker Development
  • Developer Responsibilities for Service Broker
  • Service Broker Programming Concepts
  • Creating Service Broker Objects
  • Creating Service Broker Applications
  • Transactional Messaging
  • Controlling Transactions (Database Engine)
  • BEGIN DIALOG CONVERSATION (Transact-SQL)
  • BEGIN CONVERSATION TIMER (Transact-SQL)
  • GET CONVERSATION GROUP (Transact-SQL)
  • END CONVERSATION (Transact-SQL)
  • MOVE CONVERSATION (Transact-SQL)
  • RECEIVE (Transact-SQL)
  • SEND (Transact-SQL)
  • Broker System Messages
  • Conversation Group Locks
  • State Management
  • Service Broker Application Outline
  • CREATE QUEUE (Transact-SQL)
  • Service Broker Activation
  • Error Handling for Service Broker
  • Contracts
  • CREATE CONTRACT (Transact-SQL)
  • DROP CONTRACT (Transact-SQL)
  • Creating Service Broker Contracts
  • Dialog Conversations
  • BEGIN DIALOG CONVERSATION (Transact-SQL)
  • BEGIN CONVERSATION TIMER (Transact-SQL)
  • END CONVERSATION (Transact-SQL)
  • SEND (Transact-SQL)
  • RECEIVE (Transact-SQL)
  • sys.transmission_queue (Transact-SQL)
  • Conversation Groups
  • sys.conversation_endpoints (Transact-SQL)
  • sys.conversation_groups (Transact-SQL)
  • Message Types
  • Messages
  • Queues
  • CREATE QUEUE (Transact-SQL)
  • ALTER QUEUE (Transact-SQL)
  • DROP QUEUE (Transact-SQL)
  • Services
  • CREATE SERVICE (Transact-SQL)
  • ALTER SERVICE (Transact-SQL)
  • DROP SERVICE (Transact-SQL)
  • Building Applications with Service Broker
  • Remote Service Bindings
  • CREATE CERTIFICATE (Transact-SQL)
  • CREATE LOGIN (Transact-SQL)
  • CREATE USER (Transact-SQL)
  • Security and Protection (Service Broker)
  • CREATE REMOTE SERVICE BINDING (Transact-SQL)
  • ALTER REMOTE SERVICE BINDING (Transact-SQL)
  • DROP REMOTE SERVICE BINDING (Transact-SQL)
  • Routes
  • Service Broker Routing
  • CREATE ROUTE (Transact-SQL)
• Track data changes
  
  • Configuring Change Data Capture
  • Change Tracking
  • Change Data Capture
  • Comparing Change Data Capture and Change Tracking
  • CHANGE_TRACKING_IS_COLUMN_IN_MASK (Transact-SQL)
  • CHANGE_TRACKING_CURRENT_VERSION (Transact-SQL)
  • CHANGE_TRACKING_MIN_VALID_VERSION (Transact-SQL)
  • WITH CHANGE_TRACKING_CONTEXT
  • CHANGETABLE
  • Change Tracking Functions (Transact-SQL)
  • Tracking Data Changes
  • Change Data Capture Functions (Transact-SQL)
  • Change Data Capture Stored Procedures (Transact-SQL)
  • Change Data Capture Tables (Transact-SQL)
  • Change Data Capture Related Dynamic Management Views (Transact-SQL)

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

MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 3 (Working with Query Fundamentals)

• Query data by using SELECT statements
  
  • SELECT (Transact-SQL)
  • WITH common_table_expression
  • SELECT Clause
  • INTO Clause
  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • UNION
  • EXCEPT and INTERSECT
  • ORDER BY
  • COMPUTE
  • FOR Clause
  • OPTION Clause
  • SELECT Examples (Transact-SQL)
• Modify data by using INSERT, UPDATE, and DELETE statements
  
  • INSERT (Transact-SQL)
  • INSERT Examples (Transact-SQL)
  • BULK INSERT (Transact-SQL)
  • DELETE (Transact-SQL)
  • Deleting Data in a Table
  • UPDATE (Transact-SQL)
  • Updating Data in a Table
  • TRUNCATE TABLE (Transact-SQL)
  • Row Constructors / Table Value Constructor
• Return data by using the OUTPUT clause
  
  • OUTPUT Clause (Transact-SQL)
  • DELETE
  • INSERT
  • UPDATE
  • INTO Clause
• Modify data by using MERGE statements
  
  • MERGE (Transact-SQL)
  • OUTPUT Clause (Transact-SQL)
  • DELETE
  • INSERT
  • UPDATE
  • Using Common Table Expressions
  • WITH common_table_expression (Transact-SQL)
  • Recursive Queries Using Common Table Expressions
  • TOP (Transact-SQL)
  • Optimizing MERGE Statement Performance
  • Inserting, Updating, and Deleting Data by Using MERGE
• Implement aggregate queries
  
  • AVG
  • CHECKSUM_AGG
  • COUNT
  • COUNT_BIG
  • MAX
  • MIN
  • SUM
  • STDEV
  • STDEVP
  • VAR
  • VARP
  • Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
  • GROUPING SETS Equivalents
  • GROUP BY (Transact-SQL)
  • HAVING
  • Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS
  • GROUPING (Transact-SQL)
  • GROUPING_ID (Transact-SQL)
  • Troubleshooting GROUP BY Errors
  • OVER Clause (Transact-SQL)
• Combine datasets
  
  • Using APPLY (CROSS APPLY and OUTER APPLY)
  • FROM (Transact-SQL)
  • Join Fundamentals
  • Using Joins
  • Using Inner Joins
  • Using Outer Joins
  • Using Cross Joins
  • Using Self-Joins
  • Joining Three or More Tables
  • Null Values and Joins
  • UNION (Transact-SQL)
  • EXCEPT and INTERSECT (Transact-SQL)
    
    • left anti semi join
    • left semi join
  • Performing Semi-Joins with EXCEPT and INTERSECT
  • Using UNION, EXCEPT and INTERSECT with Other Transact-SQL Statements
• Apply built-in scalar functions
  
  • Configuration Functions
  • Cursor Functions
  • Date and Time Data Types and Functions
  • Mathematical Functions
  • Metadata Functions
  • Security Functions
  • String Functions
  • System Functions
  • System Statistical Functions
  • Text and Image Functions
  • CAST and CONVERT (Transact-SQL)
  • REPLACE (Transact-SQL)
  • LEN (Transact-SQL)
  • DATALENGTH (Transact-SQL)
  • CHARINDEX (Transact-SQL)
  • PATINDEX (Transact-SQL)

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

MCTS - Microsoft SQL Server 2008, Database Development - 70-433 - Objective List Part 1 (Implementing Tables and Views)

• Create and alter tables
  
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE
  • Understanding Tables
    
    • Table Basics
    • Data Integrity Basics
    • Special Table Types
  • Designing Tables
  • Creating and Modifying Tables
  • Viewing a Table
• Computed Columns
  
  • Computed Columns
  • COLUMNPROPERTY
  • How to: Specify Computed Columns
  • How to use computed columns to improve query performance
• Persisted Columns
  
  • Look at Computed Columns (PERSISTED KEYWORD)
• Schemas
  
  • User-Schema Separation
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • GRANT
  • GRANT SCHEMA Premission
  • DENY
  • DENY SCHEMA Premission
  • REVOKE
  • REVOKE SCHEMA Premission
  • How to: Create a Database Schema
• Scripts to deploy changes to multiple environments
  
  • How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File
  • How to: Deploy Changes to New or Existing Databases
  • How to: Configure Properties for Deployment Details
  • An Overview of Database Build and Deployment
  • An Overview of Database Project Settings
  • Troubleshooting Database Project, Build, and Deployment Issues
• Create and alter views
  
  • CREATE VIEW
  • ALTER VIEW
  • DROP VIEW
  • ENCRYPTION - look at CREATE VIEW
  • sys.syscomments
  • SCHEMABINDING - look at CREATE VIEW
  • WITH CHECK OPTION - look at CREATE VIEW
• Create and alter indexes
  
  • CREATE INDEX
  • CREATE SPATIAL INDEX
  • ALTER INDEX
  • DROP INDEX
  • CREATE XML INDEX
  • XML Indexes in SQL Server 2005
  • Undestanding Indexes
  • Designing Indexes
  • Tables and Index Data Structures Architecture
  • General Index Design Guidelines
  • Determining Index Disk Space Requirements
  • Filtered Index Design Guidelines
  • Index with Included Columns
  • Unique Index Design Guidlines
  • Clustered Index Design Guidlines
  • Clustered Index Structures
  • Nonclustered Index Design Guidlines
  • Nonclustered Index Structures
  • Heap Structures
  • Optimizing Indexes
    
    • Reorganizing and Rebuilding Indexes
    • Fill Factor
  • CREATE STATISTICS
  • Using Statistics to Improve Query Performance
  • Designing Indexed Views
  • Creating Indexed Views
• Create and modify constraints
  
  • Data Integrity Basics
    
    • Constraints
    • Rules
    • Defaults
  • Enforcing Data Integrity
    
    • PRIMARY KEY Constraints
    • FOREIGN KEY Constraints
    • Cascading Referential Integrity Constraints
    • UNIQUE Constraints
    • CHECK Constraints
    • DEFAULT Constraints
    • Allowing Null Values
  • Creating and Modifying PRIMARY KEY Constraints
  • Creating and Modifying FOREIGN KEY Constraints
  • Creating and Modifying UNIQUE Constraints
  • Creating and Modifying CHECK Constraints
  • Creating and Modifying DEFAULT Definitions
  • Creating and Modifying Identifier Columns
  • Disabling Indexes
    
    • Guidelines for Disabling Indexes and Constraints
    • Guidelines for Enabling Indexes and Constraints
  • SET IDENTITY_INSERT (Transact-SQL)
• Implement Data Types
  
  • FILESTREAM Overview
  • FILESTREAM Storage in SQL Server 2008
  • Getting Started with FILESTREAM Storage
  • How to: Enable FILESTREAM
  • How to: Create a FILESTREAM-Enabled Database
  • How to: Create a Table for Storing FILESTREAM Data
  • Using FILESTREAM Storage in Client Applications
  • Using FILESTREAM with Other SQL Server Features
  • FILESTREAM Best Practices
  • Spatial Data Support In SQL Server 2008
  • Working with Spatial Data (Database Engine)
    
    • Types of Spatial Data
    • Spatial Reference Identifiers (SRIDs)
    • Getting Started with the geometry Data Type
    • Getting Started with the geography Data Type
  • Designing and Implementing Structured Storage (Database Engine)
  • Designing and Implementing Semistructured Storage (Database Engine)
  • Designing and Implementing Spatial Storage (Database Engine)
  • Using SQL Server Collations
  • Selecting a SQL Server Collation
• Implement partitioning solutions
  
  • Partitioning
  • Partitioned Tables and Indexes
    
    • Understanding Partitioning
      
      • Partitioned Table and Index Concepts
    • Designing Partitioned Tables and Indexes
      
      • Planning Guidelines for Partitioned Tables and Indexes
      • Designing Partitions to Manage Subsets of Data
      • Designing Partitions to Improve Query Performance
      • Special Guidelines for Partitioned Indexes
    • Implementing Partitioned Tables and Indexes
      
      • Creating Partitioned Tables and Indexes
      • Modifying Partitioned Tables and Indexes
      • Querying Data and Metadata from Partitioned Tables and Indexes
  • CREATE PARTITION FUNCTION (Transact-SQL)
  • CREATE PARTITION SCHEME (Transact-SQL)
  • ALTER PARTITION FUNCTION (Transact-SQL)
  • ALTER PARTITION SCHEME (Transact-SQL)
  • MERGE
  • Creating Distributed Partitioned Views
  • Modifying Data in Partitioned Views
  • Resolving Distributed Partitioned Views
  • Using Partitioned Views
  • Linking Servers