Microsoft® SQL Server® 2008 T-SQL Fundamentals

Microsoft® SQL Server® 2008 T-SQL Fundamentals

by Itzik Ben-Gan
Released October 2008
Publisher(s): Microsoft Press
ISBN: 9780735626010

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

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

Table of contents

  1. Acknowledgments
  2. Introduction
    1. Who This Book Is For
    2. What This Book Is About
    3. Companion Content
    4. Hardware and Software Requirements
    5. Find Additional Content Online
    6. Support for This Book
      1. Questions and Comments
  3. 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
          1. 1NF
          2. 2NF
          3. 3NF
      5. The Data Life Cycle
        1. OnLine Transactional Processing
        2. Data Warehouse
        3. OnLine Analytical Processing
        4. Data Mining
    2. SQL Server Architecture
      1. SQL Server Instances
      2. Databases
      3. 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
  4. 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 Option
      8. The OVER Clause
    2. Predicates and Operators
    3. CASE Expressions
    4. NULLs
    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)
        2. The SUBSTRING Function
          1. Syntax
        3. The LEFT and RIGHT Functions
          1. Syntax
        4. The LEN and DATALENGTH Functions
          1. Syntax
        5. The CHARINDEX Function
          1. Syntax
        6. The PATINDEX Function
          1. Syntax
        7. The REPLACE Function
          1. Syntax
        8. The REPLICATE Function
          1. Syntax
        9. The STUFF Function
          1. Syntax
        10. The UPPER and LOWER Functions
          1. Syntax
        11. The RTRIM and LTRIM Functions
          1. Syntax
      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 and CONVERT Functions
          1. Syntax
        3. The SWITCHOFFSET Function
          1. Syntax
        4. The TODATETIMEOFFSET Function
          1. Syntax
        5. The DATEADD Function
          1. Syntax
        6. The DATEDIFF Function
          1. Syntax
        7. The DATEPART Function
          1. Syntax
        8. The YEAR, MONTH and DAY Functions
          1. Syntax
        9. The DATENAME Function
          1. Syntax
        10. The ISDATE Function
          1. Syntax
    8. Querying Metadata
      1. Catalog Views
      2. Information Schema Views
      3. System Stored Procedures and Functions
    9. Conclusion
    10. Exercises
    11. Solutions
  5. 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. Further Join Examples
      1. Composite Joins
      2. Non-Equi Joins
      3. Multi-Table Joins
    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-Table Join
        4. Using the COUNT Aggregate with Outer Joins
    5. Conclusion
    6. Exercises
    7. Solutions
  6. 4. Subqueries
    1. Self-Contained Subqueries
      1. Self-Contained Scalar Subquery Examples
      2. Self-Contained Multi-Valued Subquery Examples
    2. Correlated Subqueries
      1. The EXISTS Predicate
    3. Beyond the Fundamentals of Subqueries
      1. Returning Previous or Next Values
      2. Running Aggregates
      3. Misbehaving Subqueries
        1. NULL Trouble
        2. Substitution Error in a Subquery Column Name
    4. Conclusion
    5. Exercises
    6. Solutions
  7. 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
      2. Using Arguments
      3. Defining Multiple CTEs
      4. Multiple References
      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 Option CHECK OPTION
    4. Inline Table-Valued Functions
    5. The APPLY Operator
    6. Conclusion
    7. Exercises
    8. Solutions
  8. 6. Set Operations
    1. The UNION Set Operation
      1. The UNION ALL Set Operation
      2. The UNION DISTINCT Set Operation
    2. The INTERSECT Set Operation
      1. The INTERSECT DISTINCT Set Operation
      2. The INTERSECT ALL Set Operation
    3. The EXCEPT Set Operation
      1. The EXCEPT DISTINCT Set Operation
      2. The EXCEPT ALL Set Operation
    4. Precedence
    5. Circumventing Unsupported Logical Phases
    6. Conclusion
    7. Exercises
    8. Solutions
  9. 7. Pivot, Unpivot, and Grouping Sets
    1. Pivoting Data
      1. Pivoting with Standard SQL
      2. Pivoting with the Native T-SQL PIVOT Operator
    2. Unpivoting Data
      1. Unpivoting with Standard SQL
      2. Unpivoting with the Native T-SQL UNPIVOT Operator
    3. Grouping Sets
      1. The GROUPING SETS Subclause
      2. The CUBE Subclause
      3. The ROLLUP Subclause
      4. The GROUPING and GROUPING_ID Functions
    4. Conclusion
    5. Exercises
    6. Solutions
  10. 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
    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 the TOP Option
    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
    10. Solutions
  11. 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. Snapshot Isolation Levels
        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
  12. 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
  13. A. Getting Started
    1. Installing 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
    2. Downloading Source Code and Installing the Sample Database
    3. Working with SQL Server Management Studio
    4. Working with SQL Server Books Online
  14. Itzik Ben-Gan
  15. Index
  16. About the Author
  17. Copyright

Product information

  • Title: Microsoft® SQL Server® 2008 T-SQL Fundamentals
  • Author(s): Itzik Ben-Gan
  • Release date: October 2008
  • Publisher(s): Microsoft Press
  • ISBN: 9780735626010