Main Page


   
  Table of Contents
  Index
  Examples
Microsoft SQL Server 2000 Unleashed, Second Edition
By Ray Rankins, Paul Bertucci, Paul Jensen
 
Publisher : Sams Publishing
Date Published : December 18, 2002
ISBN : 0-672-32467-9
Pages : 1560


Microsoft SQL Server 2000 Unleashed, 2E offers a variety of topics for system and database administrators to help them learn new features of the product and to solve problems they face on a daily basis. It shows them how to build upon their working knowledge of the product and take their experience and knowledge to a higher level. This new edition of Microsoft SQL Server 2000 Unleashed covers the latest updates and service packs to SQL Server 2000, including full support for XML, notification services, and SQL Server CE.


   
  Table of Contents
  Index
  Examples
Microsoft SQL Server 2000 Unleashed, Second Edition
By Ray Rankins, Paul Bertucci, Paul Jensen
 
Publisher : Sams Publishing
Date Published : December 18, 2002
ISBN : 0-672-32467-9
Pages : 1560


    Copyright
    About the Lead Authors
      About the Contributing Authors
      About the Technical Editors
   
    Acknowledgments
    Introduction
      Who Is This Book's Intended Audience?
      The Layout of This Book
      Conventions Used in This Book
      Good Luck!
   
    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
      Indexed 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
      Indexes
      Data Modification and Performance
      Summary
   
      Chapter 34.  Indexes and Performance
      Index Usage Criteria
      Index Selection
      Evaluating Index Usefulness
      Index Statistics
      Index Design Guidelines
      Indexed Views
      Indexes on Computed Columns
      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
   
   
    Index