Special Edition Using Microsoft SQL Server 6.5

• Credits
• About the Authors
• Acknowledgments
• We'd Like To Hear From You!
• Contents at a Glance
• Contents

Chapter 01 - Introducing Microsoft SQL Server

• Why the Move to SQL Server?
• Exploring the Origin and Evolution of SQL Server
• Understanding Relational Features of SQL Server
• Exploring Client/Server Features of SQL Server
• Examining SQL Server Features and Components
  • Devices and Databases
  • Transact-SQL
  • Command-Line Applications
  • Applications
    • ISQL/w
    • The SQL Client Configuration Utility
    • Using SQL Server Books Online
    • SQL Service Manager
    • SQL Enterprise Manager
    • SQL Performance Monitor
    • SQL Security Manager
• From Here...

Chapter 02 - Data Modeling and Database Design

• Understanding the Basics: Normalization
  • First Normal Form
  • Second Normal Form
  • Third Normal Form
• Understanding the Client/Server Model
  • Typical Roles: The Client Side
  • Typical Roles: The Server Side
  • Exceptions to the Rules and Roles
• Establishing a Roadmap to Your Database
  • Entity Relationship Diagramming: The Flowcharts of the Database World
    • Using and Referencing Entities
    • Using and Referencing Attributes
    • Using and Referencing Relationships
  • System Architecture Definition
  • Review the User's Goals for the System
  • Avoid Many-to-Many Joins
  • When to Break the Mold
• From Here...

Chapter 03 - Understanding the Underlying Operating System, Windows NT

• Understanding Multiprocessing, Multitasking, and Multithreading
• Understanding Multi-Architecture
• Understanding the Multiuser Environment of SQL Server on Windows NT
• Understanding the Windows NT Network Components
  • Sharing Resources
  • Installing Network Software
  • Configuring Adapter Cards
• Understanding Workgroups
• Understanding NT Domains
• Understanding the NT Performance Monitor
  • Selecting Objects and Counters in a Chart View
  • Displaying Information in a Report View
  • Selecting Objects and Counters in an Alert View
  • Selecting Objects in a Log View
• Displaying and Interpreting SQL Server Events
  • Configuring the Application Event Log
  • Displaying Event Details
  • Using the View menu
  • Clearing the Event Log
• From Here...

Chapter 04 - Creating Devices, Databases, and Transaction Logs

• Defining Devices
  • Creating Database Devices
    • Using SQL Enterprise Manager
    • Using DISK INIT
    • Using Device Options
      • Mirroring
      • Default Devices
    • Displaying Device Information
  • Creating Dump Devices
    • Using SQL Enterprise Manager to Add a Dump Device
    • Using sp_addumpdevice
  • Dropping Devices
    • Using SQL Enterprise Manager to Remove Devices
    • Using sp_dropdevice to Remove Devices
• Defining Databases and Transaction Logs
  • Creating a Database and Transaction Log
    • Using SQL Enterprise Manager to Create Database and Transaction Logs
    • Using the CREATE DATABASE Command to Create Database and Transaction Logs
  • Displaying Database Information
  • Increasing the Size of the Database and Transaction Log
    • Using SQL Enterprise Manager
    • Using the ALTER DATABASE Command to Extend a Database
  • Dropping Databases
    • Using SQL Enterprise Manager to Drop a Database
    • Using the DROP DATABASE Command to Drop a Database
• Defining Segments
  • Using Default Segments
  • Adding Segments
  • Extending Segments
  • Using Segments
  • Dropping Segments
• Using the Tempdb Database
  • Adjusting Tempdb's Size
  • Placing Tempdb in RAM
• Using Removable Media for Databases
  • Creating a Removable Database
  • Using the Removable Database
  • Installing the Removable Database
  • Uninstalling a Removable Media Database
• From Here...

Chapter 05 - Creating Database Tables and Using Datatypes

• Creating Tables
• Understanding Datatypes
  • Creating Temporary Tables
• Selecting Datatypes
  • Numeric integer Datatypes
    • int or integer
    • smallint
    • tinyint
    • Enforcing Value Ranges
  • Numeric floating-point Datatypes
    • The real Datatype
    • float[(n)] Datatypes
    • decimal[(p[, s])] and numeric[(p[, s])] Datatypes
  • Character Datatypes
    • char Datatype
    • varchar Datatype
    • Using Character Datatypes
  • datetime and smalldatetime Datatypes
    • datetime Datatype
    • smalldatetime
  • Specialized Datatypes
    • bit
    • timestamp
    • binary(n)
    • varbinary(n)
  • text and image Datatypes
    • text
    • image
    • Using text and image Datatypes
    • Restrictions on text and image Columns
  • money Datatype
  • smallmoney Datatype
  • sysname Datatype
