MCTS 70-431: Implementing and Maintaining Microsoft SQL Server 2005

MCTS 70-431: Implementing and Maintaining Microsoft SQL Server 2005

Released July 2006
Publisher(s): Pearson IT Certification
ISBN: 9780789735881

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

The MCTS 70-431 Exam Cram is a must-have resource in your MCTS 70-431 exam preparation. The Exam Cram focuses on exactly what you need to know to get certified. This includes features like:

  • Complete coverage of all 70-431 exam objectives

  • Exam alerts, practice questions, notes, tips, sidebars, cautions, test-taking strategies, and time-saving tips

  • Access to MeasureUp practice questions and innovative testing software

  • The popular Cram Sheet tear card, giving you the key facts you should review before you enter the testing center

  • Insight from author Thomas Moore, a Microsoft Certified Trainer (MCT) and SQL Server expert who has helped hundreds of students earn their SQL Server certifications

    •

    Table of contents

    1. Copyright
      1. Dedication
    2. About the Author
    3. Acknowledgments
    4. We Want to Hear from You!
    5. Reader Services
    6. Introduction
      1. About the 70-431 Exam and Content Areas
      2. How to Prepare for the Exam
      3. What This Book Does
      4. What This Book Does Not Do
      5. About This Book
    7. Self Assessment
      1. SQL Server Implementation and Maintenance as an MCTS
        1. The Ideal MCITP Candidate
        2. Put Yourself to the Test
          1. Educational Background
          2. Hands-On Experience
          3. Testing Your Exam Readiness
        3. Let’s Get to It!
    8. 1. Installing and Configuring SQL Server 2005
      1. Installing and Configuring SQL Server 2005
        1. Installation Requirements
          1. Hardware Requirements
          2. Operating System Requirements
          3. Support Software Requirements
        2. The Installation Process
        3. Installation Preparations
          1. Additional Considerations for Installation
        4. Postinstallation Procedures
          1. Linked Server Configuration Options
          2. SQL Server Login Security
      2. Exam Prep Questions
        1. Answers to Exam Prep Questions
    9. 2. Creating Database Objects
      1. Creating and Defining Databases
        1. Using T-SQL to Create and Alter a Database
        2. The Makeup of a Database
        3. Standard Views, Indexed Views, and Partitioned Views
          1. Using Indexed Views
          2. Using Partitioned Views
        4. Miscellaneous SQL Server Objects
      2. Defining SQL Server Tables
        1. Using Types and Schemas
          1. User-Defined Types (UDTs)
          2. CLR UDTs
          3. XML Schema Collections
          4. Other Attributes
        2. Using Columns with Automated or Calculated Values
          1. Computed Columns
          2. Identity Columns
          3. Columns with timestamp Data Types
        3. Maintaining Order by Using Indexes
          1. Indexing Through Reordering: Clustered Indexing
          2. Indexing Through Data Pointers: Nonclustered Indexing
          3. One-of-a-Kind Indexing: Unique Indexing
          4. Leaving Room for Additions in Indexes
        4. Primary/Foreign Keys and Relationships
          1. The PRIMARY KEY Constraint
          2. The FOREIGN KEY Constraint
        5. Using Cascading Actions
        6. Using DML and DDL Triggers
        7. Partitioning Tables
          1. Partitioning Strategies
          2. Table Considerations
          3. Advantages of Partitioning
        8. Creating Partitioned Tables
          1. Using $PARTITION in Queries
      3. SQL Server Programmability Objects
        1. Stored Procedures
        2. Creating CHECK Constraints
        3. Creating Your Own Functions
        4. English Query Capabilities with Full-Text Catalogs
        5. Objects with Security Context
      4. Exam Prep Questions
      5. Answers to Exam Prep Questions
    10. 3. Implementing Database Objects
      1. Data Querying and Reporting
        1. Listing the Contents of a Table
        2. Making a Report More Presentable
          1. Using TRIM to Remove White Space
          2. Returning TOP Rows
          3. Displaying Groups in Output
        3. Querying a Sampling of the Data Stored
          1. Selecting Rows Based on NULL Values
        4. Relating Data from Multiple Tables
          1. Outputting Only Matches: INNER JOIN
          2. Returning Output Even When No Match Exists: OUTER JOIN
        5. Applying Conditional Data Filtering
          1. BETWEEN, IN, and LIKE
      2. Data Querying Using Full-Text Indexes
        1. Creating and Populating a Catalog
      3. Using System Tables and Views
        1. Getting Information from System Tables
        2. Information Retrieval from System Stored Procedures
        3. Using Dynamic Management Views and Other System Views
        4. Inserting Data
        5. Using UDT and the CLR to Control Data Input
        6. Using the CLR Within Stored Procedures
        7. Inserting Individual Records
        8. Using a Query to Insert Complete Recordsets
        9. Disabling Functionality During Data Insertion
          1. Disabling Indexes
          2. Disabling Trigger Firing
          3. Disabling Constraint Checking
      4. Changing What Is Already Stored
        1. Updating a Single Record
        2. Doing Updates That Affect Multiple Records
        3. Performing Transaction Processing
        4. Upgrading Data from Previous Releases of SQL Server
      5. Removing Unwanted Data
        1. Directly Removing Records from a Table
        2. Indirectly Removing Data from a Table
        3. Escalating Privileges to Allow Deletion
        4. Controlling Privileges by Using GRANT, DENY, and REVOKE
      6. Exam Prep Questions
      7. Answers to Exam Prep Questions
    11. 4. Supporting the XML Framework
      1. Managing XML Data
        1. Newly Supported XML Features
          1. Using the xml Data Type
          2. Using XQuery with XML
          3. Using Larger XML with xarchar(max)
          4. Using XML DML
          5. Using FOR XML with PATH
          6. Using XML Document Returns
        2. XML: The Basics
          1. Outputting Data in XML Format
        3. The xml Data Type and Methods
        4. XML Method Interactions
          1. The query() Method
          2. The value() Method
          3. The exist() Method
          4. The modify() Method
          5. The nodes() Method
      2. Other SQL Server XML Support
        1. Indexing XML Data
        2. Creating Primary and Secondary Indexes
        3. Native XML Web Service Support
      3. Exam Prep Questions
      4. Answers to Exam Prep Questions
    12. 5. Data Consumption and Throughput
      1. Importing and Exporting Data
        1. Using the Bulk Copy Program (BCP)
          1. Importing Data with BCP Format File
          2. Using BULK INSERT as an Alternative to BCP
        2. Using OPENROWSET for Importing Data
        3. Using SQL Server Integration Services (SSIS)
      2. Implementing Service Broker
        1. Designing a Service
          1. Defining Message Types
          2. Providing Contract Details
          3. Creating a Queue
          4. Assembling Components into a Service
          5. The Communications Dialogue
      3. Exam Prep Questions
      4. Answers to Exam Prep Questions
    13. 6. Database Maintenance
      1. SQL Server 2005 Database Maintenance
        1. What’s New in SQL Server Maintenance?
      2. Performing Database Backups
        1. Recovery Models and Backups
        2. Recovery Models Using T-SQL
        3. Backup Types and Scenarios
        4. Setting the Options of a Backup
        5. Options of the T-SQL BACKUP Statement
          1. Overwriting Existing Backups and Preserving the Header (INIT)
          2. Appending to Existing Backups (NOINIT)
          3. Creating a New Media Set and Preparing a New Header (FORMAT)
          4. Rewinding and Unloading the Tape When Finished (REWIND and UNLOAD)
          5. Validating the Data as the Backup Occurs (CHECKSUM)
          6. Removing Inactive Log Entries and Truncating the Log (NO_LOG and TRUNCATE_ONLY)
          7. Setting Up a Warm Backup Secondary Database (NORECOVERY)
          8. Setting Up a Read-Only Secondary Server (STANDBY)
          9. Performing an Extra Backup (COPY_ONLY)
          10. Using a Backup for a System Database
      3. Restoring Data from a Backup
        1. Using the T-SQL RESTORE Statement
      4. Using Database Snapshots
        1. Creating and Deleting Database Snapshots
      5. Automating Maintenance with Job Scheduling
        1. Viewing Job Details and History
      6. Exam Prep Questions
      7. Answers to Exam Prep Questions
    14. 7. Monitoring SQL Server Performance
      1. SQL Server Management Studio
      2. Monitoring and Recording Performance
        1. Using Activity Monitor for the Here and Now
        2. Management Studio: Log File Viewer
          1. Windows NT Logs
          2. SQL Server Logs
        3. Server-Maintained Information
          1. Dynamic Management Functions and Views
          2. Database Console Command (DBCC)
          3. Trace Flags
          4. Simple Network Management Protocol (SNMP)
      3. Using Windows System Monitor
      4. Using SQL Server Profiler
        1. Defining a Profiler Trace
        2. Using Profiler Traces to Diagnose Locking
        3. Trace Playback and Diagnosis
        4. Using Profiler to Gather a Workload
      5. Exam Prep Questions
      6. Answers to Exam Prep Questions
    15. 8. Troubleshooting and Optimizing SQL Server
      1. Data Analysis and Problem Diagnosis
        1. Tuning the Operating System and Hardware
        2. Creating and Maintaining Statistics
        3. Locks, Blocks, and Deadlocks
          1. Reducing Lock Contention
          2. Levels of Locks
          3. Diagnosing Lock Problems
          4. Locking-Related Stored Procedures
          5. Locking-Related Dynamic Management Views
          6. SQL Server Profiler Lock Events
          7. System Monitor Lock Counters
      2. Tuning the Database Structure
        1. Indexing Strategies
          1. What to Index
          2. What Not to Index
          3. Indexed Views
          4. Revisiting Indexing Postimplementation
          5. Leaving Space for Inserts (Fill Factor)
        2. Data Partitioning Across Servers
          1. Partitioned Views
          2. Partitioned Tables and Indexes
        3. Using the DTA
      3. Server Configuration Maintenance
        1. Using the Database Console Command (DBCC)
          1. DBCC Validation Operations
          2. DBCC Maintenance Operations
          3. Miscellaneous DBCC Operations
        2. Setting Alerts to Automate Problem Identification
      4. Exam Prep Questions
      5. Answers to Exam Prep Questions
    16. 9. Implementing High Availability
      1. High-Availability Solutions
        1. Implementing Log Shipping
        2. Using Database Mirroring
          1. Enabling Trace Flags in a Test Environment
        3. Using Failover Clustering
      2. Using Replication
        1. Replication Strategies
          1. Using a Central Publisher and Multiple Subscribers
          2. Using Multiple Publishers and Multiple Subscribers
          3. Using Multiple Publishers and a Single Subscriber
          4. Using a Single Publisher and a Remote Distributor
        2. Types of Replication
          1. Site Autonomy in Replication
          2. Transactional Consistency in Replication
          3. Latency in Replication
        3. Elements of Replication
          1. Using Snapshot Replication
          2. Using Transactional Replication
          3. Using Merge Replication
      3. Microsoft Analysis Services
      4. Exam Prep Questions
      5. Answers to Exam Prep Questions
    17. 10. Practice Exam 1
      1. Exam Questions
    18. 11. Answers to Practice Exam 1
      1. Question 1
      2. Question 2
      3. Question 3
      4. Question 4
      5. Question 5
      6. Question 6
      7. Question 7
      8. Question 8
      9. Question 9
      10. Question 10
      11. Question 11
      12. Question 12
      13. Question 13
      14. Question 14
      15. Question 15
      16. Question 16
      17. Question 17
      18. Question 18
      19. Question 19
      20. Question 20
      21. Question 21
      22. Question 22
      23. Question 23
      24. Question 24
      25. Question 25
      26. Question 26
      27. Question 27
      28. Question 28
      29. Question 29
      30. Question 30
      31. Question 31
      32. Question 32
      33. Question 33
      34. Question 34
      35. Question 35
      36. Question 36
      37. Question 37
      38. Question 38
      39. Question 39
      40. Question 40
    19. 12. Practice Exam 2
      1. Exam Questions
    20. 13. Answers to Practice Exam 2
      1. Question 1
      2. Question 2
      3. Question 3
      4. Question 4
      5. Question 5
      6. Question 6
      7. Question 7
      8. Question 8
      9. Question 9
      10. Question 10
      11. Question 11
      12. Question 12
      13. Question 13
      14. Question 14
      15. Question 15
      16. Question 16
      17. Question 17
      18. Question 18
      19. Question 19
      20. Question 20
      21. Question 21
      22. Question 22
      23. Question 23
      24. Question 24
      25. Question 25
      26. Question 26
      27. Question 27
      28. Question 28
      29. Question 29
      30. Question 30
      31. Question 31
      32. Question 32
      33. Question 33
      34. Question 34
      35. Question 35
      36. Question 36
      37. Question 37
      38. Question 38
      39. Question 39
      40. Question 40
    21. A. Suggested Readings and Resources
      1. Chapter 1: Installing and Configuring SQL Server 2005
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      2. Chapter 2: Creating Database Objects
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      3. Chapter 3: Implementing Database Objects
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      4. Chapter 4: Supporting the XML Framework
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      5. Chapter 5: Data Consumption and Throughput
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      6. Chapter 6: Database Maintenance
        1. Books
        2. On the Web
        3. SQL Server Books Online Help Facility
      7. Chapter 7: Monitoring SQL Server Performance
        1. On the Web
        2. SQL Server Books Online Help Facility
      8. Chapter 8: Troubleshooting and Optimizing SQL Server
        1. On the Web
        2. SQL Server Books Online Help Facility
      9. Chapter 9: Implementing High Availability
        1. On the Web
        2. SQL Server Books Online Help Facility
    22. Glossary

    Product information

    • Title: MCTS 70-431: Implementing and Maintaining Microsoft SQL Server 2005
    • Author(s):
    • Release date: July 2006
    • Publisher(s): Pearson IT Certification
    • ISBN: 9780789735881