book

Microsoft® SQL Server® 2012 T-SQL Fundamentals

by Itzik Ben-Gan

July 2012

Intermediate to advanced

448 pages

12h 37m

English

Microsoft Press

Read now

Unlock full access

Who Should Read This BookAssumptionsWho Should Not Read This BookOrganization of This BookSystem RequirementsCode SamplesAcknowledgmentsErrata & Book SupportWe Want to Hear from YouStay in Touch
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
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
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
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
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
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

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

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