• Understanding null and not null
  • ANSI Support for nulls
  • null Manipulation
  • Using ISNULL()
• identity Property
• Creating and Using Constraints
  • PRIMARY KEY Constraints
  • FOREIGN KEY constraint
  • unique Constraint
  • check Constraint
  • default Constraint
• Creating User-Defined Datatypes
  • Creating User-Defined Datatypes with sp_addtype
  • Creating User-Defined Datatypes with the Enterprise Manager
  • Dropping User-Defined Datatypes

Working with Datatypes

Creating Tables and Defining Columns Through the Enterprise Manager

Adding Data to a Table with insert

From Here... Chapter 06 - Retrieving Data with Transact-SQL

• Understanding Relevant Storage Characteristics for Retrieval
• Retrieving Data from a Table with SELECT
  • Specifying the Table with FROM
  • Specifying Columns with SELECT
  • Using a Wild Card in the SELECT Clause
  • Specifying the Rows of a Table
    • Using Comparison Operators in a WHERE Clause
    • Using the Comparison Operator LIKE
    • Selecting Columns and Rows with the WHERE Clause
    • Using Boolean Operators and Other Keywords in a WHERE Clause
      • Using the OR Operator
      • Using the AND Operator
      • Using the NOT Operator
    • Using BETWEEN to Select a Range of Values
    • Using IN to Specify a List of Values
  • Using an ORDER BY Clause
  • Using DISTINCT to Retrieve Unique Column Values
  • Using Arithmetic Operators
  • Using a GROUP BY Clause
  • Using a COMPUTE Clause in a SELECt Statement
  • Using Subqueries
    • Using ANY and ALL
    • Specifying Optimizer Hints
      • Using NOLOCK
      • Using HOLDLOCK
      • Using UPDLOCK
      • Using TABLOCK
      • Using PAGLOCK
      • Using TABLOCKx
      • Using FASTFIRSTROW
  • Using the FOR BROWSE option
• From Here...

Chapter 07 - Performing Operations on Tables

• Updating Rows
  • SET Clause Options
  • Overview of the Update Process
• Deleting Rows
• Adding Columns with ALTER TABLE
• Changing the Width of a Table Column
• Removing a Column from a Table
  • Adding Constraints with ALTER TABLE
  • Using the WITH NOCHECK Clause
  • Adding Constraints Through the SQL Enterprise Manager
• Performing Relational Joins
  • Using Range Variables
  • Using Many-to-One and One-to-Many Joins
  • Using Many-to-Many Joins
  • Using Outer Joins
• Combining Query Results with UNION
• From Here...

Chapter 08 - Using Functions

• Basic SQL Functions
  • Using AVG
  • Using COUNT
  • Using MAX
  • Using MIN
  • Using SUM
  • Using DISTINCT with COUNT
  • Using CUBE and ROLLUP
• Using String Functions
  • Using ASCII
  • Using CHAR
  • Using SOUNDEX
  • Using DIFFERENCE
  • Using LOWER
  • Using UPPER
  • Using LTRIM
  • Using RTRIM
  • Using CHARINDEX
  • Using PATINDEX
  • Using REPLICATE
  • Using REVERSE
  • Using RIGHT
  • Using SPACE
  • Using STR
  • Using STUFF
  • Using SUBSTRING
• Using Arithmetic Functions
• Using TEXT and IMAGE Functions
  • Using SET TEXTSIZE
  • Using TEXTPTR
  • Using READTEXT
  • Using TEXTVALID
• Using Conversion Functions
  • Using CONVERT
• Using Date Functions
  • Using DATENAME
  • Using DATEPART
  • Using GETDATE
  • Using DATEADD
  • Using DATEDIFF
• Using System Functions
  • Using ISNULL and NULLIF
  • Using COALESCE
• Using Niladic Functions
• From Here...

Chapter 09 - Managing and Using Views

• Understanding Views
• Creating a View
  • Selective Columns
  • Selective Rows
  • Selective Columns and Rows
  • Simple and Complex Views
• Displaying Views
• Editing Views
• Adding the WITH ENCRYPTION Clause
• Displaying View Associations
• Creating Views of Views
• Renaming Columns in Views
• Renaming Views
• Dropping Views
• Inserting Rows Through Views
• Using Views to Delete Rows
• Using Views to Update Rows
• Exploring Other View Characteristics
• From Here...

Chapter 10 - Managing and Using Indexes and Keys

• Defining Indexes
  • Creating Indexes
    • Creating an Index with SQL Enterprise Manager
    • Creating an Index with CREATE INDEX
      • UNIQUE
      • CLUSTERED
      • NONCLUSTERED
      • index_name
      • table_name
      • column_name
      • FILLFACTOR = x
      • IGNORE_DUP_KEY
      • SORTED_DATA
      • SORTED_DATA_REORG
      • IGNORE_DUP_ROW
      • ALLOW_DUP_ROW
      • ON segment_name
  • Understanding Statistics
    • Updating Statistics
      • Using SQL Enterprise Manager to Update Statistics
      • Using UPDATE STATISTICS
    • Forcing the Use of a Particular Index
  • Displaying Index Information
    • sp_helpindex
    • sp_statistics
  • Dropping Indexes
    • Using SQL Enterprise Manager to Drop an Index
    • Using the DROP INDEX Command
• Defining Keys
  • Adding Primary and Foreign Keys
    • Using SQL Enterprise Manager to Add Primary and Foreign Keys
    • Using CREATE TABLE...PRIMARY KEY
    • Using ALTER TABLE...ADD CONSTRAINT
  • Displaying Key Information
    • sp_helpconstraint
    • sp_help
    • sp_pkeys and sp_fkeys
    • Examples of Using System-Stored Procedures to View Primary and Foreign Keys
  • Dropping Keys
    • Using SQL Enterprise Manager
    • Using ALTER TABLE...DROP CONSTRAINT
• From Here...

Chapter 11 - Managing and Using Rules and Defaults

• Defining Rules
  • Creating Rules
  • Binding Rules
  • Displaying Rule Bindings
  • Displaying Rules
  • Unbinding Rules
  • Renaming Rules
  • Dropping Rules
• Defining Defaults
  • Creating Defaults
  • Binding Defaults
  • Displaying Bindings
  • Displaying Defaults
  • Unbinding Defaults
  • Renaming Defaults
  • Dropping Defaults
• From Here...

Chapter 12 - Understanding Transactions and Locking

• Defining Transactions
  • Optimistic vs. Pessimistic Locking
  • Background Information on Locking
    • Page Sizes and Granularity of Data
    • Types of Locks
  • Defining Isolation Levels
    • Read Committed
    • Read Uncommitted
    • Repeatable Read (a.k.a. Serializable)
  • Creating and Working with Transactions
    • BEGIN TRAN
    • COMMIT TRAN
    • ROLLBACK TRAN
  • DDL and Database Statements
  • Using Named Transactions and SavePoints
    • Named Transactions
    • Using SavePoints
  • Serialized Columns without IDENTITY
• Understanding Locks
  • Displaying Lock Information
    • Using SQL Enterprise Manager
    • Using sp_lock
  • Killing a Locking Process
    • Using SQL Enterprise Manager
    • Using KILL
  • Holding a Lock Explicitly
    • NOLOCK
    • HOLDLOCK
    • TABLOCK and TABLOCKX
  • Lock Escalation Options
    • LE Thresholds
• From Here...

Chapter 13 - Managing Stored Procedures and Using Flow-Control Statements

• Defining Stored Procedures
  • Using Parameters with Procedures
  • Displaying and Editing Procedures
  • Dropping Procedures
  • Understanding Procedure Resolution and Compilation
  • Automatic Recompilation
  • Defining Procedure Auto Execution
  • Understanding Procedure and Batch Restrictions
  • Understanding the End-of-Batch Signal GO
• Using Flow-Control Statements
  • Using IF...ELSE
  • Using BEGIN...END
  • Using WHILE
  • Using BREAK
  • Using CONTINUE
  • Examples of Using WHILE, BREAK, and CONTINUE
• Defining and Using Variables
  • Using PRINT with Variables
  • Using Global Variables
• Using Additional Procedure and Batch Keywords
  • Using GOTO
  • Using RETURN
  • Using RAISERROR
  • Using WAITFOR
  • Using CASE Expressions
• From Here...

Chapter 14 - Creating and Managing Triggers

• Understanding SQL Server Triggers
  • Creating Triggers
  • Examining Limitations of Triggers
• Using Triggers
  • Using INSERT and UPDATE Triggers
  • Using DELETE Triggers
  • Using Triggers That Send E-Mail
• Using Nested Triggers
• Displaying Trigger Information
  • Using SQL Enterprise Manager
  • Using sp_help, sp_depends, and sp_helptext
    • Using sp_help
    • Using sp_depends
    • Using sp_helptext
• Dropping Triggers
• From Here...

Chapter 15 - Creating and Using Cursors

