Sybase SQL Server 11 Unleashed
by Jeff Garbus
Sams, Macmillan Computer Publishing
ISBN:
0672309092
Pub Date:
04/16/96
Introduction
Installing the CD-ROM
About the Author
Part I
Chapter 1—Overview of Client/Server
Roots of Client/Server Computing
Host-Based Computing
PC/LAN–Based Computing
Client/Server to the Rescue!
The Critical Factor: Cost
Summary
Chapter 2—Understanding the Sybase Client/Server Architecture
Comparative Database Architectures 101
Multi-Process Database Engines
The Pros and Cons of Multi-Process Database Engines
Single-Process, Multi-Threaded Database Engines
Pros and Cons of Multi-Threading Database Engines
System 11 Features
The Two APIs of Any Client/Server Database
Summary
Part II—Transact-SQL Programming
Chapter 3—Introduction to Transact-SQL
What Is Transact-SQL?
Structured Query Language
T-SQL and ANSI SQL
Statements, Batches, and Elements of T-SQL
Server Processing Steps
Who Uses SQL and Transact-SQL?
How Do I Test SQL Queries?
Retrieving Data with select
Tables, Rows, and Columns
The pubs2 Database
Selecting Data from a Table
Filtering Rows with where
Ordering Result Sets with order by
Retrieving Aggregate Data
Join Operations
Subqueries
union Operations
Using select with into
Adding Rows with insert
Inserting Specific Values
Inserting Several Rows with select
Omitting the Column List
Modifying Rows with update
Removing Rows with delete
Clearing a Table with truncate table
Summary
Chapter 4—Transact-SQL Datatypes
Nullability and Datatypes
Character and Binary Datatypes
char and varchar
binary and varbinary
Timestamps
Text and Image Data
Date/Time Datatypes
datetime versus smalldatetime
Date/Time Inserts
Search Behavior with Dates
Logical Datatype: bit
Numeric Datatypes
Integer Datatypes
Floating-Point Datatypes
Exact Numeric Datatypes
Money Datatypes
System Tables and Datatypes
Summary
Chapter 5—Creating and Altering Database Objects
An Approach to Learning to Manage Objects
Creating Database Objects
Tables
SQL Server Object Names
Views
Views as Security—Vertical
Views as Security—Horizontal
Views to Ease SQL
Data Modifications and Views
Views with check option
Getting View Information
Renaming Objects
Adding Columns to a Table
Temporary Tables
Permanent Temporary Tables
Rules
Creating Rules
Rule Usage
Rule Limitations
Defaults
Default Usage
Declarative Defaults
Default Limitations
When a Default Is Applied
Examining Rules and Defaults
User-Defined Datatypes
Creating User-Defined Datatypes
User-Defined Datatype Notes
Defining and Using User-Defined Datatypes
Binding Precedence with Rules and Defaults
Indexes
Index Types
Clustered Index Mechanism
Nonclustered Index Mechanism
Clustered versus Nonclustered
Creating Indexes
Notes on Indexes
Constraints
Primary Key and Unique Constraints
Check Constraints
Referential-Integrity Constraints
Adding Constraints
Removing Constraints
Information on Constraints
Guidelines on Constraints
Comparing Data-Integrity Methods
Constraints
Keys
Which One Should You Use?
Summary
Chapter 6—Transact-SQL Programming Constructs
SQL Server Functions
Examples
String Functions
Basic String Manipulation and Parsing
Conversions
String Functions—Wildcards
String Functions—Examples
Mathematical Functions
Date Functions
Date Parts (for Use with Date Functions)
Examples
convert
Date Conversions
System Functions
Useful System Functions
System Function Examples
compute and compute by
compute
compute and compute by Notes
Example
isnull Function
Programming Constructs
Batches
Examples
Comments
Local Variables
Global Variables
print
raiserror
Conditional Execution: if…else
if exists
Statement Blocks: begin…end
Repeated Execution: while
Repeated Execution: goto
Event Handling: waitfor
return
Set Options
Cursors
Some Approaches
Cursor Example and Some Syntax
Updating with Cursors
Avoiding Cursors
Summary
Syntax Summary: Programming Structures
Chapter 7—Transact-SQL Program Structures
Typical Trigger Uses
When Is a Trigger Executed (Fired)?
Trigger Creation
Deleted and Inserted Tables
What Happens on delete?
delete Trigger Example
What Happens on insert?
if update Test
insert Trigger Example
Conditional insert Trigger Example
What Happens on update?
update Trigger Example
Trigger Limitations
Triggers During Transactions
rollback trigger
Nested Triggers
Examining Triggers
Stored Procedures
Running Stored Procedures
Creating Stored Procedures
Display and Maintenance
Procedures and Parameters
Executing with Parameters
Passing Parameters In and Out
Output Parameters
Returning Procedure Status
SQL Server Status Codes
Stored Procedures and Transactions
Cursors in Stored Procedures
Procedure Limitations and Notes
Objects Referenced in Procedures
Optimizing Stored Procedures
Remote Stored Procedures
Stored Procedure Guidelines
Stored Procedure Debugging Techniques
Summary
Chapter 8—Transaction Management
Transaction Mechanisms
Transaction Control
Transaction Programming
Transactions and Batches
Savepoints
Nested Transactions
Transactions and Locking
Transactions and Triggers
@@trancount and Implicit Transactions
rollback transaction in a Trigger
Transactions and Stored Procedures
Chained Transactions
Procedure Transaction Mode
Long-Running Transactions
Summary
Part III—Performance and Tuning
Chapter 9—Defining Performance Expectations and Variables
Definition of Performance
Performance Tradeoffs
Performance Expectations
Defining and Tracking Down Bottlenecks
Performance Variables
System Architecture Issues
Application Issues
SQL Server Issues
Concurrency Issues
Defining a Tuning Approach
Summary
Chapter 10—Understanding SQL Server Storage Structures
SQL Server Storage Structures
Allocation and Object Allocation Map (OAM) Pages
Allocation Pages
OAM Pages
Allocating New Space for a Table or Index
Changes to Page Allocation Strategy for System 11
Data Pages
Data Rows
Estimating Row and Table Sizes
The Row Offset Table
Text and Image Pages
Indexes and the B-Tree Structure
Clustered Indexes
Nonclustered Indexes
SQL Server Index Maintenance
Fill Factor
Updates and Performance
Deferred Updates
Direct Updates
Summary
Chapter 11—Designing Indexes for Performance
Why Use Indexes?
Index Usage Criteria
Indexes and Performance
Index Selection
Evaluating Index Usefulness
The Distribution Page
Index Densities
Viewing Distribution Page Values
Index Design Guidelines
Clustered Index Indications
Nonclustered Index Indications
Index Covering
Composite Indexes versus Multiple Indexes
Indexing for Multiple Queries
Summary
Chapter 12—Understanding the Query Optimizer
Optimization Goals
Query Optimization Steps
Query Analysis
Identifying SARGs
OR Clauses
The OR Strategy
Join Clauses
Subquery Processing
Index Selection
Evaluating Indexes for SARGs or OR clauses
Evaluating Indexes for Join Clauses
Ranking Indexes
Estimating Page I/O
Index Covering
Join Order Processing
Determining Join Order
Increasing the Number of Tables in Join Permutations
Reformatting Strategy
Outer Joins
ORDER BY, GROUP BY, and DISTINCT Clauses
Potential Optimizer Problems and Solutions
Making Sure the Statistics Are Up-to-Date
Checking the SARGs
Ensuring that the Index Covers the Query
Checking Stored Procedures for Current Parameters
Checking the Query Plan for “Reformatting”
System 11 Query Optimization Enhancements
Buffer Management
Cache Strategies
Summary
Chapter 13—Stored Procedure Optimization
Stored Procedures and Performance Benefits
Stored Procedure Optimization
The Stored Procedure Dilemma
Recompiling Stored Procedures
Automatic Recompilation
Using sp_recompile
When Recompilation Doesn’t Work
Alternatives to Recompiling
Summary
Chapter 14—Analyzing Query Plans
Using and Understanding showplan
QUERY PLAN for STATEMENT # (at line #) (System 11 Only)
STEP #
The type of query is XXXXXXX
The type of query is SELECT (into a worktable)
GROUP BY
Evaluate Grouped/Ungrouped TYPE Aggregate
FROM TABLE tablename
TO TABLE tablename
Nested iteration
Table Scan
Using Clustered Index
Index: index_name
Additional Access Method Statements in System 11
Using Dynamic Index
Worktable created for ORDER BY or DISTINCT
Worktable created for REFORMATTING
Worktable created for SELECT_INTO
The update mode is DEFERRED/DIRECT
WITH CHECK OPTION
EXISTS TABLE: nested iteration
Additional showplan Messages for Subqueries in System 11
Using dbcc Trace Flags for Analyzing Query Plans
dbcc traceon (-1)
dbcc traceon 3604 and dbcc traceon 3605
dbcc traceon (302) and dbcc traceon (310)
Interpreting the dbcc traceon (302) Output
Scoring Search and Join Clauses
Forcing Indexes
Estimating Index Costs
Using Index Statistics to Estimate I/O Costs
Distribution Page Matching
Range Queries
When Distribution Steps Cannot Be Used
Determining Final Cost Estimates and Selectivity of Indexes
Scoring Join Clauses
Interpreting the dbcc traceon (310) Output
Ignoring Permutations
TOTAL # PERMUTATIONS: N
TOTAL # PLANS CONSIDERED: N
FINAL PLAN (total cost = ##):
OR Strategy Example
Using statistics io and statistics time
statistics io
statistics time
Summary
Chapter 15—Locking and Performance
SQL Server Locks Defined
SQL Server Lock Granularities
SQL Server Lock Types
Shared Locks
Update Locks
Exclusive Locks
Transact-SQL Statements and Associated Locks
Table-Level Locks
Demand Locks
Locking with Cursors
Lock Escalation for Large Data Sets
Setting Lock-Escalation Thresholds in System 11
Index Locking
Using Transaction Isolation Levels in SQL Server 10.0 and Later
Query Isolation-Level Setting in System 11
Examining Current Lock Activity
Configuring SQL Server Locking
Minimizing Locking Contention
Avoiding Hot Spots
Decreasing the Number of Rows per Page
Simulating Row-Level Locking
Optimistic Locking
Deadlocking
So How Do I Minimize the Chance for Deadlock?
Tracing Deadlocks
Deadlock Checking in System 11
Summary
Chapter 16—Overriding the SQL Server Optimizer
Why Override the Optimizer?
Cautions on Overriding the Optimizer
Forcing Index Selection
A Case Study
Cautions on Forcing Index Selection
Forcing Join Order
When to Force Join Orders
Cautions on Forcing Join Orders
Overriding the Optimizer in System 11
Increasing the Number of Tables in Join Permutations
Overriding the prefetch Size
Forcing a Cache Strategy
Summary
Chapter 17—Database Design and Performance
Database Design Issues
What Is Logical Design?
Normalization Conditions
Normal Forms
Benefits of Normalization
Drawbacks of Normalization
Normalization and the Database Design Picture
Denormalizing the Database
Advantages of Denormalization
Guidelines
Basic Denormalization Techniques
Redundant Data
Changing Column Definition
Redefining Tables
Data Partitioning
Summary
Chapter 18—Database Object Placement and Performance
Database Segments
Why Define and Use Segments?
Using Segments to Improve Performance
Placing Tables on Segments to Spread I/O
Placing Indexes on Segments
Splitting a Table Across Devices to Increase Throughput
Placing Text/Image Data on a Different Segment
Using RAID as an Alternative to Segments
Introduction to RAID Technology
RAID Level 0
RAID Level 1
RAID Level 2
RAID Level 3
RAID Level 4
RAID Level 5
SQL Server and Storage Device Performance
Which RAID Device to Use
RAID Levels Commonly Used with SQL Server
SQL Server and RAID—Additional Notes
Software-Based versus Hardware-Based RAID Subsystems
RAID versus Segments
Heap Table Partitioning in System 11
Benefits of Heap Table Partitioning
How to Partition Tables
Notes on Heap Table Partitioning
Tips on Heap Table Partitioning
Minimizing I/O Contention Between Databases
Summary
Chapter 19—Application Design for Performance
Considerations in Balancing Performance
Servers
Clients
Implications of Server and Client Capabilities
The Network as Bottleneck
Reducing Query Size
Reducing Result Sets
Row Processing
Data Validation Methods
Application-Based Integrity
Procedure-Based Integrity
Server-Based Integrity
Mixed-Model Integrity
Rule 1: Items May Be Deleted Only if the In-Stock Value Is 0
Rule 2: Customer Status May Be ACTIVE, INACTIVE, or PENDING Only
Rule 3: Orders May Be Entered Only for Valid Customer IDs
Rule 3a: Orders May Be Entered Only for Customers Having a Status of ACTIVE
Complex Transactions
Multiple Server Transactions
Some General Advice
Summary
Chapter 20—Advanced Transact-SQL Programming
Who Is This Chapter For?
Where Else Can I Look?
Aggregate Query Resolution
having Clauses with group by
Grouping and Worktables
Role of the Worktable
How Does SQL Server Process Queries Involving a Worktable?
Special Topics in Join Processing
Joins with or
Overriding the Optimizer
Breaking Up Large Queries
Summary
Chapter 21—Miscellaneous Performance Topics
bcp and Performance
Minimizing Data Load Time
Slow bcp and Logging
bcp and Data Integrity
Database Maintenance and Performance
tempdb and Performance
Add Memory to SQL Server
Faster Devices for tempdb
Disseminating Disk Resources
Other tempdb Performance Tips
Cursors and Performance
text and image Columns and Performance
Summary
Chapter 22—Common Performance and Tuning Problems
Out-of-Date or Unavailable Statistics
Search Argument Problems
No SARGs
Negative Logic
Operations on a Column in a where Clause
Unknown Constant Values in a where Clause
Datatype Mismatch
OR Logic in a where Clause
Other Query-Related Issues
The distinct Keyword
The count() Function
Aggregate Clauses
order by and group by
Join Clauses
Provide All Join Options
SQL Server Configuration
Memory
Physical Database Design
Indexes
Avoiding Hot Spots
DSS versus OLTP
Historical versus Active Data
Locking Issues
Maintenance Activities
Summary
Part IV—System Administration
Chapter 23—Overview of System Administration
Components of SQL Server
SQL Server Versions
System and Database Administration
The System Administrator (sa) Login and Roles
Responsibilities of the System Administrator
Responsibilities of the Site Security Officer
Operator Responsibilities
System Tables
Database-Level System Tables
System-Level System Tables
Auditing System Tables
System Stored Procedures
Special Characteristics
Useful System Procedures
Summary
Chapter 24—SQL Server Installation and Connectivity
Server Selection and Configuration
Server Hardware Characteristics
SQL Server Platforms
SQL Server Versions
Server Installation
Operating System User Privileges
Filling Out the Forms
File Transfer
Installation
Server Directories
Backup Server Installation (System 10)
Server Environmental Values
Server Startup, Login, and Shutdown
Starting the Server
Time to Log In!
Shutting Down with shutdown
Client Installation
Open Client and Open Server
DB-Library and ct-Library
UNIX
Windows
Macintosh
Networking and Connectivity
Network Types
Network Protocols
Troubleshooting
Changing Defaults: The Top 10 Items to Address as Soon as You Log In
Securing System Administrator Access
Turning Off the Master Device Default Status
Increasing the Size of tempdb
Naming the Server
Setting Obvious Configuration Settings
Setting Up the Model Database Objects, Users, and Datatypes
Installing the pubs2 Database
Installing the sybsyntax Database
Summary
Checklist
Chapter 25—Defining Physical and Mirror Devices
Disk Initialization with disk init
Syntax
Parameters
Raw Partition and File System Devices
Effects of disk init
Removing Devices with sp_dropdevice
Syntax
Example
Default Disks
Syntax of sp_diskdefault
Examples of sp_diskdefault
Disk Mirroring
Syntax
Example
Sequence of Events
Disk Mirroring Information
Deactivating Disk Mirroring
Syntax
Example
Disk Remirroring
Syntax
Example
Software Mirroring, Hardware Mirroring, and RAID
Software Mirroring
Hardware Mirroring
Redundant Array of Inexpensive Devices (RAID)
Device SQL
Summary
Chapter 26—Defining, Altering, and Maintaining Databases and Logs
What Is a Database?
Databases and Space Management
Databases and Security
Databases and Backups
System Databases
Database Creation
Syntax for create database
Examples of create database
What Happens When You Create a Database?
Sizing Databases
Database Creation Authority and Database Ownership
Making Databases Larger
Syntax for alter database
Adding Log Space
Exploring Databases
sp_helpdb
Database System Tables
Database Space Usage
Database Options
Default Database Options
Setting Database Options
Examining Database Status
Database Status Flags
The model Database
Dropping Databases
Segments and Object Placement
Segments for Performance
Segments for Control
Segment Definition
Predefined Segments
Placing Objects on Segments
Moving an Object to a New Segment
Removing a Device from a Segment
Getting Information on Segments
Segment System Tables
Thresholds
Summary
Chapter 27—Security and User Administration
Overview
Operating System Security Issues
Sybase Server Security: Logins
Sybase Database Security: Users
Object-level Security: Permissions
Sybase Server Logins
Special Logins
Generic Logins
How Logins Work
Modifying Login Information
Displaying Login Information
Passwords
Changing Passwords
Password Expiration
Database Access
Adding Users
Special Users
Adding Aliases (Alternates)
How Database Access Works
Groups
Login Approach
Sybase Login = OS/App Login
Sybase Login Independent of OS/APP Login
Single Sybase Login
Password Approach
Password = Login
General Application Login and Password
Password Independent of Login
Permissions
Users
Object Permissions
Command Permissions
Permission Approaches
Object Dependencies
Roles
System Administrator (sa_role)
Site Security Officer (sso_role)
Operator (oper_role)
Other Roles
Granting and Revoking Roles
set role
Displaying Configured Role Information: sp_displaylogin
Displaying Active Role Information: show_role
Roles and Stored Procedures: proc_role
Approaches
Summary
Chapter 28—Database Logging and Recovery
What Is a Transaction?
What Is the Transaction Log?
A Write-Ahead Log
commit
checkpoint
recovery
When the Transaction Log Fills Up
Thresholds and the Transaction Log
Summary
Chapter 29—Backing Up and Restoring Databases and Transaction Logs
Why Back Up?
Roles and Responsibilities
Types of Backups
Dump Devices
Tape Devices
Disk Devices
Adding Dump Devices
Backup Server
Remote Backup Server
Adding a Server
Starting a Backup Server
Dumping and Loading
Dumping the Database
Dumping Database Logs
Loading the Database
Restoring after a Disaster
Loading a Transaction
Additional Considerations
Automatic Backups
Frequency of Dumps
Striping Dumps
Locking
Capturing Statistics
Configuring Tape Expiration
Transaction Logging
Monitoring Available Log Space
Developing Your Backup and Recovery Plan
System Databases
User Databases
VLDB Considerations
Summary
Chapter 30—Configuring and Tuning the SQL Server
Using sp_configure in System 10 and Earlier
The sysconfigures and syscurconfigs Tables
Setting Configuration Values
System 11 Configuration Changes
Viewing Configuration Settings in System 11
Changing Configuration Settings
Using Configuration Files with sp_configure
Editing Configuration Files Directly
SQL Server Configuration Options
Backup and Recovery Group
Cache Manager Group
Disk I/O Group
General Information Group
Languages Group
Lock Manager Group
Memory Use Group
Network Communication Group
O/S Resources Group
Physical Memory Group
Processors Group
SQL Server Administration Group
User Environment Group
Configuration Variables That Require Substantial Memory
Summary
Chapter 31—Optimizing SQL Server Configuration Options
Memory Utilization
dbcc memusage
Using
dbcc memusage
Information
Other Methods for Viewing Memory Utilization
Configuring the Procedure Cache
Tuning the Data Cache
Estimating Total Memory Requirements
Buffer Management in SQL Server
Configuring Named Data Caches in System 11
Defining Buffer Pools
Using Buffer Pools
Changing the Wash Area for a Named Cache
Binding Databases and Objects to Named Caches
Getting Information on Named Cache Bindings
Tuning the Private Log Cache
Guidelines for Cache Configuration in System 11
Lock Escalation Configuration
Setting Lock Thresholds at the SQL Server Level
Setting Lock Thresholds at the Database or Table Level
Tuning Network I/O for Performance
SQL Server and the Network
Configuring Network Packet Size
tcp no delay
Network Performance Tips
Speeding Up Index Creation
Tuning an SMP Configuration
Configuring Multiple Engines
Engine Tuning Guidelines
Application Design Issues in an SMP Environment
Summary
Chapter 32—Measuring Performance
Initial Benchmark
Estimating Query Times
Determining Access Speed
Ongoing Benchmarking
CPU by Engine
I/O by Virtual Device
Concurrency
Throughput
Batch Effect
Rollout
Stress-Testing Approach
Database Level Elements
Server Stress Testing and Tuning
Notes on Measuring Performance
Summary
Chapter 33—Remote Server Management
Definitions
Remote Access Cookbook
Adding Servers with sp_addserver
Adding Remote Logins with sp_addremotelogin
Using the Remote ID as the Local ID
Using a Single Local Login for All Remote Logins
Using a New Local Name for All Remote Users
Removing Logins with sp_dropremotelogin
Remote Options
Getting Information on Remote Servers
Summary
Chapter 34—Defining Systems Administration and Naming Standards
SQL Server Environment Approach
Defining Environments
Development Environment
A Detailed Approach
Test Environment
Functional Testing Environment
Performance Testing Environment
Production Environment
Naming Standards
SQL Server Names
Operating System Names
Abbreviation Standards
Summary
Chapter 35—Administering Very Large SQL Server Databases
What Is a VLDB?
VLDB Maintenance Issues
Managing Database Dumps and Loads
Developing a VLDB Backup/Restore Procedure
Checking Database Consistency
Developing a Consistency Checking Plan
Data Maintenance
Updating Statistics
Update Statistics Scheduling
Purge/Archive Procedures
Data Partitioning Options
Vertical Data Partitioning
Horizontal Data Partitioning
Summary
Part V—Introduction to Open Client Programming
Chapter 36—DB-Library Programming
Essential Pieces of a DB-Library Client
Entry Code
Opening a Connection
The Message Handler
The Error Handler
Sending Queries
Retrieving Results
Exit Code
Cool Tricks and Handy Functions
GetServerList()
GetTimeFromServer()
Retrieving Object Information
Programming Pitfalls
Summary
Chapter 37—ct-Library Programming
Essential Pieces of a ct-Library Program
What Is a Context?
The Big Picture: How Commands, Connections, and Contexts Relate
Allocating a Context Structure
Allocating a Connection Structure
Allocating a Command Structure
Constructing a Command Batch
Submitting a Batch
Processing Results
Cleanup
Summary
Chapter 38—ODBC Programming
What Is ODBC?
ODBC Interface
API Conformance Levels
Core API
Level 1 API
Level 2 API
Using VB to Build a Simple ODBC Application
A Sample Program
Initializing ODBC
Summary of Programming Example
Summary
Appendix A
Appendix B
Appendix C
Appendix D
Appendix E
Appendix F
Index
Copyright ©
Macmillan Computer Publishing, Inc.