book

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

Name: MCITP SQL Server 2005 Database Developer All-in-One Exam Guide (Exams 70-431, 70-441 & 70-442)
Author: Darril Gibson
ISBN: 9780071643764

by Darril Gibson

April 2008

Intermediate to advanced

944 pages

24h 58m

English

McGraw-Hill

Read now

Unlock full access

Cover Page
All-In-One Mcitp Sql Server 2005 Database Developer
Copyright Page
Contents
Acknowledgments
Introduction
Chapter 1 Installing and Configuring SQL Server 2005
SQL Server EditionsOperating Systems and Supported BenefitsInstancesInstalling SQL Server 2005PrerequisitesDownloadingUpgradingConfiguring SQL Server 2005Service AccountsServicesThe SQL Server Surface Area Configuration ToolSQL Server Configuration ManagerCollationsConnecting to SQL Server 2005SQL Server Management Studio (SSMS)SQLCmdDedicated Administrator Connection (DAC)Examining the InstallationSystem DatabasesTroubleshooting the Installation or OperationOperating System LogsSQL LogsBooks OnlineAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 2 SQL Server 2005 Database Basics
TablesData TypesNew Large Value Data TypesUser-Defined Data TypesCreating TablesNulls and DefaultsComputed ColumnsViewsCreating ViewsUpdating Data in a VIEWVIEW OptionsCreating a DatabaseLocating the FilesDatabase OptionsSchemas and Naming ConventionsFour-Part NamingNaming ConventionsAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 3 Database Design
Data IntegrityPRIMARY KEYsFOREIGN KEYsTable RelationshipsExploring Relationships in AdventureWorksNormalization and Normal Forms1st Normal Form (1NF)2nd Normal Form (2NF)3rd Normal Form (3NF)DenormalizationGeneralizationCHECK ConstraintsUNIQUEPartitioning TablesHorizontal PartitioningFederations of Servers and Federated DatabasesPartition SchemesDatabase Design ExerciseThe ScenarioThe SolutionAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 4 Transact-SQL
General Rules to KnowThe ANSI StandardDelimiting IdentifiersCase Sensitive and Case InsensitiveSELECTThe Column ListWHEREORDERBYGROUP BY and HAVINGJoinsOther DML StatementsUPDATEINSERTDELETEAdvanced Query TechniquesCommon Table Expressions (CTEs)EXCEPT and INTERSECTPIVOT and UNPIVOTRankingAdditional StudySummary of What You Need to KnowQuestionsAnswers

