You are previewing Beginning T-SQL.
O'Reilly logo
Beginning T-SQL

Book Description

"

Beginning T-SQLis a performance-oriented introduction to the T-SQL language underlying the Microsoft SQL Server database engine. T-SQL is essential in writing SQL statements to get data into and out of a database. T-SQL is the foundation for business logic embedded in the database in the form of stored procedures and functions.Beginning T-SQLstarts you on the path to mastering T-SQL,with an emphasis on best-practices and sound coding techniques leading to excellent performance. This new edition is updated to cover the essential features of T-SQL found in SQL Server 2014, 2012, and 2008.Beginning T-SQL begins with an introduction to databases, normalization, and to SQL Server Management Studio. Attention is given to Azure SQL Database and how to connect to remote databases in the cloud. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in most chapters provide an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional. Important techniques such as windowing functions are covered to help write fast executing queries that solve real business problems.A stand-out feature in this book is that most chapters end with a ""Thinking About Performance"" section. These sections cover aspects of query performance relative to the content just presented. They'll help you avoid beginner mistakes by knowing about and thinking about performance from Day 1.

"

Table of Contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. About the Authors
  8. About the Technical Reviewer
  9. Acknowledgments
  10. Foreword
  11. Chapter 1: Getting Started
    1. Installing SQL Server Express Edition
    2. Installing Books Online
    3. Using Books Online
    4. Using SQL Server Management Studio
      1. Launching SQL Server Management Studio
    5. Installing the Sample Databases
    6. Get Started with SSMS
    7. Summary
  12. Chapter 2: Exploring Database Concepts
    1. What Is SQL Server?
    2. Databases in the Cloud
    3. Service vs. Application
    4. Database as Container
    5. Data Is Stored in Tables
    6. Data Types
    7. Normalization
    8. Understanding Indexes
    9. Database Schemas
    10. Summary
  13. Chapter 3: Writing Simple SELECT Queries
    1. Using the SELECT Statement
      1. Selecting a Literal Value
      2. Retrieving from a Table
      3. Generating a Select-List
      4. Mixing Literals and Column Names
    2. Filtering Data
      1. Adding a WHERE Clause
      2. Using WHERE Clauses with Alternate Operators
      3. Using BETWEEN
      4. UsingBETWEEN with NOT
      5. Filtering on Date and Time
    3. Using WHERE Clauses with Two Predicates
      1. Using the IN Operator
    4. Working with NULL
    5. Sorting Data
    6. Thinking About Performance
      1. Taking Advantage of Indexes
      2. Viewing Execution Plans
    7. Summary
    8. Answers to the Exercises
      1. Solutions to Exercise 3-1: Using the SELECT Statement
      2. Solutions to Exercise 3-2: Filtering Data
      3. Solutions to Exercise 3-3: Using WHERE Clauses with Two Predicates
      4. Solutions to Exercise 3-4: Working with NULL
      5. Solutions to Exercise 3-5: Sorting Data
  14. Chapter 4: Using Built-In Functions and Expressions
    1. Expressions Using Operators
      1. Concatenating Strings
      2. Concatenating Strings and NULL
      3. CONCAT
      4. ISNULL and COALESCE
      5. Concatenating Other Data Types to Strings
    2. Using Mathematical Operators
    3. Using String Functions
      1. RTRIM and LTRIM
      2. LEFT and RIGHT
      3. LEN and DATALENGTH
      4. CHARINDEX
      5. SUBSTRING
      6. CHOOSE
      7. REVERSE
      8. UPPER and LOWER
      9. REPLACE
    4. Nesting Functions
    5. Using Date and Time Functions
      1. GETDATE and SYSDATETIME
      2. DATEADD
      3. DATEDIFF
      4. DATENAME and DATEPART
      5. DAY, MONTH, and YEAR
      6. CONVERT
      7. FORMAT
      8. DATEFROMPARTS
      9. EOMONTH
    6. Using Mathematical Functions
      1. ABS
      2. POWER
      3. SQUARE and SQRT
      4. ROUND
      5. RAND
    7. Logical Functions and Expressions
      1. The CASE Expression
      2. IIF
      3. COALESCE
    8. Administrative Functions
    9. Using Functions in the WHERE and ORDER BY Clauses
    10. The TOP Keyword
    11. Thinking About Performance
    12. Summary
    13. Answers to the Exercises
      1. Solutions to Exercise 4-1: Expressions Using Operators
      2. Solutions to Exercise 4-2: Using Mathematical Operators
      3. Solution to Exercise 4-3: Using Functions
      4. Solution to Exercise 4-4: Using Date and Time Functions
      5. Solution to Exercise 4-5: Using Mathematical Functions
      6. Solution to Exercise 4-6: Using Logical and System Functions
      7. Solution to Exercise 4-7: Using Functions in the WHERE and ORDER BY Clauses
  15. Chapter 5: Joining Tables
    1. Using INNER JOIN
      1. Joining Two Tables
      2. Avoiding an Incorrect Join Condition
      3. Joining on a Different Column Name
      4. Joining on More Than One Column
      5. Joining Three or More Tables
    2. Using OUTER JOIN
      1. Using LEFT OUTER JOIN
      2. Using RIGHT OUTER JOIN
      3. Using OUTER JOIN to Find Rows with No Match
      4. Adding a Table to the Right Side of a LEFT JOIN
      5. Adding a Table to the Main Table of a LEFT JOIN
      6. FULL OUTER JOIN
      7. CROSS JOIN
      8. Self-Joins
    3. Thinking About Performance
      1. Merge Join
      2. Nested Loop
      3. Hash Match
    4. Summary
    5. Answers to the Exercises
      1. Solutions to Exercise 5-1: Using INNER JOIN
      2. Solutions to Exercise 5-2: Using OUTER JOIN
  16. Chapter 6: Building on Subqueries, Common Table Expressions, and Unions
    1. Writing Subqueries
      1. Using a Subquery in an IN List
      2. Using a Subquery and NOT IN
      3. Using a Subquery Containing NULL with NOT IN
      4. Using EXISTS
      5. Using CROSS APPLY and OUTER APPLY
      6. Writing UNION Queries
      7. Using EXCEPT and INTERSECT
    2. Using Derived Tables and Common Table Expressions
      1. Using Derived Tables
      2. Using Common Table Expressions
      3. Using a Common Table Expression to Solve a Complicated Join Problem
    3. Thinking About Performance
    4. Summary
    5. Answers to the Exercises
      1. Solutions to Exercise 6-1: Using Subqueries
      2. Solutions to Exercise 6-2: Using Derived Tables and Common Table Expressions
  17. Chapter 7: Grouping and Summarizing Data
    1. Aggregate Functions
    2. The GROUP BY Clause
      1. Grouping on Columns
      2. Grouping on Expressions
    3. The ORDER BY Clause
    4. The WHERE Clause
    5. The HAVING Clause
    6. DISTINCT Keyword
      1. Using DISTINCT vs. GROUP BY
      2. DISTINCT Within an Aggregate Expression
    7. Aggregate Queries with More Than One Table
    8. Aggregate Functions and NULL
    9. Thinking About Performance
    10. Summary
    11. Answers to the Exercises
      1. Solutions to Exercise 7-1: Aggregate Functions
      2. Solutions to Exercise 7-2: The GROUP BY Clause
      3. Solutions to Exercise 7-3: The HAVING Clause
      4. Solutions to Exercise 7-4: DISTINCT Keyword
      5. Solutions to Exercise 7-5: Aggregate Queries with More Than One Table
  18. Chapter 8: Discovering Windowing Functions
    1. What Is a Windowing Function?
    2. Ranking Functions
      1. Defining the Window
      2. Dividing the Window into Partitions
      3. Using NTILE
    3. Summarizing Results with Window Aggregates
    4. Defining the Window with Framing
    5. Calculating Running Totals
    6. Understanding the Difference Between ROWS and RANGE
    7. Using Window Analytic Functions
      1. LAG and LEAD
      2. FIRST_VALUE and LAST_VALUE
      3. PERCENT_RANK and CUME_DIST
      4. PERCENTILE_CONT and PERCENTILE_DISC
    8. Applying Windowing Functions
      1. Removing Duplicates
      2. Solving an Islands Problem
    9. Thinking About Performance
      1. Indexing
      2. The Trouble with Window Aggregates
      3. Framing
    10. Summary
    11. Answers to the Exercises
      1. Solutions to Exercise 8-1: Ranking Functions
      2. Solutions to Exercise 8-2: Summarizing Results with Window Aggregates
      3. Solutions to Exercise 8-3: Understanding the Difference Between ROWS and RANGE
      4. Solutions to Exercise 8-4: Using Window Analytic Functions
  19. Chapter 9: Advanced WHERE Clauses
    1. Pattern Matching
    2. Using LIKE
      1. Restricting the Characters in Pattern Matches
      2. Searching for Wildcards
      3. Combining Wildcards
      4. Using PATINDEX
    3. Using WHERE Clauses with Three or More Predicates
      1. Using NOT with Parentheses
    4. Performing a Full-Text Search
      1. Using CONTAINS
      2. Using Multiple Terms with CONTAINS
      3. Searching Multiple Columns
      4. Using FREETEXT
    5. Thinking About Performance
    6. Summary
    7. Answers to the Exercises
      1. Solutions to Exercise 9-1: Using LIKE
      2. Solution to Exercise 9-2: Using WHERE Clauses with Three or More Predicates
      3. Solution to Exercise 9-3: Performing a Full-Text Search
  20. Chapter 10: Manipulating Data
    1. Inserting New Rows
      1. Adding One Row with Literal Values
      2. Avoiding Common Insert Errors
      3. Inserting Multiple Rows with One Statement
      4. Inserting Rows from Another Table
      5. Inserting Missing Rows
      6. Creating and Populating a Table in One Statement
      7. Inserting Rows into Tables with Default Column Values
      8. Inserting Rows into Tables with Automatically Populating Columns
    2. Deleting Rows
      1. Using DELETE
      2. Deleting from a Table Using EXISTS
      3. Truncating
    3. Updating Existing Rows
      1. Using the UPDATE Statement
      2. Updating Data with Expressions and Columns
      3. Updating with a Join
    4. Thinking About Performance
      1. Database Cleanup
    5. Summary
    6. Answers to the Exercises
      1. Solution to Exercise 10-1: Inserting New Rows
      2. Solution to Exercise 10-2: Deleting Rows
      3. Solution to Exercise 10-3: Updating Existing Rows
  21. Chapter 11: Writing Advanced Queries
    1. Advanced CTE Queries
      1. Alternate CTE Syntax
      2. Using Multiple CTEs
      3. Referencing a CTE Multiple Times
      4. Joining a CTE to Another CTE
      5. Writing a Recursive Query
      6. Data Manipulation with CTEs
    2. Isolating Aggregate Query Logic
      1. Correlated Subqueries in the SELECT list
      2. Using Derived Tables
      3. Common Table Expressions
      4. Using CROSS APPLY and OUTER APPLY
    3. The OUTPUT Clause
      1. Using OUTPUT to View Data
      2. Saving OUTPUT Data to a Table
    4. The MERGE Statement
    5. GROUPING SETS
    6. CUBE and ROLLUP
    7. Pivoted Queries
      1. Pivoting Data with CASE
      2. Using the PIVOT Function
      3. Using the UNPIVOT Function
    8. Paging
    9. Summary
  22. Chapter 12: Understanding T-SQL Programming Logic
    1. Variables
      1. Declaring and Initializing a Variable
      2. Using Expressions and Functions with Variables
      3. Using Variables in WHERE and HAVING Clauses
    2. The IF . . . ELSE Construct
      1. Using IF
      2. Using ELSE
      3. Using Multiple Conditions
      4. Nesting IF ... ELSE
      5. Using IF with a Query
    3. WHILE
      1. Using a WHILE Loop
      2. Nesting WHILE Loops
      3. Exiting a Loop Early
      4. Using CONTINUE
    4. Temporary Tables and Table Variables
      1. Creating Local Temp Tables
      2. Creating Global Temp Tables
      3. Creating Table Variables
      4. Using a Temp Table or Table Variable
      5. Using a Temp Table or Table Variable Like an Array
      6. Using a Cursor
    5. Thinking About Performance
    6. Summary
    7. Answers to the Exercises
      1. Solutions to Exercise 12-1: Variables
      2. Solutions to Exercise 12-2: The IF . . . ELSE Construct
      3. Solutions to Exercise 12-3: WHILE
      4. Solutions to Exercise 12-4: Temporary Tables and Table Variables
  23. Chapter 13: Managing Transactions
    1. ACID Properties
    2. Writing an Explicit Transaction
      1. Rolling Back a Transaction
      2. Using the XACT_ABORT Setting
    3. Error Handling
      1. Using TRY . . . CATCH
      2. Viewing Untrappable Errors
      3. Using RAISERROR
      4. Using TRY . . . CATCH with Transactions
      5. Using THROW Instead of RAISERROR
    4. Thinking About Performance
    5. Summary
    6. Answers to the Exercises
      1. Solutions to Exercise 13-1: Writing an Explicit Transaction
      2. Solutions to Exercise 13-2: Error Handling
  24. Chapter 14: Implementing Logic in the Database
    1. Tables
      1. Adding Check Constraints to a Table
      2. Adding UNIQUE Constraints
      3. Adding a Primary Key to a Table
      4. Creating Foreign Keys
      5. Creating Foreign Keys with Delete and Update Rules
      6. Defining Automatically Populated Columns
    2. Views
      1. Creating Views
      2. Avoiding Common Problems with Views
      3. Manipulating Data with Views
    3. User-Defined Functions
      1. Creating User-Defined Scalar Functions
      2. Using Table-Valued User-Defined Functions
    4. Stored Procedures
      1. Using Default Values with Parameters
      2. Using the OUTPUT Parameter
      3. Saving the Results of a Stored Procedure in a Table
      4. Using a Logic in Stored Procedures
    5. User-Defined Data Types
    6. Table Types
    7. Triggers
    8. CLR Integration
    9. Thinking About Performance
      1. Database Cleanup
    10. Summary
    11. Answers to the Exercises
      1. Solutions to Exercise 14-1: Tables
      2. Solution to Exercise 14-2: Views
      3. Solution to Exercise 14-3: User-Defined Functions
      4. Solution to Exercise 14-4: Stored Procedures
  25. Chapter 15: Working with XML
    1. The Parts of XML
    2. Converting XML Using OPENXML
    3. Retrieving Data as XML Using the FOR XML Clause
      1. FOR XML RAW
      2. FOR XML AUTO
      3. FOR XML EXPLICIT
      4. FOR XML PATH
    4. The XML Data Type
    5. XML Methods
      1. The QUERY Method
      2. The VALUE Method
      3. The EXIST Method
      4. The MODIFY Method
      5. The NODES Method
    6. Namespaces
    7. Splitting a String
    8. Summary
  26. Chapter 16: Expanding on Data Type Concepts
    1. Large-Value String Data Types (MAX)
    2. Large-Value Binary Data Types
      1. Creating VARBINARY(MAX) Data
      2. Using FILESTREAM
      3. FileTables
    3. Enhanced Date and Time
      1. Using DATE, TIME, and DATETIME2
      2. Using DATETIMEOFFSET
    4. HIERARCHYID
      1. Viewing HIERARCHYID
      2. Creating a Hierarchy
      3. Using Stored Procedures to Manage Hierarchical Data
    5. Spatial Data Types
      1. Using GEOMETRY
      2. Using GEOGRAPHY
      3. Viewing the Spatial Results Tab
      4. Circular Arcs
    6. Sparse Columns
    7. Thinking About Performance
    8. Summary
  27. Chapter 17: Running SQL Server in the Cloud
    1. Procuring a Microsoft Azure Account
    2. The Azure Dashboard
    3. Windows Azure Virtual Machines
    4. Azure SQL Database
      1. Throttling
      2. Database Size Limitations
      3. Pricing
    5. Summary
  28. Chapter 18: Where to Go Next?
    1. Online Resources
    2. Conferences
    3. User Groups
    4. Vendors
    5. Books
    6. Classes
    7. SQL Server Documentation
    8. Practice, Practice, and More Practice
    9. Teach Someone Else
  29. Index