MCITP SQL Server 2005 Database Developer All-in-One Exam Guide (Exams 70-431, 70-441 & 70-442)

MCITP SQL Server 2005 Database Developer All-in-One Exam Guide (Exams 70-431, 70-441 & 70-442)

by Darril Gibson
Released April 2008
Publisher(s): McGraw-Hill
ISBN: 9780071643764

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

All-in-One is All You Need

Get complete coverage of all three Microsoft Certified IT Professional database developer exams for SQL Server 2005 in this comprehensive volume. Written by a SQL Server expert and MCITP, this definitive exam guide features learning objectives at the beginning of each chapter, exam tips, practice questions, and in-depth explanations. Detailed and authoritative, the book serves as both a complete certification study guide and an essential on-the-job reference.

Get full details on all exam topics including how to:

  • Install and configure SQL Server 2005
  • Manage database design
  • Use Transact-SQL and XML
  • Work with functions, triggers, and CLR integration
  • Optimize, monitor, and secure databases
  • Create stored procedures
  • Handle disaster recovery
  • Work with Service Broker, Web Services, and MARS
  • Use SQL Server Reporting Services and Notification Services
  • Manage locks, deadlocks, and cursors
  • Transfer data using Replication and SQL Server Integration Services

The CD-ROM features:

  • Six full practice exams-two for each exam: 70-431, 70-441, & 70-442
  • Scripts from the step-by-step exercises in the book
  • Video training clips from the author
  • Complete electronic book

