Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008

by Dan Wood, Paul Turley
Released December 2008
Publisher(s): Wrox
ISBN: 9780470257036

Explore a preview version of Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008 right now.

O’Reilly members get unlimited access to live online training experiences, plus books, videos, and digital content from 200+ publishers.

Start your free trial

Book Description

If you have never programmed with T-SQL but have some background programming knowledge and experience, Beginning T-SQL with Microsoft SQL Server 2005 and 2006 will provide you with an overview of SQL Server query operations and tools used with T-SQL, Microsoft's implementation of the SQL database query language. Review basic query language commands and syntax, learn how to design and build applications, and understand how to optimize query performance. Improve your skills with the most up-to-date T-SQL guide, which provides hands-on examples and instructions to guide you through the process.

Table of Contents

  1. Copyright
  2. About the Authors
  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
    6. Source Code
    7. Errata
    8. p2p.wrox.com
  6. 1. Introducing T-SQL and Data Management Systems
    1. 1.1. T-SQL Language
      1. 1.1.1. Programming Language or Query Language?
      2. 1.1.2. What's New in SQL Server 2008
        1. 1.1.2.1. Kiss T-SQL Goodbye?
      3. 1.1.3. Database Management Systems
    2. 1.2. SQL Server as a Relational Database Management System
      1. 1.2.1. Tables
        1. 1.2.1.1. Primary Keys
        2. 1.2.1.2. Table Columns
        3. 1.2.1.3. Column Names
        4. 1.2.1.4. Data Types
        5. 1.2.1.5. Nullability
      2. 1.2.2. Relationships
      3. 1.2.3. RDBMS and Data Integrity
        1. 1.2.3.1. Connections
        2. 1.2.3.2. Transactions
        3. 1.2.3.3. Locks
          1. 1.2.3.3.1. Shared Locks
          2. 1.2.3.3.2. Update Locks
          3. 1.2.3.3.3. Exclusive Locks
          4. 1.2.3.3.4. Intent Locks
          5. 1.2.3.3.5. Schema Locks
          6. 1.2.3.3.6. Bulk Update Locks
          7. 1.2.3.3.7. Key Range Locks
    3. 1.3. SQL Server and Other Products
      1. 1.3.1. Microsoft SQL Server
      2. 1.3.2. Oracle
      3. 1.3.3. IBM DB2
      4. 1.3.4. Informix
      5. 1.3.5. Sybase SQLAnywhere
      6. 1.3.6. Microsoft Access (Jet)
      7. 1.3.7. MySQL
    4. 1.4. Summary
  7. 2. SQL Server Fundamentals
    1. 2.1. Who Uses SQL Server?
    2. 2.2. SQL Server Editions and Features
      1. 2.2.1. SQL Server Compact Edition
      2. 2.2.2. SQL Server Express Edition
      3. 2.2.3. SQL Server Workgroup Edition
      4. 2.2.4. SQL Server Standard Edition
      5. 2.2.5. SQL Server Enterprise Edition
      6. 2.2.6. Relational Database Engine
    3. 2.3. Semantics
      1. 2.3.1. Changing Terminology
        1. 2.3.1.1. Conceptual Design
        2. 2.3.1.2. Logical Design
        3. 2.3.1.3. Physical Design
      2. 2.3.2. Relationships
      3. 2.3.3. Primary Keys
      4. 2.3.4. Foreign Keys
    4. 2.4. Normalization Rules
      1. 2.4.1. First Normal Form
      2. 2.4.2. Second Normal Form
      3. 2.4.3. Third Normal Form
      4. 2.4.4. Boyce-Codd Normal Form, Fourth and Fifth Normal Form
      5. 2.4.5. Other Normal Forms
      6. 2.4.6. Transforming Information into Data
    5. 2.5. Applying Normalization Rules
      1. 2.5.1. Thinking Ahead
      2. 2.5.2. Multiple Associations
      3. 2.5.3. Multi-Valued Columns
      4. 2.5.4. To Normalize or To De-normalize?
      5. 2.5.5. Question Authority
    6. 2.6. The Mechanics of Query Processing
    7. 2.7. The AdventureWorks Databases
    8. 2.8. Summary
  8. 3. SQL Server Tools
    1. 3.1. Common SQL Server Tasks
    2. 3.2. SQL Server Management Studio
      1. 3.2.1. Tool Windows
        1. 3.2.1.1. Object Explorer
        2. 3.2.1.2. Code Editor
        3. 3.2.1.3. Solution Explorer
        4. 3.2.1.4. Properties Window
        5. 3.2.1.5. Registered Servers
        6. 3.2.1.6. Bookmark Window
        7. 3.2.1.7. Toolbox
        8. 3.2.1.8. Object Explorer Details
        9. 3.2.1.9. Web Browser
        10. 3.2.1.10. Template Explorer
        11. 3.2.1.11. Error List
      2. 3.2.2. Toolbars
        1. 3.2.2.1. Database Diagram Toolbar
        2. 3.2.2.2. Help Toolbar
        3. 3.2.2.3. Query Designer Toolbar
        4. 3.2.2.4. Source Control Toolbar
        5. 3.2.2.5. SQL Editor Toolbar
        6. 3.2.2.6. SQL Compact Edition Toolbar
        7. 3.2.2.7. SQL Server Analysis Services Editor Toolbar
        8. 3.2.2.8. Standard Toolbar
        9. 3.2.2.9. Table Designer Toolbar
        10. 3.2.2.10. Text Editor Toolbar
        11. 3.2.2.11. View Designer Toolbar
      3. 3.2.3. SQL Server Management Studio Configuration
        1. 3.2.3.1. Environment
        2. 3.2.3.2. Text Editor
        3. 3.2.3.3. Query Execution
        4. 3.2.3.4. Query Results
        5. 3.2.3.5. SQL Server Object Explorer
        6. 3.2.3.6. Designers
        7. 3.2.3.7. Source Control
    3. 3.3. SQL Server Business Intelligence Development Studio
    4. 3.4. SQL Server Profiler
    5. 3.5. Database Tuning Advisor
    6. 3.6. SQL Server Configuration Manager
    7. 3.7. Command-Line Tools
      1. 3.7.1. SQLCMD
    8. 3.8. Writing Queries
      1. 3.8.1. Scripting Options
      2. 3.8.2. Using the Graphical Query Designer
      3. 3.8.3. Using Templates
      4. 3.8.4. Using the Debug Feature
    9. 3.9. Summary
    10. 3.10. Exercises
      1. 3.10.1. Exercise 1
      2. 3.10.2. Exercise 2
      3. 3.10.3. Exercise 3
      4. 3.10.4. Exercise 4
  9. 4. Introducing the T-SQL Language
    1. 4.1. The Nature of SQL
    2. 4.2. Where to Begin?
    3. 4.3. Data Manipulation Language
      1. 4.3.1. Queries Have Layers
      2. 4.3.2. Set-Based Operations
      3. 4.3.3. Row-Based Operations
      4. 4.3.4. Query Syntax Basics
      5. 4.3.5. Naming Conventions
      6. 4.3.6. Object Delimiting
      7. 4.3.7. Commenting Script
      8. 4.3.8. Using Templates
      9. 4.3.9. Generating Script
      10. 4.3.10. Managing Script
      11. 4.3.11. Version Control
    4. 4.4. Data Definition Language
      1. 4.4.1. Creating a Table
      2. 4.4.2. Creating a View
      3. 4.4.3. Creating a Stored Procedure
      4. 4.4.4. Creating a Trigger
      5. 4.4.5. Creating a User-Defined Function
      6. 4.4.6. Scripting Practices
        1. 4.4.6.1. Altering Objects
        2. 4.4.6.2. Dropping Objects
    5. 4.5. Data Control Language
    6. 4.6. Summary
    7. 4.7. Exercises
      1. 4.7.1. Exercise 1
      2. 4.7.2. Exercise 2
  10. 5. Data Retrieval
    1. 5.1. Storage and Retrieval
    2. 5.2. The SELECT Statement
      1. 5.2.1. Choosing Columns
        1. 5.2.1.1. Schemas and Name Resolution
      2. 5.2.2. Column Aliasing
      3. 5.2.3. Calculated and Derived Columns
      4. 5.2.4. Filtering Rows
      5. 5.2.5. The WHERE Clause
        1. 5.2.5.1. Comparison Operators
        2. 5.2.5.2. Logical Comparisons
          1. 5.2.5.2.1. The AND Operator
          2. 5.2.5.2.2. The OR Operator
        3. 5.2.5.3. The NOT Operator
        4. 5.2.5.4. The Mighty Null
        5. 5.2.5.5. Extended Filtering Techniques
        6. 5.2.5.6. The BETWEEN Operator
        7. 5.2.5.7. The IN() Function
        8. 5.2.5.8. Operator Precedence
      6. 5.2.6. Using Parentheses
      7. 5.2.7. Sorting Results
      8. 5.2.8. Top Values
        1. 5.2.8.1. WITH TIES
        2. 5.2.8.2. Percent
    3. 5.3. Summary
    4. 5.4. Exercises
      1. 5.4.1. Exercise 1
      2. 5.4.2. Exercise 2
      3. 5.4.3. Exercise 3
      4. 5.4.4. Exercise 4
  11. 6. SQL Functions
    1. 6.1. The Anatomy of a Function
      1. 6.1.1. I'd Like to Have an Argument
      2. 6.1.2. Deterministic Functions
      3. 6.1.3. Using User Variables with Functions
        1. 6.1.3.1. Using SET to Assign Variables
        2. 6.1.3.2. Using SELECT to Assign Variables
      4. 6.1.4. Using Functions in Queries
      5. 6.1.5. Nested Functions
    2. 6.2. Aggregate Functions
      1. 6.2.1. The AVG() Function
      2. 6.2.2. The COUNT() Function
      3. 6.2.3. The MIN() and MAX() Functions
      4. 6.2.4. The SUM() Function
    3. 6.3. Configuration Variables
      1. 6.3.1. The @@ERROR Variable
      2. 6.3.2. The @@SERVICENAME Variable
      3. 6.3.3. The @@TOTAL_ERRORS Variable
      4. 6.3.4. The @@TOTAL_READ Variable
      5. 6.3.5. The @@VERSION Variable
      6. 6.3.6. Error Functions
    4. 6.4. Conversion Functions
      1. 6.4.1. The CAST() Function
      2. 6.4.2. The CONVERT() Function
      3. 6.4.3. The STR() Function
      4. 6.4.4. 1.0000
    5. 6.5. Cursor Functions and Variables
      1. 6.5.1. The CURSOR_STATUS() Function
      2. 6.5.2. The @@CURSOR_ROWS Global Variable
      3. 6.5.3. The @@FETCH_STATUS Global Variable
    6. 6.6. Date Functions
      1. 6.6.1. The DATEADD() Function
      2. 6.6.2. The DATEDIFF() Function
      3. 6.6.3. The DATEPART() and DATENAME() Functions
      4. 6.6.4. The GETDATE() and GETUTCDATE() Functions
      5. 6.6.5. The SYSDATETIME() and SYSUTCDATETIME() Functions
      6. 6.6.6. The DAY(), MONTH(), and YEAR() Functions
    7. 6.7. String Manipulation Functions
      1. 6.7.1. The ASCII(), CHAR(), UNICODE(), and NCHAR() Functions
      2. 6.7.2. The CHARINDEX() and PATINDEX() Functions
      3. 6.7.3. The LEN() Function
      4. 6.7.4. The LEFT() and RIGHT() Functions
      5. 6.7.5. The SUBSTRING() Function
      6. 6.7.6. The LOWER() and UPPER() Functions
      7. 6.7.7. The LTRIM() and RTRIM() Functions
      8. 6.7.8. The REPLACE() Function
      9. 6.7.9. The REPLICATE() and SPACE() Functions
      10. 6.7.10. The REVERSE() Function
      11. 6.7.11. The STUFF() Function
      12. 6.7.12. The QUOTENAME() Function
    8. 6.8. Mathematical Functions
    9. 6.9. Metadata Functions
    10. 6.10. Ranking Functions
      1. 6.10.1. The ROW_NUMBER() Function
      2. 6.10.2. The RANK() and DENSE_RANK() Functions
      3. 6.10.3. The NTILE(n) Function
    11. 6.11. Security Functions
    12. 6.12. System Functions and Variables
      1. 6.12.1. The COALESCE() Function
      2. 6.12.2. The DATALENGTH() Function
    13. 6.13. Global System Statistical Variables
    14. 6.14. Summary
    15. 6.15. Exercises
      1. 6.15.1. Exercise 1
      2. 6.15.2. Exercise 2
      3. 6.15.3. Exercise 3
      4. 6.15.4. Exercise 4
      5. 6.15.5. Exercise 5
  12. 7. Aggregation and Grouping
    1. 7.1. To Group or Not to Group
    2. 7.2. Using Aggregate Functions
      1. 7.2.1. The COUNT() Function
      2. 7.2.2. The SUM() Function
      3. 7.2.3. The AVG() Function
    3. 7.3. Understanding Statistical Functions
      1. 7.3.1. The STDEV() Function
      2. 7.3.2. The STDEVP() Function
      3. 7.3.3. The VAR() Function
      4. 7.3.4. The VARP() Function
      5. 7.3.5. User-Defined Aggregate Functions
    4. 7.4. Grouping Data
      1. 7.4.1. The GROUP BY Clause
      2. 7.4.2. The HAVING Clause
      3. 7.4.3. Total and Subtotal Group Modifiers
        1. 7.4.3.1. Old and New Subtotal Techniques
      4. 7.4.4. Subgrouping
      5. 7.4.5. The ROLLUP Clause
      6. 7.4.6. The CUBE Clause
      7. 7.4.7. The GROUPING() Function
      8. 7.4.8. The COMPUTE and COMPUTE BY Clauses
    5. 7.5. Summary
    6. 7.6. Exercises
      1. 7.6.1. Exercise 1
      2. 7.6.2. Exercise 2
      3. 7.6.3. Exercise 3
  13. 8. Multi-Table Queries
    1. 8.1. Understanding Subqueries and Joins
      1. 8.1.1. Joining Tables in the WHERE Clause
      2. 8.1.2. Joining Tables in the FROM Clause
      3. 8.1.3. Types of Joins
      4. 8.1.4. Inner Joins
      5. 8.1.5. Outer Joins
        1. 8.1.5.1. Legacy Outer Joins
      6. 8.1.6. Multicolumn Joins
      7. 8.1.7. Non-Equijoins
      8. 8.1.8. Special-Purpose Join Operations
        1. 8.1.8.1. Full Joins
        2. 8.1.8.2. Cross Joins
        3. 8.1.8.3. Filtering Records in the Join Clause Using a Predicate
        4. 8.1.8.4. Joining on an Expression
        5. 8.1.8.5. Multi-Table Joins
      9. 8.1.9. Union Queries
        1. 8.1.9.1. Partitioning and Federating Data
        2. 8.1.9.2. Performance and Scaling Considerations
    2. 8.2. Summary
    3. 8.3. Exercises
      1. 8.3.1. Exercise 1
      2. 8.3.2. Exercise 2
      3. 8.3.3. Exercise 3
  14. 9. Advanced Queries and Scripting
    1. 9.1. Subqueries
      1. 9.1.1. Scalar Expressions
      2. 9.1.2. Alternate Join Operations
        1. 9.1.2.1. Inner Join Subqueries
        2. 9.1.2.2. Outer Join Subqueries
        3. 9.1.2.3. Table Aliasing 101
        4. 9.1.2.4. Using Aggregate Functions in Subqueries
        5. 9.1.2.5. The HAVING Clause
        6. 9.1.2.6. Creating a Derived Table
        7. 9.1.2.7. Using the IN( ) Function
        8. 9.1.2.8. Using the EXISTS() Function
          1. 9.1.2.8.1. NOT EXISTS()
      3. 9.1.3. Correlated Subqueries
        1. 9.1.3.1. Benchmarking and Best Practices
      4. 9.1.4. Business Cases for Subqueries
        1. 9.1.4.1. Top Sales by Territory
        2. 9.1.4.2. Unshipped Product Orders
        3. 9.1.4.3. Consulting Billing Time
    2. 9.2. Common Table Expressions
    3. 9.3. Cursors
      1. 9.3.1. Rowset Versus Cursor Operations
      2. 9.3.2. Creating and Navigating a Cursor
    4. 9.4. Summary
    5. 9.5. Exercises
      1. 9.5.1. Exercise 1
      2. 9.5.2. Exercise 2
      3. 9.5.3. Exercise 3
  15. 10. Transactions
    1. 10.1. Introducing Transactions
      1. 10.1.1. Transaction Types
      2. 10.1.2. The ACID Test
      3. 10.1.3. The Transaction Log
      4. 10.1.4. Logged Operations
    2. 10.2. Let's Do CRUD with Data
      1. 10.2.1. Adding Records
        1. 10.2.1.1. INSERT Statement
        2. 10.2.1.2. INSERT ... Values
        3. 10.2.1.3. INSERT ... SELECT
        4. 10.2.1.4. Inserting Multiple Records
        5. 10.2.1.5. Inserting NULL, Defaults, and Other Column Considerations
        6. 10.2.1.6. Inserting Rows from Another Table
          1. 10.2.1.6.1. SELECT INTO
          2. 10.2.1.6.2. Managing Inserts Using Stored Procedures
      2. 10.2.2. Modifying Records
        1. 10.2.2.1. UPDATE Command
        2. 10.2.2.2. Filtering Updates
          1. 10.2.2.2.1. Updating Rows Based on Multiple Tables
          2. 10.2.2.2.2. Updating Using Views
          3. 10.2.2.2.3. Updating Records Using Stored Procedures
      3. 10.2.3. Removing Records
        1. 10.2.3.1. DELETE Command
          1. 10.2.3.1.1. Embrace the WHERE Clause
          2. 10.2.3.1.2. Deleting Records Based on Another Table
        2. 10.2.3.2. TRUNCATE TABLE
      4. 10.2.4. Automating Inserts, Updates, and Deletes with the MERGE Command
    3. 10.3. Explicit Transactions
    4. 10.4. Summary
    5. 10.5. Exercises
      1. 10.5.1. Exercise 1
      2. 10.5.2. Exercise 2
      3. 10.5.3. Exercise 3
  16. 11. Advanced Capabilities
    1. 11.1. Pivoting Data
      1. 11.1.1. The PIVOT Operator
      2. 11.1.2. The UNPIVOT Operator
    2. 11.2. Full-Text Queries and Approximation Matching
    3. 11.3. Microsoft Search Service
      1. 11.3.1. Soundex Matching
      2. 11.3.2. The DIFFERENCE() Function
    4. 11.4. Managing and Populating Catalogs
      1. 11.4.1.
        1. 11.4.1.1. Creating an Index Manually
      2. 11.4.2. Full-Text Query Expressions
        1. 11.4.2.1. Quotes in Quotes
        2. 11.4.2.2. The CONTAINS Predicate
        3. 11.4.2.3. Weighting Values
        4. 11.4.2.4. Ranked Results
        5. 11.4.2.5. The CONTAINSTABLE Predicate
        6. 11.4.2.6. The FREETEXT Predicate
        7. 11.4.2.7. Logical Operators
        8. 11.4.2.8. The FREETEXTTABLE Predicate
    5. 11.5. Summary
    6. 11.6. Exercises
      1. 11.6.1. Exercise 1
      2. 11.6.2. Exercise 2
      3. 11.6.3. Exercise 3
  17. 12. T-SQL Programming Objects
    1. 12.1. Views
      1. 12.1.1. Virtual Tables
      2. 12.1.2. Creating a View
        1. 12.1.2.1. Creating a View in Management Studio
        2. 12.1.2.2. Creating a View Using SQL Script
        3. 12.1.2.3. Ordering Rows
        4. 12.1.2.4. Partitioned Views
        5. 12.1.2.5. Federated Views
      3. 12.1.3. Securing Data
      4. 12.1.4. Hiding Complexity
      5. 12.1.5. Modifying Data Through Views
    2. 12.2. Stored Procedures
      1. 12.2.1. Stored Procedures as Parameterized Views
      2. 12.2.2. Using Parameters
      3. 12.2.3. Returning Values
      4. 12.2.4. Record Maintenance
        1. 12.2.4.1. Insert Procedure
        2. 12.2.4.2. Update Procedure
        3. 12.2.4.3. Delete Procedure
      5. 12.2.5. Handling and Raising Errors
      6. 12.2.6. Error Messages
        1. 12.2.6.1. Error Handling in SQL Server
      7. 12.2.7. Processing Business Logic
        1. 12.2.7.1. Conditional Logic
          1. 12.2.7.1.1. IF
          2. 12.2.7.1.2. CASE
      8. 12.2.8. Looping
    3. 12.3. User-Defined Functions
      1. 12.3.1. Scalar Functions
      2. 12.3.2. Inline Table-Valued Functions
      3. 12.3.3. Multi-Statement Table-Valued Functions
    4. 12.4. Transaction Management
      1. 12.4.1. Locking Options
    5. 12.5. Summary
    6. 12.6. Exercises
      1. 12.6.1. Exercise 1
      2. 12.6.2. Exercise 2
      3. 12.6.3. Exercise 3
  18. 13. Creating and Managing Database Objects
    1. 13.1. Data Definition Language
      1. 13.1.1. Creating Objects
      2. 13.1.2. Altering Objects
      3. 13.1.3. Dropping Objects
      4. 13.1.4. Naming Objects
        1. 13.1.4.1. Naming Rules
        2. 13.1.4.2. Naming Guidelines
      5. 13.1.5. Creating DDL Scripts
      6. 13.1.6. CREATE TABLE
        1. 13.1.6.1. Nullability
        2. 13.1.6.2. Identity
        3. 13.1.6.3. Defaults
      7. 13.1.7. Unique Identifiers
      8. 13.1.8. Constraints
        1. 13.1.8.1. Primary Key Constraint
        2. 13.1.8.2. Unique Constraint
        3. 13.1.8.3. Check Constraint
        4. 13.1.8.4. Foreign Key Constraint
        5. 13.1.8.5. Overriding Constraints
      9. 13.1.9. CREATE VIEW
        1. 13.1.9.1. WITH CHECK OPTION
        2. 13.1.9.2. WITH SCHEMABINDING
        3. 13.1.9.3. WITH ENCRYPTION
        4. 13.1.9.4. WITH VIEW_METADATA
      10. 13.1.10. Indexed Views
      11. 13.1.11. CREATE PROCEDURE
        1. 13.1.11.1. Using Parameters
        2. 13.1.11.2. WITH ENCRYPTION
        3. 13.1.11.3. WITH RECOMPILE
        4. 13.1.11.4. EXECUTE AS
      12. 13.1.12. CREATE FUNCTION
    2. 13.2. IF EXISTS
    3. 13.3. Securing Database Objects
      1. 13.3.1. Managing Security Objects
      2. 13.3.2. Data Control Language
        1. 13.3.2.1. GRANT
        2. 13.3.2.2. DENY
        3. 13.3.2.3. REVOKE
    4. 13.4. Summary
    5. 13.5. Exercises
      1. 13.5.1. Exercise 1
      2. 13.5.2. Exercise 2
  19. 14. Analyzing and Optimizing Query Performance
    1. 14.1. Data Retrieval
    2. 14.2. Analyzing Queries
      1. 14.2.1. Session Options
        1. 14.2.1.1. STATISTICS IO
        2. 14.2.1.2. STATISTICS TIME
        3. 14.2.1.3. STATISTICS PROFILE
        4. 14.2.1.4. STATISTICS XML
        5. 14.2.1.5. SHOWPLAN_TEXT
        6. 14.2.1.6. SHOWPLAN_ALL
        7. 14.2.1.7. SHOWPLAN_XML
      2. 14.2.2. Graphical Execution Plans
    3. 14.3. Writing Efficient T-SQL (Best Practices)
      1. 14.3.1. Writing Efficient Filters
        1. 14.3.1.1. Positive Searches
        2. 14.3.1.2. Wildcards
        3. 14.3.1.3. Logic Operators
        4. 14.3.1.4. Join Operators
    4. 14.4. Summary
    5. 14.5. Exercises
      1. 14.5.1. Exercise 1
  20. 15. T-SQL in Applications and Reporting
    1. 15.1. Application Programming Models
    2. 15.2. Selecting a Model
      1. 15.2.1. Desktop Database Applications
      2. 15.2.2. Client/Server Database Solutions
      3. 15.2.3. n-tier Component Solutions
        1. 15.2.3.1. Service Oriented Architecture
        2. 15.2.3.2. Server-Side SQL Objects
        3. 15.2.3.3. Middle-Tier Component
        4. 15.2.3.4. Presentation Layer
        5. 15.2.3.5. Resource Pooling
        6. 15.2.3.6. Component Transaction Management
        7. 15.2.3.7. LINQ: .NET Standard Query Operators
      4. 15.2.4. Web Server Applications
      5. 15.2.5. Multi-Tier Web Service Solutions
      6. 15.2.6. Multi-System Integrated Solutions
        1. 15.2.6.1. System Integration and Data Exchange
        2. 15.2.6.2. Project Management Challenges
    3. 15.3. SQL Server 2008 Reporting Services
      1. 15.3.1. Reporting Services Architecture
      2. 15.3.2. SQL Server 2008 Report Design
        1. 15.3.2.1. Designing the Data Source and Dataset
        2. 15.3.2.2. Adding a Parameter to the Query
        3. 15.3.2.3. Adding a Parameter List Query
        4. 15.3.2.4. Designing the Report Layout
        5. 15.3.2.5. Adding Groups and Fields
        6. 15.3.2.6. Formatting a Number Field
        7. 15.3.2.7. Setting Up the Parameter Drop-Down List
        8. 15.3.2.8. Saving the Report
        9. 15.3.2.9. Adding Group Totals
      3. 15.3.3. Deploying the Report
      4. 15.3.4. Viewing the Report with Report Manager
      5. 15.3.5. Report Data Caching
      6. 15.3.6. Business Intelligence and Business Reporting
      7. 15.3.7. Report Application Integration
    4. 15.4. Summary
  21. A. Command Syntax Reference
    1. A.1. T-SQL Commands, Clauses, and Predicates
      1. A.1.1. WITH
      2. A.1.2. SELECT
      3. A.1.3. SELECT TOP
      4. A.1.4. SELECT INTO
      5. A.1.5. FROM
      6. A.1.6. WHERE
      7. A.1.7. GROUP BY
        1. A.1.7.1. WITH ROLLUP
        2. A.1.7.2. BY ROLLUP
        3. A.1.7.3. WITH CUBE
        4. A.1.7.4. BY CUBE
      8. A.1.8. HAVING
      9. A.1.9. UNION
      10. A.1.10. EXCEPT and INTERSECT
      11. A.1.11. ORDER BY
        1. A.1.11.1. COMPUTE and COMPUTE BY Clauses
        2. A.1.11.2. FOR Clause
        3. A.1.11.3. OPTION Clause
        4. A.1.11.4. CASE
        5. A.1.11.5. INSERT
        6. A.1.11.6. UPDATE
        7. A.1.11.7. DELETE
        8. A.1.11.8. DECLARE @local_variable
        9. A.1.11.9. SET
        10. A.1.11.10. LIKE
        11. A.1.11.11. ALTER TABLE
        12. A.1.11.12. PIVOT Operator
        13. A.1.11.13. UNPIVOT Operator
      12. A.1.12. CREATE DATABASE
        1. A.1.12.1. CREATE DEFAULT
        2. A.1.12.2. CREATE PROCEDURE
        3. A.1.12.3. CREATE RULE
        4. A.1.12.4. CREATE TABLE
        5. A.1.12.5. CREATE TRIGGER
        6. A.1.12.6. CREATE VIEW
        7. A.1.12.7. CREATE SCHEMA
        8. A.1.12.8. CREATE PARTITION FUNCTION
        9. A.1.12.9. CREATE PARTITION SCHEME
    2. A.2. Script Comment Conventions
    3. A.3. Reserved Words
      1. A.3.1. ODBC Reserved Words
      2. A.3.2. Future Reserved Words
  22. B. System Variables and Functions Reference
    1. B.1. System Global Variables
      1. B.1.1. Configuration
      2. B.1.2. Cursor
      3. B.1.3. System
      4. B.1.4. System Statistical
    2. B.2. System Functions
      1. B.2.1. Aggregation
      2. B.2.2. Checksum
      3. B.2.3. Conversion
      4. B.2.4. Cryptographic
      5. B.2.5. Cursor
      6. B.2.6. Date and Time
      7. B.2.7. Image/Text
      8. B.2.8. Error Handling
      9. B.2.9. Mathematical
      10. B.2.10. Metadata
      11. B.2.11. Ranking
      12. B.2.12. Rowset
      13. B.2.13. Security
      14. B.2.14. String Manipulation
      15. B.2.15. System
      16. B.2.16. System Statistical
  23. C. System Stored Procedure Reference
    1. C.1. Active Directory
    2. C.2. Catalog
    3. C.3. Change Data Capture (2008)
    4. C.4. Cursor Management
    5. C.5. Database Engine
    6. C.6. Database Maintenance Plan
    7. C.7. Distributed Queries
    8. C.8. External Systems and Extended Procedures
    9. C.9. Full-Text Index/Search
    10. C.10. Log Shipping
    11. C.11. Database Mail
    12. C.12. OLE Automation
    13. C.13. SQL Server Profiler
    14. C.14. Security
    15. C.15. SQL Server Agent
    16. C.16. XML
  24. D. Information Schema Views Reference
    1. D.1. CHECK_CONSTRAINTS
    2. D.2. COLUMN_DOMAIN_USAGE
    3. D.3. COLUMN_PRIVILEGES
    4. D.4. COLUMNS
    5. D.5. CONSTRAINT_COLUMN_USAGE
    6. D.6. CONSTRAINT_TABLE_USAGE
    7. D.7. DOMAIN_CONSTRAINTS
    8. D.8. DOMAINS
    9. D.9. KEY_COLUMN_USAGE
    10. D.10. PARAMETERS
    11. D.11. REFERENTIAL_CONSTRAINTS
    12. D.12. ROUTINE_COLUMNS
    13. D.13. ROUTINES
    14. D.14. SCHEMATA
    15. D.15. TABLE_CONSTRAINTS
    16. D.16. TABLE_PRIVILEGES
    17. D.17. TABLES
    18. D.18. VIEW_COLUMN_USAGE
    19. D.19. VIEW_TABLE_USAGE
    20. D.20. VIEWS
  25. E. FileStream Objects and Syntax
    1. E.1. FileStream Objects
    2. E.2. Sample T-SQL Scripts
      1. E.2.1. To Create a Database with FileStream Storage
      2. E.2.2. To Create a Table with a FileStream-Enabled Column
      3. E.2.3. To Store Column Text in an External File
  26. F. Answers to Exercises
    1. F.1. Chapter 3
      1. F.1.1. Exercise 1 Solution
      2. F.1.2. Exercise 2 Solution
      3. F.1.3. Exercise 3 Solution
      4. F.1.4. Exercise 4 Solution
    2. F.2. Chapter 4
      1. F.2.1. Exercise 1 Solution
      2. F.2.2. Exercise 2 Solution
    3. F.3. Chapter 5
      1. F.3.1. Exercise 1 Solution
      2. F.3.2. Exercise 2 Solution
      3. F.3.3. Exercise 3 Solution
      4. F.3.4. Exercise 4 Solution
    4. F.4. Chapter 6
      1. F.4.1. Exercise 1 Solution
      2. F.4.2. Exercise 2 Solution
      3. F.4.3. Exercise 3 Solution
      4. F.4.4. Exercise 4 Solution
      5. F.4.5. Exercise 5 Solution
    5. F.5. Chapter 7
      1. F.5.1. Exercise 1 Solution
      2. F.5.2. Exercise 2 Solution
      3. F.5.3. Exercise 3 Solution
    6. F.6. Chapter 8
      1. F.6.1. Exercise 1 Solution
      2. F.6.2. Exercise 2 Solution
      3. F.6.3. Exercise 3 Solution
    7. F.7. Chapter 9
      1. F.7.1. Exercise 1 Solution
      2. F.7.2. Exercise 2 Solution
      3. F.7.3. Exercise 3 Solution
    8. F.8. Chapter 10
      1. F.8.1. Exercise 1 Solution
      2. F.8.2. Exercise 2 Solution
      3. F.8.3. Exercise 3 Solution
    9. F.9. Chapter 11
      1. F.9.1. Exercise 1 Solution
      2. F.9.2. Exercise 2 Solution
      3. F.9.3. Exercise 3 Solution
    10. F.10. Chapter 12
      1. F.10.1. Exercise 1 Solution
      2. F.10.2. Exercise 2 Solution
      3. F.10.3. Exercise 3 Solution
    11. F.11. Chapter 13
      1. F.11.1. Exercise 1 Solution
      2. F.11.2. Exercise 2 Solution
    12. F.12. Chapter 14
      1. F.12.1. Exercise 1 Solution

Product Information

  • Title: Beginning T-SQL with Microsoft® SQL Server® 2005 and 2008
  • Author(s): Dan Wood, Paul Turley
  • Release date: December 2008
  • Publisher(s): Wrox
  • ISBN: 9780470257036