• Distinguishing Between Front-End and Back-End Cursors
  • Understanding Client Cursors
• Using SQL Server Cursors
  • Declaring a Cursor
  • Opening a Cursor
  • Fetching a Cursor
  • Closing a Cursor
  • Deallocating a Cursor
  • Example of Using Cursors
• Using Nested Cursors
• Processing Cursors from Front-End Applications
• From Here...

Chapter 16 - Understanding Server, Database, and Query Options

• Defining Server Options
  • Displaying and Setting Server Options
    • Using SQL Enterprise Manager
  • Using the System-Stored Procedure sp_configure
    • Understanding the RECONfigURE command
  • Server Options Explained
    • allow updates (dynamic)
    • backup buffer size (dynamic)
    • backup threads
    • cursor threshold (dynamic, advanced)
    • database size
    • default language
    • default sortorder id (advanced)
    • fill factor
    • free buffers (dynamic, advanced)
    • hash buckets (advanced)
    • language in cache
    • LE threshold maximum (dynamic)
    • LE threshold minimum (dynamic)
    • LE threshold percent (dynamic)
    • locks
    • logwrite sleep (ms) (dynamic, advanced)
    • max async io
    • max lazywrite IO (dynamic, advanced)
    • max worker threads (dynamic)
    • media retention
    • memory
    • nested triggers (dynamic)
    • network packet size (dynamic)
    • open databases
    • open objects
    • priority boost (advanced)
    • procedure cache
    • RA cache hit limit (dynamic, advanced)
    • RA cache miss limit (dynamic, advanced)
    • RA delay (dynamic, advanced)
    • RA pre-fetches (dynamic, advanced)
    • RA slots per thread (advanced)
    • RA worker threads
    • recovery flags
    • recovery interval (dynamic)
    • remote access
    • remote logon timeout (dynamic, advanced)
    • remote query timeout (dynamic, advanced)
    • resource timeout (dynamic, advanced)
    • set working set size (advanced)
    • show advanced option (dynamic)
    • SMP concurrency (advanced)
    • sort pages (dynamic, advanced)
    • spin counter (dynamic, advanced)
    • tempdb in ram (MB)
    • user connections
    • affinity mask
    • remote conn timeout
    • remote proc trans
    • user options
  • SYSCONfigURES and SYSCURCONfigS: System Catalog Tables
• Defining Database Options
  • Displaying and Setting Database Options
  • Using SQL Enterprise Manager
  • Using sp_dboption
  • Database Options Explained
    • ANSI null default (Columns Null by Default)
    • DBO use only (DBO Use Only)
    • no chkpt on recovery (No Checkpoint on Recovery)
    • offline
    • published
    • read only (Read Only)
    • select into/bulkcopy (Select Into/Bulk Copy)
    • single user (Single User)
    • subscribed
    • trunc. log on chkpt. (Truncate Log on Checkpoint)
• Understanding Query Options
  • Displaying and Setting Database Options
    • Using ISQL/w
    • SET
  • Query Options Explained
    • Arithabort (Abort on Arithmetic Error)
    • Arithignore (Ignore Arithmetic Error)
    • NOCOUNT (No Count Display)
    • NOEXEC (No Execute)
    • PARSEONLY (Parse Query Only)
    • SHOWPLAN (Show Query Plan)
    • STATISTICS TIME (Show Stats Time)
    • STATISTICS IO (Show Stats I/O)
• Starting the Server in Minimal Configuration Mode
  • Starting SQL Server in Minimal Configuration Mode from the Command line
  • Starting SQL Server in Minimal Configuration Mode with the Services Application in Control Panel
  • Repairing a Server Started in Minimal Configuration Mode
• From Here...

Chapter 17 - Optimizing Performance

• Sizing a Database
  • Datatype Sizes
  • Calculating Space Requirements For Tables
    • Tables with Clustered Indexes
    • Tables with Nonclustered Indexes
  • Effects of FILL FACTOR
• Sizing the Procedure Cache
• Using the Windows NT Performance Monitor
  • SQL Server Statistics Objects
    • SQL Server Object
    • SQL Server Replication-Published DB Object
    • SQL Server Replication-Subscriber Object
    • SQL Server Locks Object
    • SQL Server Log Object
    • SQL Server Users Object
  • Creating and Using Chart Views
  • Creating and Using Reports
  • Creating and Using Alerts
• From Here...

Understanding and Performing Checkpoints

• Using the Database Consistency Checker
  • Setting Up to Ensure the Best Results: Single-User Mode
  • Using the DBCC Options
    • Using DBCC NEWALLOC
    • Using DBCC CHECKDB
    • Using DBCC SHRINKDB
• Understanding and Using update statistics and recompile
• Backing Up and Restoring Databases and Transaction Logs
  • How Often to Back Up?
  • Backing Up and Restoring Databases
    • Setting Up Backup Devices
    • Running the Backup
      • Completing Manual Backups
      • Scheduling Automated Backups
  • Using and Understanding Information Restoration from Backups
• Understanding and Using Mirroring
• Overview of Mirroring
  • Setting Up Mirroring
  • What to Do When Mirroring is in Force
• Transferring Information to and from SQL Server
• From Here...

Chapter 19 - SQL Server Security

• Understanding the Types of Security
  • Using Standard Security
  • Using Integrated Security
  • Using Mixed Security
• Creating and Managing User Accounts
  • Using SQL Enterprise Manager to Create a LOGIN
  • Dropping Logins and Users with SQL Enterprise Manager
  • Using sp_addlogin to Add Logins to a Server
  • Using sp_adduser to Add New Users to Databases
  • sp_droplogin and sp_dropuser
• Creating and Using Groups
  • Using SQL Enterprise Manager to Add Groups
  • Dropping Groups with SQL Enterprise Manager
• Using Permissions and SQL Server
  • Object Permissions
  • Using SQL Enterprise Manager to Manage Permissions
  • Using GRANT and REVOKE
• Using Views to Enhance Security
• Using Stored Procedures to Conceal Underlying Data Objects and Business Rules
• Using Security Beyond SQL Server
  • Physical Security
  • Local Area Network (LAN) Access
  • Remote or Wide Area Network Access
  • Application Security
  • Encrypted Multi-Protocol NetLibrary
• From Here...

Chapter 20 - Setting Up and Managing Replication

• Understanding the Basics
• Is It Distributed or Replicated Information?
• SQL Server Replication Fundamentals
  • Publishing: Providing Information to Other Systems
    • Entire Databases
    • Entire Tables
    • Horizontal and Vertical Partitions of Information, Custom Views
  • Subscribing: The Recipients of Information
  • The Log Reader Process
  • Distribution Server: The Source of Information
  • Different Server Configurations for Replication
    • Combined Single Publisher/Distribution, Single Subscribers
    • Single Publisher, Distribution System, Subscribers
    • Single Publisher, Subscriber, Publisher, Subscribers
• Installing the SQL Server Replication Services
• Enabling Publishing
  • Publishing Databases: How to Replicate Entire Databases
    • Selecting the Tables to Be Published
    • Establishing Table Synchronization
    • Controlling the Recipients of Subscriptions
    • Making Changes to Publications
  • Publishing Partial Databases and/or Tables
    • Limiting Publications to a Single Table
    • Partitioning Information to Be Included in a Publication
• Enabling Subscribing
• Uninstalling Replication
  • Disallowing Specific Servers
  • Uninstalling Replication Completely
• Troubleshooting
• From Here...

Chapter 21 - Communicating with SQL Server

• Understanding the DB-Library Interface
  • What You Need
  • Concepts and Characteristics
  • Sending Commands to SQL Server
  • Working With Results Sets
  • Closing the SQL Connection
  • Client Configuration
  • Advantages and Disadvantages of Using DB-Library
• Understanding Open Database Connectivity (ODBC)
  • Concepts and Characteristics
  • Understanding ODBC Sessions
  • Client Configuration
  • Working with ODBC DSNs
  • Advantages and Disadvantages of Using ODBC
• Understanding the SQL OLE Interface
  • Concepts and Characteristics
  • Advantages and Disadvantages of Using SQL OLE
• From Here...

Chapter 22 - Accessing SQL Server Databases Through Front-End Products

• Configuring ODBC for SQL Server 6.5
• Using Gupta's SQLWindows
  • Establishing a Connection
    • Preparing SQLWindows for SQL Server 6
    • Connecting to SQL Server 6.5
  • Preparing and Executing SQL Statements
  • Using Stored Procedures and Command Batches
  • Sneak Preview of Centura
  • More Information and Examples
• Using Borland's Delphi 2
  • Establishing a Connection
  • Understanding the Delphi 2 Database Model
  • About the Sample Application DPHIDEMO
  • Preparing and Executing SQL Statements
  • Using Stored Procedures and Command Batches
  • More Information and Examples
• Using Microsoft Visual Basic 4
  • Understanding the Visual Basic 4 Database Model
  • Connecting to SQL Server
  • Preparing and Executing SQL Statements
  • About the Sample Application VB4DEMO
• From Here...