Microsoft® SQL Server™ 2005: Database Essentials Step by Step

Microsoft® SQL Server™ 2005: Database Essentials Step by Step

Released September 2006
Publisher(s): Microsoft Press
ISBN: 9780735622074

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

SQL Server 2005 is Microsoft’s next-generation data management and analysis solution that delivers enhanced scalability, availability, and security features to enterprise data and analytical applications while making them easier to create, deploy, and manage. Now you can teach yourself how to design, build, test, deploy, and maintain SQL Server databases—one step at a time. With STEP BY STEP, you work at your own pace through hands-on, learn-by-doing exercises. Instead of merely focusing on describing new features, this book shows new database programmers and administrators how to use specific features within typical business scenarios. Each chapter puts you to work, providing a highly practical learning experience that demonstrates how to build database solutions to solve common business problems.

Table of contents

  1. Microsoft® SQL Server™ 2005: Database Essentials Step by Step
  2. A Note Regarding Supplemental Files
  3. Introduction
    1. Who This Book Is For
    2. How This Book Is Organized
    3. Finding Your Best Starting Point in This Book
    4. Conventions and Features in This Book
      1. Conventions
      2. Other Features
    5. System Requirements
    6. Sample Files
      1. Installing the Sample Files
      2. Using the Sample Files
      3. Uninstalling the Sample Files
    7. Support for This Book
      1. Questions and Comments
  4. Acknowledgments
    1. Authors
    2. Technical Editor
  5. I. Introduction to Database Development with Microsoft SQL Server 2005
    1. 1. Introducing Database Development with Microsoft SQL Server 2005
      1. The Process of Storing and Managing Data
      2. Introducing Database Systems
        1. Understanding the Requirements of a Database System
      3. Defining the Architecture of a Database Application
        1. Using a Monolithic Application with Data Embedded in the Application Code
        2. Using a Monolithic Application with Data Stored in an External File
        3. Using a Database Application with Data Managed by a Database Server
        4. Using an Application with a Generic Data Access Layer
        5. Using an Application with Separated Presentation, Business, and Data Access Layers
        6. Using a Complex Application with Multiple Options for Each Layer
      4. Conclusion
    2. 2. Installing and Setting Up Your Microsoft SQL Server 2005 Development Environment
      1. Using a Virtual PC
        1. Operating System Requirements for Development of SQL Server Database Applications
      2. Recommended Productivity Tools
        1. Using Performance Monitor
        2. Using Network Monitor
        3. Using Upgrade Advisor
      3. Recommended Development Tools
      4. Microsoft SQL Server 2005 Editions
        1. Microsoft SQL Server Express Edition
        2. Microsoft SQL Server Workgroup Edition
        3. Microsoft SQL Server Standard Edition
        4. Microsoft SQL Server Enterprise Edition
      5. Installing SQL Server 2005
        1. Installing SQL Server 2005
        2. Using the Surface Area Configuration Tool
          1. Surface Area Configuration For Services And Connections
          2. Surface Area Configuration For Features
      6. Conclusion
        1. Chapter 2 Quick Reference
    3. 3. Reviewing Microsoft SQL Server 2005 Management Tools
      1. Introduction
      2. Using SQL Server Books Online
        1. Accessing SQL Server Books Online
      3. Using SQL Server Configuration Manager
        1. Using the SQL Server 2005 Services Node
        2. Using the SQL Server 2005 Network Configuration Node
          1. Static Ports, Dynamic Ports, and Windows Firewall
            1. Static ports
            2. Dynamic ports
            3. Windows Firewall
            4. What is the best configuration?
        3. Using the SQL Native Client Configuration Node
          1. Client Protocols
          2. Aliases
      4. Using SQL Server Surface Area Configuration
        1. Using Surface Area Configuration For Services And Connections
        2. Using Surface Area Configuration For Features
        3. Sac Utility
      5. Using SQL Server Management Studio
        1. Administering Servers with SQL Server Management Studio
          1. Connecting to a Server
          2. Registering a Server in SQL Server Management Studio
        2. Using Object Explorer in SQL Server Management Studio
          1. Using the Database Node
            1. Creating SELECT Queries
          2. Querying More than One Table
            1. Creating More Complex SELECT Queries
          3. Managing Other Objects
          4. Using Database Diagrams
            1. Creating a Database Diagram
        3. Writing Scripts in SQL Server Management Studio
          1. Starting a Query Using the New Query Toolbar Button
          2. Starting a New Query Using the Database Context Menu
          3. Retrieving Information about Your Queries
            1. Obtaining Query Information
          4. Retrieving Statistics
        4. Writing Solutions and Projects in SQL Server Management Studio
          1. Creating a Project in SQL Server Management Studio
        5. Using SQL Server Management Studio Templates
          1. Performing Actions with Templates
      6. Using SQL Server Profiler
        1. Using the SQL Server Profiler Tool and Creating a Trace
          1. Creating a Trace with the GUI
        2. When Should You Use SQL Server Profiler?
        3. Accessing Event Types
          1. Selecting Data in Event Classes
        4. Running SQL Server Profiler and Performance Monitor Together
          1. Running SQL Server Profiler and Performance Monitor Side by Side
        5. Viewing Deadlocks in SQL Server Profiler
          1. Exporting Deadlock Information
      7. Using Database Engine Tuning Advisor
        1. Analyzing Database Engine Tuning Advisor
        2. Working with Database Engine Tuning Advisor
          1. Starting Database Engine Tuning Advisor
          2. Evaluating the Recommendations
            1. Evaluating the Impact of Database Engine Tuning Advisor Recommendations
          3. Managing Tuning Options
        3. Managing Database Engine Tuning Advisor Sessions
          1. Exporting a Session Definition
      8. Using SQLCmd
        1. Working with the SQLCmd Utility
          1. Connecting to a Default SQL Server Instance
        2. Executing Script Files
          1. Executing the DTA.sql Script by Using the SQLCmd Utility
      9. Conclusion
        1. Chapter 3 Quick Reference
  6. II. How to Create a Microsoft SQL Server 2005 Database
    1. 4. Gathering and Understanding Business Requirements before Creating Database Objects
      1. Understanding Business Processes and User Interaction Requirements
        1. Defining the Business Problem
        2. Capturing Requirements
          1. Writing User Surveys
          2. Interviewing
          3. Shadowing
        3. Writing Requirements
      2. Understanding Business Data and Its Lifetime
        1. Architecture and Operational Requirements
        2. Availability
          1. Interoperability
          2. Manageability
          3. Performance
          4. Reliability
          5. Scalability
          6. Security
      3. Predicting the Volume of Information to Store and Manage and Predicting Database Utilization
        1. Using Transaction Cost Analysis
          1. Step 1: Compiling a Users Profile
          2. Step 2: Executing Discrete Tests
            1. Creating the Script
          3. Step 3: Measuring the Cost of Each Operation
            1. Creating a Windows Performance Log
            2. Reviewing the Captured Information
          4. Step 4: Calculating the Cost of an Average Profile
          5. Step 5: Calculating the Maximum Capacity
          6. Step 6: Verifying the Maximum Capacity
      4. Conclusion
        1. Chapter 4 Quick Reference
    2. 5. Designing a Database to Solve Business Needs
      1. Designing a Database Conceptually
        1. Validating Business Requirements through Conceptual Models
          1. Creating the First Model
          2. Diagramming the Model
            1. Creating a Conceptual ER Diagram Using Microsoft Visio
          3. Creating Entities
            1. Modeling Entities in Visio 2003
          4. Adding Attributes
            1. Modeling Attributes in Visio 2003
          5. Adding Relationships
            1. Modeling a Parent-Child Relationship
          6. Other Types of Relationships
            1. Modeling intersection relationships
            2. Modeling an Intersection Relationship
            3. Modeling multi-intersection relationships
            4. Modeling self-referencing relationships
            5. Modeling a Self-Referencing Relationship
        2. Approving the Model
      2. Designing a Database Logically to Leverage the Relational Engine
        1. Creating Columns to Capture Object Attributes
          1. Declaring a Datatype Graphically
            1. Defining a Datatype Using Microsoft Visio 2003
          2. Using T-SQL Code to Define a Datatype
          3. Numeric Attributes
            1. Integers and quantities
            2. Precise numbers and accounting data
            3. Scientific and engineering data
          4. String Attributes
            1. Unicode and character datatypes
            2. Fixed and variable-length datatypes
            3. String syntax
            4. Long text datatypes
            5. Choosing the appropriate datatype
          5. Date and Time Attributes
          6. Binary Data
          7. Complex Attributes
            1. T-SQL user-defined datatypes
            2. Common language runtime-based UDTs
        2. Validating the Data
          1. NOT NULL Constraints
          2. DEFAULT Constraints
          3. PRIMARY KEY Constraints
          4. UNIQUE Constraints
          5. CHECK Constraints
          6. FOREIGN KEY Constraints
      3. Physically Creating a Database
        1. Selecting an Appropriate Storage Design for a Database
          1. What Is a Database?
          2. How Is Information Stored?
          3. Designing Database Storage
        2. Creating a Database
          1. Using SQL Server Management Studio to Create a Database
          2. Using T-SQL to Create a Database
        3. Designing Database Schemas to Logically Group Database Objects
          1. Using SQL Server Management Studio to Create Database Schemas
          2. Using T-SQL to Create Database Schemas
        4. Creating Tables to Implement a Design
          1. Using SQL Server Management Studio to Create Tables
          2. Defining the Primary Key in a Table Using SQL Server Management Studio
          3. Defining a CHECK Constraint Using SQL Server Management Studio
          4. Using T-SQL to Create Tables
      4. Conclusion
        1. Chapter 5 Quick Reference
  7. III. How to Query Data from Microsoft SQL Server 2005
    1. 6. Reading Microsoft SQL Server 2005 Data from Client Applications
      1. Introducing Microsoft Data Access Components
        1. Avoiding Deprecated MDAC Components
        2. Outlining the MDAC Architecture
        3. Understanding Open Database Connectivity (ODBC)
          1. Creating a Data Source Name
            1. Creating an ODBC Data Source
          2. Programming ODBC Applications
        4. Understanding OLE DB and ADO
          1. Using an ADO Connection Object
            1. Creating an Appropriate Connection String
          2. Using an ADO Command Object
          3. Using an ADO Recordset Object
        5. Understanding ADO.NET
          1. Outlining the ADO.NET Architecture
          2. Using ADO.NET Namespaces
      2. Introducing the .NET SQL Server Data Provider
        1. Using the SqlConnection Class
        2. Using the SqlCommand Class
        3. Using the SqlDataReader Class
        4. Using the SqlDataAdapter Class
          1. Creating a SqlDataAdapter Graphically
        5. Using the TableAdapter Class
          1. Creating a TableAdapter
      3. Introducing SQL Native Client
        1. Using the XML Datatype
        2. Using Multiple Active Result Sets (MARS)
        3. Using Query Notification
          1. Creating an Application that Uses Query Notification
      4. Conclusion
        1. Chapter 6 Quick Reference
    2. 7. Selecting the Data You Need
      1. Selecting Data from a Single Table
        1. Running a SELECT Statement in SQL Server Management Studio
        2. Using AND and OR Operators
        3. Comparing NULL Values
        4. Using the CASE Statement
        5. Using Search Arguments
      2. Selecting Data from Multiple Tables
        1. Using Aliases
        2. Using the INNER JOIN Syntax
        3. Using More than Two Tables
        4. Using LEFT JOIN
        5. Using RIGHT JOIN
        6. Using FULL JOIN
      3. Reading Single Values
        1. Using System-Supplied Scalar Functions
          1. Aggregate Functions
          2. Configuration Functions
          3. Cursor Functions
          4. Date and Time Functions
          5. Mathematical Functions
          6. Metadata Functions
          7. Security Functions
          8. String Functions
          9. System Functions
          10. System Statistical Functions
          11. Ranking Functions
        2. Designing and Using Scalar UDFs
          1. Designing UDFs that Do Not Access Tables or Views
          2. Designing UDFs that Access Tables or Views
        3. Designing and Using Stored Procedures to Retrieve Scalar Values
          1. Using Output Parameters
      4. Reading Relational and XML Data
        1. Viewing XML Results in SQL Server Management Studio
          1. Setting the Results Pane View
        2. Converting Relational Data to XML Format
        3. Converting XML Data to Relational Format
          1. Converting XML Data into a Relational Format Using the nodes() Method
      5. Querying XML and Relational Data Using XQuery
        1. Using XQUERY in Queries
      6. Sorting Data
      7. Conclusion
        1. Chapter 7 Quick Reference
    3. 8. Creating Views to Encapsulate Queries
      1. Selecting Data from a Single View
        1. Creating a View
          1. Creating a View in SQL Server Management Studio
          2. Creating a View Using T-SQL
          3. Obtaining Information about Views
          4. Accessing Dependency Information
          5. Creating View Options
        2. Modifying a View Definition
        3. Updating Data through a View
        4. Partitioned Views
      2. Mixing Data from Views and Tables
      3. Working with Views within Client Applications
        1. Creating a Data Source with Visual Studio 2005
      4. Conclusion
        1. Chapter 8 Quick Reference
    4. 9. Retrieving Data Using Programmable Objects
      1. Introduction
      2. Working through a Simple Problem
        1. Understanding Scalar UDFs
        2. Retrieving Result Sets
          1. Using In-Line UDFs
          2. Using Phantom Tables
          3. Using Table-Valued UDFs
        3. Updating Data
          1. Simplifying Procedures
          2. Using Stored Procedures and UDFs
      3. Common Language Runtime UDFs and Procedures
        1. Working with Statistical Calculations
          1. Writing a Factorial Function Using Visual Basic.NET
          2. Using Table-Valued UDFs
            1. Creating a Table-Valued UDF
        2. Working with CLR Stored Procedures
          1. Performing File Operations
            1. Creating a CLR Stored Procedure
      4. Conclusion
        1. Chapter 9 Quick Reference
  8. IV. How to Modify Data in Microsoft SQL Server 2005
    1. 10. Inserting Data in Microsoft SQL Server 2005
      1. Using the INSERT Statement
        1. Creating an INSERT Sentence with SQL Server Management Studio
          1. Creating an INSERT Sentence
        2. Using Special Values for Row Insertion
        3. Using Other Forms of the INSERT Statement
      2. Inserting Data through Views
        1. Inserting a Value into a View
        2. Using the WITH Clause
        3. Using INSTEAD OF INSERT Triggers on Views
      3. Importing Data into SQL Server 2005
        1. Using the BCP Utility
        2. Using the BULK INSERT Command
      4. Using Integration Services to Insert Data
        1. Creating an Export Package
        2. Editing the Package
        3. Creating Your Own Package
          1. Creating a New Project
          2. Defining the Source
          3. Defining the Destination
          4. Defining the Transformation
          5. Defining the Process
      5. Encapsulating Insert Operations in Stored Procedures
      6. Triggering Actions Automatically when Inserting Data
      7. Dealing with Errors when Inserting Data
        1. Managing the Error
      8. Inserting Data from ADO.NET
        1. Using ADO.NET Objects
      9. Conclusion
        1. Chapter 10 Quick Reference
    2. 11. Deleting Data from Microsoft SQL Server 2005
      1. Using the DELETE Statement
        1. Creating a DELETE Sentence with SQL Server Management Studio
          1. Creating a DELETE Sentence
        2. Defining the WHERE Condition
        3. Using Relationships to Perform Deletions
      2. Deleting Data through Views
        1. Using INSTEAD OF DELETE Triggers on Views
      3. Encapsulating Delete Operations in Stored Procedures
        1. Implementing Pessimistic Concurrency for Delete Operations
          1. Changing Row Information in a Table
        2. Implementing Optimistic Concurrency for Delete Operations
          1. Method 1. Comparing All Columns
          2. Method 2. Using the ModifiedDate Column
          3. Method 3. Using a Timestamp Column
            1. Using a Timestamp column from .NET
          4. Testing a Stored Procedure
          5. Method 4. Using a Unique Identifier Column
        3. Triggering Actions Automatically when Deleting Data
      4. Dealing with Errors
        1. Data Does Not Exist
        2. Data to Be Deleted Is Related to Other Tables
        3. Other Errors
      5. Deleting Data from ADO.NET
        1. Deleting Rows Using ADO.NET
        2. Managing Errors during the Delete Process
          1. Evaluating Errors in ADO.NET
      6. Conclusion
        1. Chapter 11 Quick Reference
    3. 12. Updating Data from Microsoft SQL Server 2005
      1. Using the UPDATE Statement
        1. Creating an UPDATE Sentence with SQL Server Management Studio
          1. Creating an UPDATE Sentence
        2. Defining the WHERE Condition
        3. Assigning New Values to Columns
        4. Using Arithmetic to Update Information
        5. Changing the Content of a Large (MAX) Column
        6. Changing the Content of a Large (BLOB) Column
      2. Updating Data through Views
        1. Using INSTEAD OF UPDATE Triggers on Views
          1. Understanding the INSTEAD OF UPDATE Trigger
          2. Detecting Changes in a View
      3. Encapsulating Update Operations in Stored Procedures
        1. Implementing Pessimistic and Optimistic Concurrency for Update Operations
      4. Triggering Actions Automatically when Updating Data
      5. Dealing with Errors
        1. Assigned Value Is Higher than the Field Datatype Limit
        2. Column Value Violates Referential Integrity
      6. Updating Data from ADO.NET
        1. Updating a Row Using a Stored Procedure in ADO.NET
        2. Using Datasets and Table Adapters
          1. Creating a Dataset and Table Adapter
          2. Creating a User Interface
        3. Using Stored Procedures with Table Adapters
          1. Creating Stored Procedures for Table Adapters
          2. Creating an Editor
      7. Conclusion
        1. Chapter 12 Quick Reference
  9. Index
  10. About the Author
  11. Copyright

Product information

  • Title: Microsoft® SQL Server™ 2005: Database Essentials Step by Step
  • Author(s):
  • Release date: September 2006
  • Publisher(s): Microsoft Press
  • ISBN: 9780735622074