MCTS^®: Microsoft^® SQL Server^TM 2005 Implementation and Maintenance Study Guide: Exam 70-431

by Joseph L. Jorden; Dandy Weyn

• ISBN13: 9780470080061
• ISBN10: 047008006X
• Format: eBook
• Copyright: 2006-10-01
• Publisher: Sybex
• More Book Details
• Purchase Benefits

Summary

With the release of SQL Server 2005, Microsoft is introducing a new multi-exam certification program. The Microsoft SQL Server 2005 Implementation and Maintenance exam (70-431) is the first stop for everyone entering this new certification track, and serves as both a single exam certification as well as the entry exam for the MCITP-level certifications. This book provides an introduction to the development and administrative aspects of SQL Server 2005 and features practical guidance for all aspects of the exam. Get a great start in your certification process today with this comprehensive guide which includes: Hundreds of challenging practice questions plus two bonus exams Leading-edge exam preparation software, including a test engine Authoritative coverage of all key exam objectives, including: Installing and Configuring SQL Server 2005 Implementing High Availability and Disaster Recovery Supporting Data Consumers Maintaining Databases Monitoring and Troubleshooting SQL Server Performance Creating and Implementing Database Objects Note: CD-ROM/DVD and other supplementary materials are not included as part of eBook file.

Table of Contents

Introduction

Assessment Test

Installing Microsoft SQL Server 2005

Meeting the Prerequisites

Preparing to Install

Choosing Default Instances or Named Instances

Choosing Service Accounts

Selecting an Authentication Mode

Choosing a Collation Setting

Upgrading from a Previous Version

Installing SQL Server 2005

Installing a Second Instance

Troubleshooting the Installation

Creating and Configuring Databases

Planning Your Database

Introducing Database Files

Introducing Filegroups

Deciding on Database File Placement

Introducing RAID-0

Introducing RAID-1

Introducing RAID-5

Introducing RAID-10

Creating Data Storage Structures

Introducing Extents

Introducing Pages

Estimating Storage Requirements

Estimating Table Storage Requirements

Estimating Index Storage Requirements

Creating and Configuring Databases

Creating a Database

Gathering Information about Your Database

Setting Database Options

Working with Tables and Views

Planning Tables

Introducing Built-in Datatypes

Introducing Computed Columns

Creating Tables

Restricting the Data

Introducing Constraints

Partitioning Tables

Understanding Views

Modifying Data through a View

Working with Indexed Views

Performing Indexing and Full-Text Searching

Understanding Index Architecture

Understanding Heaps

Understanding Clustered Indexes

Understanding Nonclustered Indexes

Creating Indexes

Using Primary Keys

Using Full-Text Searching

Introducing More Database Objects

Introducing Stored Procedures

Understanding the Types of Stored Procedures

Creating Stored Procedures

Recompiling Stored Procedures

Introducing Triggers

Understanding the Types of Triggers

Understanding DML Triggers

Understanding DDL Triggers

Understanding Trigger Recursion and Nesting

Understanding Disabling Triggers

Understanding Event Notifications

Introducing Functions

Understanding the Types of Functions

Using Scalar Functions

Introducing Table-Valued Functions

Introducing Built-in Functions

Introducing CLR Functions

Introducing Deterministic and Nondeterministic Functions

Introducing User-Defined Types

Creating T-SQL User-Defined Types

Creating CLR User-Defined Types

Getting More CLR Functionality

Implementing Security in SQL Server 2005

Understanding Security Modes 190

Using Windows Authentication Mode 190

Using Mixed Mode 191

Setting the Authentication Mode 191

Understanding SQL Server Logins 193

Using Standard Logins 193

Using Windows Logins 196

Understanding the Items Common to All Logins 199

Understanding Fixed Server Roles 199

Creating Database User Accounts 202

Understanding Permissions 204

Applying Statement Permissions 205

Applying Object Permissions 207

Understanding Database Roles 210

Using Fixed Database Roles 210

Using Custom Database Roles 212

Using Application Roles 215

Understanding Permission States 217

Granting a Permission 217

Revoking a Permission 217

Denying a Permission 217

Introducing Ownership Chains 221

Introducing Linked Server Security 223

Introducing Encryption 224

Creating a Security Plan

Working with Relational Data

Understanding and Using Transactions 242

Executing Implicit and Explicit Transactions 243

Committing and Rolling Back 244

Executing Distributed Transactions 246

Populating Tables 2

Table of Contents provided by Publisher. All Rights Reserved.