- ホーム
- > 洋書
- > 英文書
- > Computer / General
Full Description
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.
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.
