Table of Contents
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 (>, . 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.Promotional Information
Sams Teach Yourself Transact-SQL in 21 Days, 2E will teach
programmers how to develop Transact-SQL queries. There will be a
focus on providing methods for improving productivity without a
reducing performance. Specifically, the reader will:Learn
Transact-SQL syntax Learn how to add, delete, and modifying data
using Transact-SQL Understand coding standards Review variations
from ANSI-standard SQL Be presented with basic server operations.
Recognize performance issues with queries. This book will also
include:Constructs such as CUBE, ROLLUP, CASE, and JOIN. Techniques
to solve complex problems How the server uses indexes Methods to
write (correctly) stored procedures and triggers. Templates of
procedures and triggers (reference) Advanced Topics such as: Outer
and self joins Temporary tables Sub-queries.
About the Author
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).