Curtis Reese - Microsoft Certified Master: SQL Server 2008 Exam Preparation Course in a Book for Passing the Microsoft Certified Master: SQL Server 2008 Exam - The How To Pass on Your First Try Certification Study

Foreword This Exam Preparation book is intended for those preparing for the Microsoft Certified Master: SQL Server 2008 certification. This book is not a replacement for completing the course. This is a study aid to assist those who have completed an accredited course and preparing for the exam. Do not underestimate the value of your own notes and study aids. The more you have, the more prepared you will be. While it is not possible to pre-empt every question and content that MAY be asked in the Microsoft exams, This Book covers the main concepts covered within the Database Management discipline.

Due to licensing rights, we are unable to provide actual Microsoft Exams. However, the study notes and sample exam questions in this book will allow you to more easily prepare for Microsoft exams. Ivanka Menken Executive Director The Art of Service Write a review to receive any free eBook from our Catalog - $99 Value! If you recently bought this book we would love to hear from you! Benefit from receiving a free eBook from our catalog at http://www.emereo.org/ if you write a review on Amazon (or the online store where you purchased this book) about your last purchase! How does it work? To post a review on Amazon, just log in to your account and click on the Create your own review button (under Customer Reviews) of the relevant product page. You can find examples of product reviews in Amazon. If you purchased from another online store, simply follow their procedures. What happens when I submit my review? Once you have submitted your review, send us an email at review@emereo.orgwith the link to your review, and the eBook you would like as our thank you from http://www.emereo.org/.

Pick any book you like from the catalog, up to $99 RRP. You will receive an email with your eBook as download link. It is that simple! Notice of Rights All rights reserved. No part of this book may be reproduced or transmitted in any form by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Notice of Liability The information in this book is distributed on an NAs IsO basis without warranty. While every precaution has been taken in the preparation of the book, neither the author nor the publisher shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the instructions contained in this book or by the products described in it.

Trademarks Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations appear as requested by the owner of the trademark. All other product names and services identified throughout this book are used in editorial fashion only and for the benefit of such companies with no intention of infringement of the trademark. No such use, or the use of any trade name, is intended to convey endorsement or other affiliation with this book. Contents Foreword 1 3 Microsoft Certified Manager 11 4 Exam Specifics 13 5 Structured Query Language 14

5.1.1 SQL Statements 14 5.1.2 SELECT Statements 17 5.1.3 SQL Data Types 18 5.1.4 Recursive Query 18 5.1.5 SQL Functions 19 5.1.6 Cursors 19 5.1.7 SQL Applications 20 5.1.8 EXPLAIN Request Modifier 21 5.2.1 Databases and Users 22 5.2.2 Tables 22 5.2.3 Columns 26 5.2.4 Data Types 26 5.2.5 UserDefined Types 29 5.2.6 Keys 30 5.2.7 Indexes 30 5.2.8 Primary Index 31 5.2.9 Secondary Index 32 5.2.10 Join Index 34 5.2.11 Hash Index 35 5.2.12 Referential Integrity 35 5.2.13 Views 37 5.2.14 Triggers 37 5.2.15 Macros 38 5.2.16 Stored Procedures 39 5.2.17 UserDefined Functions 41 5.2.18 Profiles 41 5.2.19 Roles 42 5.3.1 Statement Structure 43 5.3.2 Keywords 43 5.3.3 Expressions 44 5.3.4 Literals 44 5.3.5 Operators 45 5.3.6 Functions 45 5.3.7 Delimiters and Separators 46 5.5.1 Data Definition Language 47 5.5.2 General DDL Statements 49 5.5.3 Data Control Language 50 5.5.4 Data Manipulation Language 51 5.5.5 Query and Workload Analysis 53 6 SQL Server Development 55 6.1.1 Data Types 55 6.1.2 Tables 56 6.1.3 Identifiers 57 6.1.4 Identity Property 58 6.1.5 Compression 58 6.1.6 Data Integrity 58 6.1.7 Join Tables 60 6.2.1 INSERT, UPDATE, and DELETE 60 6.2.2 OUTPUT and MERGE 64 6.2.3 Transactions 66 6.2.4 Locks 68 6.2.5 Transaction Isolation Levels 70 6.3.1 Query Statements 71 6.3.2 Aggregate Queries 72 6.3.3 Dataset Combinations 73 6.3.4 Built-in Functions 73 6.3.5 String Functions 74 6.3.6 Recursive Queries 75 6.3.7 Subqueries 75 6.3.8 Ranking Functions 75 6.4.1 Stored Procedures 76 6.4.2 Commenting Code 77 6.4.3 Variables 77 6.4.4 Parameters 78 6.4.5 Flow Control 78 6.4.6 Error Messages 79 6.4.7 Error Handling 79 6.4.8 Procedure Execution 80 6.4.9 Cursors 81 6.4.10 System Functions 82 6.4.11 UserDefined Functions 83 6.4.12 Triggers 84 6.4.13 Views 85 6.5.1 Extensible Markup Language 86 6.5.2 XML Variable or Column 87 6.5.3 SQLCLR 88 6.5.4 Filestream 89 7 SQL Database Administration 90 7.1.1 Hardware and Software Requirements 90 7.1.2 SQL Server Editions 91 7.2.1 Service Accounts 92 7.2.2 Collation Sequences 93 7.2.3 Authentication Modes 93 7.2.4 SQL Server Instances 93 7.2.5 SQL Server Configuration Manager 94 7.2.6 Database Mail 94 7.3.1 Endpoints 94 7.3.3 Principals 96 7.3.4 Server Roles 98 7.3.5 Database Users 98 7.3.6 Database Roles 99 7.3.7 Permissions 100 7.3.8 Master Keys 100 7.3.9 Certificates 100 7.3.10 Signatures 101 7.3.11 DDL Triggers 101 7.3.12 Audits 102 7.4.1 File Configuration 103 7.4.2 Transaction Logs 104 7.4.3 FILESTREAM Data 105 7.4.4 tempdb Database 105 7.4.5 Database Options 105 7.4.6 Data Integrity 107 7.4.7 Backup and Restore 108 7.4.8 Database Restores 111 7.4.9 Database Snapshots 113 7.5.1 Tables 115 7.5.2 Columns 117 7.5.3 Compression 118 7.5.4 Constraints 119 7.5.5 Indexes 119 7.5.6 Clustered Indexes 120 7.5.7 Nonclustered Indexes 120 7.5.8 Index Options 120 7.5.9 XML Indexes 121 7.5.10 Spatial Indexes 121 7.5.11 Full Text Catalog 122 7.5.12 Full Text Indexes 122 7.5.13 Full Text Queries 124 7.5.14 Thesaurus 125 7.5.15 Partition Function 127 7.5.16 Partition Scheme 127 7.5.17 Policy-Based Management 127 7.6.1 Database Engine Tuning Advisor 128 7.6.2 Resource Governor 130 7.6.3 Dynamic Management 130 7.6.4 Performance Data Warehouse 132 7.7.1 Windows Clustering 132 7.7.2 Configuration 133 7.7.3 Database Mirroring 133 8 Practice Exam 135 11 Index 159 Microsoft Certified Manager The MCM program is designed for the most experienced and talented IT professionals. Five MCM certifications exist: L MCM: Microsoft Exchange Server 2010 L MCM: Microsoft Lync Server 2010 L MCM: Microsoft SharePoint Server 2010 L MCM: Microsoft SQL Server 2008 L MCM: Windows Server 2008 R2: Directory For the MCM certification in SQL Server 2008, the candidate must pass the following exams: L Exam 88-970: SQL Server 2008 Microsoft Certified Master: Knowledge Exam.

L Exam 88-971: SQL Server 2008 Microsoft Certified Master: Lab Exam. A candidate must have the following certifications: L MCITP: Database Administrator SQL Server 2008 L MCITP: Database Developer SQL Server 2008 The skills measured by the exam include: L Recoverability and Integrity L Manageability L Security L Performance L Availability L Developer Support L Scalability The required skills and work experience relevant to the seven architect competencies, including: L Leadership D ability to articulate a vision of change and encourage action. L Technology Depth D possesses detailed knowledge of concepts, application, and issues of two depth competencies: security, networking, operations and health management, enterprise storage, systems management, messaging, and virtualization. L Technology Breadth D understands and applies best practices in architecture. L Strategy D understands and applies project and operational frameworks. L Organization dynamics D can identify and work with key stakeholders.

Next page