book

Microsoft® SQL Server® 2012 T-SQL Fundamentals

Name: Microsoft® SQL Server® 2012 T-SQL Fundamentals
Author: Itzik Ben-Gan
ISBN: 9780735673939

by Itzik Ben-Gan

July 2012

Intermediate to advanced

448 pages

12h 37m

English

Microsoft Press

Read now

Unlock full access

Table of Contents
Dedication
Foreword
Introduction
Who Should Read This BookAssumptionsWho Should Not Read This BookOrganization of This BookSystem RequirementsCode SamplesAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in Touch
1. Background to T-SQL Querying and Programming
Theoretical BackgroundSQLSet TheoryPredicate LogicThe Relational ModelPropositions, Predicates, and RelationsMissing ValuesConstraintsNormalizationThe Data Life CycleOnline Transactional ProcessingData WarehousesThe Business Intelligence Semantic ModelData MiningSQL Server ArchitectureThe ABC Flavors of SQL ServerApplianceBoxCloudSQL Server InstancesDatabasesSchemas and ObjectsCreating Tables and Defining Data IntegrityCreating TablesDefining Data IntegrityPrimary Key ConstraintsUnique ConstraintsForeign Key ConstraintsCheck ConstraintsDefault ConstraintsConclusion
2. Single-Table Queries
Elements of the SELECT StatementThe FROM ClauseThe WHERE ClauseThe GROUP BY ClauseThe HAVING ClauseThe SELECT ClauseThe ORDER BY ClauseThe TOP and OFFSET-FETCH FiltersThe TOP FilterThe OFFSET-FETCH FilterA Quick Look at Window FunctionsPredicates and OperatorsCASE ExpressionsNULL MarksAll-at-Once OperationsWorking with Character DataData TypesCollationOperators and FunctionsString Concatenation (Plus Sign [+] Operator and CONCAT Function)The SUBSTRING FunctionThe LEFT and RIGHT FunctionsThe LEN and DATALENGTH FunctionsThe CHARINDEX FunctionThe PATINDEX FunctionThe REPLACE FunctionThe REPLICATE FunctionThe STUFF FunctionThe UPPER and LOWER FunctionsThe RTRIM and LTRIM FunctionsThe FORMAT FunctionThe LIKE PredicateThe % (Percent) WildcardThe _ (Underscore) WildcardThe [<List of Characters>] WildcardThe [<Character>-<Character>] WildcardThe [^Character List or Range>] WildcardThe ESCAPE CharacterWorking with Date and Time DataDate and Time Data TypesLiteralsWorking with Date and Time SeparatelyFiltering Date RangesDate and Time FunctionsCurrent Date and TimeThe CAST, CONVERT, and PARSE Functions and Their TRY_ CounterpartsThe SWITCHOFFSET FunctionThe TODATETIMEOFFSET FunctionThe DATEADD FunctionThe DATEDIFF FunctionThe DATEPART FunctionThe YEAR, MONTH, and DAY FunctionsThe DATENAME FunctionThe ISDATE FunctionThe FROMPARTS FunctionsThe EOMONTH FunctionQuerying MetadataCatalog ViewsInformation Schema ViewsSystem Stored Procedures and FunctionsConclusionExercises12345678Solutions12345678
3. Joins
Cross JoinsANSI SQL-92 SyntaxANSI SQL-89 SyntaxSelf Cross JoinsProducing Tables of NumbersInner JoinsANSI SQL-92 SyntaxANSI SQL-89 SyntaxInner Join SafetyMore Join ExamplesComposite JoinsNon-Equi JoinsMulti-Join QueriesOuter JoinsFundamentals of Outer JoinsBeyond the Fundamentals of Outer JoinsIncluding Missing ValuesFiltering Attributes from the Nonpreserved Side of an Outer JoinUsing Outer Joins in a Multi-Join QueryUsing the COUNT Aggregate with Outer JoinsConclusionExercises1-11-2 (Optional, Advanced)23456 (Optional, Advanced)7 (Optional, Advanced)Solutions1-11-2234567
4. Subqueries
Self-Contained SubqueriesSelf-Contained Scalar Subquery ExamplesSelf-Contained Multivalued Subquery ExamplesCorrelated SubqueriesThe EXISTS PredicateBeyond the Fundamentals of SubqueriesReturning Previous or Next ValuesUsing Running AggregatesDealing with Misbehaving SubqueriesNULL TroubleSubstitution Errors in Subquery Column NamesConclusionExercises12 (Optional, Advanced)34567 (Optional, Advanced)8 (Optional, Advanced)Solutions12345678
5. Table Expressions
Derived TablesAssigning Column AliasesUsing ArgumentsNestingMultiple ReferencesCommon Table ExpressionsAssigning Column Aliases in CTEsUsing Arguments in CTEsDefining Multiple CTEsMultiple References in CTEsRecursive CTEsViewsViews and the ORDER BY ClauseView OptionsThe ENCRYPTION OptionThe SCHEMABINDING OptionThe CHECK OPTION OptionInline Table-Valued FunctionsThe APPLY OperatorConclusionExercises1-11-22-12-23 (Optional, Advanced)4-14-2 (Optional, Advanced)5-15-2Solutions1-11-22-12-234-14-25-15-2
6. Set Operators
The UNION OperatorThe UNION ALL Multiset OperatorThe UNION Distinct Set OperatorThe INTERSECT OperatorThe INTERSECT Distinct Set OperatorThe INTERSECT ALL Multiset OperatorThe EXCEPT OperatorThe EXCEPT Distinct Set OperatorThe EXCEPT ALL Multiset OperatorPrecedenceCircumventing Unsupported Logical PhasesConclusionExercises12345 (Optional, Advanced)Solutions12345

