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

Content preview from Microsoft® SQL Server® 2012 T-SQL Fundamentals

ISBN: 978-0-735-65814-1

Fifth Printing: March 2015

Printed and bound in the United States of America.

Microsoft Press books are available through booksellers and distributors worldwide. If you need support related to this book, email Microsoft Press Book Support at mspinput@microsoft.com. Please tell us what you think of this book at http://www.microsoft.com/learning/booksurvey.

Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the Microsoft group of companies. ...

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

Microsoft® SQL Server® 2008 T-SQL Fundamentals

Itzik Ben-Gan

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

Lubor Kollar Itzik Ben-Gan Dejan Sarka, and Steve Kass

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

Dejan Sarka Itzik Ben-Gan Greg Low, Roger Wolter, Ed Katibah, and Isaac Kunen

Microsoft® SQL Server® 2012 High-Performance T-SQL Using Window Functions

Itzik Ben-Gan

Publisher Resources

ISBN: 9780735673939Purchase book