book

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

Name: Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
Author: Dejan Sarka Itzik Ben-Gan and Ron Talmage
ISBN: 9780735667259

by Dejan Sarka Itzik Ben-Gan and Ron Talmage

December 2012

Intermediate to advanced

752 pages

22h 12m

English

Microsoft Press

Read now

Unlock full access

Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012
Contents
Introduction
System RequirementsSQL Server Software and Data RequirementsHardware and Operating System RequirementsUsing the Companion CDHow to Install the Practice TestsHow to Use the Practice TestsHow to Uninstall the Practice TestsAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in TouchPreparing for the Exam
1. Foundations of Querying
Before You BeginLesson 1: Understanding the Foundations of T-SQLEvolution of T-SQLUsing T-SQL in a Relational WayUsing Correct TerminologyPractice Using T-SQL in a Relational WayPractice Using T-SQL in a Relational WayLesson SummaryLesson ReviewLesson 2: Understanding Logical Query ProcessingT-SQL As a Declarative English-Like LanguageLogical Query Processing Phases1. Evaluate the FROM Clause2. Filter Rows Based on the WHERE Clause3. Group Rows Based on the GROUP BY Clause4. Filter Rows Based on the HAVING Clause5. Process the SELECT Clause6. Handle Presentation OrderingPractice Logical Query ProcessingLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Importance of TheoryCase Scenario 2: Interviewing for a Code Reviewer PositionSuggested PracticesVisit T-SQL Public Newsgroups and Review CodeDescribe Logical Query ProcessingAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
2. Getting Started with the SELECT Statement
Before You BeginLesson 1: Using the FROM and SELECT ClausesThe FROM ClauseThe SELECT ClauseDelimiting IdentifiersPractice Using the FROM and SELECT ClausesPractice Using the FROM and SELECT ClausesLesson SummaryLesson ReviewLesson 2: Working with Data Types and Built-in FunctionsChoosing the Appropriate Data TypeChoosing a Data Type for KeysDate and Time FunctionsCurrent Date and TimeDate and Time PartsAdd and DiffOffsetCharacter FunctionsConcatenationSubstring Extraction and PositionString LengthString AlterationString FormattingCASE Expression and Related FunctionsPractice Working with Data Types and Built-in FunctionsPractice Working with Data Types and Built-in FunctionsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Reviewing the Use of TypesCase Scenario 2: Reviewing the Use of FunctionsSuggested PracticesAnalyze the Data Types in the Sample DatabaseAnalyze Code Samples in Books Online for SQL Server 2012AnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
3. Filtering and Sorting Data
Before You BeginLesson 1: Filtering Data with PredicatesPredicates, Three-Valued Logic, and Search ArgumentsCombining PredicatesFiltering Character DataFiltering Date and Time DataPractice Filtering Data with PredicatesPractice Filtering Data with PredicatesLesson SummaryLesson ReviewLesson 2: Sorting DataUnderstanding When Order Is GuaranteedUsing the ORDER BY Clause to Sort DataPractice Sorting DataPractice Sorting DataLesson SummaryLesson ReviewLesson 3: Filtering Data with TOP and OFFSET-FETCHFiltering Data with TOPFiltering Data with OFFSET-FETCHPractice Filtering Data with TOP and OFFSET-FETCHPractice Filtering Data with TOP and OFFSET-FETCHLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Filtering and Sorting Performance RecommendationsCase Scenario 2: Tutoring a Junior DeveloperSuggested PracticesIdentify Logical Query Processing Phases and Compare FiltersUnderstand DeterminismAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
4. Combining Sets
Before You BeginLesson 1: Using JoinsCross JoinsInner JoinsOuter JoinsMulti-Join QueriesPractice Using JoinsLesson SummaryLesson ReviewLesson 2: Using Subqueries, Table Expressions, and the APPLY OperatorSubqueriesSelf-Contained SubqueriesCorrelated SubqueriesTable ExpressionsDerived TablesCTEsViews and Inline Table-Valued FunctionsAPPLYCROSS APPLYOUTER APPLYPractice Using Subqueries, Table Expressions, and the APPLY OperatorLesson SummaryLesson ReviewLesson 3: Using Set OperatorsUNION and UNION ALLINTERSECTEXCEPTPractice Using Set OperatorsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Code ReviewCase Scenario 2: Explaining Set OperatorsSuggested PracticesCombine SetsAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
5. Grouping and Windowing
Before You BeginLesson 1: Writing Grouped QueriesWorking with a Single Grouping SetWorking with Multiple Grouping SetsPractice Writing Grouped QueriesPractice Writing Grouped QueriesLesson SummaryLesson ReviewLesson 2: Pivoting and Unpivoting DataPivoting DataUnpivoting DataPractice Pivoting DataPractice Pivoting DataLesson SummaryLesson ReviewLesson 3: Using Window FunctionsWindow Aggregate FunctionsWindow Ranking FunctionsWindow Offset FunctionsPractice Using Window FunctionsPractice Using Window FunctionsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Improving Data Analysis OperationsCase Scenario 2: Interviewing for a Developer PositionSuggested PracticesLogical Query ProcessingAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
6. Querying Full-Text Data
Before You BeginLesson 1: Creating Full-Text Catalogs and IndexesFull-Text Search ComponentsCreating and Managing Full-Text Catalogs and IndexesPractice Creating a Full-Text IndexPractice Creating a Full-Text IndexLesson SummaryLesson ReviewLesson 2: Using the CONTAINS and FREETEXT PredicatesThe CONTAINS PredicateThe FREETEXT PredicatePractice Using the CONTAINS and FREETEXT PredicatesPractice Using the CONTAINS and FREETEXT PredicatesLesson SummaryLesson ReviewLesson 3: Using the Full-Text and Semantic Search Table-Valued FunctionsUsing the Full-Text Search FunctionsUsing the Semantic Search FunctionsPractice Using the Full-Text and Semantic Search FunctionsPractice Using the Full-Text and Semantic Search FunctionsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Enhancing the SearchesCase Scenario 2: Using the Semantic SearchSuggested PracticesCheck the FTS Dynamic Management Views and Backup and Restore of a Full-Text Catalog and IndexesAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
7. Querying and Managing XML Data
Before You BeginLesson 1: Returning Results As XML with FOR XMLIntroduction to XMLProducing XML from Relational DataFOR XML RAWFOR XML AUTOFOR XML PATHShredding XML to TablesPractice Using the FOR XML ClausePractice Using the FOR XML ClauseLesson SummaryLesson ReviewLesson 2: Querying XML Data with XQueryXQuery BasicsXQuery Data TypesXQuery FunctionsNavigationPredicatesFLWOR ExpressionsPractice Using XQuery/XPath NavigationPractice Using XQuery/XPath NavigationLesson SummaryLesson ReviewLesson 3: Using the XML Data TypeWhen to Use the XML Data TypeXML Data Type MethodsUsing the XML Data Type for Dynamic SchemaXML IndexesPractice Using XML Data Type MethodsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Reports from XML DataCase Scenario 2: Dynamic SchemaSuggested PracticesQuery XML DataAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2