7. Beyond the Fundamentals of Querying
Window FunctionsRanking Window FunctionsOffset Window FunctionsAggregate Window FunctionsPivoting DataPivoting with Standard SQLPivoting with the Native T-SQL PIVOT OperatorUnpivoting DataUnpivoting with Standard SQLUnpivoting with the Native T-SQL UNPIVOT OperatorGrouping SetsThe GROUPING SETS SubclauseThe CUBE SubclauseThe ROLLUP SubclauseThe GROUPING and GROUPING_ID FunctionsConclusionExercises12345Solutions12345
8. Data Modification
Inserting DataThe INSERT VALUES StatementThe INSERT SELECT StatementThe INSERT EXEC StatementThe SELECT INTO StatementThe BULK INSERT StatementThe Identity Property and the Sequence ObjectIdentitySequenceDeleting DataThe DELETE StatementThe TRUNCATE StatementDELETE Based on a JoinUpdating DataThe UPDATE StatementUPDATE Based on a JoinAssignment UPDATEMerging DataModifying Data Through Table ExpressionsModifications with TOP and OFFSET-FETCHThe OUTPUT ClauseINSERT with OUTPUTDELETE with OUTPUTUPDATE with OUTPUTMERGE with OUTPUTComposable DMLConclusionExercises11-11-21-323456Solutions1-11-21-32345
9. Transactions and Concurrency
TransactionsLocks and BlockingLocksLock Modes and CompatibilityLockable Resource TypesTroubleshooting BlockingIsolation LevelsThe READ UNCOMMITTED Isolation LevelThe READ COMMITTED Isolation LevelThe REPEATABLE READ Isolation LevelThe SERIALIZABLE Isolation LevelIsolation Levels Based on Row VersioningThe SNAPSHOT Isolation LevelConflict DetectionThe READ COMMITTED SNAPSHOT Isolation LevelSummary of Isolation LevelsDeadlocksConclusionExercises1-11-21-31-41-51-62-12-1a2-1b2-1c2-1d2-22-2a2-2b2-2c2-2d2-2e2-32-3a2-3b2-3c2-3d2-3e2-42-4a2-4b2-4c2-4d2-4e2-4f2-52-5a2-5b2-5c2-5d2-5e2-5f2-5g2-62-6a2-6b2-6c2-6d2-6e2-6f2-6g2-6h3-13-23-33-43-53-63-7
10. Programmable Objects
VariablesBatchesA Batch As a Unit of ParsingBatches and VariablesStatements That Cannot Be Combined in the Same BatchA Batch As a Unit of ResolutionThe GO n OptionFlow ElementsThe IF … ELSE Flow ElementThe WHILE Flow ElementAn Example of Using IF and WHILECursorsTemporary TablesLocal Temporary TablesGlobal Temporary TablesTable VariablesTable TypesDynamic SQLThe EXEC CommandThe sp_executesql Stored ProcedureUsing PIVOT with Dynamic SQLRoutinesUser-Defined FunctionsStored ProceduresTriggersDML TriggersDDL TriggersError HandlingConclusion
A. Getting Started
Getting Started with SQL DatabaseInstalling an On-Premises Implementation of SQL Server1. Obtain SQL Server2. Create a User Account3. Install Prerequisites4. Install the Database Engine, Documentation, and ToolsDownloading Source Code and Installing the Sample DatabaseWorking with SQL Server Management StudioWorking with SQL Server Books Online
Index
About the Author
Copyright

Overview

Gain a solid understanding of T-SQL—and write better queries

Master the fundamentals of Transact-SQL—and develop your own code for querying and modifying data in Microsoft SQL Server 2012. Led by a SQL Server expert, you’ll learn the concepts behind T-SQL querying and programming, and then apply your knowledge with exercises in each chapter. Once you understand the logic behind T-SQL, you’ll quickly learn how to write effective code—whether you’re a programmer or database administrator.

Discover how to:

Work with programming practices unique to T-SQL

Create database tables and define data integrity

Query multiple tables using joins and subqueries

Simplify code and improve maintainability with table expressions

Implement insert, update, delete, and merge data modification strategies

Tackle advanced techniques such as window functions, pivoting and grouping sets

Control data consistency using isolation levels, and mitigate deadlocks and blocking

Take T-SQL to the next level with programmable objects

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

Microsoft® SQL Server® 2008 T-SQL Fundamentals

Publisher Resources

ISBN: 9780735673939Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills