Microsoft® SQL Server® 2012 T-SQL Fundamentals

Microsoft® SQL Server® 2012 T-SQL Fundamentals

by Itzik Ben-Gan
Released July 2012
Publisher(s): Microsoft Press
ISBN: 9780735673939

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

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

    •

    Table of contents

    1. Table of Contents
    2. Dedication
    3. Foreword
    4. Introduction
      1. Who Should Read This Book
        1. Assumptions
      2. Who Should Not Read This Book
      3. Organization of This Book
      4. System Requirements
      5. Code Samples
      6. Acknowledgments
      7. Errata & Book Support
      8. We Want to Hear from You
      9. Stay in Touch
    5. 1. Background to T-SQL Querying and Programming
      1. Theoretical Background
        1. SQL
        2. Set Theory
        3. Predicate Logic
        4. The Relational Model
          1. Propositions, Predicates, and Relations
          2. Missing Values
          3. Constraints
          4. Normalization
        5. The Data Life Cycle
          1. Online Transactional Processing
          2. Data Warehouses
          3. The Business Intelligence Semantic Model
          4. Data Mining
      2. SQL Server Architecture
        1. The ABC Flavors of SQL Server
          1. Appliance
          2. Box
          3. Cloud
        2. SQL Server Instances
        3. Databases
        4. Schemas and Objects
      3. Creating Tables and Defining Data Integrity
        1. Creating Tables
        2. Defining Data Integrity
          1. Primary Key Constraints
          2. Unique Constraints
          3. Foreign Key Constraints
          4. Check Constraints
          5. Default Constraints
      4. Conclusion
    6. 2. Single-Table Queries
      1. Elements of the SELECT Statement
        1. The FROM Clause
        2. The WHERE Clause
        3. The GROUP BY Clause
        4. The HAVING Clause
        5. The SELECT Clause
        6. The ORDER BY Clause
        7. The TOP and OFFSET-FETCH Filters
          1. The TOP Filter
          2. The OFFSET-FETCH Filter
        8. A Quick Look at Window Functions
      2. Predicates and Operators
      3. CASE Expressions
      4. NULL Marks
      5. All-at-Once Operations
      6. Working with Character Data
        1. Data Types
        2. Collation
        3. Operators and Functions
          1. String Concatenation (Plus Sign [+] Operator and CONCAT Function)
          2. The SUBSTRING Function
          3. The LEFT and RIGHT Functions
          4. The LEN and DATALENGTH Functions
          5. The CHARINDEX Function
          6. The PATINDEX Function
          7. The REPLACE Function
          8. The REPLICATE Function
          9. The STUFF Function
          10. The UPPER and LOWER Functions
          11. The RTRIM and LTRIM Functions
          12. The FORMAT Function
        4. The LIKE Predicate
          1. The % (Percent) Wildcard
          2. The _ (Underscore) Wildcard
          3. The [<List of Characters>] Wildcard
          4. The [<Character>-<Character>] Wildcard
          5. The [^Character List or Range>] Wildcard
          6. The ESCAPE Character
      7. Working with Date and Time Data
        1. Date and Time Data Types
        2. Literals
        3. Working with Date and Time Separately
        4. Filtering Date Ranges
        5. Date and Time Functions
          1. Current Date and Time
          2. The CAST, CONVERT, and PARSE Functions and Their TRY_ Counterparts
          3. The SWITCHOFFSET Function
          4. The TODATETIMEOFFSET Function
          5. The DATEADD Function
          6. The DATEDIFF Function
          7. The DATEPART Function
          8. The YEAR, MONTH, and DAY Functions
          9. The DATENAME Function
          10. The ISDATE Function
          11. The FROMPARTS Functions
          12. The EOMONTH Function
      8. Querying Metadata
        1. Catalog Views
        2. Information Schema Views
        3. System Stored Procedures and Functions
      9. Conclusion
      10. Exercises
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
        6. 6
        7. 7
        8. 8
      11. Solutions
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
        6. 6
        7. 7
        8. 8
    7. 3. Joins
      1. Cross Joins
        1. ANSI SQL-92 Syntax
        2. ANSI SQL-89 Syntax
        3. Self Cross Joins
        4. Producing Tables of Numbers
      2. Inner Joins
        1. ANSI SQL-92 Syntax
        2. ANSI SQL-89 Syntax
        3. Inner Join Safety
      3. More Join Examples
        1. Composite Joins
        2. Non-Equi Joins
        3. Multi-Join Queries
      4. Outer Joins
        1. Fundamentals of Outer Joins
        2. Beyond the Fundamentals of Outer Joins
          1. Including Missing Values
          2. Filtering Attributes from the Nonpreserved Side of an Outer Join
          3. Using Outer Joins in a Multi-Join Query
          4. Using the COUNT Aggregate with Outer Joins
      5. Conclusion
      6. Exercises
        1. 1-1
        2. 1-2 (Optional, Advanced)
        3. 2
        4. 3
        5. 4
        6. 5
        7. 6 (Optional, Advanced)
        8. 7 (Optional, Advanced)
      7. Solutions
        1. 1-1
        2. 1-2
        3. 2
        4. 3
        5. 4
        6. 5
        7. 6
        8. 7
    8. 4. Subqueries
      1. Self-Contained Subqueries
        1. Self-Contained Scalar Subquery Examples
        2. Self-Contained Multivalued Subquery Examples
      2. Correlated Subqueries
        1. The EXISTS Predicate
      3. Beyond the Fundamentals of Subqueries
        1. Returning Previous or Next Values
        2. Using Running Aggregates
        3. Dealing with Misbehaving Subqueries
          1. NULL Trouble
          2. Substitution Errors in Subquery Column Names
      4. Conclusion
      5. Exercises
        1. 1
        2. 2 (Optional, Advanced)
        3. 3
        4. 4
        5. 5
        6. 6
        7. 7 (Optional, Advanced)
        8. 8 (Optional, Advanced)
      6. Solutions
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
        6. 6
        7. 7
        8. 8
    9. 5. Table Expressions
      1. Derived Tables
        1. Assigning Column Aliases
        2. Using Arguments
        3. Nesting
        4. Multiple References
      2. Common Table Expressions
        1. Assigning Column Aliases in CTEs
        2. Using Arguments in CTEs
        3. Defining Multiple CTEs
        4. Multiple References in CTEs
        5. Recursive CTEs
      3. Views
        1. Views and the ORDER BY Clause
        2. View Options
          1. The ENCRYPTION Option
          2. The SCHEMABINDING Option
          3. The CHECK OPTION Option
      4. Inline Table-Valued Functions
      5. The APPLY Operator
      6. Conclusion
      7. Exercises
        1. 1-1
        2. 1-2
        3. 2-1
        4. 2-2
        5. 3 (Optional, Advanced)
        6. 4-1
        7. 4-2 (Optional, Advanced)
        8. 5-1
        9. 5-2
      8. Solutions
        1. 1-1
        2. 1-2
        3. 2-1
        4. 2-2
        5. 3
        6. 4-1
        7. 4-2
        8. 5-1
        9. 5-2
    10. 6. Set Operators
      1. The UNION Operator
        1. The UNION ALL Multiset Operator
        2. The UNION Distinct Set Operator
      2. The INTERSECT Operator
        1. The INTERSECT Distinct Set Operator
        2. The INTERSECT ALL Multiset Operator
      3. The EXCEPT Operator
        1. The EXCEPT Distinct Set Operator
        2. The EXCEPT ALL Multiset Operator
      4. Precedence
      5. Circumventing Unsupported Logical Phases
      6. Conclusion
      7. Exercises
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5 (Optional, Advanced)
      8. Solutions
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
    11. 7. Beyond the Fundamentals of Querying
      1. Window Functions
        1. Ranking Window Functions
        2. Offset Window Functions
        3. Aggregate Window Functions
      2. Pivoting Data
        1. Pivoting with Standard SQL
        2. Pivoting with the Native T-SQL PIVOT Operator
      3. Unpivoting Data
        1. Unpivoting with Standard SQL
        2. Unpivoting with the Native T-SQL UNPIVOT Operator
      4. Grouping Sets
        1. The GROUPING SETS Subclause
        2. The CUBE Subclause
        3. The ROLLUP Subclause
        4. The GROUPING and GROUPING_ID Functions
      5. Conclusion
      6. Exercises
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
      7. Solutions
        1. 1
        2. 2
        3. 3
        4. 4
        5. 5
    12. 8. Data Modification
      1. Inserting Data
        1. The INSERT VALUES Statement
        2. The INSERT SELECT Statement
        3. The INSERT EXEC Statement
        4. The SELECT INTO Statement
        5. The BULK INSERT Statement
        6. The Identity Property and the Sequence Object
          1. Identity
          2. Sequence
      2. Deleting Data
        1. The DELETE Statement
        2. The TRUNCATE Statement
        3. DELETE Based on a Join
      3. Updating Data
        1. The UPDATE Statement
        2. UPDATE Based on a Join
        3. Assignment UPDATE
      4. Merging Data
      5. Modifying Data Through Table Expressions
      6. Modifications with TOP and OFFSET-FETCH
      7. The OUTPUT Clause
        1. INSERT with OUTPUT
        2. DELETE with OUTPUT
        3. UPDATE with OUTPUT
        4. MERGE with OUTPUT
        5. Composable DML
      8. Conclusion
      9. Exercises
        1. 1
        2. 1-1
        3. 1-2
        4. 1-3
        5. 2
        6. 3
        7. 4
        8. 5
        9. 6
      10. Solutions
        1. 1-1
        2. 1-2
        3. 1-3
        4. 2
        5. 3
        6. 4
        7. 5
    13. 9. Transactions and Concurrency
      1. Transactions
      2. Locks and Blocking
        1. Locks
          1. Lock Modes and Compatibility
          2. Lockable Resource Types
        2. Troubleshooting Blocking
      3. Isolation Levels
        1. The READ UNCOMMITTED Isolation Level
        2. The READ COMMITTED Isolation Level
        3. The REPEATABLE READ Isolation Level
        4. The SERIALIZABLE Isolation Level
        5. Isolation Levels Based on Row Versioning
          1. The SNAPSHOT Isolation Level
          2. Conflict Detection
          3. The READ COMMITTED SNAPSHOT Isolation Level
        6. Summary of Isolation Levels
      4. Deadlocks
      5. Conclusion
      6. Exercises
        1. 1-1
        2. 1-2
        3. 1-3
        4. 1-4
        5. 1-5
        6. 1-6
        7. 2-1
          1. 2-1a
          2. 2-1b
          3. 2-1c
          4. 2-1d
        8. 2-2
          1. 2-2a
          2. 2-2b
          3. 2-2c
          4. 2-2d
          5. 2-2e
        9. 2-3
          1. 2-3a
          2. 2-3b
          3. 2-3c
          4. 2-3d
          5. 2-3e
        10. 2-4
          1. 2-4a
          2. 2-4b
          3. 2-4c
          4. 2-4d
          5. 2-4e
          6. 2-4f
        11. 2-5
          1. 2-5a
          2. 2-5b
          3. 2-5c
          4. 2-5d
          5. 2-5e
          6. 2-5f
          7. 2-5g
        12. 2-6
          1. 2-6a
          2. 2-6b
          3. 2-6c
          4. 2-6d
          5. 2-6e
          6. 2-6f
          7. 2-6g
          8. 2-6h
        13. 3-1
        14. 3-2
        15. 3-3
        16. 3-4
        17. 3-5
        18. 3-6
        19. 3-7
    14. 10. Programmable Objects
      1. Variables
      2. Batches
        1. A Batch As a Unit of Parsing
        2. Batches and Variables
        3. Statements That Cannot Be Combined in the Same Batch
        4. A Batch As a Unit of Resolution
        5. The GO n Option
      3. Flow Elements
        1. The IF … ELSE Flow Element
        2. The WHILE Flow Element
        3. An Example of Using IF and WHILE
      4. Cursors
      5. Temporary Tables
        1. Local Temporary Tables
        2. Global Temporary Tables
        3. Table Variables
        4. Table Types
      6. Dynamic SQL
        1. The EXEC Command
        2. The sp_executesql Stored Procedure
        3. Using PIVOT with Dynamic SQL
      7. Routines
        1. User-Defined Functions
        2. Stored Procedures
        3. Triggers
          1. DML Triggers
          2. DDL Triggers
      8. Error Handling
      9. Conclusion
    15. A. Getting Started
      1. Getting Started with SQL Database
      2. Installing an On-Premises Implementation of SQL Server
        1. 1. Obtain SQL Server
        2. 2. Create a User Account
        3. 3. Install Prerequisites
        4. 4. Install the Database Engine, Documentation, and Tools
      3. Downloading Source Code and Installing the Sample Database
      4. Working with SQL Server Management Studio
      5. Working with SQL Server Books Online
    16. Index
    17. About the Author
    18. Copyright

    Product information

    • Title: Microsoft® SQL Server® 2012 T-SQL Fundamentals
    • Author(s): Itzik Ben-Gan
    • Release date: July 2012
    • Publisher(s): Microsoft Press
    • ISBN: 9780735673939