book

Microsoft® SQL Server® 2008 T-SQL Fundamentals

Name: Microsoft® SQL Server® 2008 T-SQL Fundamentals
Author: Itzik Ben-Gan
ISBN: 9780735626010

by Itzik Ben-Gan

October 2008

Intermediate to advanced

416 pages

11h 59m

English

Microsoft Press

Read now

Unlock full access

Acknowledgments
Introduction
Who This Book Is ForWhat This Book Is AboutCompanion ContentHardware and Software RequirementsFind Additional Content OnlineSupport for This BookQuestions and Comments
1. Background to T-SQL Querying and Programming
Theoretical BackgroundSQLSet TheoryPredicate LogicThe Relational ModelPropositions, Predicates, and RelationsMissing ValuesConstraintsNormalization1NF2NF3NFThe Data Life CycleOnLine Transactional ProcessingData WarehouseOnLine Analytical ProcessingData MiningSQL Server ArchitectureSQL 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 OptionThe OVER ClausePredicates and OperatorsCASE ExpressionsNULLsAll-At-Once OperationsWorking with Character DataData TypesCollationOperators and FunctionsString Concatenation (Plus Sign [+] Operator)The SUBSTRING FunctionSyntaxThe LEFT and RIGHT FunctionsSyntaxThe LEN and DATALENGTH FunctionsSyntaxThe CHARINDEX FunctionSyntaxThe PATINDEX FunctionSyntaxThe REPLACE FunctionSyntaxThe REPLICATE FunctionSyntaxThe STUFF FunctionSyntaxThe UPPER and LOWER FunctionsSyntaxThe RTRIM and LTRIM FunctionsSyntaxThe 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 and CONVERT FunctionsSyntaxThe SWITCHOFFSET FunctionSyntaxThe TODATETIMEOFFSET FunctionSyntaxThe DATEADD FunctionSyntaxThe DATEDIFF FunctionSyntaxThe DATEPART FunctionSyntaxThe YEAR, MONTH and DAY FunctionsSyntaxThe DATENAME FunctionSyntaxThe ISDATE FunctionSyntaxQuerying MetadataCatalog ViewsInformation Schema ViewsSystem Stored Procedures and FunctionsConclusionExercisesSolutions
3. Joins
Cross JoinsANSI SQL-92 SyntaxANSI SQL-89 SyntaxSelf Cross JoinsProducing Tables of NumbersInner JoinsANSI SQL-92 SyntaxANSI SQL-89 SyntaxInner Join SafetyFurther Join ExamplesComposite JoinsNon-Equi JoinsMulti-Table JoinsOuter 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-Table JoinUsing the COUNT Aggregate with Outer JoinsConclusionExercisesSolutions
4. Subqueries
Self-Contained SubqueriesSelf-Contained Scalar Subquery ExamplesSelf-Contained Multi-Valued Subquery ExamplesCorrelated SubqueriesThe EXISTS PredicateBeyond the Fundamentals of SubqueriesReturning Previous or Next ValuesRunning AggregatesMisbehaving SubqueriesNULL TroubleSubstitution Error in a Subquery Column NameConclusionExercisesSolutions
5. Table Expressions
Derived TablesAssigning Column AliasesUsing ArgumentsNestingMultiple ReferencesCommon Table ExpressionsAssigning Column AliasesUsing ArgumentsDefining Multiple CTEsMultiple ReferencesRecursive CTEsViewsViews and the ORDER BY ClauseView OptionsThe ENCRYPTION OptionThe SCHEMABINDING OptionThe Option CHECK OPTIONInline Table-Valued FunctionsThe APPLY OperatorConclusionExercisesSolutions
6. Set Operations
The UNION Set OperationThe UNION ALL Set OperationThe UNION DISTINCT Set OperationThe INTERSECT Set OperationThe INTERSECT DISTINCT Set OperationThe INTERSECT ALL Set OperationThe EXCEPT Set OperationThe EXCEPT DISTINCT Set OperationThe EXCEPT ALL Set OperationPrecedenceCircumventing Unsupported Logical PhasesConclusionExercisesSolutions
7. Pivot, Unpivot, and Grouping Sets
Pivoting 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 FunctionsConclusionExercisesSolutions
8. Data Modification
Inserting DataThe INSERT VALUES StatementThe INSERT SELECT StatementThe INSERT EXEC StatementThe SELECT INTO StatementThe BULK INSERT StatementThe IDENTITY PropertyDeleting DataThe DELETE StatementThe TRUNCATE StatementDELETE Based on a JoinUpdating DataThe UPDATE StatementUPDATE Based on a JoinAssignment UPDATEMerging DataModifying Data Through Table ExpressionsModifications with the TOP OptionThe OUTPUT ClauseINSERT with OUTPUTDELETE with OUTPUTUPDATE with OUTPUTMERGE with OUTPUTComposable DMLConclusionExercisesSolutions

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 LevelSnapshot Isolation LevelsThe SNAPSHOT Isolation LevelConflict DetectionThe READ COMMITTED SNAPSHOT Isolation LevelSummary of Isolation LevelsDeadlocksConclusionExercises
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
Installing 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
Itzik Ben-Gan
Index
About the Author
Copyright

Overview

Master the foundations of T-SQL with the right balance of conceptual and practical content. Get hands-on guidance—including exercises and code samples—that show you how to develop code to query and modify data. You’ll gain a solid understanding of the T-SQL language and good programming practices, and learn to write more efficient and powerful queries.

Discover how to:

Apply T-SQL fundamentals, create tables, and define data integrity Understand logical query processing Query multiple tables using joins and subqueries Simplify code and improve maintainability with table expressions Explore pivoting techniques and how to handle grouping sets Write code that modifies data Isolate inconsistent data and address deadlock and blocking scenarios

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

Inside Microsoft® SQL Server® 2008: T-SQL Querying

Publisher Resources

ISBN: 9780735626010Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills