Programming Languages: SQL
|
|
||||||
 Sams Teach Yourself Transact-SQL in 21 Days provides you with the skills you need to write flexible and effective applications. The techniques included in the book allow you to maximize the performance of SQL Server by optimizing your new or existing queries. The syntax and examples can be used with either Microsoft or Sybase SQL Server. Whether you are a programmer or a database administrator, Sams Teach Yourself Transact-SQL in 21 Days shows you how to write effective Transact-SQL queries that provide efficient results and decrease the performance demands on your server. |
|||||||
- Introduction
- About This Book
- About the Authors
- About This Book
- Week 1—At a Glance
- Day 1—Introduction to Transact-SQL
- What Is SQL?
- What Is Transact-SQL?
- Some SQL Server Background Information
- SQL Server Fairy Tale—And It Really Happened This Way
- Who Uses SQL?
- How Is SQL Used in the Real World?
- Getting Connected
- Installing the Client Software
- Starting ISQL/w
- If You Have Trouble Starting ISQL/w
- Connecting to the metis Server
- Starting ISQL/w
- Your First Query
- Looking at a Result Set
- What You’ll Be Learning the Rest of the Week
- Summary
- Q&A
- What Is Transact-SQL?
- What Is SQL?
- Week 1
- Day 2—Retrieving Data
- Basic SELECT Syntax
- The Simplest Query
- Now Back to the Simplest Query
- The ISQL/w Interface
- Listing More Than One Column
- Altering Data in the Result Set
- Now Back to the Simplest Query
- Column Aliases
- Getting All the Columns in a Table
- Listing Tables and Columns
- The Direct Approach
- Listing Columns in a Table
- Using SQL to Get Column Information
- Listing Columns in a Table
- Sorting Data with ORDER BY
- Sorting by Multiple Columns
- Sorting in Ascending or Descending Order
- Sorting on Columns That Do Not Appear in the Result Set
- An order by Shortcut
- Sorting in Ascending or Descending Order
- Understanding Server Messages and Errors
- Raising Errors
- Raising Your Own Error Messages
- Some Common Errors and What They Mean
- Raising Your Own Error Messages
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- The Simplest Query
- Basic SELECT Syntax
- Week 1
- Day 3—Selecting Rows with the WHERE Clause
- What Does the WHERE Clause Do?
- Equality Conditions
- Inequality Conditions
- Boolean Logic
- Boolean Operators
- Using NOT By Itself
- Compound WHERE Clauses Using Boolean Logic
- Order of Operations in Boolean Expressions
- Inequality Operators and Dates
- Syntax for WHERE
- Using NOT By Itself
- Using BETWEEN
- BETWEEN Is Inclusive, NOT BETWEEN Is Exclusive
- Using IN
- Using NOT with IN
- A Simple Example Using LIKE
- How to Match Uppercase and Lowercase Letters
- Using LIKE to Find Dates
- A Simple Example Using LIKE
- Null Values
- A Final Note on Matching Null
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Equality Conditions
- What Does the WHERE Clause Do?
- Week 1
- Day 4—All About Columns
- Data Types
- Column Manipulation
- Selecting and Using Constant Strings
- Using the Addition Operator on Strings
- Another Column Manipulation Example
- Using the Addition Operator on Strings
- The convert() Function
- Using convert to Format Dates
- SQL Server Functions
- Effect of Functions on Performance
- Function Examples
- String Functions
- Math Functions
- Date Functions
- System Functions
- Function Examples
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Column Manipulation
- Data Types
- Week 1
- Day 5—Aggregates
- Introduction to Aggregates
- Using SUM
- Using AVG
- Using MIN and MAX
- Using COUNT
- Using AVG
- Aggregates with Null
- The Aggregate of No Rows
- Using Aggregates on Nonnumeric Columns
- Aggregates with WHERE
- What You Can’t Do
- Expressions Inside Aggregates
- The ISNULL() Function
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Using SUM
- Introduction to Aggregates
- Week 1
- Day 6—Totals and Subtotals
- GROUP BY
- Must I GROUP BY a Column in the SELECT List?
- Ordering Grouped Results
- CUBE and ROLLUP
- CUBE
- ROLLUP
- COMPUTE
- COMPUTE as It More Commonly Appears in the Real World
- COMPUTE…BY
- COMPUTE Is Strange
- COMPUTE…BY
- HAVING
- Order of Evaluation
- SELECT Syntax
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Must I GROUP BY a Column in the SELECT List?
- GROUP BY
- Week 1
- Day 7—Joining Tables
- Understanding the Join
- ANSI Style Syntax Versus Old Style Syntax
- Another Table Join Example
- Qualifying Column Names
- Primary and Foreign Keys
- Composite (Multipart) Keys
- Cross Joins or Cartesian Products
- The Danger of Cartesian Products
- Joining Multiple Tables
- How to Read an Entity Relationship Diagram
- Referential Integrity
- Referential Integrity Summary
- Joins and Performance
- Joining Multiple Tables That Share a Join Key
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- ANSI Style Syntax Versus Old Style Syntax
- Understanding the Join
- Week 1—In Review
- Week 2—At a Glance
- Day 8—Subqueries
- Subqueries Defined
- Benefits of Using Subqueries
- Subqueries Returning a Single Result
- Syntax for Subquery Returning One Result
- Subqueries that Return Multiple Rows
- Example of IN with a Subquery
- Guidelines for Using IN
- Subquery Returns No Rows
- Syntax for Subqueries Returning Multiple Rows
- Guidelines for Using IN
- Subqueries As Joins
- Complex Joins As Subqueries
- Subqueries and Aggregates
- The HAVING Clause with Subqueries
- A Special Kind of Subquery: WHERE EXISTS
- The CASE Expression
- That’s Great, But What Does It Do?
- A Simple CASE Example
- Want More?
- Crosstab Reports Using the CASE Expression
- Tips for Creating Crosstabs
- A Simple CASE Example
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Subqueries Defined
- Week 2
- Day 9—INSERT, UPDATE, and DELETE
- Creating Tables
- Deleting Tables
- Limits on Table Creations
- Adding Identity Columns
- Defining Default Values
- Limits on Table Creations
- The INSERT Statement
- Inserting into Tables with Identity Columns
- Inserting into Tables with Default Columns
- Using Niladic Functions as a Default
- How to Insert Each Type of Data
- SELECT INTO and INSERT… SELECT
- Inserting into Tables with Default Columns
- UPDATE
- Table Joins in Updates
- Limits on the UPDATE Statement
- Updating to Default Values
- Syntax Statement for UPDATE
- Limits on the UPDATE Statement
- DELETE
- Ensuring Successful Deletion
- DELETE and Table Joins
- DELETE and Views
- TRUNCATE TABLE, DROP TABLE, and DELETE
- DELETE Syntax
- DELETE and Table Joins
- Declarative Referential Integrity
- What’s So Great About Referential Integrity, Anyway?
- Understanding Referential Integrity
- Implementing Declarative Referential Integrity
- Understanding Referential Integrity
- DRI Example
- Step One: Build the Tables
- Inserting Data
- Exercise Complete
- Inserting Data
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Deleting Tables
- Creating Tables
- Week 2
- Day 10—Temporary Tables and Views
- What Is a Temporary Table?
- Local Temp Tables
- SELECT… INTO
- Global Temp Tables
- ”Permanent” Temp Tables
- How to Build a Temporary Table on System Startup
- SELECT… INTO
- Views
- Restricting Rows Using a View
- Joining Tables with a View
- Inserting into a View
- Updating a View
- Deleting from a View
- Advanced View Examples
- Joining Tables with a View
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Local Temp Tables
- Day 11—Databases, Object Naming, and Batches
- What Is a Database?
- Current Context
- Finding the Database Context in an Application Program
- Complete Object Names
- Finding the Database Context in an Application Program
- Changing Database Contexts
- Changing Databases using ISQL/w
- Changing Databases with DB-Library
- Changing Databases with ODBC
- Changing Databases with DB-Library
- What Databases Are Available?
- The Master Database
- tempdb
- model
- msdb
- pubs
- User-Defined Databases
- tempdb
- Batches
- Order of Execution
- Multiple Statements in a Single Batch
- Two Batches, Two Statements
- Performance and Batching
- Multiple Statements in a Single Batch
- SQL Scripts
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Current Context
- What Is a Temporary Table?
- Week 2
- Day 12—Batch Programming Structures
- Local Variables
- Assigning Values to a Variable
- What Are Local Variables Good For?
- Persistence of Local Variables
- Assigning Values in an UPDATE Statement
- DECLARE Syntax
- What Are Local Variables Good For?
- Global Variables
- Using Global Variables in SQL
- Who Am I?
- PRINT
- Who Am I?
- Flow Control
- IF
- Logical Shortcutting in the IF Statement
- BEGIN and END
- WHILE
- RETURN
- The RAND Function
- Logical Shortcutting in the IF Statement
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Assigning Values to a Variable
- Local Variables
- Week 2
- Day 13—Transactions
- What Is a Transaction?
- Transaction Control Statements
- Implicit Transactions
- Error Checking in Transactions
- Nesting Transactions
- Implicit Transactions
- Transactions and Batches
- Transaction Example with Multiple Batches
- Grouping Statements in Transactions
- Locking
- Why Is Locking Necessary?
- How Does Locking Help?
- How Can I Observe Locking Behavior?
- What Types of Locks Are Available?
- Lock Granularity: Table Locking, Page Locking, and Row Locking
- Locks Blocking Processes
- The sp_lock Example
- How Do I Affect Locking?
- Optimizer Lock Hints
- Other Tips for Speeding Transactions
- Deadlock
- How Does Locking Help?
- Summary
- Q&A
- Workshop
- Quiz
- Exercise
- Transaction Control Statements
- What Is a Transaction?
- Week 2
- Day 14—Cursors
- What Is a Cursor?
- Creating a Cursor
- A Simple Cursor Example
- Client-Side and Server-Side Cursors
- Set Processing Versus Row Processing
- The “Why You Need a Cursor” Exercise
- Scrollable Cursors
- Scroll Cursor Examples
- Cursor Syntax
- Cursors and Performance
- Limitations of Cursors
- Summary
- Q&A
- Workshop
- Quiz
- Exercise
- Creating a Cursor
- What Is a Cursor?
- Week 2—In Review
- Week 3—At a Glance
- Day 15—How the Server Uses Indexes
- Basic SQL Server Storage
- Why Should You Care About Storage Structures?
- Index Types
- Optimization Without Indexes
- Clustered and Nonclustered Indexes
- Clustered Indexes
- Nonclustered Indexes
- Clustered Versus Nonclustered Indexes
- Clustered and Nonclustered Indexes
- A Brief Look at Optimization
- Step 1: Find SARGs
- Step 2: Match SARGs to Indexes
- Step 3: Estimate Work Using Each Index
- Step 4: Choose the Most Efficient Way to Resolve the Query
- What You Need to Remember from This Discussion
- Step 2: Match SARGs to Indexes
- Analyzing Optimization Plans
- Using showplan
- Analyzing Execution with statistics io
- Overriding the Optimizer
- Analyzing Execution with statistics io
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Basic SQL Server Storage
- Week 3
- Day 16—Stored Procedures
- Benefits of Stored Procedures
- A First Stored Procedure
- Getting Information About Procedures
- Procedure Execution
- Some Practical Advice About Editing
- Using Parameters
- Execution with a Parameter
- Details About Parameter Passing
- Syntax for Passing Parameters
- Details About Parameter Passing
- Parameter Defaults
- Parameter Defaults and Customized Error Messages
- Parameter Defaults and Listed Values
- Parameter Defaults and an Overall Average Value
- Parameter Defaults and Listed Values
- Output Parameters
- Output Parameters and RPCs
- Procedure Return Status
- Setting Return Status
- Five Ways to Communicate
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- A First Stored Procedure
- Benefits of Stored Procedures
- Week 3
- Day 17—Procedures and Performance
- Stored Procedure Debugging Methods
- Create a Good Work Environment
- Take Small Bites
- Handling Debugging Messages
- Take Small Bites
- Examining Procedure Performance
- Tracking Times
- Elapsed Time
- showplan and statistics io
- Stored Procedure Optimization
- Understanding Stored Procedure Compilation
- Controlling Procedure Compilation
- CREATE PROC … WITH RECOMPILE
- How to Avoid Optimization Problems
- Temporary Tables in Procedures
- Compilation and Changes to Indexes
- On-the-Fly Execution
- Elapsed Time
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Create a Good Work Environment
- Stored Procedure Debugging Methods
- Week 3
- Day 18—Advanced Stored Procedure Issues
- Procedures and INSERT
- Transaction and INSERT … EXECUTE
- Optimization and INSERT … EXECUTE
- Pros and Cons of INSERT … EXECUTE
- Optimization and INSERT … EXECUTE
- Procedures and Transactions
- Problems with Nested Transactions
- A Proper Coding Approach
- Error Checking and Stored Procs
- System Procedures
- Requirements of System Procedures
- Special Characteristics of System Procedures
- Remote Procedures
- Setup Issues
- Coding Issues
- Transactions
- Coding Issues
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Transaction and INSERT … EXECUTE
- Procedures and INSERT
- Week 3
- Day 19—Triggers
- Understanding Triggers
- Getting Information on Triggers
- Trigger Maintenance
- My Trigger Naming Convention
- Executing Triggers
- When Do Triggers Fire?
- How Are Triggers Executed?
- Trigger Resources
- Inserted and Deleted Tables
- The UPDATE() Function
- Trigger Examples
- Cascaded UPDATE and DELETE
- Maintain Derived Columns
- Write an Audit Trail
- Maintain Derived Columns
- When to Use Triggers
- Summary
- Q&A
- Workshop
- Quiz
- Exercise
- Getting Information on Triggers
- Understanding Triggers
- Week 3
- Day 20—Advanced Topics: Single-Table Queries
- The case Operator
- case with SELECT: Crosstabs
- The Solution
- case with a View
- The Solution
- Locking Solutions for Multiuser Editing
- No Locks
- Persistent Shared Locks
- Persistent Exclusive Locks
- Manual Check-Out
- Optimistic Locking
- Locking Wrap-Up
- Persistent Shared Locks
- Understanding Worktables
- What T-SQL Operations Use Worktables
- Can I See a Worktable?
- When Is a Worktable Used?
- Worktable Tricks and Techniques
- Can I See a Worktable?
- Summary
- Q&A
- Workshop
- Quiz
- Exercise
- case with SELECT: Crosstabs
- The case Operator
- Week 3
- Day 21—Advanced Topics: Joins and Subqueries
- Understanding Join Behavior and Performance
- An Anecdote
- Join Processing: The Logical Model
- Outer and Inner Tables
- Join Processing: The Physical Model
- Join Optimization
- Join Processing: The Logical Model
- Outer Joins
- Self-Joins
- A Full Outer Join
- Self-Join Dos and Don’ts
- Summary
- Q&A
- Workshop
- Quiz
- Exercises
- Quiz
- An Anecdote
- Understanding Join Behavior and Performance
- Day 1—Introduction to Transact-SQL