8. Creating Tables and Enforcing Data Integrity
Before You BeginLesson 1: Creating and Altering TablesIntroductionCreating a TableSpecifying a Database SchemaNaming Tables and ColumnsChoosing Column Data TypesNULL and Default ValuesThe Identity Property and Sequence NumbersComputed ColumnsTable CompressionAltering a TableChoosing Table IndexesPractice Creating and Altering TablesPractice Creating and Altering TablesLesson SummaryLesson ReviewLesson 2: Enforcing Data IntegrityUsing ConstraintsPrimary Key ConstraintsUnique ConstraintsForeign Key ConstraintsCheck ConstraintsDefault ConstraintsPractice Enforcing Data IntegrityPractice Enforcing Data IntegrityLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Working with Table ConstraintsCase Scenario 2: Working with Unique and Default ConstraintsSuggested PracticesCreate Tables and Enforce Data IntegrityAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
9. Designing and Creating Views, Inline Functions, and Synonyms
Before You BeginLesson 1: Designing and Implementing Views and Inline FunctionsIntroductionViewsDatabase Views SyntaxView OptionsThe SELECT and UNION Statements in a ViewWITH CHECK OPTIONView NamesRestrictions on ViewsIndexed ViewsQuerying from ViewsAltering a ViewDropping a ViewModifying Data Through a ViewPartitioned ViewsViews and MetadataInline FunctionsInline Function OptionsPractice Working with Views and Inline FunctionsLesson SummaryLesson ReviewLesson 2: Using SynonymsCreating a SynonymDropping a SynonymAbstraction LayerSynonyms and References to Nonexisting ObjectsSynonym PermissionsComparing Synonyms with Other Database ObjectsPractice Using SynonymsPractice Using SynonymsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Comparing Views, Inline Functions, and SynonymsCase Scenario 2: Converting Synonyms to Other ObjectsSuggested PracticesDesign and Create Views, Inline Functions, and SynonymsAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
10. Inserting, Updating, and Deleting Data
Before You BeginLesson 1: Inserting DataSample DataINSERT VALUESINSERT SELECTINSERT EXECSELECT INTOPractice Inserting DataPractice Inserting DataLesson SummaryLesson ReviewLesson 2: Updating DataSample DataUPDATE StatementUPDATE Based on JoinNondeterministic UPDATEUPDATE and Table ExpressionsUPDATE Based on a VariableUPDATE All-at-OncePractice Updating DataPractice Updating DataLesson SummaryLesson ReviewLesson 3: Deleting DataSample DataDELETE StatementTRUNCATE StatementDELETE Based on a JoinDELETE Using Table ExpressionsPractice Deleting and Truncating DataPractice Deleting and Truncating DataLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Using Modifications That Support Optimized LoggingCase Scenario 2: Improving a Process That Updates DataSuggested PracticesDELETE vs. TRUNCATEAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
11. Other Data Modification Aspects
Before You BeginLesson 1: Using the Sequence Object and IDENTITY Column PropertyUsing the IDENTITY Column PropertyUsing the Sequence ObjectPractice Using the Sequence ObjectPractice Using the Sequence ObjectLesson SummaryLesson ReviewLesson 2: Merging DataUsing the MERGE StatementPractice Using the MERGE StatementPractice Using the MERGE StatementLesson SummaryLesson ReviewLesson 3: Using the OUTPUT OptionWorking with the OUTPUT ClauseINSERT with OUTPUTDELETE with OUTPUTUPDATE with OUTPUTMERGE with OUTPUTComposable DMLPractice Using the OUTPUT ClausePractice Using the OUTPUT ClauseLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Providing an Improved Solution for Generating KeysCase Scenario 2: Improving ModificationsSuggested PracticesCompare Old and New FeaturesAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
12. Implementing Transactions, Error Handling, and Dynamic SQL
Before You BeginLesson 1: Managing Transactions and ConcurrencyUnderstanding TransactionsACID Properties of TransactionsTypes of TransactionsTransaction CommandsTransaction Levels and StatesTransaction ModesAutocommit ModeAutocommit ModeImplicit Transaction ModeExplicit Transaction ModeNested TransactionsMarking a TransactionAdditional Transaction OptionsBasic LockingLock CompatibilityBlockingDeadlockingTransaction Isolation LevelsPractice Implementing TransactionsPractice Implementing TransactionsLesson SummaryLesson ReviewLesson 2: Implementing Error HandlingDetecting and Raising ErrorsAnalyzing Error MessagesRaiserrorTHROWTRY_CONVERT and TRY_PARSEHandling Errors After DetectionUnstructured Error Handling Using @@ERRORUsing XACT_ABORT with TransactionsStructured Error Handling Using TRY/CATCHTHROW vs. RAISERROR in TRY/CATCHUsing XACT_ABORT with TRY/CATCHPractice: Working with Error HandlingLesson SummaryLesson ReviewLesson 3: Using Dynamic SQLDynamic SQL OverviewUses for Dynamic SQLGenerating T-SQL StringsThe EXECUTE CommandSQL InjectionUsing sp_executesqlPractice Writing and Testing Dynamic SQLPractice Writing and Testing Dynamic SQLLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Implementing Error HandlingCase Scenario 2: Implementing TransactionsSuggested PracticesImplement Error HandlingAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
13. Designing and Implementing T-SQL Routines
Before You BeginLesson 1: Designing and Implementing Stored ProceduresUnderstanding Stored ProceduresTesting for the Existence of a Stored ProcedureStored Procedure ParametersBEGIN/ENDSET NOCOUNT ONRETURN and Return CodesExecuting Stored ProceduresInput ParametersOutput ParametersBranching LogicIF/ELSEWHILEWAITFORGOTODeveloping Stored ProceduresStored Procedure ResultsCalling Other Stored ProceduresStored Procedures and Error HandlingDynamic SQL in Stored ProceduresPractice Implementing Stored ProceduresLesson SummaryLesson ReviewLesson 2: Implementing TriggersDML TriggersAFTER TriggersNested AFTER TriggersINSTEAD OF TriggersDML Trigger FunctionsPractice Writing DML TriggersPractice Writing DML TriggersLesson SummaryLesson ReviewLesson 3: Implementing User-Defined FunctionsUnderstanding User-Defined FunctionsScalar UDFsTable-Valued UDFsAn Inline Table-Valued UDFMultistatement Table-Valued UDFLimitations on UDFsUDF OptionsUDF Performance ConsiderationsPractice Writing User-Defined FunctionsPractice Writing User-Defined FunctionsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Implementing Stored Procedures and UDFsCase Scenario 2: Implementing TriggersSuggested PracticesUse Stored Procedures, Triggers, and UDFsAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
14. Using Tools to Analyze Query Performance
Before You BeginLesson 1: Getting Started with Query OptimizationQuery Optimization Problems and the Query OptimizerSQL Server Extended Events, SQL Trace, and SQL Server ProfilerPractice Using Extended EventsPractice Using Extended EventsLesson SummaryLesson ReviewLesson 2: Using SET Session Options and Analyzing Query PlansSET Session OptionsExecution PlansPractice Using SET Session Options and Execution PlansPractice Using SET Session Options and Execution PlansLesson SummaryLesson ReviewLesson 3: Using Dynamic Management ObjectsIntroduction to Dynamic Management ObjectsThe Most Important DMOs for Query TuningPractice Using Index-Related DMOsPractice Using Index-Related DMOsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Analysis of QueriesCase Scenario 2: Constant MonitoringSuggested PracticesLearn More About Extended Events, Execution Plans, and Dynamic Management ObjectsAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
15. Implementing Indexes and Statistics
Before You BeginLesson 1: Implementing IndexesHeaps and Balanced TreesHeapsClustered IndexesImplementing Nonclustered IndexesImplementing Indexed ViewsPractice Analyzing Nonclustered IndexesPractice Analyzing Nonclustered IndexesLesson SummaryLesson ReviewLesson 2: Using Search ArgumentsSupporting Queries with IndexesSearch ArgumentsPractice Using the OR and AND Logical OperatorsPractice Using the OR and AND Logical OperatorsLesson SummaryLesson ReviewLesson 3: Understanding StatisticsAuto-Created StatisticsManually Maintaining StatisticsPractice Manually Maintaining StatisticsPractice Manually Maintaining StatisticsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Table ScansCase Scenario 2: Slow UpdatesSuggested PracticesLearn More About Indexes and How Statistics Influence Query ExecutionAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
16. Understanding Cursors, Sets, and Temporary Tables
Before You BeginLesson 1: Evaluating the Use of Cursor/Iterative Solutions vs. Set-Based SolutionsThe Meaning of “Set-Based”Iterations for Operations That Must Be Done Per RowCursor vs. Set-Based Solutions for Data Manipulation TasksPractice Evaluating Cursor-Based vs. Set-Based SolutionsPractice Evaluating Cursor-Based vs. Set-Based SolutionsLesson SummaryLesson ReviewLesson 2: Using Temporary Tables vs. Table VariablesScopeDDL and IndexesPhysical Representation in tempdbTransactionsStatisticsPractice Choosing an Optimal Temporary ObjectPractice Choosing an Optimal Temporary ObjectLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Performance Improvement Recommendations for Cursors and Temporary ObjectsCase Scenario 2: Identifying Inaccuracies in AnswersSuggested PracticesIdentify DifferencesAnswersLesson 1Lesson 2Case Scenario 1Case Scenario 2
17. Understanding Further Optimization Aspects
Before You BeginLesson 1: Understanding Plan IteratorsAccess MethodsJoin AlgorithmsOther Plan IteratorsPractice Determining Execution Plan IteratorsPractice Determining Execution Plan IteratorsLesson SummaryLesson ReviewLesson 2: Using Parameterized Queries and Batch OperationsParameterized QueriesBatch ProcessingPractice Working with Query Parameterization and Stored ProceduresPractice Working with Query Parameterization and Stored ProceduresLesson SummaryLesson ReviewLesson 3: Using Optimizer Hints and Plan GuidesOptimizer HintsPlan GuidesPractice Using Optimizer HintsPractice Using Optimizer HintsLesson SummaryLesson ReviewCase ScenariosCase Scenario 1: Query OptimizationCase Scenario 2: Table HintSuggested PracticesAnalyze Execution Plans and Force PlansAnswersLesson 1Lesson 2Lesson 3Case Scenario 1Case Scenario 2
Index
About the Authors
Copyright

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

About the Authors

Itzik Ben-Gan is a mentor and cofounder of SolidQ. A Microsoft MVP for SQL Server since 1999, Ben-Gan teaches and consults internationally on T-SQL querying, programming, and query tuning. He is the author of Microsoft SQL Server T-SQL Fundamentals and coauthor of Inside Microsoft SQL Server: T-SQL Querying and Inside Microsoft SQL Server:T-SQL Programming. He has written numerous articles for SQL Server Magazine and MSDN, and speaks at industry events such as Microsoft TechEd, DevWeek, PASS, and SQL Server Connections.

Dejan Sarka is a mentor with SolidQ and focuses on development of database and business intelligence applications. He is a frequent speaker at international conferences such as TechEd, SqlDevCon, and PASS. He is ...

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

Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 Databases

Publisher Resources

ISBN: 9780735667259Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

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

by Dejan Sarka Itzik Ben-Gan and Ron Talmage

About the Authors

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.