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