Professional Microsoft® SQL Server® 2008 Programming

Professional Microsoft® SQL Server® 2008 Programming

by
Released April 2009
Publisher(s): Wrox
ISBN: 9780470257029

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Master the increasingly complex feature set of the latest release of Microsoft SQL Server with the information in Professional Microsoft SQL Server 2008 Programming. Review the new features of SQL Server that will be of interest to you as an experienced developer and move on to more detailed, practical code examples. Learn how to write complex queries, build different types of data structures, improve application speed and performance, manage advanced scripting and errors, and design advanced databases the latest edition of this valuable programming guide.

Table of contents

  1. Copyright
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Version Issues
    2. Who This Book Is For
    3. What This Book Covers
    4. How This Book Is Structured
    5. Conventions
    6. Source Code
    7. Errata
    8. p2p.wrox.com
    9. www.professionalsql.com
  6. 1. Being Objective: Re-Examining Objects in SQL Server
    1. 1.1. So, What Exactly Do We Have Here?
    2. 1.2. An Overview of Database Objects
      1. 1.2.1. The Database Object
        1. 1.2.1.1. The master Database
        2. 1.2.1.2. The model Database
        3. 1.2.1.3. The msdb Database
        4. 1.2.1.4. The tempdb Database
        5. 1.2.1.5. AdventureWorks/AdventureWorks2008
        6. 1.2.1.6. AdventureWorksLT/AdventureWorksLT2008
        7. 1.2.1.7. AdventureWorksDW/AdventureWorksDW2008
      2. 1.2.2. The Transaction Log
      3. 1.2.3. The Most Basic Database Object: Table
        1. 1.2.3.1. Indexes
        2. 1.2.3.2. Triggers
        3. 1.2.3.3. Constraints
      4. 1.2.4. Schemas
      5. 1.2.5. Filegroups
      6. 1.2.6. Diagrams
      7. 1.2.7. Views
      8. 1.2.8. Stored Procedures
      9. 1.2.9. User-Defined Functions
      10. 1.2.10. Users and Roles
      11. 1.2.11. Rules
      12. 1.2.12. Defaults
      13. 1.2.13. User-Defined Data Types
      14. 1.2.14. Full-Text Catalogs
    3. 1.3. SQL Server Data Types
      1. 1.3.1. NULL Data
    4. 1.4. SQL Server Identifiers for Objects
      1. 1.4.1. What Gets Named?
      2. 1.4.2. Rules for Naming
    5. 1.5. Summary
  7. 2. Tool Time
    1. 2.1. Books Online
    2. 2.2. The SQL Server Configuration Manager
      1. 2.2.1. Service Management
      2. 2.2.2. Network Configuration
      3. 2.2.3. The Protocols
        1. 2.2.3.1. Named Pipes
        2. 2.2.3.2. TCP/IP
        3. 2.2.3.3. Shared Memory
        4. 2.2.3.4. VIA
      4. 2.2.4. On to the Client
    3. 2.3. The SQL Server Management Studio
      1. 2.3.1. Getting Started
        1. 2.3.1.1. Server Type
        2. 2.3.1.2. SQL Server
        3. 2.3.1.3. Authentication Type
          1. 2.3.1.3.1. Windows Authentication
          2. 2.3.1.3.2. Mixed Authentication
      2. 2.3.2. Query Editor
        1. 2.3.2.1. Getting Started
        2. 2.3.2.2. Results in Text
        3. 2.3.2.3. Results in Grid
        4. 2.3.2.4. Results to File
        5. 2.3.2.5. SQLCMD Mode
        6. 2.3.2.6. Show Execution Plan
        7. 2.3.2.7. The DB Combo Box
        8. 2.3.2.8. The Object Explorer
    4. 2.4. SQL Server Business Intelligence Development Studio
    5. 2.5. SQL Server Integration Services (SSIS)
    6. 2.6. Reporting Services
    7. 2.7. Bulk Copy Program (bcp)
    8. 2.8. SQL Server Profiler
    9. 2.9. sqlcmd
    10. 2.10. Summary
  8. 3. Asking a Better Question: Advanced Queries
    1. 3.1. A Quick Review of Subqueries
    2. 3.2. Building a Nested Subquery
      1. 3.2.1. Nested Queries Using Single Value SELECT Statements
      2. 3.2.2. Nested Queries Using Subqueries That Return Multiple Values
        1. 3.2.2.1. Using a Nested SELECT to Find Orphaned Records
      3. 3.2.3. The ANY, SOME, and ALL Operators
    3. 3.3. Correlated Subqueries
      1. 3.3.1. How Correlated Subqueries Work
      2. 3.3.2. Correlated Subqueries in the WHERE Clause
      3. 3.3.3. Correlated Subqueries in the SELECT List
    4. 3.4. Derived Tables
    5. 3.5. The EXISTS Operator
      1. 3.5.1. Using EXISTS in Other Ways
    6. 3.6. The INTERSECT and EXCEPT Operators
      1. 3.6.1. EXCEPT
      2. 3.6.2. INTERSECT
      3. 3.6.3. Comparing EXCEPT and INTERSECT with Their EXISTS and NOT EXISTS Equivalents
    7. 3.7. Common Table Expressions (CTEs)
    8. 3.8. Recursive Queries
    9. 3.9. MERGE
    10. 3.10. Using External Calls to Perform Complex Actions
    11. 3.11. Performance Considerations
      1. 3.11.1. JOINs vs. Subqueries vs. ?
    12. 3.12. Summary
  9. 4. XML Integration
    1. 4.1. The XML Data Type
      1. 4.1.1. Defining a Column as Being of XML Type
      2. 4.1.2. XML Schema Collections
      3. 4.1.3. Creating, Altering, and Dropping XML Schema Collections
        1. 4.1.3.1. CREATE XML SCHEMA COLLECTION
        2. 4.1.3.2. ALTER XML SCHEMA COLLECTION
        3. 4.1.3.3. DROP XML SCHEMA COLLECTION
      4. 4.1.4. XML Data Type Methods
        1. 4.1.4.1. .query (SQL Server's Implementation of XQuery)
        2. 4.1.4.2. .value
        3. 4.1.4.3. .modify
        4. 4.1.4.4. .nodes
        5. 4.1.4.5. .exist
      5. 4.1.5. Enforcing Constraints beyond the Schema Collection
    2. 4.2. Retrieving Relational Data in XML Format
      1. 4.2.1. The FOR XML Clause
        1. 4.2.1.1. RAW
        2. 4.2.1.2. AUTO
        3. 4.2.1.3. EXPLICIT
          1. 4.2.1.3.1. Tag and Parent
          2. 4.2.1.3.2. Column Naming
          3. 4.2.1.3.3. element
          4. 4.2.1.3.4. xml
          5. 4.2.1.3.5. hide
          6. 4.2.1.3.6. id, idref, and idrefs
          7. 4.2.1.3.7. xmltext
          8. 4.2.1.3.8. cdata
        4. 4.2.1.4. PATH
          1. 4.2.1.4.1. PATH 101
          2. 4.2.1.4.2. Unnamed Columns
          3. 4.2.1.4.3. Named Columns
          4. 4.2.1.4.4. But Wait, There's More ...
      2. 4.2.2. OPENXML
    3. 4.3. A Quick Heads Up Regarding XML Indexes
    4. 4.4. A Brief Word on Hierarchical Data
    5. 4.5. Summary
  10. 5. Daring to Design
    1. 5.1. Normalization 201
      1. 5.1.1. Where to Begin
      2. 5.1.2. Getting to Third Normal Form
      3. 5.1.3. Other Normal Forms
    2. 5.2. Relationships
    3. 5.3. Diagramming
      1. 5.3.1. A Couple of Relationship Types
        1. 5.3.1.1. Identifying Relationships
        2. 5.3.1.2. Non-Identifying Relationships
      2. 5.3.2. The Entity Box
      3. 5.3.3. The Relationship Line
      4. 5.3.4. Terminators
    4. 5.4. Logical versus Physical Design
      1. 5.4.1. Purpose of a Logical Model
      2. 5.4.2. Parts of a Logical Model
        1. 5.4.2.1. Structure
        2. 5.4.2.2. Constraints
        3. 5.4.2.3. Rules
    5. 5.5. Dealing with File-Based Information Via Classic BLOBs
    6. 5.6. Subcategories
      1. 5.6.1. Types of Subcategories
      2. 5.6.2. Keeping Track of What's What — Implementing Subcategories
      3. 5.6.3. Getting Physical — The Physical Implementation of Subcategories
      4. 5.6.4. Adding to Extensibility with Subcategories
    7. 5.7. Database Reuse
      1. 5.7.1. Candidates for Reusable Databases
      2. 5.7.2. How to Break Things Up
      3. 5.7.3. The High Price of Reusability
    8. 5.8. De-Normalization
    9. 5.9. Partitioning for Scalability
    10. 5.10. The SQL Server Diagramming Tools
      1. 5.10.1. Tables
        1. 5.10.1.1. Adding Tables
        2. 5.10.1.2. Dropping Tables from Either the Database or Diagram
      2. 5.10.2. Dealing with Constraints
        1. 5.10.2.1. Primary Keys
        2. 5.10.2.2. Foreign Keys
        3. 5.10.2.3. CHECK Constraints
    11. 5.11. Regarding Date Columns
    12. 5.12. Summary
  11. 6. Core Storage and Index Structure
    1. 6.1. SQL Server Storage
      1. 6.1.1. The Database
      2. 6.1.2. The File
      3. 6.1.3. The Extent
      4. 6.1.4. The Page
        1. 6.1.4.1. Data Pages
        2. 6.1.4.2. Index Pages
        3. 6.1.4.3. BLOB Pages
        4. 6.1.4.4. Global Allocation Map, Shared Global Allocation Map, and Page Free Space Pages
        5. 6.1.4.5. Bulk Changed Map
        6. 6.1.4.6. Differential Changed Map
        7. 6.1.4.7. Page Splits
      5. 6.1.5. Rows
      6. 6.1.6. Full-Text Catalogs
      7. 6.1.7. File Streams
    2. 6.2. Understanding Indexes
      1. 6.2.1. To "B," or Not to "B": B-Trees
        1. 6.2.1.1. Page Splits — A Deeper Look
      2. 6.2.2. How Data Is Accessed in SQL Server
        1. 6.2.2.1. Use of Table Scans
        2. 6.2.2.2. Use of Indexes
      3. 6.2.3. Index Types and Index Navigation
        1. 6.2.3.1. Clustered Indexes
          1. 6.2.3.1.1. Navigating the Tree
        2. 6.2.3.2. Non-Clustered Indexes on a Heap
        3. 6.2.3.3. Non-Clustered Indexes on a Clustered Table
    3. 6.3. Creating, Altering, and Dropping Indexes
      1. 6.3.1. The CREATE INDEX Statement
        1. 6.3.1.1. ASC/DESC
        2. 6.3.1.2. INCLUDE
        3. 6.3.1.3. WITH
        4. 6.3.1.4. PAD_INDEX
        5. 6.3.1.5. FILLFACTOR
        6. 6.3.1.6. IGNORE_DUP_KEY
        7. 6.3.1.7. DROP_EXISTING
        8. 6.3.1.8. STATISTICS_NORECOMPUTE
        9. 6.3.1.9. SORT_IN_TEMPDB
        10. 6.3.1.10. ONLINE
        11. 6.3.1.11. ALLOW ROW/PAGE LOCKS
        12. 6.3.1.12. MAXDOP
        13. 6.3.1.13. ON
      2. 6.3.2. Implied Indexes Created with Constraints
      3. 6.3.3. ALTER INDEX
        1. 6.3.3.1. Index Name
        2. 6.3.3.2. Table or View Name
        3. 6.3.3.3. REBUILD
        4. 6.3.3.4. DISABLE
        5. 6.3.3.5. REORGANIZE
      4. 6.3.4. DROP INDEX
    4. 6.4. Choosing Wisely: Deciding What Index Goes Where and When
      1. 6.4.1. Selectivity
      2. 6.4.2. Watching Costs: When Less Is More
      3. 6.4.3. Choosing That Clustered Index
        1. 6.4.3.1. The Pros
        2. 6.4.3.2. The Cons
      4. 6.4.4. Column Order Matters
      5. 6.4.5. Dropping Indexes
      6. 6.4.6. Use the Database Engine Tuning Advisor
    5. 6.5. Maintaining Your Indexes
      1. 6.5.1. Fragmentation
      2. 6.5.2. Identifying Fragmentation
        1. 6.5.2.1. sy.sdm_db_index_physical_stats
        2. 6.5.2.2. Backward Compatibility
        3. 6.5.2.3. DBREINDEX — That Other Way of Maintaining Your Indexes
    6. 6.6. Summary
  12. 7. More Advanced Index Structures
    1. 7.1. XML Indexes
      1. 7.1.1. The Primary XML Index
      2. 7.1.2. Secondary XML Indexes
        1. 7.1.2.1. PATH XML Indexes
        2. 7.1.2.2. VALUE XML Indexes
        3. 7.1.2.3. PROPERTY XML Indexes
      3. 7.1.3. Creating XML Indexes
    2. 7.2. User-Defined Data Types
      1. 7.2.1. Classic UDTs
      2. 7.2.2. .NET UDTs
      3. 7.2.3. Tabular UDTs
        1. 7.2.3.1. Creating a Table User-Defined Data Type
      4. 7.2.4. Dropping a User-Defined Type
    3. 7.3. Hierarchical Data
      1. 7.3.1. Understanding Depth Versus Fanout
      2. 7.3.2. HierarchyID Type Structure
      3. 7.3.3. Working with HierarchyID Values — HierarchyID Methods
        1. 7.3.3.1. Methods Related to Retrieving a Given Level of Hierarchy Data
        2. 7.3.3.2. Methods Related to Retrieving Parent or Child Hierarchy Data
        3. 7.3.3.3. Inserting New Hierarchical Data
        4. 7.3.3.4. Moving Sub-Trees Between Parents
        5. 7.3.3.5. Getting the Root of a Hierarchy
      4. 7.3.4. Indexing Hierarchy Data
      5. 7.3.5. Performance Considerations
    4. 7.4. Spatial Data
      1. 7.4.1. Spatial Concepts
        1. 7.4.1.1. Planar (Flat Earth) Data
        2. 7.4.1.2. Geodetic (Round Earth) Data
        3. 7.4.1.3. Representing Spatial Data
      2. 7.4.2. Implementing Planar Data Representations — The GEOMETRY Data Type
      3. 7.4.3. Implementing Geodetic Representations — The GEOGRAPHY Type
    5. 7.5. Filestreams
    6. 7.6. Enabling Filestreaming
      1. 7.6.1. Enabling a Database for Filestreams
      2. 7.6.2. Creating a Filestream-Enabled Table
      3. 7.6.3. Using T-SQL with Filestreams
      4. 7.6.4. Using Filestreams with .NET
    7. 7.7. Table Compression
      1. 7.7.1. Enabling Compression
    8. 7.8. Summary
  13. 8. Views
    1. 8.1. Reviewing View Syntax
    2. 8.2. More Complex Views
      1. 8.2.1. Using a View to Change Data — Before INSTEAD OF Triggers
        1. 8.2.1.1. Dealing with Changes in Views with Joined Data
        2. 8.2.1.2. Required Fields Must Appear in the View or Have the Default Value
        3. 8.2.1.3. Limit What's Inserted into Views — WITH CHECK OPTION
    3. 8.3. Editing Views with T-SQL
    4. 8.4. Dropping Views
    5. 8.5. Auditing: Displaying Existing Code
    6. 8.6. Protecting Code: Encrypting Views
    7. 8.7. About Schema Binding
    8. 8.8. Making Your View Look Like a Table with VIEW_METADATA
    9. 8.9. Indexed (Materialized) Views
    10. 8.10. Partitioned Views
    11. 8.11. Summary
  14. 9. Scripts and Batches
    1. 9.1. Script Basics
    2. 9.2. Batches
      1. 9.2.1.
        1. 9.2.1.1. A Line to Itself
        2. 9.2.1.2. Each Batch Is Sent to the Server Separately
        3. 9.2.1.3. GO Is Not a T-SQL Command
      2. 9.2.2. Errors in Batches
      3. 9.2.3. When to Use Batches
        1. 9.2.3.1. Statements That Require Their Own Batch
        2. 9.2.3.2. Using Batches to Establish Precedence
    3. 9.3. SQLCMD
    4. 9.4. Dynamic SQL: Generating Your Code on the Fly with the EXEC Command
      1. 9.4.1. The Gotchas of EXEC
        1. 9.4.1.1. The Scope of EXEC
          1. 9.4.1.1.1. A Small Exception to the Rule
        2. 9.4.1.2. Security Contexts and EXEC
        3. 9.4.1.3. Use of Functions in Concatenation and EXEC
        4. 9.4.1.4. EXEC and UDFs
    5. 9.5. Control-of-Flow Statements
      1. 9.5.1. The IF ... ELSE Statement
        1. 9.5.1.1. The ELSE Clause
        2. 9.5.1.2. Grouping Code into Blocks
      2. 9.5.2. The CASE Statement
        1. 9.5.2.1. A Simple CASE
        2. 9.5.2.2. A Searched CASE
      3. 9.5.3. Looping with the WHILE Statement
      4. 9.5.4. The WAITFOR Statement
        1. 9.5.4.1. The DELAY Parameter
        2. 9.5.4.2. The TIME Parameter
      5. 9.5.5. TRY/CATCH Blocks
    6. 9.6. Summary
  15. 10. Advanced Programmability
    1. 10.1. A More Advanced Look At Stored Procedures
      1. 10.1.1. Output Parameters
      2. 10.1.2. Dealing with Errors
        1. 10.1.2.1. The Way We Were
          1. 10.1.2.1.1. Handling Inline Errors
          2. 10.1.2.1.2. Using @@ERROR
          3. 10.1.2.1.3. Using @@ERROR in a Sproc
        2. 10.1.2.2. Manually Raising Errors
          1. 10.1.2.2.1. Message ID/Message String
          2. 10.1.2.2.2. Severity
          3. 10.1.2.2.3. State
          4. 10.1.2.2.4. Error Arguments
          5. 10.1.2.2.5. WITH <option>
          6. 10.1.2.2.6. WITH LOG
          7. 10.1.2.2.7. WITH SETERROR
          8. 10.1.2.2.8. WITH NOWAIT
        3. 10.1.2.3. Adding Your Own Custom Error Messages
          1. 10.1.2.3.1. @lang
          2. 10.1.2.3.2. @with_log
          3. 10.1.2.3.3. @replace
          4. 10.1.2.3.4. Using sp_addmessage
          5. 10.1.2.3.5. Removing an Existing Custom Message
    2. 10.2. Table-Valued Parameters (TVPs)
    3. 10.3. Debugging
      1. 10.3.1. Starting the Debugger
      2. 10.3.2. Parts of the Debugger
        1. 10.3.2.1. The Locals Window
        2. 10.3.2.2. The Watch Window
        3. 10.3.2.3. The Call Stack Window
        4. 10.3.2.4. The Output Window
        5. 10.3.2.5. The Command Window
      3. 10.3.3. Using the Debugger Once It's Started
    4. 10.4. Understanding the SQLCLR and .NET Programming in SQL Server
      1. 10.4.1. Assemblies 101
      2. 10.4.2. Compiling an Assembly
      3. 10.4.3. Uploading Your Assembly to SQL Server
      4. 10.4.4. Creating Your Assembly-Based Stored Procedure
      5. 10.4.5. Creating Scalar User-Defined Functions from Assemblies
      6. 10.4.6. Creating Table-Valued Functions
    5. 10.5. Creating Aggregate Functions
      1. 10.5.1. Creating Triggers from Assemblies
    6. 10.6. Custom Data Types
      1. 10.6.1. Creating Your Data Type from Your Assembly
      2. 10.6.2. Accessing Your Complex Data Type
      3. 10.6.3. Dropping Data Types
    7. 10.7. Summary
  16. 11. Transactions and Locks
    1. 11.1. Transactions
      1. 11.1.1. BEGIN TRAN
        1. 11.1.1.1. Regarding Marking Transactions
      2. 11.1.2. COMMIT TRAN
      3. 11.1.3. ROLLBACK TRAN
      4. 11.1.4. SAVE TRAN
    2. 11.2. How the SQL Server Log Works
      1. 11.2.1. Using the CHECKPOINT Command
      2. 11.2.2. At Normal Server Shutdown
      3. 11.2.3. At a Change of Database Options
      4. 11.2.4. When the Truncate on Checkpoint Option Is Active
      5. 11.2.5. When Recovery Time Would Exceed the Recovery Interval Option Setting
      6. 11.2.6. Failure and Recovery
        1. 11.2.6.1. Transaction 1
        2. 11.2.6.2. Transaction 2
        3. 11.2.6.3. Transaction 3
        4. 11.2.6.4. Transaction 4
        5. 11.2.6.5. Transaction 5
      7. 11.2.7. Implicit Transactions
    3. 11.3. Locks and Concurrency
      1. 11.3.1. What Problems Can Be Prevented by Locks
        1. 11.3.1.1. Dirty Reads
        2. 11.3.1.2. Non-Repeatable Reads
        3. 11.3.1.3. Phantoms
        4. 11.3.1.4. Lost Updates
      2. 11.3.2. Lockable Resources
      3. 11.3.3. Lock Escalation and Lock Effects on Performance
      4. 11.3.4. Lock Modes
        1. 11.3.4.1. Shared Locks
        2. 11.3.4.2. Exclusive Locks
        3. 11.3.4.3. Update Locks
        4. 11.3.4.4. Intent Locks
        5. 11.3.4.5. Schema Locks
        6. 11.3.4.6. Bulk Update Locks
        7. 11.3.4.7. Ranged Keylocks
      5. 11.3.5. Lock Compatibility
      6. 11.3.6. Specifying a Specific Lock Type — Optimizer Hints
        1. 11.3.6.1. Determining Locks Using the Management Studio
    4. 11.4. Setting the Isolation Level
      1. 11.4.1. READ COMMITTED
      2. 11.4.2. READ UNCOMMITTED
      3. 11.4.3. REPEATABLE READ
      4. 11.4.4. SERIALIZABLE
      5. 11.4.5. SNAPSHOT
    5. 11.5. Dealing with Deadlocks (a.k.a. "A 1205")
      1. 11.5.1. How SQL Server Figures Out There's a Deadlock
      2. 11.5.2. How Deadlock Victims Are Chosen
      3. 11.5.3. Avoiding Deadlocks
        1. 11.5.3.1. Using Objects in the Same Order
        2. 11.5.3.2. Keeping Transactions As Short As Possible
        3. 11.5.3.3. Using the Lowest Transaction Isolation Level Possible
        4. 11.5.3.4. Allowing No Open-Ended Transactions
        5. 11.5.3.5. Using Bound Connections
    6. 11.6. Summary
  17. 12. Triggers
    1. 12.1. What Is a Trigger?
      1. 12.1.1. ON
      2. 12.1.2. WITH ENCRYPTION
      3. 12.1.3. The FOR\AFTER versus the INSTEAD OF Clause
        1. 12.1.3.1. INSERT Trigger
        2. 12.1.3.2. DELETE Trigger
        3. 12.1.3.3. UPDATE Trigger
      4. 12.1.4. WITH APPEND
      5. 12.1.5. NOT FOR REPLICATION
      6. 12.1.6. AS
    2. 12.2. Using Triggers for Data Integrity Rules
      1. 12.2.1. Dealing with Requirements Sourced from Other Tables
      2. 12.2.2. Using Triggers to Check the Delta of an Update
      3. 12.2.3. Using Triggers for Custom Error Messages
    3. 12.3. Other Common Uses for Triggers
      1. 12.3.1. Updating Summary Information
      2. 12.3.2. Feeding Data into De-normalized Tables for Reporting
      3. 12.3.3. Setting Condition Flags
    4. 12.4. Other Trigger Issues
      1. 12.4.1. Triggers Can Be Nested
      2. 12.4.2. Triggers Can Be Recursive
      3. 12.4.3. Debugging Triggers
      4. 12.4.4. Triggers Don't Get in the Way of Architecture Changes
      5. 12.4.5. Triggers Can Be Turned Off without Being Removed
      6. 12.4.6. Trigger Firing Order
        1. 12.4.6.1. Controlling Firing Order for Logic Reasons
        2. 12.4.6.2. Controlling Firing Order for Performance Reasons
    5. 12.5. INSTEAD OF Triggers
      1. 12.5.1. INSTEAD OF INSERT Triggers
      2. 12.5.2. INSTEAD OF UPDATE Triggers
      3. 12.5.3. INSTEAD OF DELETE Triggers
    6. 12.6. IF UPDATE() and COLUMNS_UPDATED()
      1. 12.6.1. The UPDATE() Function
      2. 12.6.2. The COLUMNS_UPDATED() Function
    7. 12.7. Performance Considerations
      1. 12.7.1. Triggers Are Reactive Rather Than Proactive
      2. 12.7.2. Triggers Don't Have Concurrency Issues with the Process That Fires Them
      3. 12.7.3. Keep It Short and Sweet
      4. 12.7.4. Don't Forget Triggers When Choosing Indexes
      5. 12.7.5. Try Not to Roll Back within Triggers
    8. 12.8. Dropping Triggers
    9. 12.9. Summary
  18. 13. SQL Cursors
    1. 13.1. What Is a Cursor?
    2. 13.2. The Life Span of a Cursor
    3. 13.3. Types of Cursors and Extended Declaration Syntax
      1. 13.3.1. Scope
      2. 13.3.2. Scrollability
        1. 13.3.2.1. FORWARD_ONLY
        2. 13.3.2.2. SCROLLABLE
      3. 13.3.3. Cursor Types
        1. 13.3.3.1. Static Cursors
        2. 13.3.3.2. Keyset-Driven Cursors
        3. 13.3.3.3. Dynamic Cursors
        4. 13.3.3.4. FAST_FORWARD Cursors
      4. 13.3.4. Concurrency Options
        1. 13.3.4.1. READ_ONLY
        2. 13.3.4.2. SCROLL_LOCKS
        3. 13.3.4.3. OPTIMISTIC
      5. 13.3.5. Detecting Conversion of Cursor Types: TYPE_WARNING
      6. 13.3.6. FOR <SELECT>
      7. 13.3.7. FOR UPDATE
    4. 13.4. Navigating the Cursor: The FETCH Statement
    5. 13.5. Altering Data within Your Cursor
    6. 13.6. Summary
  19. 14. Reporting Services
    1. 14.1. A Quick Look at Reports as a Concept
    2. 14.2. Reporting Services 101
      1. 14.2.1. Tools Used with Reporting Services
      2. 14.2.2. Other Means of Accessing Reporting Services
    3. 14.3. Report Server Projects
      1. 14.3.1. Data Sources
        1. 14.3.1.1. Creating a Data Source
      2. 14.3.2. Using the Report Wizard
      3. 14.3.3. Editing Reports
      4. 14.3.4. Parameterizing Reports
      5. 14.3.5. Providing/Controlling Parameter Values and How They Are Used
        1. 14.3.5.1. Creating Pre-set Parameter Lists
        2. 14.3.5.2. Creating Parameter Lists from Queries
        3. 14.3.5.3. Getting Headings and Other Fields from Parameters
      6. 14.3.6. Adding Charts
      7. 14.3.7. Linking Reports
      8. 14.3.8. Deploying the Report
    4. 14.4. A Brief Note on RDL
    5. 14.5. Summary
  20. 15. Buying in Bulk: The Bulk Copy Program (BCP) and Other Basic Bulk Operations
    1. 15.1. bcp Utility
      1. 15.1.1. bcp Syntax
      2. 15.1.2. bcp Import
        1. 15.1.2.1. Data Import Example
        2. 15.1.2.2. Logged vs. Non-logged
      3. 15.1.3. bcp Export
    2. 15.2. Format Files
      1. 15.2.1. When Your Columns Don't Match
        1. 15.2.1.1. Files with Fewer Columns Than the Table
        2. 15.2.1.2. More Columns in the File Than in the Table
        3. 15.2.1.3. Mismatched Field Order
      2. 15.2.2. Using Format Files
      3. 15.2.3. Maximizing Import Performance
    3. 15.3. BULK INSERT
    4. 15.4. OPENROWSET (BULK)
      1. 15.4.1. ROWS_PER_BATCH
      2. 15.4.2. SINGLE_BLOB, SINGLE_CLOB, SINGLE_NCLOB
    5. 15.5. Summary
  21. 16. Getting Integrated
    1. 16.1. Understanding the Problem
    2. 16.2. An Overview of Packages
      1. 16.2.1. Tasks
      2. 16.2.2. The Main Window
        1. 16.2.2.1. Control Flow
        2. 16.2.2.2. Data Flow
        3. 16.2.2.3. Event Handlers
        4. 16.2.2.4. Package Explorer
      3. 16.2.3. Solution Explorer
      4. 16.2.4. The Properties Window
    3. 16.3. Building a Simple Package
    4. 16.4. Executing Packages
      1. 16.4.1. Using the Execute Package Utility
        1. 16.4.1.1. General
        2. 16.4.1.2. Configurations
        3. 16.4.1.3. Command Files
        4. 16.4.1.4. Connection Managers
        5. 16.4.1.5. Execution Options
        6. 16.4.1.6. Reporting
        7. 16.4.1.7. Logging
        8. 16.4.1.8. Set Values
        9. 16.4.1.9. Verification
        10. 16.4.1.10. Command Line
        11. 16.4.1.11. Executing the Package
      2. 16.4.2. Executing within Management Studio
    5. 16.5. Summary
  22. 17. Replication
    1. 17.1. Replication Basics
      1. 17.1.1. Considerations When Planning for Replication
        1. 17.1.1.1. Autonomy
        2. 17.1.1.2. Latency
        3. 17.1.1.3. Data Consistency
        4. 17.1.1.4. Schema Consistency
        5. 17.1.1.5. Other Considerations
      2. 17.1.2. Replication Roles
        1. 17.1.2.1. The Publisher
        2. 17.1.2.2. The Distributor
        3. 17.1.2.3. The Subscriber
      3. 17.1.3. Subscriptions
      4. 17.1.4. Types of Subscribers
      5. 17.1.5. Filtering Data
    2. 17.2. Replication Models
      1. 17.2.1. Snapshot Replication
        1. 17.2.1.1. How Snapshot Replication Works
          1. 17.2.1.1.1. Snapshot Agent
          2. 17.2.1.1.2. Distribution Agent
          3. 17.2.1.1.3. The Process of Snapshot Replication
        2. 17.2.1.2. When to Use Snapshot Replication
        3. 17.2.1.3. Special Planning Requirements
      2. 17.2.2. Merge Replication
        1. 17.2.2.1. Merge Agent
        2. 17.2.2.2. The Process of Merge Replication
        3. 17.2.2.3. When to Use Merge Replication
        4. 17.2.2.4. Special Planning Requirements
      3. 17.2.3. Transactional Replication
        1. 17.2.3.1. Log Reader Agent
        2. 17.2.3.2. The Process of Transactional Replication
        3. 17.2.3.3. When to Use Transactional Replication
        4. 17.2.3.4. Special Planning Requirements
      4. 17.2.4. Immediate-Update Subscribers
      5. 17.2.5. Mixing Replication Types
    3. 17.3. Replication Topology
      1. 17.3.1. Simple Models
        1. 17.3.1.1. Central Publisher/Distributor
        2. 17.3.1.2. Central Publisher/Remote Distributor
        3. 17.3.1.3. Central Subscriber
      2. 17.3.2. Mixed Models
        1. 17.3.2.1. Publishing Subscriber
        2. 17.3.2.2. Publisher/Subscriber
        3. 17.3.2.3. Multiple Subscribers/Multiple Publishers
        4. 17.3.2.4. Self-Publishing
    4. 17.4. Planning for Replication
      1. 17.4.1. Data Concerns
        1. 17.4.1.1. timestamp
        2. 17.4.1.2. uniqueidentifier
        3. 17.4.1.3. User-Defined Data Types
        4. 17.4.1.4. NOT FOR REPLICATION
      2. 17.4.2. Mobile Devices
    5. 17.5. Setting Up Replication in Management Studio
      1. 17.5.1. Configuring the Server for Replication
      2. 17.5.2. Configuring a Publication
      3. 17.5.3. Setting Up Subscribers (via Management Studio)
      4. 17.5.4. Using Our Replicated Database
    6. 17.6. Replication Management Objects (RMO)
    7. 17.7. Summary
  23. 18. Looking at Things in Full: Full-Text Search
    1. 18.1. Full-Text Search Architecture
    2. 18.2. Setting Up Full-Text Indexes and Catalogs
      1. 18.2.1. Enabling Full-Text for Your Database
      2. 18.2.2. Creating, Altering, Dropping, and Manipulating a Full-Text Catalog
        1. 18.2.2.1. Altering Full-Text Catalogs
          1. 18.2.2.1.1. REBUILD
          2. 18.2.2.1.2. REORGANIZE
          3. 18.2.2.1.3. AS DEFAULT
          4. 18.2.2.1.4. Dropping Full-Text Catalogs
      3. 18.2.3. Creating, Altering, Dropping, and Manipulating Full-Text Indexes
        1. 18.2.3.1. Creating Full-Text Indexes
          1. 18.2.3.1.1. Column List
          2. 18.2.3.1.2. LANGUAGE
          3. 18.2.3.1.3. TYPE COLUMN
          4. 18.2.3.1.4. KEY INDEX
          5. 18.2.3.1.5. ON
          6. 18.2.3.1.6. WITH
          7. 18.2.3.1.7. CHANGE_TRACKING
          8. 18.2.3.1.8. NO POPULATION
          9. 18.2.3.1.9. STOPLIST
        2. 18.2.3.2. Altering Full-Text Indexes
          1. 18.2.3.2.1. ENABLE/DISABLE
          2. 18.2.3.2.2. ADD
          3. 18.2.3.2.3. DROP
          4. 18.2.3.2.4. START ... POPULATION
          5. 18.2.3.2.5. FULL
          6. 18.2.3.2.6. INCREMENTAL
          7. 18.2.3.2.7. UPDATE
          8. 18.2.3.2.8. STOP, PAUSE, RESUME
        3. 18.2.3.3. Dropping Full-Text Indexes
      4. 18.2.4. A Note Regarding the Older Syntax
    3. 18.3. More on Index Population
    4. 18.4. Full-Text Query Syntax
      1. 18.4.1. CONTAINS
      2. 18.4.2. FREETEXT
      3. 18.4.3. CONTAINSTABLE
      4. 18.4.4. FREETEXTTABLE
      5. 18.4.5. Dealing with Phrases
      6. 18.4.6. Booleans
      7. 18.4.7. Proximity
      8. 18.4.8. Weighting
      9. 18.4.9. Inflectional
    5. 18.5. Stop Words
    6. 18.6. Summary
  24. 19. Feeling Secure
    1. 19.1. Security Basics
      1. 19.1.1. One Person, One Login, One Password
      2. 19.1.2. Password Expiration
        1. 19.1.2.1. What Do You Get for Your Effort?
        2. 19.1.2.2. Now the Bad News
      3. 19.1.3. Password Length and Makeup
        1. 19.1.3.1. Password Length
        2. 19.1.3.2. Password Makeup
      4. 19.1.4. Number of Tries to Log In
      5. 19.1.5. Storage of User and Password Information
    2. 19.2. Security Options
      1. 19.2.1. SQL Server Security
      2. 19.2.2. Creating and Managing Logins
        1. 19.2.2.1. CREATE LOGIN
          1. 19.2.2.1.1. CREATE LOGIN ... WITH
          2. 19.2.2.1.2. CREATE LOGIN ... FROM
          3. 19.2.2.1.3. ALTER LOGIN
          4. 19.2.2.1.4. DROP LOGIN
        2. 19.2.2.2. Creating a Login Using the Management Studio
        3. 19.2.2.3. SQL Management Objects
        4. 19.2.2.4. Legacy Options
          1. 19.2.2.4.1. A Quick Look at sp_addlogin
          2. 19.2.2.4.2. sp_password
          3. 19.2.2.4.3. sp_grantlogin
      3. 19.2.3. Windows Authentication
    3. 19.3. User Permissions
      1. 19.3.1. Granting Access to a Specific Database
        1. 19.3.1.1. CREATE USER
        2. 19.3.1.2. sp_grantdbaccess
      2. 19.3.2. Granting Object Permissions within the Database
        1. 19.3.2.1. GRANT
        2. 19.3.2.2. DENY
        3. 19.3.2.3. REVOKE
      3. 19.3.3. User Rights and Statement-Level Permissions
    4. 19.4. Server and Database Roles
      1. 19.4.1. Server Roles
      2. 19.4.2. Database Roles
        1. 19.4.2.1. Fixed Database Roles
        2. 19.4.2.2. User-Defined Database Roles
          1. 19.4.2.2.1. Creating a User-Defined Role Using CREATE ROLE
        3. 19.4.2.3. Using sp_addrole
          1. 19.4.2.3.1. Adding Users to a Role
          2. 19.4.2.3.2. Removing a User from a Role
          3. 19.4.2.3.3. Dropping Roles
    5. 19.5. Application Roles
      1. 19.5.1. Creating Application Roles
      2. 19.5.2. Adding Permissions to the Application Role
      3. 19.5.3. Using the Application Role
      4. 19.5.4. Getting Rid of Application Roles
    6. 19.6. More Advanced Security
      1. 19.6.1. What to Do About the Guest Account
      2. 19.6.2. TCP/IP Port Settings
      3. 19.6.3. Don't Use the sa Account
      4. 19.6.4. Keep xp_cmdshell under Wraps
      5. 19.6.5. Don't Forget Views, Stored Procedures, and UDFs as Security Tools
    7. 19.7. Certificates and Asymmetric Keys
      1. 19.7.1. Certificates
      2. 19.7.2. Asymmetric Keys
      3. 19.7.3. Database Encryption
    8. 19.8. Summary
  25. 20. A Grand Performance: Designing a Database That Performs Well
    1. 20.1. When to Tune
    2. 20.2. Index Choices
      1. 20.2.1. Check the Index Tuning Tool in the Database Engine Tuning Advisor
    3. 20.3. Client vs. Server-Side Processing
    4. 20.4. Strategic De-Normalization
    5. 20.5. Organizing Your Sprocs Well
      1. 20.5.1. Keeping Transactions Short
      2. 20.5.2. Using the Least Restrictive Transaction Isolation Level Possible
      3. 20.5.3. Implementing Multiple Solutions if Necessary
      4. 20.5.4. Avoiding Cursors if Possible
    6. 20.6. Uses for Temporary Tables
      1. 20.6.1. Using Temp Tables to Break Apart Complex Problems
      2. 20.6.2. Using Temp Tables to Allow Indexing on Working Data
    7. 20.7. Update Your Code In a Timely Fashion
    8. 20.8. Sometimes, It's the Little Things
    9. 20.9. Hardware Considerations
      1. 20.9.1. Exclusive Use of the Server
      2. 20.9.2. I/O vs. CPU Intensive
        1. 20.9.2.1. I/O Intensive
          1. 20.9.2.1.1. A Brief Look at RAID
        2. 20.9.2.2. CPU Intensive
      3. 20.9.3. OLTP vs. OLAP
      4. 20.9.4. On-Site vs. Off-Site
      5. 20.9.5. The Risks of Being Down
      6. 20.9.6. Lost Data
      7. 20.9.7. Is Performance Everything?
      8. 20.9.8. Driver Support
      9. 20.9.9. The Ideal System
    10. 20.10. Summary
  26. 21. What Comes After: Forensic Performance Tuning
    1. 21.1. When to Tune (Mark Two)
    2. 21.2. Routine Maintenance
    3. 21.3. Troubleshooting
      1. 21.3.1. The Data Collector
      2. 21.3.2. The Various Showplans and STATISTICS
        1. 21.3.2.1. SHOWPLAN TEXT|ALL
        2. 21.3.2.2. Graphical Showplan
        3. 21.3.2.3. STATISTICS
          1. 21.3.2.3.1. SET STATISTICS IO ON|OFF
          2. 21.3.2.3.2. SET STATISTICS TIME ON|OFF
          3. 21.3.2.3.3. Include Client Statistics
      3. 21.3.3. Database Console Commands (DBCC)
      4. 21.3.4. Dynamic Management Views
      5. 21.3.5. The Activity Monitor
        1. 21.3.5.1. Overview
        2. 21.3.5.2. Processes
        3. 21.3.5.3. Resource Waits
        4. 21.3.5.4. Data File I/O
        5. 21.3.5.5. Recent Expensive Queries
      6. 21.3.6. The SQL Server Profiler
      7. 21.3.7. The Performance Monitor (PerfMon)
    4. 21.4. Summary
  27. 22. Administration
    1. 22.1. Scheduling Jobs
      1. 22.1.1. Creating an Operator
        1. 22.1.1.1. Creating an Operator Using Management Studio
        2. 22.1.1.2. Creating an Operator Using T-SQL
      2. 22.1.2. Creating Jobs and Tasks
        1. 22.1.2.1. Creating Jobs and Tasks Using Management Studio
        2. 22.1.2.2. Creating Jobs and Tasks Using T-SQL
          1. 22.1.2.2.1. sp_add_job
          2. 22.1.2.2.2. sp_add_jobserver
          3. 22.1.2.2.3. sp_add_jobstep
          4. 22.1.2.2.4. sp_add_jobschedule
        3. 22.1.2.3. Maintaining and Deleting Jobs and Tasks
          1. 22.1.2.3.1. Editing and Deleting Jobs with T-SQL
    2. 22.2. Backup and Recovery
      1. 22.2.1. Creating a Backup — a.k.a. "A Dump"
        1. 22.2.1.1. Backup Type
        2. 22.2.1.2. Backup Set
        3. 22.2.1.3. Destination
        4. 22.2.1.4. Options
        5. 22.2.1.5. Schedule
        6. 22.2.1.6. Backing Up Using T-SQL
      2. 22.2.2. Recovery Models
      3. 22.2.3. Recovery
        1. 22.2.3.1. Restoring to a Different Location
        2. 22.2.3.2. Recovery Status
        3. 22.2.3.3. Restoring Data Using T-SQL
    3. 22.3. Index Maintenance
      1. 22.3.1. ALTER INDEX
      2. 22.3.2. Index Name
      3. 22.3.3. Table or View Name
      4. 22.3.4. REBUILD
      5. 22.3.5. DISABLE
      6. 22.3.6. REORGANIZE
    4. 22.4. Archiving of Data
    5. 22.5. PowerShell
      1. 22.5.1. Trying Out PowerShell
      2. 22.5.2. Navigating in PowerShell
      3. 22.5.3. A Final Word on PowerShell
    6. 22.6. Policy-Based Management
    7. 22.7. Summary
  28. 23. SMO: SQL Management Objects
    1. 23.1. The History of SQL Server Management Object Models
      1. 23.1.1. SQL Distributed Management Objects
      2. 23.1.2. SQL Namespaces
      3. 23.1.3. Windows Management Instrumentation
      4. 23.1.4. SMO
    2. 23.2. The SMO Object Model
    3. 23.3. Walking through Some Examples
      1. 23.3.1. Getting Started
        1. 23.3.1.1. Declarations
        2. 23.3.1.2. Basic Connection and Server References
      2. 23.3.2. Creating a Database
      3. 23.3.3. Creating Tables
    4. 23.4. Dropping a Database
    5. 23.5. Backing Up a Database
    6. 23.6. Scripting
    7. 23.7. Pulling It All Together
    8. 23.8. Summary
  29. 24. Data Warehousing
    1. 24.1. Considering Differing Requirements
      1. 24.1.1. Online Transaction Processing (OLTP)
      2. 24.1.2. Online Analytical Processing (OLAP)
      3. 24.1.3. A Brief Word on Data Mining
      4. 24.1.4. OLTP or OLAP?
    2. 24.2. Dimensional Databases
      1. 24.2.1. The Fact Table
      2. 24.2.2. The Dimension Tables
      3. 24.2.3. The Star and Snowflake Schemas
      4. 24.2.4. Data Cubes
    3. 24.3. Data Warehouse Concepts
      1. 24.3.1. Data Warehouse Characteristics
      2. 24.3.2. Data Marts
    4. 24.4. SQL Server Integration Services
      1. 24.4.1. Data Validation
      2. 24.4.2. Data Scrubbing
    5. 24.5. Creating an Analysis Services Solution
    6. 24.6. Accessing a Cube
    7. 24.7. Summary
  30. 25. Being Well Connected
    1. 25.1. Access Models Past and Present
      1. 25.1.1. DB-Lib/VBSQL
      2. 25.1.2. Open Database Connectivity: ODBC
      3. 25.1.3. Data Access Objects (DAO)
      4. 25.1.4. Remote Data Objects (RDO)
      5. 25.1.5. Java Database Connectivity (JDBC)
      6. 25.1.6. OLE-DB
      7. 25.1.7. ActiveX Data Objects (ADO)
      8. 25.1.8. Multi-Dimensional OLE-DB/ADO (OLE-DB MD/ADO MD)
      9. 25.1.9. ADO.NET
      10. 25.1.10. Language Integrated Query (aka LINQ)
    2. 25.2. Some General Concepts
      1. 25.2.1. Connection
      2. 25.2.2. Command
      3. 25.2.3. Data Set
      4. 25.2.4. Bound Controls
    3. 25.3. General Performance Considerations
      1. 25.3.1. Connection Management
      2. 25.3.2. Roundtrips
      3. 25.3.3. Hierarchies
      4. 25.3.4. Dependent Data
      5. 25.3.5. Bandwidth
    4. 25.4. Getting Ready for the More Individual Examples
      1. 25.4.1. Setting Up the Example Project
    5. 25.5. Exploring ADO.NET
      1. 25.5.1. Getting Connected with ADO.NET
      2. 25.5.2. Using Bound Controls in ADO.NET
    6. 25.6. Language Integrated Query (aka LINQ)
      1. 25.6.1. Understanding Key Differences Between LINQ Query Syntax and SQL
      2. 25.6.2. Building a LINQ to SQL Entity Model
      3. 25.6.3. Utilizing a LINQ to SQL Class in Code
      4. 25.6.4. A Few Other Notes on LINQ
    7. 25.7. A Quick Discussion of Entity Frameworks
    8. 25.8. Pros and Cons
    9. 25.9. Summary
  31. A. System Functions
    1. A.1. Legacy System Functions (a.k.a. Global Variables)
      1. A.1.1. @@CONNECTIONS
      2. A.1.2. @@CPU_BUSY
      3. A.1.3. @@IDLE
      4. A.1.4. @@IO_BUSY
      5. A.1.5. @@PACK_RECEIVED and @@PACK_SENT
      6. A.1.6. @@PACKET_ERRORS
      7. A.1.7. @@TIMETICKS
      8. A.1.8. @@TOTAL_ERRORS
      9. A.1.9. @@TOTAL_READ and @@TOTAL_WRITE
      10. A.1.10. @@TRANCOUNT
    2. A.2. Aggregate Functions
      1. A.2.1. AVG
      2. A.2.2. CHECKSUM
      3. A.2.3. CHECKSUM_AGG
      4. A.2.4. COUNT
      5. A.2.5. COUNT_BIG
      6. A.2.6. GROUPING
      7. A.2.7. MAX
      8. A.2.8. MIN
      9. A.2.9. STDEV
      10. A.2.10. STDEVP
      11. A.2.11. SUM
      12. A.2.12. VAR
      13. A.2.13. VARP
    3. A.3. Configuration Functions
      1. A.3.1. @@DATEFIRST
      2. A.3.2. @@DBTS
      3. A.3.3. @@LANGID and @@LANGUAGE
      4. A.3.4. @@LOCK_TIMEOUT
      5. A.3.5. @@MAX_CONNECTIONS
      6. A.3.6. @@MAX_PRECISION
      7. A.3.7. @@NESTLEVEL
      8. A.3.8. @@OPTIONS
      9. A.3.9. @@REMSERVER
      10. A.3.10. @@SERVERNAME
      11. A.3.11. @@SERVICENAME
      12. A.3.12. @@SPID
      13. A.3.13. @@TEXTSIZE
      14. A.3.14. @@VERSION
    4. A.4. Cryptographic Functions
      1. A.4.1. AsymKey_ID
      2. A.4.2. Cert_ID
      3. A.4.3. CertProperty
      4. A.4.4. DecryptByAsmKey
      5. A.4.5. DecryptByCert
      6. A.4.6. DecryptByKey
      7. A.4.7. DecryptByPassPhrase
      8. A.4.8. EncryptByAsmKey
      9. A.4.9. EncryptByCert
      10. A.4.10. EncryptByKey
      11. A.4.11. EncryptByPassPhrase
      12. A.4.12. Key_GUID
      13. A.4.13. Key_ID
      14. A.4.14. SignByAsymKey
      15. A.4.15. SignByCert
      16. A.4.16. VerifySignedByAsymKey
      17. A.4.17. VerifySignedByCert
    5. A.5. Cursor Functions
      1. A.5.1. @@CURSOR_ROWS
      2. A.5.2. @@FETCH_STATUS
      3. A.5.3. CURSOR_STATUS
    6. A.6. Date and Time Functions
      1. A.6.1. CURRENT_TIMESTAMP
      2. A.6.2. DATEADD
      3. A.6.3. DATEDIFF
      4. A.6.4. DATENAME
      5. A.6.5. DATEPART
      6. A.6.6. DAY
      7. A.6.7. GETDATE
      8. A.6.8. GETUTCDATE
      9. A.6.9. ISDATE
      10. A.6.10. MONTH
      11. A.6.11. SYSDATETIME
      12. A.6.12. SYSDATETIMEOFFSET
      13. A.6.13. SYSUTCDATETIME
      14. A.6.14. SWITCHOFFSET
      15. A.6.15. TODATETIMEOFFSET
      16. A.6.16. YEAR
    7. A.7. Mathematical Functions
      1. A.7.1. ABS
      2. A.7.2. ACOS
      3. A.7.3. ASIN
      4. A.7.4. ATAN
      5. A.7.5. ATN2
      6. A.7.6. CEILING
      7. A.7.7. COS
      8. A.7.8. COT
      9. A.7.9. DEGREES
      10. A.7.10. EXP
      11. A.7.11. FLOOR
      12. A.7.12. LOG
      13. A.7.13. LOG10
      14. A.7.14. PI
      15. A.7.15. POWER
      16. A.7.16. RADIANS
      17. A.7.17. RAND
      18. A.7.18. ROUND
      19. A.7.19. SIGN
      20. A.7.20. SIN
      21. A.7.21. SQRT
      22. A.7.22. SQUARE
      23. A.7.23. TAN
    8. A.8. Basic Metadata Functions
      1. A.8.1. COL_LENGTH
      2. A.8.2. COL_NAME
      3. A.8.3. COLUMNPROPERTY
      4. A.8.4. DATABASEPROPERTY
      5. A.8.5. DATABASEPROPERTYEX
      6. A.8.6. DB_ID
      7. A.8.7. DB_NAME
      8. A.8.8. FILE_ID
      9. A.8.9. FILE_NAME
      10. A.8.10. FILEGROUP_ID
      11. A.8.11. FILEGROUP_NAME
      12. A.8.12. FILEGROUPPROPERTY
      13. A.8.13. FILEPROPERTY
      14. A.8.14. FULLTEXTCATALOGPROPERTY
      15. A.8.15. FULLTEXTSERVICEPROPERTY
      16. A.8.16. INDEX_COL
      17. A.8.17. INDEXKEY_PROPERTY
      18. A.8.18. INDEXPROPERTY
      19. A.8.19. OBJECT_ID
      20. A.8.20. OBJECT_NAME
      21. A.8.21. OBJECTPROPERTY
      22. A.8.22. OBJECTPROPERTYEX
      23. A.8.23. @@PROCID
      24. A.8.24. SCHEMA_ID
      25. A.8.25. SCHEMA_NAME
      26. A.8.26. SQL_VARIANT_PROPERTY
      27. A.8.27. TYPEPROPERTY
    9. A.9. Rowset Functions
      1. A.9.1. CHANGETABLE
      2. A.9.2. CONTAINSTABLE
      3. A.9.3. FREETEXTTABLE
      4. A.9.4. OPENDATASOURCE
      5. A.9.5. OPENQUERY
      6. A.9.6. OPENROWSET
      7. A.9.7. OPENXML
    10. A.10. Security Functions
      1. A.10.1. HAS_DBACCESS
      2. A.10.2. IS_MEMBER
      3. A.10.3. IS_SRVROLEMEMBER
      4. A.10.4. SUSER_ID
      5. A.10.5. SUSER_NAME
      6. A.10.6. SUSER_SID
      7. A.10.7. SUSER_SNAME
      8. A.10.8. USER
      9. A.10.9. USER_ID
      10. A.10.10. USER_NAME
    11. A.11. String Functions
      1. A.11.1. ASCII
      2. A.11.2. CHAR
      3. A.11.3. CHARINDEX
      4. A.11.4. DIFFERENCE
      5. A.11.5. LEFT
      6. A.11.6. LEN
      7. A.11.7. LOWER
      8. A.11.8. LTRIM
      9. A.11.9. NCHAR
      10. A.11.10. PATINDEX
      11. A.11.11. QUOTENAME
      12. A.11.12. REPLACE
      13. A.11.13. REPLICATE
      14. A.11.14. REVERSE
      15. A.11.15. RIGHT
      16. A.11.16. RTRIM
      17. A.11.17. SOUNDEX
      18. A.11.18. SPACE
      19. A.11.19. STR
      20. A.11.20. STUFF
      21. A.11.21. SUBSTRING
      22. A.11.22. UNICODE
      23. A.11.23. UPPER
    12. A.12. System Functions
      1. A.12.1. APP_NAME
      2. A.12.2. CASE
        1. A.12.2.1. Simple CASE function:
        2. A.12.2.2. Searched CASE function:
      3. A.12.3. CAST and CONVERT
        1. A.12.3.1. Using CAST:
        2. A.12.3.2. Using CONVERT:
      4. A.12.4. COALESCE
      5. A.12.5. COLLATIONPROPERTY
      6. A.12.6. CURRENT_USER
      7. A.12.7. DATALENGTH
      8. A.12.8. @@ERROR
      9. A.12.9. FORMATMESSAGE
      10. A.12.10. GETANSINULL
      11. A.12.11. HOST_ID
      12. A.12.12. HOST_NAME
      13. A.12.13. IDENT_CURRENT
      14. A.12.14. IDENT_INCR
      15. A.12.15. IDENT_SEED
      16. A.12.16. @@IDENTITY
      17. A.12.17. IDENTITY
      18. A.12.18. ISNULL
      19. A.12.19. ISNUMERIC
      20. A.12.20. NEWID
      21. A.12.21. NULLIF
      22. A.12.22. PARSENAME
      23. A.12.23. PERMISSIONS
      24. A.12.24. @@ROWCOUNT
      25. A.12.25. ROWCOUNT_BIG
      26. A.12.26. SCOPE_IDENTITY
      27. A.12.27. SERVERPROPERTY
      28. A.12.28. SESSION_USER
      29. A.12.29. SESSIONPROPERTY
      30. A.12.30. STATS_DATE
      31. A.12.31. SYSTEM_USER
      32. A.12.32. USER_NAME
    13. A.13. Text and Image Functions
      1. A.13.1. TEXTPTR
      2. A.13.2. TEXTVALID
  32. B. Going Meta: Getting Data About Your Data
    1. B.1. System Views
      1. B.1.1. sys.assemblies
      2. B.1.2. sys.columns
      3. B.1.3. sys.databases
      4. B.1.4. sys.database_files
      5. B.1.5. sys.identity_columns
      6. B.1.6. sys.indexes
      7. B.1.7. sys.index_columns
      8. B.1.8. sys.objects
      9. B.1.9. sys.partitions
      10. B.1.10. sys.partition_functions
      11. B.1.11. sys.schemas
      12. B.1.12. sys.servers
      13. B.1.13. sys.spatial_indexes
      14. B.1.14. sys.synonyms
      15. B.1.15. sys.user_token
      16. B.1.16. sys.xml_indexes
    2. B.2. Dynamic Management Views
      1. B.2.1. Index-Related dm Views and Functions
        1. B.2.1.1. sys.db_db_index_physical_stats
        2. B.2.1.2. sys.dm_db_index_usage_stats
        3. B.2.1.3. The sys.dm_db_missing_index_* family of views
  33. C. The Basics
    1. C.1. Everything to Do with Queries
      1. C.1.1. The Basic SELECT Statement
        1. C.1.1.1. The WHERE Clause
        2. C.1.1.2. ORDER BY
        3. C.1.1.3. The GROUP BY Clause
        4. C.1.1.4. The HAVING Clause
      2. C.1.2. Beyond Inner Joins
        1. C.1.2.1. OUTER Joins
        2. C.1.2.2. FULL Joins
        3. C.1.2.3. CROSS JOIN
    2. C.2. The INSERT Statement
      1. C.2.1. The INSERT INTO ... SELECT Statement
    3. C.3. The UPDATE Statement
    4. C.4. The DELETE Statement
    5. C.5. Exploring Alternative Syntax for Joins
      1. C.5.1. An Alternative Inner Join
      2. C.5.2. An Alternative Outer Join
      3. C.5.3. An Alternative CROSS JOIN
    6. C.6. UNION
      1. C.6.1. Subqueries and Derived Tables
        1. C.6.1.1. Nested Subqueries
        2. C.6.1.2. Correlated Subqueries
        3. C.6.1.3. Derived Tables
    7. C.7. Summary

Product information

  • Title: Professional Microsoft® SQL Server® 2008 Programming
  • Author(s):
  • Release date: April 2009
  • Publisher(s): Wrox
  • ISBN: 9780470257029