Sams Teach Yourself Transact-SQL in 21 Days

Lowell Mauer has been in data processing for more than 20 years as a programmer, instructor, and consultant. He has taught programming at Montclair State College in New Jersey and has developed and marketed several Visual Basic applications, including a SQL Server-based reservation system for a private golf course. As a manager of technical support, he has attended seminars and training sessions in several countries and is an expert in more than six computer languages. He currently is the Manager of Consulting for Cognos Corporation in New York. David Solomon, President of Metis Technologies, LLC: I've been working in, on, and around SQL Server (in its incarnations from Sybase and Microsoft) since 1990, and I can't believe that I can still make a living in this technology. I consult and teach courses on SQL Server, and write books like this one from time to time. When I'm not wasting my time working, I coach youth soccer enthusiastically. If you come to Troy, you'll probably find me on a soccer field, shouting and chasing kids and pretending I'm one of them. I save the best of my time for my kids, Adam and Luke, my wife Carola, and my three cats, Lucifer, Sam, and Daisy. Right now, Lucifer is sitting in my desk chair, which leaves me standing. Bennett McEwan is president of Geist, LLC, a company in scenic upstate New York, specializing in explaining SQL Server. The work Ben enjoys most is mentoring, where he helps programmers or database administrators through the tough spots in mission-critical applications. Ben also teaches SQL Server training classes, writes, and designs Visual Basic client/ server applications, and consults on difficult SQL Server performance issues. You can catch him on comp.databases.ms-sqlserver and in the pages of Microsoft SQL Server Professional (Pinnacle Publishing).

INTRODUCTION.

WEEK 1. WORKING WITH ROWS AND COLUMNS.

Day 1. Introduction to SQL and the Query Analyzer.


SQL and SQL Server. What Is T-SQL? Retrieving Data with Query Analyzer.



Installing the Query Analyzer. Logging In to and Changing the Database. The Query Toolbar. Executing Your First Query. Understanding SELECT and FROM. Using the Query Analyzer Editor. Using Query Analyzer to Learn about Tables.



Introducing SQL Server 2000 Enterprise Manager.



Registering a Server. Seeing How Enterprise Manager Works.



Summary. Q&A. Workshop.



Quiz. Exercise.

Day 2. Filtering and Sorting Data.


Filtering Data with WHERE Clauses.



Matching Column Data with Equality Statements. Matching Rows Using Not Equals (!= or <>). Searching for Numeric Values.



Combining Conditions with OR and AND.



The AND Condition. Combining AND and.



Searching for Ranges of Values with Inequalities (>, <, BETWEEN).



Combining Inequality Conditions. Searching for Ranges of Values with BETWEEN. Using Inequalities with Strings.



Getting Correct Results from Date Comparisons.



Dates in Other Languages and Formats.



Looking for Values in a List: The IN Operator.



Using the NOT IN Operator.



Wildcard Searches with LIKE.



The NOT LIKE Operator. Searching for Wildcard Characters.



Summary of Search Conditions. Sorting Data (ORDER BY).



Sorting by One Column. Sorting by Multiple Columns. Sorting by Column Number.



Retrieving Unique Rows.



Using DISTINCT to Find Unique Rows. Avoiding DISTINCT.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 3. Working with Columns.


Column Characteristics.



Data Types. Data Type Precedence. Using Null Data.



Changing Result Sets.



Using Column Aliases. Using String Constants. Using Operators. Using Functions. Using String Functions. Using Arithmetic Functions. Using Date Functions. Using the CASE Statement.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 4. Working with Row Aggregates and Grouping.


Working with Aggregate Functions.



Using SUM() and AVG(). Using MIN() and MAX(). Using Aggregates in a WHERE Clause. Using COUNT(). Implementing Error Handling for Aggregates. Understanding Aggregates and Null. Computing Aggregates with DISTINCT. Reviewing Aggregates.



Grouping Data.



GROUP BY Clauses. Grouping with all. Using Multi-Level Grouping. Grouping by Complex Expressions. Using HAVING to Remove Grouped Rows. Combining Subtotals and Totals with ROLLUP and CUBE.



Creating Pivot Tables. Summary. Q&A. Workshop.



Quiz. Exercises.

Day 5. Joining Tables.


Understanding Joins.



Creating and Using Entity Relationship Diagrams (ERDs). Writing Your First Join Using an Inner Join.



Understanding More about Joins.



Introducing Cross Joins. Understanding More about Joins. Using Table Aliases. Working with Multi-Table Joins. Joining Tables with Multi-Part Keys.



Writing Outer Joins. Joining a Table to Itself.



Using Parent-Child Relationships. Naming Standards in Self-Joins.



Using Union to Merge Result Sets.



SQL Keywords and UNION.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 6. Using Subqueries.


Understanding Subqueries.



A Basic Subquery. Benefits of Using Subqueries. The Rules of Using Subqueries.



Matching a Value with Subqueries.



Creating Subqueries with Comparison Operators. Using ALL and ANY.



Checking for Membership.



Writing Your First Subquery with IN. Using the NOT IN Condition. Creating Correlated Subqueries. Things to Remember When Using IN. Writing Subqueries with EXIST. Comparing Joins and Subqueries.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 7. Adding, Changing, and Deleting Rows.


Creating Tables. Deleting Tables. Inserting Rows.



Inserting Rows with INSERT…VALUES. Inserting Rows with INSERT…SELECT. Inserting Rows with SELECT INTO.



Updating Rows.



Updating Data Using Data in Other Tables. Reviewing the Limits on the UPDATE Statement.



Deleting Rows.



Ensuring a Successful Deletion. Reviewing the Limits on the DELETE Statement. Truncating Tables. Understanding Identity Columns and the TRUNCATE Statement.



Summary. Q&A. Workshop.



Quiz. Exercises.

WEEK 1. IN REVIEW.

WEEK 2. PROGRAMMING WITH T-SQL.

Day 8. Defining Data.


What Is Data Definition Language (DDL)?



Creating a New Database. Altering a Database. Dropping or Deleting a Database. DDL Versus the Enterprise Manager.



Working with Tables.



Rules about Table and Column Names. Understanding Column Data Types. User-Defined Data Types. Choosing Nullability. Default Columns. Computed Columns.



Altering Tables.



Renaming Tables and Columns. Changing Data Types and Nullability.



Defining Constraints.



CHECK Constraints.



Defining Table Relationships.



Primary and Foreign Keys. Using Declarative Referential Integrity (DRI).



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 9. Indexes and Performance.


How SQL Server Uses Indexes.



Why Use Indexes? Understanding Optimization. Understanding Clustered and Nonclustered Indexes. Understanding Unique Indexes. Indexing Computed Columns. Forcing Index Selection.



Defining and Managing Indexes.



Creating an Index. Using Full-Text Indexing.



Performing Index Maintenance and Using Performance Tools.



Full-Text Indexing Scheduling. Using Server Statistics. Using the Query Analyzer Tools.



Summary. Q&A /li>. Workshop.



Quiz. Exercises.

Day 10. Views and Temporary Tables.


Using Views to Access Data.



Creating a Basic View. Using Enterprise Manager Query Builder to Create Views. Using the Enterprise Manager Create View Wizard. Editing Views in Query Analyzer. Using Views for Security. Creating Views with Multiple Tables. Modifying Data in a View. Modifying and Renaming Views. Deleting a View.



Using Temporary Tables for Storing Data.



Creating a Temporary Table. Creating a Temporary Table on System Startup. Determining When to Use a Temporary Table.



Temporary Tables Versus Views. Summary. Q&A. Workshop.



Quiz. Exercises.

Day 11. T-SQL Programming Fundamentals.


Handling Variables.



Declaring and Setting Local Variables. Using Global Variables.



Reviewing Programming Structures.



Understanding Batches. Controlling Program Flow. Using the PRINT Statement. Using Comments.



Trapping Errors and Implementing Error Handling.



Communicating Error Messages. Raising an Error Message. Creating an Error Message. Coding Standard Error Handling Methods.



Summary. Q&A. Workshop.



Quiz. Exercise.

Day 12. Understanding Database Locking.


Lock Fundamentals.



Understanding Lock Modes. Examining Lock Scope. Examining Locks.



Testing Lock Interaction.



Setting the Lock Timeout. Monitoring Blocking Locks.



Understanding Lock Persistence.



Working with Transaction Isolation Levels. Using Hints to Control Lock Types. Deadlocks.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 13. Programming with Transactions.


Understanding Transaction Basics.



Understanding Automatic Transactions and the Transaction Log. Understanding the Write-Ahead Log. Rolling Back Changes.



Controlling Transactions in Your Programs.



Transaction Control Statements. Implicit, Explicit, and Autocommit Transactions. Transaction Error Handling.



Managing Concurrency.



Optimistic Locking. Application Resource Locking.



Handling Long-Running Transactions. Summary. Q&A. Workshop.



Quiz. Exercise.

Day 14. Using Cursors.


Defining Cursors.



Creating a Cursor. Cursor Types. Cursor Scope.



Working with Cursors.



Declaring the Cursor. Opening the Cursor. Declaring the Variables. Fetching the Rows. Closing the Cursor. Destroying the Cursor. Other Cursor Functions.



Cursor Locking. Summary. Q&A. Workshop.



Quiz. Exercise.

WEEK 2. IN REVIEW.

WEEK 3. PROCEDURES AND TRIGGERS.

Day 15. Writing and Executing Stored Procedures.


Benefits of Stored Procedures. Writing Your First Stored Procedure.



The INSERT Statement. The insert Procedure. Getting Information about Procedures. Executing Procedures.



Working with Parameters.



Execution Using a Parameter. Default Parameters.



Returning Procedure Status. Using Output Parameters.



Putting Output Parameters to Work. Communicating Between Stored Procedures. Using Output Parameters with Remote Procedure Calls.



Five Ways to Communicate Between Stored Procedures and Client Programs. Summary. Q&A. Workshop.



Quiz. Exercises.

Day 16. Optimizing Stored Procedures. Understanding Stored Procedure Optimization.


Using Good Procedure Performance Practices. Understanding the Compilation Process. Using the RECOMPILE Option in Stored Procedures. Using Table Variables and Temporary Tables with Procedures.



Using Table Variables in a Stored Procedure. Understanding the Scope of a Temporary Table. Indexing Temporary Tables. Handling Transactions in Stored Procedures.



Solving the Transactional Problem. Summary. Q&A. Workshop.



Quiz. Exercises.

Day 17. Debugging Stored Procedures.


Debugging Explained.



Finding the Problems. Creating a Good Work Environment. Displaying Debugging Messages. Using the PRINT Statement. Tracking Time. Tracking Elapsed Time Using Temporary Tables. Performing Error Checking in a Stored Procedure.



Using the T-SQL Debugger.



Starting the Debugger. Using the T-SQL Debugger Interface. Setting Breakpoints. Single-Stepping in a Procedure. Getting Output.



Summary. Q&A. Workshop.



Quiz. Exercises.

Day 18. Writing and Testing Triggers.


Understanding Triggers.



Writing Your First Trigger. Getting Information on Triggers. Maintaining Triggers. Naming Triggers.



Executing Triggers.



When Do Triggers Fire? How Does a Trigger Fire? INSTEAD OF Triggers.



Using Trigger Resources.



Accessing the INSERTED and DELETED Tables. Using the UPDATE() Function. Using Rollback in a Trigger.



Determining When to Use Triggers. Summary. Q&A. Workshop.



Quiz. Exercise.

Day 19. Solving Special Trigger Problems.


Managing Referential Integrity with Triggers.



Enforcing Referential Integrity with Triggers. Enforcing Business Rules with Triggers. Cascading Update and Delete Triggers.



Managing Special Database Actions with Triggers.



Managing Derived Columns. Synchronizing Real-Time Updates Between Databases. Maintaining an Audit Trail.



Summary. Q&A. Workshop.



Quiz. Exercise.

Day 20. User-Defined Functions in SQL.


Understanding User-Defined Functions.



Creating a Function. Creating a Scalar Function. Creating a Single Table Value Function. Creating a Multi-Table Function.



Reviewing Advanced UDF Topics.



Rewriting Stored Procedures as Functions. UDF Performance and Recommendations. Using Functions Instead of Views. Error Trapping and Functions.



Summary. Q&A. Workshop.



Quiz. Exercise.

Day 21. Handling BLOBs in T-SQL.


Understanding Large Data Types.



How Large Data Is Stored. Data Storage Options.



Managing BLOB Data.



Using the Large Object System Functions. Using the READTEXT, WRITETEXT, and UPDATETEXT Statements.



Summary. Q&A. Workshop.



Quiz. Exercises.

WEEK 3. IN REVIEW.

APPENDIX.

Appendix A. Answers to Quizzes and Exercises.
Index.