Table of contents

  1. Cover Page
  2. All-In-One Mcitp Sql Server 2005 Database Developer
  3. Copyright Page
  4. Contents
  5. Acknowledgments
  6. Introduction
  7. Chapter 1 Installing and Configuring SQL Server 2005
    1. SQL Server Editions
      1. Operating Systems and Supported Benefits
      2. Instances
    2. Installing SQL Server 2005
      1. Prerequisites
      2. Downloading
      3. Upgrading
    3. Configuring SQL Server 2005
      1. Service Accounts
      2. Services
      3. The SQL Server Surface Area Configuration Tool
      4. SQL Server Configuration Manager
      5. Collations
    4. Connecting to SQL Server 2005
      1. SQL Server Management Studio (SSMS)
      2. SQLCmd
      3. Dedicated Administrator Connection (DAC)
    5. Examining the Installation
      1. System Databases
    6. Troubleshooting the Installation or Operation
      1. Operating System Logs
      2. SQL Logs
    7. Books Online
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  8. Chapter 2 SQL Server 2005 Database Basics
    1. Tables
      1. Data Types
      2. New Large Value Data Types
      3. User-Defined Data Types
      4. Creating Tables
      5. Nulls and Defaults
      6. Computed Columns
    2. Views
      1. Creating Views
      2. Updating Data in a VIEW
      3. VIEW Options
    3. Creating a Database
      1. Locating the Files
      2. Database Options
    4. Schemas and Naming Conventions
      1. Four-Part Naming
      2. Naming Conventions
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  9. Chapter 3 Database Design
    1. Data Integrity
      1. PRIMARY KEYs
      2. FOREIGN KEYs
      3. Table Relationships
      4. Exploring Relationships in AdventureWorks
    2. Normalization and Normal Forms
      1. 1st Normal Form (1NF)
      2. 2nd Normal Form (2NF)
      3. 3rd Normal Form (3NF)
      4. Denormalization
      5. Generalization
    3. CHECK Constraints
      1. UNIQUE
    4. Partitioning Tables
      1. Horizontal Partitioning
      2. Federations of Servers and Federated Databases
      3. Partition Schemes
    5. Database Design Exercise
      1. The Scenario
      2. The Solution
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  10. Chapter 4 Transact-SQL
    1. General Rules to Know
      1. The ANSI Standard
      2. Delimiting Identifiers
      3. Case Sensitive and Case Insensitive
    2. SELECT
      1. The Column List
      2. WHERE
      3. ORDERBY
      4. GROUP BY and HAVING
      5. Joins
    3. Other DML Statements
      1. UPDATE
      2. INSERT
      3. DELETE
    4. Advanced Query Techniques
      1. Common Table Expressions (CTEs)
      2. EXCEPT and INTERSECT
      3. PIVOT and UNPIVOT
      4. Ranking
      5. Additional Study
      6. Summary of What You Need to Know
      7. Questions
      8. Answers
  11. Chapter 5 XML
    1. XML Overview
      1. XML Data
      2. Well-formed XML
    2. Storing XML Data
      1. The XML Data Type
      2. Typed vs. Untyped XML
      3. XML Schema Collection
    3. Retrieving XML Data
      1. FORXML
      2. XML Methods
      3. XQuery
      4. Using OPENXML to Shred XML Data
      5. XMLReader and XMLWriter
      6. XML Indexes
      7. Additional Study
      8. Summary of What You Need to Know
      9. Questions
      10. Answers
  12. Chapter 6 Advanced Database Objects
    1. Functions
      1. An Overview of the Functions
      2. Built-in Functions
      3. User-Defined Functions
    2. Triggers
      1. An Overview of Triggers
      2. DML Triggers
      3. INSTEAD OF
      4. DDL Triggers
      5. Disabling Triggers
      6. Recursive and Nested Triggers
    3. Common Language Runtime (CLR) Integration
      1. Execution Context
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  13. Chapter 7 Optimizing Databases
    1. Indexes
      1. Clustered
      2. Nonclustered
      3. Full-Text Indexes
      4. XML Indexes
    2. Index Design
      1. When and Why to Create an Index
      2. Analyzing Queries
      3. The Leaf and Nonleaf Levels
      4. Fill Factors
      5. Calculating Size
      6. tempdb
      7. Creating an Indexed VIEW
    3. Statistics
      1. Statistics in SQL Server
      2. Viewing and Updating Statistics
    4. Full-Text Indexes
      1. Full-Text Catalogs and Full-Text Indexes
    5. Database Engine Tuning Advisor (DTA)
      1. Setup and Configuration
    6. Index Maintenance
      1. Dynamic Management VIEWs and Functions
      2. Fragmentation
      3. System Monitor Counters
      4. DBCC
    7. Filegroups
      1. Filegroup Possibilities
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  14. Chapter 8 Security
    1. Server Security Basics
      1. Authentication Modes
      2. The sa Account
      3. Creating Logins
      4. Server Roles
      5. Groups
    2. Security Principals
      1. Database Users
      2. Database Roles
      3. Application Roles
      4. Schemas
    3. Database Securables
      1. Permissions
      2. Ownership Chaining
    4. Designing the Security Strategy
      1. Services
      2. Auditing
    5. Encryption
      1. Keys and Algorithms
      2. Encrypting Data
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  15. Chapter 9 Stored Procedures
    1. An Overview of Stored Procedures
      1. Used for Optimization
      2. Security
      3. Adds Modularity
      4. Limitations
      5. Types of Stored Procedures
    2. Transactions
      1. An Overview of Transactions
      2. Commit Transaction
      3. ROLLBACK TRANSACTION
      4. Error Catching
      5. Distributed Transactions
    3. User-Defined Stored Procedures
      1. Creating Stored Procedures
      2. Recompiling Stored Procedures
    4. Security
      1. Permissions and Access
      2. SQL Injection Attacks
    5. CLR-Integrated Stored Procedures
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  16. Chapter 10 Disaster Recovery
    1. A Disaster Recovery Overview
    2. The Transaction Log
      1. Checkpoints
      2. Restoring from the Transaction Log
    3. Recovery Models
      1. The Full Recovery Model
      2. The Bulk-Logged Recovery Model
      3. The Simple Recovery Model
      4. Recovery Model Summary
      5. Setting the Recovery Model
    4. Database Backups
      1. Backup Types
      2. Backup Devices
      3. Mirrors and Sets
      4. Backup and Restore Commands
      5. Before the Backup
    5. Restores
      1. Restore's First Step
      2. Restoring with NORECOVERY and RECOVERY
      3. Restore Strategies
      4. Restoring to a Point in Time (STOPAT)
      5. Verifying Backups
      6. Restoring Files and Filegroups
      7. Minimizing Restores
      8. Detach and Attach
    6. Protecting System Databases
      1. Rebuilding the Master
    7. Log Shipping
      1. Benefits and Requirements
      2. Procedures for Changing Roles
    8. Database Snapshots
      1. Purpose and Benefits
      2. Creating and Using a Database Snapshot
      3. Managing Snapshots
      4. Recovering Data from a Snapshot
    9. Database Mirroring
      1. Requirements
      2. Benefits
      3. The Witness Server
      4. Implement Database Mirroring
    10. High-Availability Comparisons
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  17. Chapter 11 Data Access
    1. Service Broker
      1. Service Broker Object Types
      2. Service Broker Applications
      3. Event Notifications
      4. Implementing Event Notifications
    2. Web Services and HTTP Endpoints
      1. HTTP Endpoints
    3. Linked Servers
      1. Creating a Linked Server
      2. Configuring Logins for a Linked Server
      3. OPENQUERY
      4. OPENDATASOURCE and OPENROWSET
    4. Data Access Methods
      1. Multiple Active Result Sets (MARS)
      2. DataSets and DataReaders
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  18. Chapter 12 Support Services
    1. SQL Server Reporting Services (SSRS)
      1. Reporting Services Databases
      2. Requirements
      3. Report Models
      4. Report Manager
      5. Creating and Modifying Reports in BIDS
      6. Report Formats
      7. SSRS Server Placement
    2. Notification Services
      1. Notification Application
      2. Notification Services vs. SQL Server Agent
    3. Analysis Services
      1. Additional Study
      2. Summary of What You Need to Know
      3. Questions
      4. Answers
  19. Chapter 13 Maintenance Tools
    1. System Monitor
      1. Measuring Counters in System Monitor
      2. SQL Server Agent
      3. SQL Server Agent Service
      4. SQL Server Agent Properties
      5. Creating Operators, Alerts, and Jobs
      6. SQL Server Agent Mail
    2. Maintenance Plans
    3. SQL Server Profiler
      1. Templates
      2. SQL Trace
    4. Dynamic Management VIEWs and Functions
      1. Dashboard Reports
      2. Additional Study
      3. Summary of What You Need to Know
      4. Questions
      5. Answers
  20. Chapter 14 Locks and Cursors
    1. Locks and Deadlocks
      1. What Are Locks?
      2. What Are Deadlocks?
      3. Preventing Locks and Deadlocks
      4. Troubleshooting Locks and Deadlocks
      5. Profiler and System Monitor
    2. Transaction Isolation Levels
      1. Concurrency Effects
      2. Picking a Transaction Isolation Level
    3. Hints
      1. Table Hints
      2. Query Plan Guides
    4. Cursors
      1. Building a Cursor
      2. Cursor Performance
      3. Additional Study
      4. Summary of What You Need to Know
      5. Questions
      6. Answers
  21. Chapter 15 Transferring Data
    1. Replication
      1. The Publisher Metaphor
      2. Replication Methods
      3. Replication Monitor
    2. SQL Server Integration Services (SSIS)
      1. ETL
      2. Requirements
      3. Business Intelligence Development Studio (BIDS)
      4. Packages
      5. Deploying Packages
      6. Optimizing an SSIS Solution
    3. Bulk Imports and Exports
      1. The bcp Utility
      2. BULK INSERT Statement
      3. OPENROWSET and OPENDATASOURCE
      4. The Import/Export Wizard
      5. Additional Study
      6. Summary of What You Need to Know
      7. Questions
      8. Answers
  22. Appendix A About the Download
    1. Steps to Download MasterExam
      1. System Requirements
      2. MasterExam
    2. CertCams
    3. Scripts
    4. Help
    5. Removing Installation
    6. Technical Support
      1. LearnKey Technical Support
  23. Appendix B Exam 70-431: What You Need to Know to Pass
    1. Introduction
      1. Picking a Date
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-431 Topics
  24. Appendix C Inside the Design Exam
    1. The Design Exam
      1. Design Exam Success Tips
      2. Scenario Topics
      3. Sample Scenario Questions
      4. Sample Scenario: MCITPSuccess Corporation
      5. Sample Scenario Answers
      6. Final Notes
  25. Appendix D Exam 70-441: What You Need to Know to Pass
    1. Introduction
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-441 Topics
  26. Appendix E Exam 70-442: What You Need to Know to Pass
    1. Introduction
    2. What You Can Expect
    3. What's Expected of You
      1. Chapters Covering 70-442 Topics
  27. Appendix F SQL Database Design Object Summary
  28. Glossary
  29. Index

Product information

  • Title: MCITP SQL Server 2005 Database Developer All-in-One Exam Guide (Exams 70-431, 70-441 & 70-442)
  • Author(s): Darril Gibson
  • Release date: April 2008
  • Publisher(s): McGraw-Hill
  • ISBN: 9780071643764