Microsoft SQL Server™ 2000

Rahul Sharma is a senior database administrator for Manhattan Associates, Inc., and a columnist for both Swynk.com and SQLServerCentral.com. He has been working with Microsoft SQL Server since the release of SQL Server 6.5 and is currently working with both SQL Server 2000 and Oracle 9i. He is a Microsoft Certified Professional with more than six years of experience in database administration. 0201752832AB11162001

(NOTE: Each chapter concludes with a Summary.)

Preface.


Acknowledgments.


1. SQL Server 2000 Overview.


SQL Server Editions.



Hardware Requirements for Installation.



Installation of SQL Server 2000.



Debugging Installation Issues with SQL Server 2000.



Upgrading from a Previous Version of SQL Server.



Edition Upgrades.



Adding Components to an Instance of SQL Server 2000.



Uninstalling SQL Server 2000.



Unattended Installs.



Installing SQL Server Using SMS.



Rebuilding the Registry.



Multiple Instances.



Default Instances.



Named Instances.



File Locations for Multiple Instances of SQL Server.



Network Protocols.



Collations Support for Internationalization.



Checklist for a Successful Install of SQL Server.



Maintenance and Integrity Checks.

I. NEW FEATURES AND IMPROVEMENTS OVER SQL SERVER 7.0.

2. Enhancements and Changes to Existing Features.


Feature Sets.



Fully Web-Enabled.



English Query.



More Scalable and Reliable.



BizTalk Server 2000.



Multi-Instance Support.



Reduction in Development Time.



Accessing Data via Internet.



Web-Based Analysis.



VI SAN Support.



Indexed Database Views.



T-SQL Debugger.



User-Defined Functions.



New Data Types.



Cascading DRI.



Distributed Partitioned Views.



OLAP Services.



DTS.



INSTEAD OF and AFTER Triggers.



Collation Enhancements.



Full-Text Search Enhancements.



Multiple Instances of SQL Server.



Index Enhancements.



Failover Clustering Enhancements.



Net-Library Enhancements.



64-GB Memory Support.



Distributed Query Enhancements.



Kerberos and Security Delegation.



Backup and Restore Enhancements.



Scalability Enhancements for Utility Operations.



Text in Row Data.

3. XML Support in SQL Server 2000.


Generating XML with the SELECT Statement.



Generating XML over the Internet.



Retrieving XML-Formatted Data from SQL Server.



Reserved Characters.



XML Templates.



Using EXPLICIT Mode.



XSL Stylesheets.



XML System Stored Procedures.



OPENXML.



Writing Queries Against an XML Document.



OPENXML Parameters.



XPath Expression to Identify the Nodes to Be Processed (rowpattern).



Description of the Rowset to Be Generated.



Mapping Between the Rowset Columns and the XML Nodes.



Specifying Metaproperties in OPENXML.



Examples.

4. Engine Enhancements in SQL Server 2000.


The Enhancements.



Interacting with Data.



Reading Data More Effectively.



Top n Enhanced.



Shared Scans.



Concurrency.



Tables and Indexes.



In-Row Text.



New Data Types.



Indexes.



Logging and Recovery.



Logical Log Marks.



Shrinking the Transaction Log.



Recovery Models.



Improved Backup Functionality.



Administrative Improvements.



Database Verification.



Database State Control.



System Process IDs and Units of Work.



Dynamic Tuning.



Data Storage Components.



Memory Pool.



Files, Filegroups, and Disks.

5. DTS Enhancements.


New Features in DTS.



New Custom Tasks.



Enhanced Logging Facilities.



Saving DTS Packages to Visual Basic Files.



Using the Multiphase Data Pump.



Using Parameterized Queries.



Using Global Variables to Pass Information Between DTS Packages.



Using DTS Designer.



Tasks: Defining Steps in a Package.



Workflows: Setting Task Precedence.



Connections: Accessing and Moving Data.



Data Pump: Transforming Data.



