Tutorial: Microsoft SQL Server 2000

Please select a chapter from the "Contents" menu at the very bottom of the page.

Microsoft SQL Server 2000

Part I: Welcome to Microsoft SQL Server Chapter 1. The Microsoft SQL Server Environment Overview of Client/Server SQL Server 2000 Components and Features Summary Chapter 2. SQL Server 2000 Environments and Editions SQL Server 2000 Environments SQL Server 2000 Editions SQL Server Licensing Models Summary Chapter 3. What's New in SQL Server 2000 New SQL Server 2000 Features SQL Server 2000 Enhancements SQL Server and the .NET Framework Summary Part II: SQL Server Tools and Utilities Chapter 4. SQL Server Enterprise Manager Establishing Server Groups and Registering SQL Server in Enterprise Manager Server Configuration Databases Security Server Management Data Transformation Services Support Services System Tools Replication Using the Database Taskpad Using the Query Designer Scripting Objects Getting Help Summary Chapter 5. SQL Server Command-Line Utilities BCP Data Transformation Services Utilities ISQL/OSQL 'makepipe' and 'readpipe' ODBCcmpt odbcping rebuildm regrebld Replication Utilities SQLDiag SQLMaint SQLServr VSwitch Summary Chapter 6. SQL Server Query Analyzer and SQL Debugger Query Analyzer The SQL Debugger Summary Chapter 7. Using the SQL Server Profiler SQL Server Profiler Architecture Creating Traces Saving and Exporting Traces Replaying Trace Data Defining Server-Side Traces Profiler Usage Scenarios Summary Part III: SQL Server Administration Chapter 8. Installing and Upgrading SQL Server Choosing a SQL Server Edition Hardware Requirements Software Requirements Selecting Installation Configuration Options Verifying Installation Starting, Stopping, and Pausing SQL Server Post-Installation Configuration Installation Troubleshooting Remote Installation Unattended Installation Installing a Named Instance Upgrading from Previous Versions Summary Chapter 9. Client Installation and Configuration The SQL Server Client Architecture Installing Client Tools Client Configuration Network Protocol Support Installing ODBC Microsoft Driver for JDBC Connecting to SQL Server over the Internet Summary Chapter 10. SQL Server System and Database Administration Responsibilities of the System Administrator System Databases System Tables System-Stored Procedures Useful System Procedures Other Methods of Querying the System Tables Summary Chapter 11. Creating and Managing Databases What Makes Up a SQL Server Database? Data Storage in SQL Server Database Files Creating Databases Managing Databases Setting Database Options Summary Chapter 12. Creating and Managing Tables in SQL Server Datatypes Numeric Datatypes Datatype Synonyms User-Defined Datatypes Creating Tables Creating Tables Using T-SQL Creating Tables Using Table Designer Viewing Table Properties and Data in Enterprise Manager Limitations on Table Size and Dimensions Dropping Tables Temporary Tables Summary Chapter 13. Creating and Managing Indexes Types of Indexes Creating Indexes Using T-SQL Creating Indexes Using Enterprise Manager Managing Indexes Dropping Indexes Summary Chapter 14. Implementing Data Integrity Types of Data Integrity Enforcing Data Integrity Constraints Rules Defaults Summary Chapter 15. Security and User Administration An Overview of SQL Server Security Authentication Methods SQL Server Security: Logins SQL Server Security: Users SQL Server Security: Roles Managing SQL Server Logins Managing SQL Server Users Managing Database Roles Managing SQL Server Permissions SQL Server Permission Approaches Object Owners Using Encryption Summary Chapter 16. Database Backup and Restore Developing a Backup and Restore Plan Why Back Up Your Databases? A Typical Backup and Restore Scenario Types of Backups Setting the Recovery Mode Backup Devices Media Sets and Families Creating Backup Devices with Transact-SQL Creating Backup Devices with SQL Enterprise Manager Backing Up the Database Backing Up the Transaction Log Restoring the Database Transact-SQL Restore Examples Restoring to a Different Database Restoring a File or Filegroup Restoring to a Point in Time Performing a Partial Database Restore Restoring the System Databases Additional Backup Considerations Summary Chapter 17. Database Maintenance What Needs to Be Maintained The Database Maintenance Plan Wizard Setting Up Your Own Maintenance Tasks The Database Console Commands (DBCC) Using 'SQLMAINT.EXE' for Database Maintenance Summary Chapter 18. SQL Server Scheduling and Notification SQL Agent Overview Configuring SQL Server Agent Viewing the SQL Server Agent Error Log Operators Jobs Alerts Scripting Jobs and Alerts Multiserver Job Management Event Forwarding Summary Chapter 19. Managing Linked and Remote Servers Remote Servers Linked Servers Adding, Dropping, and Configuring Linked Servers Mapping Local Logins to Logins on Linked Servers Obtaining General Information About Linked Servers Executing a Stored Procedure Via a Linked Server Setting Up Linked Servers Through Enterprise Manager Summary Chapter 20. Importing and Exporting SQL Server Data Using BCP and DTS Bulk-Copy Program (BCP) The Bulk-Copy Program Logged and Non-Logged Operations The 'BULK INSERT' Statement (Transact-SQL) Improving Load Performance BCP Extras Data Transformation Services (DTS) DTS Architecture and Concepts Package Execution Utilities Running the DTS Wizard DTS Designer A Bit More on Metadata Summary Chapter 21. Administering Very Large SQL Server Databases Do I Have a Very Large Database (VLDB)? VLDB Maintenance Issues Partitioning Data Summary Chapter 22. Data Replication What Is Replication? The Publisher, Distributor, and Subscriber Metaphor Replication Scenarios Replication Agents Planning for SQL Server Data Replication SQL Server Replication Types User Requirements Drive the Replication Design Setting Up Replication Scripting Replication Monitoring Replication Summary Chapter 23. SQL Mail Setting Up an E-Mail Client/Profile Configuring SQL Mail Configuring SQLAgentMail SQL Mail Stored Procedures Summary Chapter 24. SQL Server Clustering From Windows NT Enterprise Edition to Windows 2000 Advanced Server Cluster Services SQL Clustering and Fail-Over Support Network Load Balancing Summary Chapter 25. Defining System Administration and Naming Standards The SQL Server Environment Approach SQL Server Naming Standards Summary Part IV: Transact-SQL Chapter 26. Using Transact-SQL in SQL Server 2000 T-SQL and ANSI/ISO SQL What's New for T-SQL in SQL Server 2000 'SELECT', 'INSERT', 'UPDATE', and 'DELETE' SQL Server Functions Programming Constructs Cursors Summary Chapter 27. Creating and Managing Views in SQL Server Definition of Views Using Views to Simplify Retrieval of Data Using Views to Hide Underlying Table Structures Using Views as a Security Mechanism Data Modifications and Views Creating Views Altering and Dropping Views Partitioned Views Views and Query Performance Summary Chapter 28. Creating and Managing Stored Procedures in SQL Server Advantages of Stored Procedures Creating and Executing Stored Procedures Deferred Name Resolution Viewing and Modifying Stored Procedures Using Input Parameters Using Output Parameters Returning Procedure Status Cursors in Stored Procedures Nested Stored Procedures Using Temporary Tables in Stored Procedures Using the 'table' Datatype Remote Stored Procedures Debugging Stored Procedures with Query Analyzer Debugging with Microsoft Visual Studio and Visual Basic System Stored Procedures Stored-Procedure Performance Using Dynamic SQL in Stored Procedures Autostart Procedures Extended Stored Procedures Stored Procedure Coding Guidelines and Limitations Summary Chapter 29. Creating and Managing Triggers Benefits and Uses of Triggers Creating Triggers 'AFTER' Triggers 'inserted' and 'deleted' Tables Checking for Column Updates Nested Triggers Recursive Triggers Enforcing Referential Integrity with Triggers 'INSTEAD OF' Triggers Summary Chapter 30. User-Defined Functions Why Use User-Defined Functions? Types of Functions Creating and Managing Functions System-Wide Table Valued Functions Rewriting Stored Procedures as Functions Summary Chapter 31. Transaction Management and the Transaction Log What Is a Transaction? How SQL Server Manages Transactions Defining Transactions Transaction Logging and the Recovery Process Transactions and Batches Transactions and Stored Procedures Transactions and Triggers Transactions and Locking Coding Effective Transactions Long-Running Transactions Bound Connections Distributed Transactions Summary Chapter 32. Distributed Transaction Processing Distributed Transaction Processing Distributed Queries Linked Servers Distributed Transactions MS DTC Architecture Summary Part V: SQL Server Internals and Performance Tuning Chapter 33. SQL Server Internals SQL Server Memory Management SQL Server Process Management SQL Server Disk I/O SQL Server Storage Structures Database Files and Filegroups Database Pages Tables Data Modification and Performance Summary Chapter 34. Indexes and Performance Evaluating Index Usefulness The Index Tuning Wizard Choosing Indexes: Query Versus Update Performance Summary Chapter 35. Understanding Query Optimization What Is a Query Optimizer? Query Compilation and Optimization Step 1: Query Analysis Step 2: Index Selection Step 3: Join Selection Step 4: Execution Plan Selection Reusing Query Plans Other Query Processing Strategies Parallel Query Processing Data Warehousing and Large Database Query Strategies Common Query Optimization Problems Managing the Optimizer Summary Chapter 36. Query Analysis Displaying Execution Plans in Query Analyzer Query Analyzer Server Trace Query Analyzer Client Statistics 'SHOWPLAN_ALL' and 'SHOWPLAN_TEXT' Statistics Query Analysis with SQL Profiler Summary Chapter 37. Monitoring SQL Server Performance Performance-Monitoring Approach Performance Monitor Windows Performance Counters SQL Server Performance Counters SNMP Support Using 'DBCC' to Examine Performance Other SQL Server Performance Considerations Summary Chapter 38. Locking and Performance The Need for Locking Transaction Isolation Levels in SQL Server The Lock Manager Monitoring Lock Activity in SQL Server SQL Server Lock Types SQL Server Lock Granularity Lock Compatibility Locking Contention and Deadlocks Table Hints for Locking Optimistic Locking Summary Chapter 39. Database Design and Performance Basic Tenets of Designing for Performance Logical Database Design Issues Denormalizing the Database Database Filegroups and Performance RAID Technology Summary Chapter 40. Configuring, Tuning, and Optimizing SQL Server Options SQL Server Instance Architecture Configuration Options Fixing an Incorrect Option Setting Setting Configuration Options with SQL Enterprise Manager Obsolete Configuration Options Configuration Options and Performance Summary Part VI: Additional SQL Server Features Chapter 41. Using XML in SQL Server 2000 Creating a Virtual Directory for Use with SQL Server 2000 Exploring Extensible Markup Language (XML) Retrieving Data Using the 'FOR XML' Clause Using 'FOR XML RAW' Using 'FOR XML AUTO' Using 'FOR XML AUTO, ELEMENTS' Using 'FOR XML EXPLICIT' Retrieving XML-Data Schemas Retrieving Binary Data in XML The Basics of XML Path Language (XPath) Using XML in Stored Procedures Using 'OPENXML' to Read XML Using URL Queries Using XML Templates Using XML Updategrams Summary Chapter 42. Microsoft SQL Server Analysis Services What Is Analysis Services and OLAP? Understanding the Analysis Services Environment and the 'Land of Wizards' An Analytics Design Methodology An Analytics Mini-Methodology An OLAP Requirements Example OLAP Cube Creation Creating an OLAP Database Working with a Relational Database Files at the Operating System Level Summary Chapter 43. Microsoft Transaction Server MTS Overview Auilding an MTS Component Installing an MTS Component Configuring Security Running an MTS Application Using Database Connection Pooling Summary Chapter 44. SQL Server Full-Text Search Services How Search Server Works Setting Up a Full-Text Index Maintaining Full-Text Indexes Full-Text Searches Using Document Filters Summary Chapter 45. SQL Server Notification Services Introduction to SQL Notification Services Obtaining and Installing SQL Notification Services SQL Notification Services Architecture Instances and Applications Deploying and Configuring a Notification Services Instance Monitoring and Administering a Notification Services Instance Summary Chapter 46. SQL Server and the Microsoft .NET Framework The Microsoft .NET Framework Class Library ADO.NET .NET SQL Providers .NET Concurrency Model (Default) XML Externalization in .NET A .NET Application Within Visual Studio .NET Summary Chapter 47. English Query What Is English Query? Getting Started with English Query How Does English Query Work? Building a Simple English Query Application Contextual Information Summary