Table of Contents
Introduction
Chapter 1 An Introdution SQL Server 6.5
SQL Server 6.5 Is new and Improved
SQL Enterprise Manager
Transact-SQL
SQL-DMO
Database Maintenance Plan Wizard
SQL Server Capacities
Tools and Utilities Included in SQL Server 6.5
ISQL/W
Working with Queries
Using Graphical Statistics I/O
MS Query
Starting MS Query
Working with an MS Query SQL Statement
SQL Enterprise Manager
Starting SQL Enterprise Manager
Accessing the Pubs Sample Database
Verifying the Pubs Database
Installing the Pubs Sample Database
SQL Server Books Online
The Downtown Delivery Service Project
Summary
Review Questions
Chapter 2 Data Modeling
The Relational Model
The Client/Server Model
File-Server Versus Distributed Client/Server
The Entity-Relationship Model
Entity-Relationship Diagramming
Entities
Entities in a Data Model
Naming Entities
Drawing Entities
Adding Attributes to a Data Model
Divide and Conquer
Uniqueness and Keys
Primary Keys
Column Constraints
Understanding NULL and NOT NULL
Disallowing Duplicates
Adding Defaults
Prohibiting Changes
Improving the ER Model for the Downtown Delivery Service
Relationships
One-to-One Relationships
One-to Many Relationships
Many-to-Many Relationships
Enforcing Data Integrity
Entity Integrity
Domain Integrity
Referential Integrity
Normalizing a Database Design
First Normal Form
Second Normal Form
Benefits of Normalization
Drawbacks of Normalization
Summary
Review Questions
Chapter 3 System Databases and Tables
Examining the System Databases
Identifying the Role of the Model Database
Identifying the Role of the msdb Database
Identifying the Types of Temporary Tables
Adjusting the Size of the Tempdb Database
Placing empdb in RAM
Discussing System Tables
System Catalog
Database Catalog
Querying System Tables
Accessing the System with System Stored Procedures
Catalog Stored Procedures
Extended Stored Procedures
Replication Stored Procedures
SQL Executive Stored Procedures
System Stored Procedures
Executing Stored Procedures
sp_help [objectname]
sp_helpdb[bdname]
sp_helpsql[topic]
sp_who
Estimating Space Requirements
Understanding SQL Server's Units of Storage
Data Pages
Data Rows
Allocation Units
Calculating Row and Table Size
Calculating the Number of Nonclustered Index Pages
Calculating the Size of the Inventory Table
Summary
Review Questions
Chapter 4 Data Definition
Managing Database Devices
Creating a Device
Creating the DownTown2 Device
Viewing Information about a Device
Using SQL Enterprise Manager to View Information about All Devices
Using sp_helpdevice to View Information about a Device
Expanding a Database Device
Using the Enterprise Manager to Expand a Database Device
Using Transact-SQL to Expand a Database Device
Expanding a Device with DISK RESIZE
Dropping a Device
Dropping a Device with SQL Enterprise Manager
Dropping a Device with sp_dropdevice Stored Procedure
Introducing SQL Server Databases
Exploring the Transaction Logs Role in SQL Server
Following the Execution Path of the Transaction Log
Managing SQL Server Databases
Creating a Database Using SQL Enterprise Manager
Creating a Database with the CREATE DATABASE Statement
Using CREATE DATABASE to Create a Database
Exploring the Available Database Options
Setting Database Options from within SQL Enterprise manager
Using sp_dboption to Set Database Options
Setting Off an ANSI Null default with sp_dboption
Modifying Databases and Transaction Logs
Expanding a Database
Using SQL Enterprise manager to Expand a Database
increasing the Size of a Database with ALTER DATABASE
Using ALTER DATABASE to Increase a Database's Size
Shrinking a Database
Using SQL Enterprise Manager to Shrink a Database
Shrinking a Database with DBCC SHRINKDB
Using DBCC SHRINKDB to Shrink the DowntownDB Database
Dropping a Database
Dropping a Database from SQL Enterprise Manager
Using DROP DATABASE to Drop a Database
Dropping a Database with DROP DATABASE
Understanding SQL Server 6.5 Datatypes
System Supplied Datatypes
User-Defined Datatypes
Managing Tables in SQL Server 6.5
Creating a Table with SQL Enterprise Manager
Using the CREATE TABLE Statement to Create a Table
Creating a Table with CREATE TABLE
Dropping a Table
Dropping a Table with SQL Enterprise Manager
Using DROP TABLE to Delete a Table
Dropping a Table with DROP TABLE
Discussing Data Integrity
Identifying the Types of Data Integrity
Enforcing Data Integrity with the IDENTITY Property
Identifying Guidelines Associated with the IDENTITY Property
Creating an Identity Column
Creating a Table with an Identity Column
Adding a Column with the IDENTITY Property to an Existing Table
Using Constraints to Enforce Data Integrity
Managing Constraints with the SQL Enterprise Manager
Managing Constraints with the CREATE TABLE and ALTERTABLE Statements
Creating a PRIMARY KEY Constraint
Creating a FOREIGN KEY Constraint
Creating a UNIQUE Constraint
Creating a CHECK Constraint
Summary
Review Questions
Chapter 5 Data Retrieval
A Refresher Course in SQL
Coding a SQL SELECT Statement
Using Character Strings and Quotes
Arithmetic Operators
Operator Precedence
Comparison Operators in SQL Statements
Keywords Used in a WHERE Clause
Using SQL Statements
Coding the SELECT Statement
Querying with Wildcards and the LIKE Clause
Sorting Records with the ORDER BY Clause
Using SQL's Built-In Functions to Format Output Data
Converting Datatypes with the CONVERT Functions
Performing Mathematical Operations with the Built-in Mathematical Functions
Performing Operations with String Functions
Retrieving Special Server or Database Information with System Functions
Using Text and Image Functions
Summary
Review Questions
Chapter 6 Advanced Data Retrieval Techniques
Returning Summary Values with Aggregate Functions
Calculating an Average
Counting Rows with the COUNT Aggregate Function
Counting Rows With Nulls
Counting All Rows with COUNT(*)
Finding the Maximum Value with MAX
Finding the Minimum Value with MIN
Adding the Value of a Column with SUM
Using the GROUP BY Clause
Grouping the Impact of the ALL Keyword on a GROUP BY Clause
Counting Groups of Data
AVG. and SUM with the GROUP BY Clause
Restricting Rows with the HAVING Clause
Coding a HAVING Clause
Including Multiple Conditions in a HAVING Clause
Using the COMPUTE and COMPUTE BY Clauses
Computing Totals with COMPUTE
Computing Multiple Values for the Same Column
Adding BY to a COMPUTE Clause
Using COMPUTE BY Multiple Times in the Same SELECT Statement
Joining Tables
ANSI-Standard versus Old-Style Joins
Creating a CROSS JOIN
Creating an INNER JOIN
Creating Outer Joins
Creating a LEFT OUTER JOIN
Creating a RIGHT OUTER JOIN
Creating a RIGHT OUTER JOIN
Creating a FULL OUTER JOIN
Using Self-Joins
Creating Joins with MS Query
Using Subqueries
Types of Subqueries
Defining Subquery Rules
Creating Subqueries That Return a Single Value
Creating Subqueries That Return Multiple Values
Adding NOT IN to a Subquery
Testing Existence with Subqueries
Writing Correlated Subqueries
Creating Derived Tables
Summary
Review Questions
Chapter 7 Creating Action Queries with Transact-SQL to Modify Data
Adding Rows with the Insert Statement
Exploring the INSERT Statement Rules
Inserting Rows with Values
Inserting a Partial Row with a Column List
Adding a Row with Defaults and Nulls
Using DEFAULT to Insert a Value for a Column a Row
Inserting Rows in a Table with an IDENTITY Column
Inserting Specific Values into an IDENTITY Column
Inserting Multiple Rows with a SELECT Statement
Inserting Rows with a SELECT Statement
Inserting Rows with a SELECT Clause
Copying a Subset of Data
Copying a Subset of Rows
Adding Constants to the Copied Data
Cleaning UP
Modifying Data with the UPDATE Statement
Overview of Direct and Deferred Updates
Writing a Simple UPDATE Statement
Updating Select Rows
Using Arithmetic in Updates
Including Subqueries in an UPDATE Statement
Providing a Column Value with a Subquery
Transact-SQL Extensions for the UPDATE Statement
Transact-SQL UPDATE Extension Example
Cleaning Up after Inserting Data
Deleting Rows of Data with the DELETE Statement
Defining the Rules for the DELETE Statement
Deleting All Rows from a Table
Deleting Selected Rows
Deleting Rows of Data Based on a Subquery
Deleting Rows Based on the Value in a Subquery
Transact-SQL Extensions for the DELETE Statement
Deleting Data with Transact -SQL Extensions
Cleaning Up after Deleting Data
Importing and Exporting
Using SQL Transfer Manager to Import and Export Data and Objects
Verifying Permissions Needed to Transfer Data
Identifying the Transfer Manager Options
Transferring the Authors Table with the Transfer Manager
Verifying the Results of the Transfer Manager
Examining the Files Created by the Transfer Manager
Transferring Data with the Bulk Copy Program
Permissions Requirements to Perform a Transfer with the Bulk Copy Program
Defining BCP Rules
Defining the BCP Mode Types
Saving Your BCP Options in a Format File
Copying a Table with the BCP Utility
Comparing the Transfer Manager and the Bulk Copy Program Review Questions
Chapter 8 Using Indexes
Advantages to Using Indexes
Disadvantages to Using Indexes
Creating Useful Indexes
Selecting the Right Index
Guidelines for Choosing Indexes
Guidelines for Choosing What Not to Index
SQL Server Index Types
Examining Clustered Indexes
Exploring Nonclustered Indexes
Refining Index Characteristics
Keeping Up with Index Statistics
Managing SQL Server Indexes
Defining the CREATE INDEX Options
Applying the FILLFACTOR and PAD_INDEX Options
Using the SORTED_DATA and SORTED_DATA_REORG Options
Rebuilding an Index
Examining the DBCC DBREINDEX Syntax
Rebuilding a Specific Index
Rebuilding All Indexes in a Table
Dropping Indexes
Understanding DROP INDEX Permissions
Dropping an Index
Monitoring an Index's Performance and Use
Managing Indexes with SQL Enterprise manager
Summary
Review Questions
Chapter 9 Designing and Using Views,Defaults ,and Rules
An Overview to a View
Creating a View
Guidelines for Creating Views
The CREATE VIEW Statement
Recognizing the Benefits of Using Views
Simplifying Queries
Focusing on Data
Providing Constant Data
Enforcing Security
Exporting Data
Recognizing a View's Limitations
Creating Views
Selecting Specific Columns
Selecting Specific Rows
Joining Tables to Create a View
Including Aggregate Functions in Views
Creating Views on Views
Modifying Data through Views
Inserting Rows through Views
Deleting Rows through Views
Updating Rows through Views
Dropping Rows through Views
Dropping Views
Defining the DROP VIEW Statement
Understanding Rules and Defaults
Creating Rules
Examining the CREATE RULE Statement
Understanding a Rule's Limitations
Creating a Sample Rule
Creating a Rule with the SQL Enterprise Manager
Creating Defaults
Examining the CREATE DEFAULT Statement
Defining a Default's Limitations
Creating a Sample Default
Creating a Default with the SQL Enterprise manager
Binding Rules
Some Guidelines for Binding Rules
Examining the sp_bindrule System Stored Procedure
Binding a Rule
Using SQL Enterprise Manager to Bind a Rule
Unbinding Rules
Examining the sp_unbindrule System Stored Procedure
An Example of unbinding a Rule
Using SQL Enterprise Manager to Unbind a Rule
Dropping Rules
Using SQL Enterprise Manager to Unbind a Rule
Dropping Rules
Using SQL Enterprise Manager to Unbind a Rule
Dropping Rules
Using SQL Enterprise Manager to Drop a Rule
Binding Defaults
Guidelines for Binding Defaults
Examining the sp_bindefault System Stored Procedure
Binding a Default
Using SQL Enterprise Manager to Bind a Default
Unbinding Default
Using SQL Enterprise Manager to Bind a Default
Unbinding Defaults
Examining the sp_unbindefault System Stored Procedure
Binding a Default
Using SQL Enterprise manager to Bind a Default
Dropping Defaults Examining the DROP DEFAULT Statement
Using SQL Enterprise Manager to Drop a Default
Summary
Review Questions
Chapter 10 Programmability
Statement Execution with Batches
Working with Variables
Defining and Using Local Variables
Examining the Syntax Required to Declare and Assign a Local Variable
Declaring and Assigning a Local Variable
Using Global Variables
obtaining the Version of SQL Server with a Global Variable
Adding Comments to Your Code
Using Control-of-Flow Statements
Creating Statement Blocks with BEGIN and END
Using CASE Expressions
Inspecting the Syntax of the CASE Expression
Testing a CASE Expression
Moving to a Label with GOTO
Investigating the GOTO Syntax
Transferring Execution with a GOTO Command
Controlling Program Execution with IF...ELSE
IF...ELSE Guidelines
Executing Code Based on an IF...ELSE Statement Block
Stopping Execution with the RETURN Command
Exiting a Batch with the RETURN Statement
Timing Execution with WAITFOR
Processing in the Future with WAITFOR
Creating Loops with WHILE Blocks
Creating a Loop with a WHILE Block
Working with Cursors
Exploring Cursor States
Declaring a Cursor
Guidelines for Declaring Cursors
Opening a Cursor
Counting the Number of Rows in an Opened Cursor
Fetching Data for a Cursor
Guidelines for the FETCH Statement
Keeping Track of the FETCH Status
Closing a Cursor
Deallocating a Cursor
Demonstrating Cursors
Discussing the Benefits of Using Stored Procedures
Achieving Faster Execution
Producing Modula Programming
Realizing Enhanced Reliability
Enforcing Consistency
Enhancing Security
Discussing the Capabilities of SQL Server and MAPI
Using MAPI to Respond to Server Problems
Understanding MAPI's Stored Procedures
Examining the String and Variable Extensions to the EXECUTE Statement
Executing Procedures
Examining the EXECUTE Statement Syntax for a Stored Procedure
EXECUTE Procedure Guidelines
Executing a Stored Procedure
Returning the Status of a Stored Procedure
Executing a Procedure with a Variable
Executing String Statements
Examining the EXECUTE Statement Syntax for a Character String
Executing a Character String
Managing User-Defined Error Messages
Providing Feedback with the RAISERROR Statement
Discussing RAISERROR Specifics
Raising a User-Defined Error
Summary
Review Questions
Chapter 11 Triggers
Understanding How Triggers Work
Creating Triggers
Examining the Syntax of the CREATE TRIGGER Statement
Trigger Guidelines
Identifying the Limitations of Triggers
Creating an INSERT Trigger
Creating an UPDATE Trigger
Creating a Table Level UPDATE Trigger
Creating a Column Level UPDATE Trigger
Creating a DELETE Trigger
Dropping Triggers
Enforcing Referential Integrity
Creating Triggers to Enforce Referential Integrity
Enforcing Data Integrity
Summary
Review Questions
Chapter 12 Replication
Examining the Publisher/Subscriber Metaphor
Applying Replication Appropriately
Applying the Appropriate Replication Model
Central Publisher
Central Publisher with a Remote Distributor
Publishing Subscriber
Multiple Publishers of One Table
Downloaded Data
Tracing the Log Reader Process
Examining the Server's Role in Replication
Recognizing the Role of the Publication Server
Managing the Publications
Managing Articles
Replication Synchronization
Replication Security
Examining the Frequency of Replication
Recognizing the Role of the Distribution Server
Recognizing the Role of the Subscription Server
Identifying the Tables Used in Replication
Tracing the Replication Process
The Publisher
The Distributor
The Subscriber
Summary
Review Questions
Chapter 13 Application Development and Open Data Services
Defining Open Architecture
Open Database Connectivity's Role in SQL Server
Creating an ODBC Data Source
Using ODBC with Visual Basic and SQL Server
Data Access Objects
Remote Data Objects
ActiveX Data Objects
The Benefits of Integrating OLE Architecture with SQL Server
Exposing Objects with SQL-Distributed Management Objects
Examining the SQL-DMO Model
Understanding Application Objects and Collections
Using SQL-DMO
Extending SQL Server with DB-Library
Extending SQL Server with Open Data Services
Introducing SQL Distributed Management Framework
The SQL-DMF Model
Summary
Review Questions
Appendix A Review Questions and Answers
鄂公网安备 42010302001612号 