Data Pump Error Logging.



Data Pump Phases.



Options for Saving DTS Packages.



DTS as an Application Development Platform.

6. Profiler and Index-Tuning Wizard Enhancements.


SQL Profiler Terminology 113Starting Profiler Trace.



General.



Events.



Data Columns.



Filters.



Templates.



Defining Your Own Trace Using Stored Procedures.



Defining a Server-Side Trace.



Known Bug in SQL Server 2000 Profiler.



Symptoms.



Cause.



Resolution.



Index Tuning Wizard.



Analyzing Index Tuning Wizard Output.



Starting the Index Tuning Wizard.



Itwiz.exe.

7. Replication Enhancements.


Types of Replication.



Replication Options.



New Features and Improvements.



Implementing Replication.



Merge Replication.



Transactional Replication.



Queued Updating.



Transforming Published Data.



Replication Usability.



Log Shipping.



Configuring Log Shipping with the Database Maintenance Plan Wizard.



Configuring Log Shipping Manually.

8. New Data Types.


Bigint.



sql_variant.



Table.

9. User-Defined Functions.


Types of User-Defined Functions.



Scalar Functions.



Table-Valued Functions.



Obtaining Information About Functions.

10. Indexed Views.


Performance Gains from Indexed Views.



Benefits of Using Indexed Views.



Query Optimizer.



Design Considerations.



Creating an Indexed View.



Using SET Options to Obtain Consistent Results.



Using Deterministic Functions.



Additional Requirements.



Maintaining Indexed Views.

11. Trigger Alternatives.


Types of Triggers.



AFTER Triggers.



INSTEAD OF Triggers.



Core Difference Between INSTEAD OF and AFTER Triggers.



Which Trigger to Pick?



Designing INSTEAD OF Triggers.

12. Meta Data Services Enhancements.


Meta Data Fundamentals.



Meta Data Is Abstract.



Meta Data Has Context.



Meta Data Has Multiple Purposes.



Meta Data Management.



Information Model Fundamentals.



Information Model Building Blocks.



Standard Information Models.



Importance of Information Models.



New Features in Meta Data Services.



Meta Data Browser Enhancement.



XML Encoding Enhancements.



XML in Meta Data Services.

13. Tool Enhancements.


Query Analyzer.



Templates.



Using Templates in SQL Query Analyzer.



Building Your Own Template.



Copy Database Wizard.



Uses of Copy Database Wizard.



Copy and Move Process.

14. Backup and Recovery Enhancements.


Backup and Restore Architecture.



Capabilities.



Recovery Models.



Simple Recovery.



Full Recovery.



Bulk-Logged Recovery.



Switching Recovery Models.

15. Analysis Services Enhancements.


Cube Enhancements.



Dimension Enhancements.



Data Mining Enhancements.



Other Enhancements.

16. Distributed Partitioned Views.


What Are Distributed Partitioned Views?



How Distributed Partitioned Views Work.



Building Distributed Partitioned Tables and Views.



How the Query Processor Uses Distributed Partitioned Views.



Designing Systems That Maximize Distributed Partitioned View Performance.



Designing Partitions.



Designing the Application Tier for Load Balancing.



Designing for High Availability.



Backing Up and Restoring Federated Database Servers.



Updatable Partitioned Views.



Table Rules.



Column Rules.



Partitioning Column Rules.



Data Modification Rules.



INSERT Statements.



UPDATE Statements.



DELETE Statements.



Distributed Partition View Rules.

17. T-SQL Debugger.
18. Cascading Declarative Referential Integrity.


Cascading Referential Integrity Constraints.



Multiple Cascading Actions.



Triggers and Cascading Referential Actions.



Cascading Referential Constraints Catalog Information.

19. Collation Enhancements.


How Character Data Is Stored.



International Data and Unicode.



Sort Order.



What Is Unicode and How Can It Be Used?



UCS-2.



UTF-16.



UTF-8.



