Beginning Microsoft® SQL Server® 2008 Programming

Beginning Microsoft® SQL Server® 2008 Programming

by Robert Vieira
Released January 2009
Publisher(s): Wrox
ISBN: 9780470257012

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

  • This comprehensive introduction to SQL Server begins with an overview of database design basics and the SQL query language along with an in-depth look at SQL Server itself

  • Progresses on to a clear explanation of how to implement fundamental concepts with the new 2008 version of SQL Server

  • Discusses creating and changing tables, managing keys, writing scripts, working with stored procedures, programming with XML, using SQL Server Reporting and Integration Services, and more

  • Features updated and new material, including new examples using Microsoft's AdventureWorks sample database

Table of contents

  1. Copyright
  2. About the Author
  3. Credits
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. What This Book Covers
    3. How This Book Is Structured
    4. What You Need to Use This Book
    5. Conventions
        1. Try It Out
        2. How It Works
    6. Source Code
    7. Errata
    8. p2p.wrox.com
    9. www.professionalsql.com
  6. 1. RDBMS Basics: What Makes Up a SQL Server Database?
    1. 1.1. An Overview of Database Objects
      1. 1.1.1. The Database Object
        1. 1.1.1.1. The master Database
        2. 1.1.1.2. The model Database
        3. 1.1.1.3. The msdb Database
        4. 1.1.1.4. The tempdb Database
        5. 1.1.1.5. ReportServer
        6. 1.1.1.6. ReportServerTempDB
        7. 1.1.1.7. AdventureWorks2008
        8. 1.1.1.8. AdventureWorksLT2008
        9. 1.1.1.9. AdventureWorksDW2008
        10. 1.1.1.10. The pubs Database
        11. 1.1.1.11. The Northwind Database
      2. 1.1.2. The Transaction Log
      3. 1.1.3. The Most Basic Database Object: Table
        1. 1.1.3.1. Indexes
        2. 1.1.3.2. Triggers
        3. 1.1.3.3. Constraints
      4. 1.1.4. Filegroups
      5. 1.1.5. Diagrams
      6. 1.1.6. Views
      7. 1.1.7. Stored Procedures
      8. 1.1.8. User-Defined Functions
      9. 1.1.9. Users and Roles
      10. 1.1.10. Rules
      11. 1.1.11. Defaults
      12. 1.1.12. User-Defined Data Types
      13. 1.1.13. Full-Text Catalogs
    2. 1.2. SQL Server Data Types
      1. 1.2.1. NULL Data
    3. 1.3. SQL Server Identifiers for Objects
      1. 1.3.1. What Gets Named?
      2. 1.3.2. Rules for Naming
    4. 1.4. Summary
  7. 2. Tools of the Trade
    1. 2.1. Books Online
    2. 2.2. 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.4. On to the Client
    3. 2.3. SQL Server Management Studio
      1. 2.3.1. Getting Started with the Management Studio
        1. 2.3.1.1. Server Type
        2. 2.3.1.2. Server Name
        3. 2.3.1.3. Authentication
          1. 2.3.1.3.1. Windows Authentication
          2. 2.3.1.3.2. SQL Server Authentication
      2. 2.3.2. Query Window
        1. 2.3.2.1. Getting Started
        2. 2.3.2.2. Results to Text
        3. 2.3.2.3. Results to 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 Available Databases Combo Box
        8. 2.3.2.8. The Object Explorer
    4. 2.4. SQL Server Integration Services (SSIS)
    5. 2.5. Bulk Copy Program (bcp)
    6. 2.6. SQL Server Profiler
    7. 2.7. sqlcmd
    8. 2.8. PowerShell
    9. 2.9. Summary
  8. 3. The Foundation Statements of T-SQL
    1. 3.1. Getting Started with a Basic SELECT Statement
      1. 3.1.1. The SELECT Statement and FROM Clause
      2. 3.1.2. The WHERE Clause
      3. 3.1.3. ORDER BY
      4. 3.1.4. Aggregating Data Using the GROUP BY Clause
        1. 3.1.4.1. Aggregates
          1. 3.1.4.1.1. AVG
          2. 3.1.4.1.2. MIN/MAX
          3. 3.1.4.1.3. COUNT(Expression|*)
      5. 3.1.5. Placing Conditions on Groups with the HAVING Clause
      6. 3.1.6. Outputting XML Using the FOR XML Clause
      7. 3.1.7. Making Use of Hints Using the OPTION Clause
      8. 3.1.8. The DISTINCT and ALL Predicates
    2. 3.2. Adding Data with the INSERT Statement
      1. 3.2.1. Multirow Inserts
      2. 3.2.2. The INSERT INTO . . . SELECT Statement
    3. 3.3. Changing What You've Got with the UPDATE Statement
    4. 3.4. The DELETE Statement
    5. 3.5. Summary
    6. 3.6. Exercises
  9. 4. JOINs
    1. 4.1. JOINs
    2. 4.2. INNER JOINs
      1. 4.2.1. How an INNER JOIN Is Like a WHERE Clause
    3. 4.3. OUTER JOINs
      1. 4.3.1. The Simple OUTER JOIN
        1. 4.3.1.1. Finding Orphan or Non-Matching Records
      2. 4.3.2. Dealing with More Complex OUTER JOINs
    4. 4.4. Seeing Both Sides with FULL JOINs
    5. 4.5. CROSS JOINs
    6. 4.6. Exploring Alternative Syntax for Joins
      1. 4.6.1. An Alternative INNER JOIN
      2. 4.6.2. An Alternative OUTER JOIN
      3. 4.6.3. An Alternative CROSS JOIN
    7. 4.7. The UNION
    8. 4.8. Summary
    9. 4.9. Exercises
  10. 5. Creating and Altering Tables
    1. 5.1. Object Names in SQL Server
      1. 5.1.1. Schema Name (aka Ownership)
        1. 5.1.1.1. A Little More About Schemas
        2. 5.1.1.2. The Default Schema: dbo
      2. 5.1.2. The Database Name
      3. 5.1.3. Naming by Server
      4. 5.1.4. Reviewing the Defaults
    2. 5.2. The CREATE Statement
      1. 5.2.1. CREATE DATABASE
        1. 5.2.1.1. ON
        2. 5.2.1.2. NAME
        3. 5.2.1.3. FILENAME
        4. 5.2.1.4. SIZE
        5. 5.2.1.5. MAXSIZE
        6. 5.2.1.6. FILEGROWTH
        7. 5.2.1.7. LOG ON
        8. 5.2.1.8. COLLATE
        9. 5.2.1.9. FOR ATTACH
        10. 5.2.1.10. WITH DB CHAINING ON|OFF
        11. 5.2.1.11. TRUSTWORTHY
        12. 5.2.1.12. Building a Database
      2. 5.2.2. CREATE TABLE
        1. 5.2.2.1. Table and Column Names
        2. 5.2.2.2. Data Types
        3. 5.2.2.3. DEFAULT
        4. 5.2.2.4. IDENTITY
        5. 5.2.2.5. NOT FOR REPLICATION
        6. 5.2.2.6. ROWGUIDCOL
        7. 5.2.2.7. COLLATE
        8. 5.2.2.8. NULL/NOT NULL
        9. 5.2.2.9. Column Constraints
        10. 5.2.2.10. Computed Columns
        11. 5.2.2.11. Table Constraints
        12. 5.2.2.12. ON
        13. 5.2.2.13. TEXTIMAGE_ON
        14. 5.2.2.14. Creating a Table
    3. 5.3. The ALTER Statement
      1. 5.3.1. ALTER DATABASE
        1. 5.3.1.1. Option and Termination Specs
      2. 5.3.2. ALTER TABLE
    4. 5.4. The DROP Statement
    5. 5.5. Using the GUI Tool
      1. 5.5.1. Creating a Database Using the Management Studio
      2. 5.5.2. Backing into the Code: The Basics of Creating Scripts with the Management Studio
    6. 5.6. Summary
    7. 5.7. Exercises
  11. 6. Constraints
    1. 6.1. Types of Constraints
      1. 6.1.1. Domain Constraints
      2. 6.1.2. Entity Constraints
      3. 6.1.3. Referential Integrity Constraints
    2. 6.2. Constraint Naming
    3. 6.3. Key Constraints
      1. 6.3.1. PRIMARY KEY Constraints
        1. 6.3.1.1. Creating the Primary Key at Table Creation
        2. 6.3.1.2. Creating a Primary Key on an Existing Table
      2. 6.3.2. FOREIGN KEY Constraints
        1. 6.3.2.1. Adding a Foreign Key to an Existing Table
        2. 6.3.2.2. Making a Table Self-Referencing
        3. 6.3.2.3. Cascading Actions
          1. 6.3.2.3.1. Those Other CASCADE Actions ...
        4. 6.3.2.4. Other Things to Think About with Foreign Keys
          1. 6.3.2.4.1. What Makes Values in Foreign Keys Required vs. Optional
          2. 6.3.2.4.2. How Foreign Keys Are Bi-Directional
      3. 6.3.3. UNIQUE Constraints
        1. 6.3.3.1. Creating UNIQUE Constraints on Existing Tables
    4. 6.4. CHECK Constraints
    5. 6.5. DEFAULT Constraints
      1. 6.5.1. Defining a DEFAULT Constraint in Your CREATE TABLE Statement
      2. 6.5.2. Adding a DEFAULT Constraint to an Existing Table
    6. 6.6. Disabling Constraints
      1. 6.6.1. Ignoring Bad Data When You Create the Constraint
      2. 6.6.2. Temporarily Disabling an Existing Constraint
    7. 6.7. Rules and Defaults — Cousins of Constraints
      1. 6.7.1. Rules
        1. 6.7.1.1. Dropping Rules
      2. 6.7.2. Defaults
        1. 6.7.2.1. Dropping Defaults
      3. 6.7.3. Determining Which Tables and Data Types Use a Given Rule or Default
    8. 6.8. Triggers for Data Integrity
    9. 6.9. Choosing What to Use
    10. 6.10. Summary
  12. 7. Adding More to Our Queries
    1. 7.1. What Is a Subquery?
      1. 7.1.1. Building a Nested Subquery
        1. 7.1.1.1. Nested Queries Using Single-Value SELECT Statements
        2. 7.1.1.2. Nested Queries Using Subqueries That Return Multiple Values
        3. 7.1.1.3. Using a Nested SELECT to Find Orphaned Records
    2. 7.2. Correlated Subqueries
      1. 7.2.1. How Correlated Subqueries Work
      2. 7.2.2. Correlated Subqueries in the WHERE Clause
        1. 7.2.2.1. Correlated Subqueries in the SELECT List
      3. 7.2.3. Dealing with NULL Data — the ISNULL Function
    3. 7.3. Derived Tables
    4. 7.4. The EXISTS Operator
      1. 7.4.1. Using EXISTS in Other Ways
    5. 7.5. Mixing Data Types: CAST and CONVERT
    6. 7.6. The MERGE Command
      1. 7.6.1. A Brief Look at BY TARGET versus BY SOURCE
      2. 7.6.2. The Output Clause
    7. 7.7. Performance Considerations
      1. 7.7.1. JOINs versus Subqueries versus ?
    8. 7.8. Summary
    9. 7.9. Exercises
  13. 8. Being Normal: Normalization and Other Basic Design Issues
    1. 8.1. Tables
    2. 8.2. Keeping Your Data "Normal"
      1. 8.2.1. Before the Beginning
      2. 8.2.2. The First Normal Form
      3. 8.2.3. The Second Normal Form
      4. 8.2.4. The Third Normal Form
      5. 8.2.5. Other Normal Forms
    3. 8.3. Relationships
      1. 8.3.1. One-to-One
        1. 8.3.1.1. Zero or One-to-One
      2. 8.3.2. One-to-One or Many
        1. 8.3.2.1. One-to-Zero, One, or Many
      3. 8.3.3. Many-to-Many
    4. 8.4. Diagramming
      1. 8.4.1. Tables
      2. 8.4.2. Adding and Deleting Tables
        1. 8.4.2.1. Editing Table Properties and Objects That Belong to the Table
        2. 8.4.2.2. Properties Window
        3. 8.4.2.3. Relationships
        4. 8.4.2.4. Indexes/Keys
        5. 8.4.2.5. Check Constraints
      3. 8.4.3. Relationships
        1. 8.4.3.1. Adding Relationships in the Diagramming Tool
    5. 8.5. De-Normalization
    6. 8.6. Beyond Normalization
      1. 8.6.1. Keep It Simple
      2. 8.6.2. Choosing Data Types
      3. 8.6.3. Err on the Side of Storing Things
    7. 8.7. Drawing Up a Quick Example
      1. 8.7.1. Creating the Database
      2. 8.7.2. Adding the Diagram and Our Initial Tables
      3. 8.7.3. Adding the Relationships
      4. 8.7.4. Adding Some Constraints
    8. 8.8. Summary
    9. 8.9. Exercises
  14. 9. SQL Server Storage and Index Structures
    1. 9.1. SQL Server Storage
      1. 9.1.1. The Database
      2. 9.1.2. The Extent
      3. 9.1.3. The Page
        1. 9.1.3.1. Page Splits
      4. 9.1.4. Rows
      5. 9.1.5. Sparse Columns
    2. 9.2. Understanding Indexes
      1. 9.2.1. B-Trees
        1. 9.2.1.1. Page Splits — A First Look
      2. 9.2.2. How Data Is Accessed in SQL Server
        1. 9.2.2.1. Use of Table Scans
        2. 9.2.2.2. Use of Indexes
        3. 9.2.2.3. Index Types and Index Navigation
          1. 9.2.2.3.1. Clustered Tables
          2. 9.2.2.3.2. Heaps
        4. 9.2.2.4. Clustered Indexes
          1. 9.2.2.4.1. Navigating the Tree
        5. 9.2.2.5. Non-Clustered Indexes on a Heap
        6. 9.2.2.6. Non-Clustered Indexes on a Clustered Table
    3. 9.3. Creating, Altering, and Dropping Indexes
      1. 9.3.1. The CREATE INDEX Statement
        1. 9.3.1.1. ASC/DESC
        2. 9.3.1.2. INCLUDE
        3. 9.3.1.3. WITH
        4. 9.3.1.4. PAD_INDEX
        5. 9.3.1.5. FILLFACTOR
        6. 9.3.1.6. IGNORE_DUP_KEY
        7. 9.3.1.7. DROP_EXISTING
        8. 9.3.1.8. STATISTICS_NORECOMPUTE
        9. 9.3.1.9. SORT_IN_TEMPDB
        10. 9.3.1.10. ONLINE
        11. 9.3.1.11. ALLOW ROW/PAGE LOCKS
        12. 9.3.1.12. MAXDOP
        13. 9.3.1.13. ON
      2. 9.3.2. Creating XML Indexes
        1. 9.3.2.1. The Primary XML Index
        2. 9.3.2.2. Secondary XML Indexes
      3. 9.3.3. Implied Indexes Created with Constraints
      4. 9.3.4. Creating Indexes on Sparse and Geospatial Columns
    4. 9.4. Choosing Wisely: Deciding What Index Goes Where and When
      1. 9.4.1. Selectivity
      2. 9.4.2. Watching Costs: When Less Is More
      3. 9.4.3. Choosing That Clustered Index
        1. 9.4.3.1. The Pros
        2. 9.4.3.2. The Cons
      4. 9.4.4. Column Order Matters
      5. 9.4.5. ALTER INDEX
        1. 9.4.5.1. Index Name
        2. 9.4.5.2. Table or View Name
        3. 9.4.5.3. REBUILD
        4. 9.4.5.4. DISABLE
        5. 9.4.5.5. REORGANIZE
      6. 9.4.6. Dropping Indexes
      7. 9.4.7. Take a Hint from the Query Plan
      8. 9.4.8. Use the Database Engine Tuning Advisor
    5. 9.5. Maintaining Your Indexes
      1. 9.5.1. Fragmentation
      2. 9.5.2. Identifying Fragmentation vs. Likelihood of Page Splits
        1. 9.5.2.1. ALTER INDEX and FILLFACTOR
    6. 9.6. Summary
    7. 9.7. Exercises
  15. 10. Views
    1. 10.1. Simple Views
      1. 10.1.1. Views as Filters
    2. 10.2. More Complex Views
      1. 10.2.1.
        1. 10.2.1.1. The DATEADD and CAST Functions
      2. 10.2.2. Using a View to Change Data — Before INSTEAD OF Triggers
        1. 10.2.2.1. Dealing with Changes in Views with Joined Data
        2. 10.2.2.2. Required Fields Must Appear in the View or Have Default Value
        3. 10.2.2.3. Limit What's Inserted into Views — WITH CHECK OPTION
    3. 10.3. Editing Views with T-SQL
    4. 10.4. Dropping Views
    5. 10.5. Creating and Editing Views in the Management Studio
      1. 10.5.1. Editing Views in the Management Studio
    6. 10.6. Auditing: Displaying Existing Code
    7. 10.7. Protecting Code: Encrypting Views
    8. 10.8. About Schema Binding
    9. 10.9. Making Your View Look Like a Table with VIEW_METADATA
    10. 10.10. Indexed (Materialized) Views
    11. 10.11. Summary
    12. 10.12. Exercises
  16. 11. Writing Scripts and Batches
    1. 11.1. Script Basics
      1. 11.1.1. The USE Statement
      2. 11.1.2. Declaring Variables
        1. 11.1.2.1. Setting the Value in Your Variables
          1. 11.1.2.1.1. Setting Variables Using SET
          2. 11.1.2.1.2. Setting Variables Using SELECT
        2. 11.1.2.2. Reviewing System Functions
      3. 11.1.3. Using @@IDENTITY
      4. 11.1.4. Using @@ROWCOUNT
    2. 11.2. Batches
      1. 11.2.1.
        1. 11.2.1.1. A Line to Itself
        2. 11.2.1.2. Each Batch Is Sent to the Server Separately
        3. 11.2.1.3. GO Is Not a T-SQL Command
      2. 11.2.2. Errors in Batches
      3. 11.2.3. When to Use Batches
        1. 11.2.3.1. Statements That Require Their Own Batch
        2. 11.2.3.2. Using Batches to Establish Precedence
    3. 11.3. sqlcmd
    4. 11.4. Dynamic SQL: Generating Your Code On the Fly with the EXEC Command
      1. 11.4.1. The Gotchas of EXEC
        1. 11.4.1.1. The Scope of EXEC
          1. 11.4.1.1.1. A Small Exception to the Rule
        2. 11.4.1.2. Security Contexts and EXEC
        3. 11.4.1.3. Use of Functions in Concatenation and EXEC
        4. 11.4.1.4. EXEC and UDFs
    5. 11.5. Control-of-Flow Statements
      1. 11.5.1. The IF ... ELSE Statement
        1. 11.5.1.1. The ELSE Clause
        2. 11.5.1.2. Grouping Code into Blocks
      2. 11.5.2. The CASE Statement
        1. 11.5.2.1. A Simple CASE
        2. 11.5.2.2. A Searched CASE
      3. 11.5.3. Looping with the WHILE Statement
      4. 11.5.4. The WAITFOR Statement
        1. 11.5.4.1. The DELAY Parameter
        2. 11.5.4.2. The TIME Parameter
      5. 11.5.5. TRY/CATCH Blocks
    6. 11.6. Summary
    7. 11.7. Exercises
  17. 12. Stored Procedures
    1. 12.1. Creating the Sproc: Basic Syntax
      1. 12.1.1. An Example of a Basic Sproc
    2. 12.2. Changing Stored Procedures with ALTER
    3. 12.3. Dropping Sprocs
    4. 12.4. Parameterization
      1. 12.4.1. Declaring Parameters
        1. 12.4.1.1. Supplying Default Values
        2. 12.4.1.2. Creating Output Parameters
    5. 12.5. Confirming Success or Failure with Return Values
      1. 12.5.1. How to Use RETURN
    6. 12.6. More On Dealing with Errors
      1. 12.6.1. The Way We Were ...
        1. 12.6.1.1. Handling Inline Errors
        2. 12.6.1.2. Making Use of @@ERROR
        3. 12.6.1.3. Using @@ERROR in a Sproc
      2. 12.6.2. Handling Errors Before They Happen
      3. 12.6.3. Manually Raising Errors
        1. 12.6.3.1. Message ID/Message String
        2. 12.6.3.2. Severity
        3. 12.6.3.3. State
        4. 12.6.3.4. Error Arguments
        5. 12.6.3.5. WITH <option>
          1. 12.6.3.5.1. WITH LOG
          2. 12.6.3.5.2. WITH SETERROR
          3. 12.6.3.5.3. WITH NOWAIT
      4. 12.6.4. Adding Your Own Custom Error Messages
        1. 12.6.4.1. @lang
        2. 12.6.4.2. @with_log
        3. 12.6.4.3. @replace
        4. 12.6.4.4. Using sp_addmessage
        5. 12.6.4.5. Removing an Existing Custom Message
    7. 12.7. What a Sproc Offers
      1. 12.7.1. Creating Callable Processes
      2. 12.7.2. Using Sprocs for Security
      3. 12.7.3. Sprocs and Performance
        1. 12.7.3.1. When a Good Sproc Goes Bad
          1. 12.7.3.1.1. Using the WITH RECOMPILE Option
    8. 12.8. Extended Stored Procedures (XPs)
    9. 12.9. A Brief Look at Recursion
    10. 12.10. Debugging
      1. 12.10.1. Starting the Debugger
      2. 12.10.2. Parts of the Debugger
        1. 12.10.2.1. The Locals Window
        2. 12.10.2.2. The Watch Window
        3. 12.10.2.3. The Call Stack Window
        4. 12.10.2.4. The Output Window
        5. 12.10.2.5. The Command Window
      3. 12.10.3. Using the Debugger Once It's Started
    11. 12.11. .NET Assemblies
    12. 12.12. Summary
  18. 13. User-Defined Functions
    1. 13.1. What a UDF Is
    2. 13.2. UDFs Returning a Scalar Value
    3. 13.3. UDFs That Return a Table
      1. 13.3.1. Understanding Determinism
    4. 13.4. Debugging User-Defined Functions
    5. 13.5. .NET in a Database World
    6. 13.6. Summary
    7. 13.7. Exercise
  19. 14. Transactions and Locks
    1. 14.1. Transactions
      1. 14.1.1. BEGIN TRAN
      2. 14.1.2. COMMIT TRAN
      3. 14.1.3. ROLLBACK TRAN
      4. 14.1.4. SAVE TRAN
    2. 14.2. How the SQL Server Log Works
      1. 14.2.1. Failure and Recovery
        1. 14.2.1.1. Transaction 1
        2. 14.2.1.2. Transaction 2
        3. 14.2.1.3. Transaction 3
        4. 14.2.1.4. Transaction 4
        5. 14.2.1.5. Transaction 5
      2. 14.2.2. Implicit Transactions
    3. 14.3. Locks and Concurrency
      1. 14.3.1. What Problems Can Be Prevented by Locks
        1. 14.3.1.1. Dirty Reads
        2. 14.3.1.2. Non-Repeatable Reads
        3. 14.3.1.3. Phantoms
        4. 14.3.1.4. Lost Updates
      2. 14.3.2. Lockable Resources
      3. 14.3.3. Lock Escalation and Lock Effects on Performance
      4. 14.3.4. Lock Modes
        1. 14.3.4.1. Shared Locks
        2. 14.3.4.2. Exclusive Locks
        3. 14.3.4.3. Update Locks
        4. 14.3.4.4. Intent Locks
        5. 14.3.4.5. Schema Locks
        6. 14.3.4.6. Bulk Update Locks
      5. 14.3.5. Lock Compatibility
      6. 14.3.6. Specifying a Specific Lock Type — Optimizer Hints
        1. 14.3.6.1. Determining Locks Using the Management Studio
    4. 14.4. Setting the Isolation Level
      1. 14.4.1. READ COMMITTED
      2. 14.4.2. READ UNCOMMITTED
      3. 14.4.3. REPEATABLE READ
      4. 14.4.4. SERIALIZABLE
      5. 14.4.5. SNAPSHOT
    5. 14.5. Dealing with Deadlocks (aka "a 1205")
      1. 14.5.1. How SQL Server Figures Out There's a Deadlock
      2. 14.5.2. How Deadlock Victims Are Chosen
      3. 14.5.3. Avoiding Deadlocks
        1. 14.5.3.1. Use Objects in the Same Order
        2. 14.5.3.2. Keeping Transactions as Short as Possible
        3. 14.5.3.3. Use the Lowest Transaction Isolation Level Possible
        4. 14.5.3.4. No Open-Ended Transactions
    6. 14.6. Summary
  20. 15. Triggers
    1. 15.1. What Is a Trigger?
      1. 15.1.1. ON
      2. 15.1.2. WITH ENCRYPTION
      3. 15.1.3. The FOR|AFTER vs. the INSTEAD OF Clause
        1. 15.1.3.1. FOR|AFTER
          1. 15.1.3.1.1. INSERT Trigger
          2. 15.1.3.1.2. DELETE Trigger
          3. 15.1.3.1.3. UPDATE Trigger
      4. 15.1.4. WITH APPEND
      5. 15.1.5. NOT FOR REPLICATION
      6. 15.1.6. AS
    2. 15.2. Using Triggers for Data Integrity Rules
      1. 15.2.1. Dealing with Requirements Sourced from Other Tables
      2. 15.2.2. Using Triggers to Check the Delta of an Update
      3. 15.2.3. Using Triggers for Custom Error Messages
    3. 15.3. Other Common Uses for Triggers
    4. 15.4. Other Trigger Issues
      1. 15.4.1. Triggers Can Be Nested
      2. 15.4.2. Triggers Can Be Recursive
      3. 15.4.3. Triggers Don't Prevent Architectural Changes
      4. 15.4.4. Triggers Can Be Turned Off Without Being Removed
      5. 15.4.5. Trigger Firing Order
        1. 15.4.5.1. Controlling Firing Order for Logic Reasons
        2. 15.4.5.2. Controlling Firing Order for Performance Reasons
    5. 15.5. INSTEAD OF Triggers
    6. 15.6. Performance Considerations
      1. 15.6.1. Triggers Are Reactive Rather Than Proactive
      2. 15.6.2. Triggers Don't Have Concurrency Issues with the Process That Fires Them
      3. 15.6.3. Using IF UPDATE() and COLUMNS_UPDATED
        1. 15.6.3.1. The UPDATE() Function
        2. 15.6.3.2. The COLUMNS_UPDATED() Function
      4. 15.6.4. Keep It Short and Sweet
      5. 15.6.5. Don't Forget Triggers When Choosing Indexes
      6. 15.6.6. Try Not to Roll Back Within Triggers
    7. 15.7. Dropping Triggers
    8. 15.8. Debugging Triggers
    9. 15.9. Summary
  21. 16. A Brief XML Primer
    1. 16.1. XML Basics
      1. 16.1.1. Parts of an XML Document
        1. 16.1.1.1. The Document
        2. 16.1.1.2. Declaration
        3. 16.1.1.3. Elements
        4. 16.1.1.4. Nodes
          1. 16.1.1.4.1. The "Root" Node
        5. 16.1.1.5. Attributes
        6. 16.1.1.6. No Defects—Being Well Formed
        7. 16.1.1.7. An XML Example
        8. 16.1.1.8. Determining Elements vs. Attributes
      2. 16.1.2. Namespaces
      3. 16.1.3. Element Content
      4. 16.1.4. Being Valid vs. Being Well Formed—Schemas and DTDs
    2. 16.2. What SQL Server Brings to the Party
      1. 16.2.1. Defining a Column as Being of XML Type
      2. 16.2.2. XML Schema Collections
      3. 16.2.3. Creating, Altering, and Dropping XML Schema Collections
        1. 16.2.3.1. CREATE XML SCHEMA COLLECTION
        2. 16.2.3.2. ALTER XML SCHEMA COLLECTION
        3. 16.2.3.3. DROP XML SCHEMA COLLECTION
      4. 16.2.4. XML Data Type Methods
        1. 16.2.4.1. .query (SQL Server's Implementation of XQuery)
        2. 16.2.4.2. .value
        3. 16.2.4.3. .modify
        4. 16.2.4.4. .nodes
        5. 16.2.4.5. .exist
      5. 16.2.5. Enforcing Constraints Beyond the Schema Collection
      6. 16.2.6. Retrieving Relational Data in XML Format
        1. 16.2.6.1. The FOR XML Clause
      7. 16.2.7. RAW
      8. 16.2.8. AUTO
      9. 16.2.9. EXPLICIT
      10. 16.2.10. PATH
        1. 16.2.10.1. PATH 101
          1. 16.2.10.1.1. Unnamed Columns
          2. 16.2.10.1.2. Named Columns
      11. 16.2.11. OPENXML
    3. 16.3. A Brief Word on XSLT
    4. 16.4. Summary
  22. 17. Reporting for Duty, Sir! A Look At Reporting Services
    1. 17.1. Reporting Services 101
    2. 17.2. Building Simple Report Models
      1. 17.2.1. Data Source Views
        1. 17.2.1.1. Manipulating Relationships Between Objects In Data Source Views
      2. 17.2.2. Building the Data Model
        1. 17.2.2.1. Deploying Our Model
      3. 17.2.3. Report Creation
        1. 17.2.3.1. A Few Last Words on Report Models
    3. 17.3. Report Server Projects
      1. 17.3.1. Deploying the Report
    4. 17.4. Summary
  23. 18. Getting Integrated with Integration Services
    1. 18.1. Understanding the Problem
    2. 18.2. Using the Import/Export Wizard to Generate Basic Packages
    3. 18.3. Examining Package Basics
      1. 18.3.1. Connection Managers
      2. 18.3.2. The Package Editor Pane
        1. 18.3.2.1. Control Flow
        2. 18.3.2.2. Data Flow
        3. 18.3.2.3. Event Handlers
        4. 18.3.2.4. Package Explorer
    4. 18.4. Executing Packages
      1. 18.4.1. Executing a Package Inside the Development Studio
      2. 18.4.2. Using the Execute Package Utility
        1. 18.4.2.1. General
        2. 18.4.2.2. Configurations
        3. 18.4.2.3. Command Files
        4. 18.4.2.4. Connection Managers
        5. 18.4.2.5. Execution Options
        6. 18.4.2.6. Reporting
        7. 18.4.2.7. Logging
        8. 18.4.2.8. Set Values
        9. 18.4.2.9. Verification
        10. 18.4.2.10. Command Line
        11. 18.4.2.11. Executing the Package
      3. 18.4.3. Executing Using the SQL Server Agent
      4. 18.4.4. Executing a Package from Within a Program
    5. 18.5. A Final Word on Packages
    6. 18.6. Summary
  24. 19. Playing Administrator
    1. 19.1. Scheduling Jobs
      1. 19.1.1. Creating an Operator
        1. 19.1.1.1. Creating an Operator Using Management Studio
      2. 19.1.2. Creating Jobs and Tasks
        1. 19.1.2.1. Creating Jobs and Tasks Using Management Studio
    2. 19.2. Backup and Recovery
      1. 19.2.1. Creating a Backup: a.k.a. "A Dump"
        1. 19.2.1.1. Backup Type
        2. 19.2.1.2. Backup Set
        3. 19.2.1.3. Destination
        4. 19.2.1.4. Options
        5. 19.2.1.5. Schedule
      2. 19.2.2. Recovery Models
      3. 19.2.3. Recovery
        1. 19.2.3.1. Restoring to a Different Location
        2. 19.2.3.2. Recovery Status
    3. 19.3. Index Maintenance
      1. 19.3.1. ALTER INDEX
        1. 19.3.1.1. Index Name
        2. 19.3.1.2. Table or View Name
        3. 19.3.1.3. REBUILD
        4. 19.3.1.4. DISABLE
        5. 19.3.1.5. REORGANIZE
        6. 19.3.1.6. Archiving Data
    4. 19.4. Policy Based Management
    5. 19.5. Summary
    6. 19.6. Exercises
  25. 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. Hierarchy Functions
      1. A.7.1. GetAncestor
      2. A.7.2. GetDescendant
      3. A.7.3. GetLevel
      4. A.7.4. GetRoot
      5. A.7.5. IsDescendantOf
      6. A.7.6. Parse
      7. A.7.7. GetReparentedValue
      8. A.7.8. ToString
    8. A.8. Mathematical Functions
      1. A.8.1. ABS
      2. A.8.2. ACOS
      3. A.8.3. ASIN
      4. A.8.4. ATAN
      5. A.8.5. ATN2
      6. A.8.6. CEILING
      7. A.8.7. COS
      8. A.8.8. COT
      9. A.8.9. DEGREES
      10. A.8.10. EXP
      11. A.8.11. FLOOR
      12. A.8.12. LOG
      13. A.8.13. LOG10
      14. A.8.14. PI
      15. A.8.15. POWER
      16. A.8.16. RADIANS
      17. A.8.17. RAND
      18. A.8.18. ROUND
      19. A.8.19. SIGN
      20. A.8.20. SIN
      21. A.8.21. SQRT
      22. A.8.22. SQUARE
      23. A.8.23. TAN
    9. A.9. Basic Metadata Functions
      1. A.9.1. COL_LENGTH
      2. A.9.2. COL_NAME
      3. A.9.3. COLUMNPROPERTY
      4. A.9.4. DATABASEPROPERTY
      5. A.9.5. DATABASEPROPERTYEX
      6. A.9.6. DB_ID
      7. A.9.7. DB_NAME
      8. A.9.8. FILE_ID
      9. A.9.9. FILE_NAME
      10. A.9.10. FILEGROUP_ID
      11. A.9.11. FILEGROUP_NAME
      12. A.9.12. FILEGROUPPROPERTY
      13. A.9.13. FILEPROPERTY
      14. A.9.14. FULLTEXTCATALOGPROPERTY
      15. A.9.15. FULLTEXTSERVICEPROPERTY
      16. A.9.16. INDEX_COL
      17. A.9.17. INDEXKEY_PROPERTY
      18. A.9.18. INDEXPROPERTY
      19. A.9.19. OBJECT_ID
      20. A.9.20. OBJECT_NAME
      21. A.9.21. OBJECTPROPERTY
      22. A.9.22. OBJECTPROPERTYEX
      23. A.9.23. @@PROCID
      24. A.9.24. SCHEMA_ID
      25. A.9.25. SCHEMA_NAME
      26. A.9.26. SQL_VARIANT_PROPERTY
      27. A.9.27. TYPEPROPERTY
    10. A.10. Rowset Functions
      1. A.10.1. CHANGETABLE
      2. A.10.2. CONTAINSTABLE
      3. A.10.3. FREETEXTTABLE
      4. A.10.4. OPENDATASOURCE
      5. A.10.5. OPENQUERY
      6. A.10.6. OPENROWSET
      7. A.10.7. OPENXML
    11. A.11. Security Functions
      1. A.11.1. HAS_DBACCESS
      2. A.11.2. IS_MEMBER
      3. A.11.3. IS_SRVROLEMEMBER
      4. A.11.4. SUSER_ID
      5. A.11.5. SUSER_NAME
      6. A.11.6. SUSER_SID
      7. A.11.7. SUSER_SNAME
      8. A.11.8. USER
      9. A.11.9. USER_ID
      10. A.11.10. USER_NAME
    12. A.12. String Functions
      1. A.12.1. ASCII
      2. A.12.2. CHAR
      3. A.12.3. CHARINDEX
      4. A.12.4. DIFFERENCE
      5. A.12.5. LEFT
      6. A.12.6. LEN
      7. A.12.7. LOWER
      8. A.12.8. LTRIM
      9. A.12.9. NCHAR
      10. A.12.10. PATINDEX
      11. A.12.11. QUOTENAME
      12. A.12.12. REPLACE
      13. A.12.13. REPLICATE
      14. A.12.14. REVERSE
      15. A.12.15. RIGHT
      16. A.12.16. RTRIM
      17. A.12.17. SOUNDEX
      18. A.12.18. SPACE
      19. A.12.19. STR
      20. A.12.20. STUFF
      21. A.12.21. SUBSTRING
      22. A.12.22. UNICODE
      23. A.12.23. UPPER
    13. A.13. System Functions
      1. A.13.1. APP_NAME
      2. A.13.2. CASE
        1. A.13.2.1. Simple CASE function:
        2. A.13.2.2. Searched CASE function:
      3. A.13.3. CAST and CONVERT
        1. A.13.3.1. Using CAST:
        2. A.13.3.2. Using CONVERT:
      4. A.13.4. COALESCE
      5. A.13.5. COLLATIONPROPERTY
      6. A.13.6. CURRENT_USER
      7. A.13.7. DATALENGTH
      8. A.13.8. @@ERROR
      9. A.13.9. FORMATMESSAGE
      10. A.13.10. GETANSINULL
      11. A.13.11. HOST_ID
      12. A.13.12. HOST_NAME
      13. A.13.13. IDENT_CURRENT
      14. A.13.14. IDENT_INCR
      15. A.13.15. IDENT_SEED
      16. A.13.16. @@IDENTITY
      17. A.13.17. IDENTITY
      18. A.13.18. ISNULL
      19. A.13.19. ISNUMERIC
      20. A.13.20. NEWID
      21. A.13.21. NULLIF
      22. A.13.22. PARSENAME
      23. A.13.23. PERMISSIONS
      24. A.13.24. @@ROWCOUNT
      25. A.13.25. ROWCOUNT_BIG
      26. A.13.26. SCOPE_IDENTITY
      27. A.13.27. SERVERPROPERTY
      28. A.13.28. SESSION_USER
      29. A.13.29. SESSIONPROPERTY
      30. A.13.30. STATS_DATE
      31. A.13.31. SYSTEM_USER
      32. A.13.32. USER_NAME
    14. A.14. Text and Image Functions
      1. A.14.1. TEXTPTR
      2. A.14.2. TEXTVALID
  26. B. Very Simple Connectivity Examples
    1. B.1. Some General Concepts
    2. B.2. Connectivity Examples
      1. B.2.1. Connecting in C#
        1. B.2.1.1. Returning a Data Set
        2. B.2.1.2. Executing Commands with No Data Set
      2. B.2.2. Connecting in VB.NET
        1. B.2.2.1. Returning a Data Set
        2. B.2.2.2. Executing Commands with No Data Set
    3. B.3. A Brief Word on LINQ

Product information

  • Title: Beginning Microsoft® SQL Server® 2008 Programming
  • Author(s): Robert Vieira
  • Release date: January 2009
  • Publisher(s): Wrox
  • ISBN: 9780470257012