[T 01] • Beginning T-SQL 2012 by Kellenberger, Kathi -- Read -- Imperial Library of Trantor

Index

Cover Title Copyright Dedication Contents at a Glance Contents Foreword About the Authors About the Technical Reviewer Acknowledgments Introduction Chapter 1: Getting Started

Installing SQL Server Express Edition Installing the Sample Databases

Installing Books Online Using Books Online

Using SQL Server Management Studio

Launching SQL Server Management Studio Running Queries

Exploring Database Concepts

What Is SQL Server? Service vs. Application Database As Container Data Is Stored in Tables Data Types Normalization Understanding Indexes Database Schemas

Summary

Chapter 2: Writing Simple SELECT Queries

Using the SELECT Statement

Selecting a Literal Value Retrieving from a Table Generating a Select-List Mixing Literals and Column Names

Filtering Data

Adding a WHERE Clause Using WHERE Clauses with Alternate Operators Using BETWEEN Using NOT BETWEEN Filtering On Date and Time Pattern Matching with LIKE Restricting the Characters in Pattern Matches Combining Wildcards Using WHERE Clauses with Two Predicates Using WHERE Clauses with Three or More Predicates Using NOT with Parentheses Using the IN Operator

Working with Nothing Performing a Full-Text Search

Using CONTAINS Using Multiple Terms with CONTAINS Searching Multiple Columns Using FREETEXT

Sorting Data Thinking About Performance

Taking Advantage of Indexes Viewing Execution Plans

Summary

Chapter 3: Using Functions and Expressions

Expressions Using Operators

Concatenating Strings Concatenating Strings and NULL CONCAT ISNULL and COALESCE Concatenating Other Data Types to Strings Using Mathematical Operators Data Type Precedence

Using Functions

Using String Functions Nesting Functions Using Date Functions Using Mathematical Functions System Functions Using Functions in the WHERE and ORDER BY Clauses

The TOP Keyword Ranking Functions

Using ROW_NUMBER Using RANK and DENSE_RANK Using NTILE

Thinking About Performance

Using Functions in the WHERE Clause

Summary

Chapter 4: Querying Multiple Tables

Writing INNER JOINS

Joining Two Tables Avoiding an Incorrect Join Condition Joining on a Different Column Name Joining on More Than One Column Joining Three or More Tables

Writing Outer Joins

Using LEFT OUTER JOIN Using RIGHT OUTER JOIN Using OUTER JOIN to Find Rows with No Match Adding a Table to the Right Side of a Left Join Adding a Table to the Left Side of a Left Join FULL OUTER JOIN CROSS JOIN Self-Joins

Writing Subqueries

Using a Subquery in an IN List Using a Subquery and NOT IN Using a Subquery Containing NULL with NOT IN

Writing UNION Queries Exploring Derived Tables and Common Table Expressions

Using Derived Tables Using Common Table Expressions Using a CTE to Solve a Complicated Join Problem

Thinking About Performance Summary

Chapter 5: Grouping and Summarizing Data

Aggregate Functions The GROUP BY Clause

Grouping on Columns Grouping on Expressions

The ORDER BY Clause The WHERE Clause The HAVING Clause DISTINCT

Using DISTINCT vs. GROUP BY DISTINCT Within an Aggregate Expression

Aggregate Queries with More Than One Table Isolating Aggregate Query Logic

Using a Correlated Subquery in the WHERE Clause Inline Correlated Subqueries Using Derived Tables Common Table Expressions Using Derived Tables and CTEs to Display Details The OVER Clause

GROUPING SETS CUBE and ROLLUP Thinking About Performance Summary

Chapter 6: Manipulating Data

Inserting New Rows

Adding One Row with Literal Values Avoiding Common Insert Errors Inserting Multiple Rows with One Statement Inserting Rows from Another Table Inserting Missing Rows Creating and Populating a Table in One Statement Inserting Rows into Tables with Default Column Values Inserting Rows into Tables with Automatically Populating Columns

Deleting Rows

Using DELETE Deleting from a Table Using a Join or a Subquery Truncating

Updating Existing Rows

Using the UPDATE Statement Updating Data with Expressions and Columns Updating with a Join Updating with Aggregate Functions

Using Transactions

Writing an Explicit Transaction Rolling Back a Transaction Locking Tables

Thinking About Performance Database Cleanup Summary

Chapter 7: Understanding T-SQL Programming Logic

Variables

Declaring and Initializing a Variable Using Expressions and Functions with Variables Using Variables in WHERE and HAVING Clauses

The IF… ELSE Construct

Using IF Using ELSE Using Multiple Conditions Nesting IF…ELSE Using IF EXISTS

WHILE

Using a WHILE Loop Using ROWCOUNT Nesting WHILE Loops Exiting a Loop Early Using CONTINUE

Error Handling

Using @@ERROR Using GOTO Using TRY … CATCH Viewing Untrappable Errors Using RAISERROR Using TRY…CATCH with Transactions Using THROW Instead of RAISERROR

Temporary Tables and Table Variables

Creating Local Temp Tables Creating Global Temp Tables Creating Table Variables Using a Temp Table or Table Variable Using a Temp Table or Table Variable as an Array Using a Cursor

Thinking About Performance Summary

Chapter 8: Working with XML

Converting XML into Data Using OPENXML Retrieving Data as XML Using the FOR XML Clause

FOR XML RAW FOR XML AUTO FOR XML EXPLICIT FOR XML PATH

XML Data Type XML Methods

Query Method The value() Method The exist() Method The modify() Method Node Method

Summary

Chapter 9: Moving Logic to the Database

Tables

Adding Check Constraints to a Table Adding UNIQUE Constraints Adding a Primary Key to a Table Creating Foreign Keys Creating Foreign Keys with Delete and Update Rules Defining Automatically Populated Columns

Views

Creating Views Avoiding Common Problems with Views Manipulating Data with Views

User-Defined Functions

Creating User-Defined Scalar Functions Using Table-Valued User-Defined Functions

Stored Procedures

Using Default Values with Parameters Using the OUTPUT Parameter Saving the Results of a Stored Proc in a Table Using a Logic in Stored Procedures CLR Integration

User-Defined Data Types Triggers Thinking About Performance Database Cleanup Summary

Chapter 10: Working with Data Types

Large-Value String Data Types (MAX) Large-Value Binary Data Types

Creating VARBINARY(MAX) Data Using FILESTREAM FileTables

Enhanced Date and Time

Using DATE, TIME, and DATETIME2 Using DATETIMEOFFSET

HIERARCHYID

Viewing HIERARCHYID Creating a Hierarchy Using Stored Procedures to Manage Hierarchical Data

Spatial Data Types

Using GEOMETRY Using GEOGRAPHY Viewing the Spatial Results Tab Circular Arcs

Sparse Columns Summary

Chapter 11: Writing Advanced Queries

Advanced CTE Queries

Using Multiple CTEs Calling a CTE Multiple Times Joining a CTE to Another CTE Using the Alternate CTE Syntax Writing a Recursive Query

The OUTPUT Clause

Using OUTPUT to View Data Saving OUTPUT Data to a Table

The MERGE Statement GROUPING SETS Pivoted Queries

Pivoting Data with CASE Using the PIVOT Function

Numbers Table Database Cleanup Summary

Chapter 12: Where to Go Next?

Online Resources Conferences User Groups Vendors Books Classes SQL Server Books Online Practice, Practice, and More Practice Teach Someone Else

Index

← Prev
Back
Next →

← Prev
Back
Next →