Data Types in SQL Server 2000.



Non-Unicode Text Types: char, varchar, text.



Unicode Text Types: nchar, nvarchar, ntext.



Date/Time Types: datetime, smalldatetime.



Collation in SQL Server 2000.



Collations Specified at Multiple Levels.



Collations at the Server Level.



Collations at the Database Level.



Collations at the Column Level.



Collations in Expressions.



Considerations Before Changing the Collation of a Database.



Collation Precedence.



COLLATE Keyword.



Limitations of the COLLATE Keyword.



Issues with Defining Collation at the Column Level.



LCIDs and Collations.



ISO Strings and Collations.



Multilingual Data in the User Interface.



Multilingual Information in the Grid and SQL Panes of SQL Query Analyzer.



Format Issues in the Query Designer.



Sort Order.



Double-Byte (DBCS) Characters.



Getting to SQL Server Data (Data Access Methods).



Multilingual Transact-SQL.

II. ADVANCED DBA TOPICS.

20. SQL-Distributed Management Objects.


What Is SQL-DMO?



SQL-DMO Object Model.



ExecuteImmediate Method.



Script Method.



SQL-DMO Code for Executing a Job.



SQL-DMO Code to Play with Triggers.

21. Microsoft Desktop Engine.


Portable and Compatible.



Minimum Requirements.



Features and Limitations.



MSDE Install.



Scenarios for Using MSDE.



Developing Applications with MSDE.



Sample Application.



MSDE Service Pack1.

22. Administration.


Database Consistency Checks (DBCCs).



Undocumented DBCC Commands.



Information Schema Views.



Orphaned Sessions.



Issues with Orphaned Sessions.



Resolving Issues through the Query Analyzer.



Changing the Check Frequency.



Linked Servers.



Creating Linked Servers.



Setting the Linked Server Options.



Creating Logins for the Linked Server.



Querying the Linked Server.

23. Debugging Database Performance Issues.


Performance Monitor or Task Manager?



Performance Monitor.



Using the Performance Monitor.



Which Process Is the Bottleneck?



Task Manager.



Performance Monitor Counters.



CPU Performance Counters.



I/O Performance Counters.



Memory Performance Counters.



Network Performance Counters.



SQL Server Performance Counters.



Settings.



Event Viewer.



SQL Server Profiler.



Hardware Issues.



Database Setup.



SQL Tips.



Create and Use Proper Indexes.



Use Clustered Indexes.



Short Index Keys.



Use Covering Indexes.



Help SQL Server Choose Indexes.



Estimate Index Selectivity.



Use the SQL Server Query Optimizer.



Use Indexes Effectively.



Use Foreign Keys for Joins.



Use Concatenated Keys Correctly.



Use Only Selective Indexes.



Avoid NULL Checks.



Avoid Functions and Expressions on the Indexed Columns.



Avoid the Inequality Operator.



Avoid Full Table Scans.



Use EXISTS to Check for the Existence of a Record.



Replace NOT IN with NOTEXISTS.



Large Insert, Update, and Delete SQLs.



Do Not Use Positional Numbers.



Use Group Functions Carefully.

24. SQL Server 2000 Service Pack 1.


Before the Install.



Installing Service Pack 1.



Install Database Components SP1.



Install Analysis Services SP1.



Install Desktop Engine SP1.



Removing Service Pack 1/Rolling Back to the Previous State.



Fixing Bugs.



Fixes for MDAC.



SQL Server 2000 Fixes.

III. COMPARISON WITH ORACLE 9I.

25. Oracle 9i vs. SQL Server 2000.


Database Definition.



Database System Catalogs.



Physical and Logical Storage Structures.



Striping Data.



Transaction Logs and Automatic Recovery.



Networks.



Comparison of Features Provided by the Two RDBMS.



New Features in Oracle 9i.

26. Migrating from Oracle 9i Databases to SQL Server 2000.


Database Security and Roles.



Database File Encryption.



Network Security.



Login Accounts.



Groups, Roles, and Permissions.



Database Users and the Guest Account.



Sysadmin Role.



db-owner Role.



Defining Database Objects.



Database Object Identifiers.



Qualifying Table Names.



Creating Tables.



Table and Index Storage Parameters.



Views.



Indexed Views/Materialized Views.



Indexes.



Clustered Indexes.



Nonclustered Indexes.



Index Syntax and Naming.



Index Data Storage Parameters.



Ignoring Duplicate Keys.



Indexes on Computed Columns.



Using Temporary Tables.



Data Types.



Using Unicode Data.



Object-Level Permissions.



Enforcing Data Integrity and Business Rules.



Naming Constraints.



Primary Keys and Unique Columns.



Adding and Removing Constraints.



Generating Unique Values.



Domain Integrety.



Referential Integrity.



User-Defined Integrity.



Delaying the Execution of a Stored Procedure.



Specifying Parameters in a Stored Procedure.



Triggers.



Transactions, Locking, and Concurrency.



Transactions.



Locking and Transaction Isolation.



Dynamic Locking.



Changing Default Locking Behavior.



Handling Deadlocks.



Insert Statements: The Forgotten Culprit.



Remote Transactions.



Distributed Transactions.



Two-Phase Commit Processing.



SQL Language Support.



SELECT and Data Manipulation Statements.



SELECT Statements.



INSERT Statements.



UPDATE Statements.



DELETE Statements.



TRUNCATE TABLE Statement.



Manipulating Data in Identity and Timestamp Columns.



Locking Requested Rows.



Row Aggregates and the Compute Clause.



Join Clauses.



Reading and Modifying BLOBs.



Functions.



Conditional Tests.



Converting Values to Different Data Types.



User-Defined Functions.



Comparison Operators.



Pattern Matches.



Using NULL in Comparisons.



String Concatenation.



Control-of-Flow Language.



Declaring Variables.



Assigning Variables.



Statement Blocks.



Conditional Processing.



Repeated Statement Execution (Looping).



GOTO Statement.



PRINT Statement.



Returning from Stored Procedures.



Raising Program Errors.



Implementing Cursors.



Declaring a Cursor.



Opening a Cursor.



Fetching Data.



CURRENT OF Clause.



Closing a Cursor.



Developing and Administering Database Replication.



Moving Data and Applications.

IV. SCRIPTS AND UTILITIES.

27. Administration and Utility Scripts.


Scripts.



Disable Constraints.



Enable Constraints.



USP_Find_Lock.



USP_Lock_Info.



Greatest.



Update Statistics.



USP_Active.



Objects.



Number_Of_Rows.



USP_Update_One_Table.



USP_System_Info.



USP_Null_Columns.



USP_SetUP_Paths.



USP_Identity_Columns.



USP_DB_File_Info.



USP_NOIndexes.



USP_RenameDB.



USP_ForceDropDB.



USP_Trace_BlackBox.



Registry_Info.



USP_KillConnections.



USP_FK_PK.



USP_DefragDatabase.



USP_DBUpdate.



USP_Calc_Space.



BackUp.vbs.



USP_PingServers.



Schema Comparison Scripts.



USP_MonitorServices.



USP_DTSReplaceOwner.



DTS Packages.



Refresh Databases.



Back Up Databases and Version Control.



ScriptData.



ExportPackages.vbs.



ImportPackages.vbs.



Executing DTS Through T-SQL.



Copying Databases from Server to Server.



Method 1.



Method 2.



Method 3.



Method 4.



Method 5.

V. SQL SERVER.NET.

28. SQL Server 2000 and .NET.


What Is Microsoft.NET?



From Products Vendor to Services Vendor.



Web Services.



.NET Servers.



SQL Server 2000 and .NET.



Next Release of Microsoft Server.



Windows XP and .NET.

Appendix.
Index. 0201752832T03082002