book

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

by Darril Gibson

April 2008

Intermediate to advanced

944 pages

24h 58m

English

McGraw-Hill

Read now

Unlock full access

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
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
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
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

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
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
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
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
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
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
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
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
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
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
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
Steps to Download MasterExamSystem RequirementsMasterExamCertCamsScriptsHelpRemoving InstallationTechnical SupportLearnKey Technical Support
IntroductionPicking a DateWhat You Can ExpectWhat's Expected of YouChapters Covering 70-431 Topics
The Design ExamDesign Exam Success TipsScenario TopicsSample Scenario QuestionsSample Scenario: MCITPSuccess CorporationSample Scenario AnswersFinal Notes
IntroductionWhat You Can ExpectWhat's Expected of YouChapters Covering 70-441 Topics
IntroductionWhat You Can ExpectWhat's Expected of YouChapters Covering 70-442 Topics

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

GLOSSARY

ad hoc query A query that is executed infrequently. Ad hoc queries can sometimes be troublesome because untrained users can create queries that are inefficient, causing the server to suffer significant performance degradation.

aggregate A group such as grouped data. For example, when aggregating data, we are grouping data. A common aggregate function is Avg (average). It looks at a group of data (an aggregate) and provides an average.

alerts, Performance A Performance alert is an alert configured within the operating system Performance Logs and Alerts tool. Performance alerts can be configured to log an entry into the application event log, send a network message, start a performance log, or run a program. They are not directly ...

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.

Start your free trial

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

Publisher Resources

ISBN: 9780071546690

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

by Darril Gibson

GLOSSARY

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.

You might also like

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

Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Mastering SQL Server® 2008

MCITP Administrator: Microsoft® SQL Server™ 2005 Database Server Infrastructure Design Study Guide

Publisher Resources

GLOSSARY

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.

You might also like

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

Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Mastering SQL Server® 2008

MCITP Administrator: Microsoft® SQL Server™ 2005 Database Server Infrastructure Design Study Guide

Publisher Resources

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.