Chapter 5 XML
XML OverviewXML DataWell-formed XMLStoring XML DataThe XML Data TypeTyped vs. Untyped XMLXML Schema CollectionRetrieving XML DataFORXMLXML MethodsXQueryUsing OPENXML to Shred XML DataXMLReader and XMLWriterXML IndexesAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 6 Advanced Database Objects
FunctionsAn Overview of the FunctionsBuilt-in FunctionsUser-Defined FunctionsTriggersAn Overview of TriggersDML TriggersINSTEAD OFDDL TriggersDisabling TriggersRecursive and Nested TriggersCommon Language Runtime (CLR) IntegrationExecution ContextAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 7 Optimizing Databases
IndexesClusteredNonclusteredFull-Text IndexesXML IndexesIndex DesignWhen and Why to Create an IndexAnalyzing QueriesThe Leaf and Nonleaf LevelsFill FactorsCalculating SizetempdbCreating an Indexed VIEWStatisticsStatistics in SQL ServerViewing and Updating StatisticsFull-Text IndexesFull-Text Catalogs and Full-Text IndexesDatabase Engine Tuning Advisor (DTA)Setup and ConfigurationIndex MaintenanceDynamic Management VIEWs and FunctionsFragmentationSystem Monitor CountersDBCCFilegroupsFilegroup PossibilitiesAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 8 Security
Server Security BasicsAuthentication ModesThe sa AccountCreating LoginsServer RolesGroupsSecurity PrincipalsDatabase UsersDatabase RolesApplication RolesSchemasDatabase SecurablesPermissionsOwnership ChainingDesigning the Security StrategyServicesAuditingEncryptionKeys and AlgorithmsEncrypting DataAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 9 Stored Procedures
An Overview of Stored ProceduresUsed for OptimizationSecurityAdds ModularityLimitationsTypes of Stored ProceduresTransactionsAn Overview of TransactionsCommit TransactionROLLBACK TRANSACTIONError CatchingDistributed TransactionsUser-Defined Stored ProceduresCreating Stored ProceduresRecompiling Stored ProceduresSecurityPermissions and AccessSQL Injection AttacksCLR-Integrated Stored ProceduresAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 10 Disaster Recovery
A Disaster Recovery OverviewThe Transaction LogCheckpointsRestoring from the Transaction LogRecovery ModelsThe Full Recovery ModelThe Bulk-Logged Recovery ModelThe Simple Recovery ModelRecovery Model SummarySetting the Recovery ModelDatabase BackupsBackup TypesBackup DevicesMirrors and SetsBackup and Restore CommandsBefore the BackupRestoresRestore's First StepRestoring with NORECOVERY and RECOVERYRestore StrategiesRestoring to a Point in Time (STOPAT)Verifying BackupsRestoring Files and FilegroupsMinimizing RestoresDetach and AttachProtecting System DatabasesRebuilding the MasterLog ShippingBenefits and RequirementsProcedures for Changing RolesDatabase SnapshotsPurpose and BenefitsCreating and Using a Database SnapshotManaging SnapshotsRecovering Data from a SnapshotDatabase MirroringRequirementsBenefitsThe Witness ServerImplement Database MirroringHigh-Availability ComparisonsAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 11 Data Access
Service BrokerService Broker Object TypesService Broker ApplicationsEvent NotificationsImplementing Event NotificationsWeb Services and HTTP EndpointsHTTP EndpointsLinked ServersCreating a Linked ServerConfiguring Logins for a Linked ServerOPENQUERYOPENDATASOURCE and OPENROWSETData Access MethodsMultiple Active Result Sets (MARS)DataSets and DataReadersAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 12 Support Services
SQL Server Reporting Services (SSRS)Reporting Services DatabasesRequirementsReport ModelsReport ManagerCreating and Modifying Reports in BIDSReport FormatsSSRS Server PlacementNotification ServicesNotification ApplicationNotification Services vs. SQL Server AgentAnalysis ServicesAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 13 Maintenance Tools
System MonitorMeasuring Counters in System MonitorSQL Server AgentSQL Server Agent ServiceSQL Server Agent PropertiesCreating Operators, Alerts, and JobsSQL Server Agent MailMaintenance PlansSQL Server ProfilerTemplatesSQL TraceDynamic Management VIEWs and FunctionsDashboard ReportsAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 14 Locks and Cursors
Locks and DeadlocksWhat Are Locks?What Are Deadlocks?Preventing Locks and DeadlocksTroubleshooting Locks and DeadlocksProfiler and System MonitorTransaction Isolation LevelsConcurrency EffectsPicking a Transaction Isolation LevelHintsTable HintsQuery Plan GuidesCursorsBuilding a CursorCursor PerformanceAdditional StudySummary of What You Need to KnowQuestionsAnswers
Chapter 15 Transferring Data
ReplicationThe Publisher MetaphorReplication MethodsReplication MonitorSQL Server Integration Services (SSIS)ETLRequirementsBusiness Intelligence Development Studio (BIDS)PackagesDeploying PackagesOptimizing an SSIS SolutionBulk Imports and ExportsThe bcp UtilityBULK INSERT StatementOPENROWSET and OPENDATASOURCEThe Import/Export WizardAdditional StudySummary of What You Need to KnowQuestionsAnswers
Appendix A About the Download
Steps to Download MasterExamSystem RequirementsMasterExamCertCamsScriptsHelpRemoving InstallationTechnical SupportLearnKey Technical Support
Appendix B Exam 70-431: What You Need to Know to Pass
IntroductionPicking a DateWhat You Can ExpectWhat's Expected of YouChapters Covering 70-431 Topics
Appendix C Inside the Design Exam
The Design ExamDesign Exam Success TipsScenario TopicsSample Scenario QuestionsSample Scenario: MCITPSuccess CorporationSample Scenario AnswersFinal Notes
Appendix D Exam 70-441: What You Need to Know to Pass
IntroductionWhat You Can ExpectWhat's Expected of YouChapters Covering 70-441 Topics
Appendix E Exam 70-442: What You Need to Know to Pass
IntroductionWhat You Can ExpectWhat's Expected of YouChapters Covering 70-442 Topics
Appendix F SQL Database Design Object Summary
Glossary
Index

Overview

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

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

MCITP SQL Server 2005 Database Administration All-in-One Exam Guide (Exams 70-431, 70-443, & 70-444)

Publisher Resources

ISBN: 9780071546690

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills