Accessing and Changing Relational Data Overview
Query Tools and Programming Interfaces
Query Tools
SQL Server Tools
Using SQL Query Analyzer
Using SQL Server Enterprise Manager
Using the osql Utility
Using the bcp Utility
Programming Interfaces
Microsoft Programming Environments
Application Programming Interfaces
Connecting to and Disconnecting from an Instance
Preparing and Executing Statements
Processing Results
Transact-SQL Syntax Elements
Using Identifiers
Using Identifiers as Object Names
Object Visibility and Qualification Rules
Delimited Identifiers
Using Data Types
Using Binary Data
Using char and varchar Data
Using Date and Time Data
Alphabetic Date Format
Numeric Date Format
Unseparated String Format
Time Formats
ODBC Datetime Format
Using Integer Data
Using bigint Data
Using decimal, float, and real Data
Using Monetary Data
Using text and image Data
Using uniqueidentifier Data
Using Special Data
Using sql_variant Data
Using Unicode Data
Data Type Conversion
Moving Data to Program Variables
Converting binary and varbinary Data
Converting bit Data
Converting Character Data
Converting datetime and smalldatetime Data
Converting float and real Data
Converting money Data
Converting decimal and numeric Data
Data Type Conversions Using OLE Automation Stored Procedures
Using Constants
Using Functions
Using System Functions
Using String Functions
Using SUBSTRING
Comparing CHARINDEX and PATINDEX
Using STR
Using STUFF
Comparing SOUNDEX and DIFFERENCE
Using text, ntext, and image Functions
Using Mathematical Functions
Using Trigonometric Functions
Comparing CEILING and FLOOR
Comparing LOG and LOG10
Using the POWER and EXP Exponential Functions
Using RAND
Date Functions
Using GETDATE
Comparing DATEPART and DATENAME
Comparing DATEADD and DATEDIFF
Functions That Return User Names and User IDs
Conversion Functions
Invoking User-Defined Functions
Invoking User-Defined Functions That Return a Scalar Value
Invoking User-Defined Functions That Return a Table Data Type
Invoking Built-in User-Defined Functions
Expressions
Using Operators in Expressions
Arithmetic Operators
Bitwise Operators
Comparison Operators
String Concatenation Operator
Null Values
Using Comments
Using Reserved Keywords
Accessing and Changing Data Fundamentals
Choosing a Database
Using Multiple Statements
Batches
Specifying Batches
Batch Examples
Stored Procedures and Triggers
Transact-SQL Scripts
Using Variables and Parameters
Transact-SQL Variables
Parameters
Application Variables
Parameter Markers
Control-of-Flow
Using BEGIN...END
Using GOTO
Using IF...ELSE
Using RETURN
Using WAITFOR
Using WHILE...BREAK or CONTINUE
Using CASE
Building Statements at Run Time
Using sp_executesql
Permissions
Using Options in SQL Server
SET Options
Database Options
Server Options
Hints
Database Compatibility Level Option
Behavior if Both ARITHABORT and ARITHIGNORE Are Set ON
Query Fundamentals
Parts of a SELECT Statement
Using the Select List
Choosing All Columns
Choosing Specific Columns
Constants in Query Result Sets
Computed Values in the Select List
Assigning Result Set Column Names
Delimiting Result Set Column Names
Eliminating Duplicates with DISTINCT
Limiting Result Sets Using TOP and PERCENT
Using the FROM Clause
Using Table Aliases
Filtering Rows with WHERE and HAVING
Comparison Search Conditions
Range Search Conditions
List Search Conditions
Pattern Matching in Search Conditions
NULL Comparison Search Conditions
Logical Operators
Logical Operator Precedence
Transact-SQL Joins
Sorting Rows with ORDER BY
Join Fundamentals
Using Joins
Using Inner Joins
Using Outer Joins
Using Cross Joins
Using Self-Joins
Joining Three or More Tables
Null Values and Joins
Specifying Joins in FROM or WHERE Clauses
Advanced Query Concepts
Using Aggregate Functions in the Select List
Using COUNT(*)
Using DISTINCT
Null Values
Grouping Rows with GROUP BY
GROUP BY Components
GROUP BY and the WHERE Clause
Choosing Rows with the HAVING Clause
GROUP BY and ALL
GROUP BY and Null Values
Combining Results with UNION
Guidelines when Using UNION
Using UNION with Other Transact-SQL Statements
Using Partitioned Views
Designing Applications to Use Federated Database Servers
Subquery Fundamentals
Subquery Rules
Qualifying Column Names in Subqueries
Subquery Types
Subqueries with Aliases
Subqueries with IN
Subqueries with NOT IN
Subqueries in UPDATE, DELETE, and INSERT Statements
Subqueries with Comparison Operators
Comparison Operators Modified by ANY, SOME, or ALL
Subqueries with EXISTS
Subqueries with NOT EXISTS
Using EXISTS and NOT EXISTS to Find Intersection and Difference
Subqueries Used in Place of an Expression
Multiple Levels of Nesting
Correlated Subqueries
Correlated Subqueries with Aliases
Correlated Subqueries with Comparison Operators
Correlated Subqueries in a HAVING Clause
Conditional Data Processing Using CASE
Summarizing Data
Summarizing Data Using CUBE
Summarizing Data Using ROLLUP
Summarizing Data Using COMPUTE and COMPUTE BY
Error Handling
Handling Errors and Messages in Applications
Using @@ERROR
Using PRINT
Using RAISERROR
Querying SQL Server System Catalogs
Managing ntext, text, and image Data
Retrieving ntext, text, or image Values
Modifying ntext, text, or image Values
OLE Automation Objects in Transact-SQL
OLE Automation Return Codes and Error Information
OLE Automation Result Sets
Diagnosing OLE Automation Objects in Transact-SQL
OLE Automation Sample Script
Transact-SQL Tips
Cross-Tab Reports
Expanding Hierarchies
Expanding Networks
Writing International Transact-SQL Statements
Writing Readable Code
Modifying Data
Adding Data
Adding Rows with INSERT
Inserting a Row Using INSERT�Values
Inserting Rows Using INSERT�SELECT
Inserting Rows Using SELECT INTO
Adding a Row Using a Result Set Position
Adding ntext, text, or image Data to Inserted Rows
Adding Rows Using Bulk Copy Operations
Changing Data
Changing Data with UPDATE
Changing Data Using the SET Clause
Changing Data Using the WHERE Clause
Changing Data Using the FROM Clause
Changing Data with a Cursor
Changing ntext, text or image Data
Deleting Data
Deleting Rows with DELETE
Deleting Rows in Result Sets
Deleting All Rows Using TRUNCATE TABLE
Transactions
Controlling Transactions
Explicit Transactions
Autocommit Transactions
Implicit Transactions
Transact-SQL Implicit Transactions
API Implicit Transactions
Distributed Transactions
Transact-SQL Distributed Transactions
MS DTC Distributed Transactions
Distributed Queries and Distributed Transactions
Advanced Topics
Nesting Transactions
Transaction Savepoints
Using Bound Connections
Adjusting Transaction Isolation Levels
Rollbacks in Stored Procedures and Triggers
Transact-SQL Statements Allowed in Transactions
Coding Efficient Transactions
Locking
Concurrency Problems
Optimistic and Pessimistic Concurrency
Isolation Levels
Understanding Locking in SQL Server
Lock Compatibility
Key-Range Locking
Lock Escalation
Dynamic Locking
Displaying Locking Information
Customizing Locking with SQL Server
Deadlocking
Detecting and Ending Deadlocks
Handling Deadlocks
Minimizing Deadlocks
Customizing the Lock Time-out
Customizing Transaction Isolation Level
Locking Hints
Customizing Locking for an Index
Cursors
Default Result Sets
Cursor Implementations
Specifying Cursors
Transact-SQL Cursors
API Server Cursors
Client Cursors
Fetching and Scrolling
Controlling Cursor Behavior
Cursor Types
Forward-only Cursors
Fast Forward-only Cursors
Static Cursors
Keyset-driven Cursors
Dynamic Cursors
Cursor Behaviors
Scrollable
Sensitivity
Cursor Locking
Cursors and Transactions
Cursor Concurrency
Cursor Transaction Isolation Levels
Changing Rows with Positioned Operations
Cursor Programming Details
Choosing a Cursor Type
Block Cursors
Implicit Cursor Conversions
Asynchronous Population
Scope of Transact-SQL Cursor Names
Getting Server Cursor Metadata
Using Cursors with Distributed Queries
Distributed Queries
Accessing External Data
Identifying a Data Source Using a Linked Server Name
Identifying a Data Source Using the Ad Hoc Name
Using Pass-Through Queries as Tables
External Data and Transact-SQL
Setting SQL-92 Options for Distributed Queries
Using Transactions with Distributed Queries
Data Type Mapping
Collations in Distributed Queries
Obtaining Meta Data from Linked Servers
OLE DB Providers Tested with SQL Server
OLE DB Provider for SQL Server
Linked Server Considerations in a Clustered SQL Server
Distributed Queries on Multiple Instances of SQL Server
OLE DB Provider for ODBC
OLE DB Provider for Jet
OLE DB Provider for DTS Packages
OLE DB Provider for Oracle
OLE DB Provider for Microsoft Directory Services
OLE DB Provider for Microsoft Indexing Service
OLE DB Provider for DB2
OLE DB Provider for Exchange
OLE DB Provider Reference for Distributed Queries
OLE DB Objects Consumed by Distributed Queries
Four-Part Name Requirements for OLE DB providers
UPDATE and DELETE Requirements for OLE DB Providers
INSERT Requirements for OLE DB Providers
Keyset-Driven Cursors Requirements for OLE DB Providers
Distribution Statistics Requirements for OLE DB Providers
SQL Dialect Requirements for OLE DB Providers
DBPROPVAL_SQL_SUBMINIMUM Syntax
Programming the SQLPROPSET_OPTHINTS Property Set
Full-text Search
Full-text Querying SQL Server Data
Full-text Index and Querying Concepts
Implementation of Full-text Search
Full-text Query Transact-SQL Components
Using the CONTAINS Predicate
Searching for Specific Words or Phrases (Simple Term)
Combining Full-text Search Operators Using AND, OR, and AND NOT
Searching for Multiple Forms of Words or Phrases (Prefix Term)
Searching for Any Form of a Specific Word (Generation Term)
Searching for Words or Phrases Using Weighted Values (Weighted Term)
Searching for Words or Phrases Close to Another Word or Phrase (Proximity Term)
Using the FREETEXT Predicate
Using Full-text Predicates to Query image Columns
Combining Full-text Predicates with Other Transact-SQL Predicates
Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued Functions
Using Transact-SQL Functions to Obtain Full-text Property Values
Example of Combining Full-text Administration and Full-text Query
Full-text Querying of File Data
Using Microsoft Internet Information Services and Indexing Service for File Content Searches
Using Virtual Tables for File Content Queries
Using SCOPE Function for File System Queries
Using File Properties for File Content Searches
Sample Full-text Query Using File Content and Database Data