You are previewing Beginning SQL Server 2008 Express for Developers: From Novice to Professional.
O'Reilly logo
Beginning SQL Server 2008 Express for Developers: From Novice to Professional

Book Description

Beginning SQL Server 2008 Express for Developers: From Novice to Professional takes a developer, and even a DBA, from knowing nothing about SQL Server 2008 Express Edition to being ready to design and build a fully functioning system that is secure, reliable, maintainable, and robust. The Express Edition of SQL Server is a free resource commonly used by students, developers of small systems, and those transferring skills from other databases. This book focuses on the features within the Express Edition and therefore will not confuse readers with coverage of features found only in the Development Edition. By the end of the book, readers will be ready to move on to Accelerated SQL Server 2008 as well as other books within the Apress SQL Server lineup such as Pro SQL Server 2008 Reporting Services.

  • Covers using the free Express Edition of SQL Server

  • Describes creating a database from scratch and builds on this

  • Provides an understanding of SQL Server Express 2008, allowing you to build future solutions for you and your business

Table of Contents

  1. Copyright
  2. About the Author
  3. About the Technical Reviewer
  4. Acknowledgments
  5. Introduction
    1. Who This Book Is For
    2. How This Book Is Structured
    3. Downloading the Code
    4. Contacting the Author
  6. 1. SQL Server 2008 Express Overview and Installation
    1. 1.1. What Is SSE?
    2. 1.2. Evolution of SQL Server
    3. 1.3. Hardware Requirements
      1. 1.3.1. CPU
      2. 1.3.2. Memory
      3. 1.3.3. Hard Disk Space
      4. 1.3.4. Operating System Requirements
    4. 1.4. The Example
    5. 1.5. Installation
    6. 1.6. A Standard Installation
      1. 1.6.1. Preparing to Install
        1. 1.6.1.1. Choosing the Features to Install
        2. 1.6.1.2. Naming the Instance
        3. 1.6.1.3. Service Accounts
        4. 1.6.1.4. Authentication Mode
      2. 1.6.2. Defining the Data Directories
        1. 1.6.2.1. Collation Settings
        2. 1.6.2.2. Creating the Reporting Services Database
        3. 1.6.2.3. Error and Usage Report Settings
    7. 1.7. Security
      1. 1.7.1. Services Accounts
      2. 1.7.2. Looking at the Authentication Mode
        1. 1.7.2.1. Windows Authentication Mode
        2. 1.7.2.2. Mixed Mode
      3. 1.7.3. The sa Login
    8. 1.8. Summary
  7. 2. SQL Server Management Studio Express
    1. 2.1. A Quick Overview of SSMSE
    2. 2.2. Environment Node
      1. 2.2.1. General Node
      2. 2.2.2. Fonts and Colors Node
      3. 2.2.3. Keyboard Node
      4. 2.2.4. Help Node
    3. 2.3. Text Editor Node
      1. 2.3.1. File Extensions
      2. 2.3.2. All Languages General
      3. 2.3.3. All Languages Tabs
    4. 2.4. Query Execution Node
      1. 2.4.1. SQL Server General
      2. 2.4.2. SQL Server Advanced
      3. 2.4.3. SQL Server ANSI
    5. 2.5. Query Results Node
      1. 2.5.1. SQL Server General
      2. 2.5.2. SQL Server Results to Grid
      3. 2.5.3. SQL Server Results to Text
      4. 2.5.4. Multiserver Results
    6. 2.6. SQL Server Object Explorer
      1. 2.6.1. Commands
      2. 2.6.2. SQL Server Object Explorer Scripting
    7. 2.7. Designers
      1. 2.7.1. Table and Database Designers
    8. 2.8. Source Control
    9. 2.9. Query Editor
    10. 2.10. Summary
  8. 3. Database Design and Creation
    1. 3.1. Defining a Database
    2. 3.2. Prebuilt Databases Within SSE
      1. 3.2.1. master
      2. 3.2.2. tempdb
      3. 3.2.3. model
      4. 3.2.4. msdb
      5. 3.2.5. AdventureWorks/AdventureWorksDW
    3. 3.3. Choosing the Database System Type
      1. 3.3.1. OLTP
        1. 3.3.1.1. Backups
        2. 3.3.1.2. Indexes
      2. 3.3.2. OLAP
      3. 3.3.3. Example System Choice
    4. 3.4. Gathering the Data
    5. 3.5. Determining the Information to Store in the Database
      1. 3.5.1. Financial Products
      2. 3.5.2. Customers
      3. 3.5.3. Customer Addresses
      4. 3.5.4. Shares
      5. 3.5.5. Transactions
    6. 3.6. External and Ignored Information
    7. 3.7. Building Relationships
      1. 3.7.1. Using Keys
        1. 3.7.1.1. Primary Key
        2. 3.7.1.2. Foreign/Referencing Key
        3. 3.7.1.3. Candidate/Alternate Key
        4. 3.7.1.4. A Unique Constraint Instead of a Primary Key
      2. 3.7.2. Creating Relationships
        1. 3.7.2.1. Relationships and Referential Integrity
        2. 3.7.2.2. Types of Relationships
          1. 3.7.2.2.1. One-to-One
          2. 3.7.2.2.2. One-to-Many
          3. 3.7.2.2.3. Many-to-Many
      3. 3.7.3. More on Foreign Keys
    8. 3.8. Normalization
      1. 3.8.1. Each Entity Should Have a Unique Identifier
      2. 3.8.2. Only Store Information That Directly Relates to That Entity
      3. 3.8.3. Avoid Repeating Values or Columns
      4. 3.8.4. Normal Forms
        1. 3.8.4.1. First Normal Form
          1. 3.8.4.1.1. Customers
          2. 3.8.4.1.2. Shares
        2. 3.8.4.2. Second Normal Form
        3. 3.8.4.3. Third Normal Form
      5. 3.8.5. Denormalization
    9. 3.9. Creating the Sample Database
      1. 3.9.1. Creating a Database in SQL Server Management Studio Express
      2. 3.9.2. Dropping the Database in SQL Server Management Studio Express
      3. 3.9.3. Creating a Database in a Query Pane
    10. 3.10. Summary
  9. 4. Security and Compliance
    1. 4.1. Logins
    2. 4.2. Roles
      1. 4.2.1. Fixed Server Roles
      2. 4.2.2. Database Roles
      3. 4.2.3. Application Roles
    3. 4.3. Schemas
    4. 4.4. Before You Can Proceed with Your Solution
    5. 4.5. Declarative Management Framework
    6. 4.6. Summary
  10. 5. Defining Tables
    1. 5.1. What Is a Table?
    2. 5.2. SQL Server Data Types
      1. 5.2.1. Table Data Types
        1. 5.2.1.1. char
        2. 5.2.1.2. nchar
        3. 5.2.1.3. varchar
        4. 5.2.1.4. nvarchar
        5. 5.2.1.5. text
        6. 5.2.1.6. ntext
        7. 5.2.1.7. image
        8. 5.2.1.8. int
        9. 5.2.1.9. bigint
        10. 5.2.1.10. smallint
        11. 5.2.1.11. tinyint
        12. 5.2.1.12. decimal/numeric
        13. 5.2.1.13. float
        14. 5.2.1.14. real
        15. 5.2.1.15. money
        16. 5.2.1.16. smallmoney
        17. 5.2.1.17. date
        18. 5.2.1.18. datetime
        19. 5.2.1.19. datetime2
        20. 5.2.1.20. smalldatetime
        21. 5.2.1.21. datetimeoffset
        22. 5.2.1.22. time
        23. 5.2.1.23. hierarchyid
        24. 5.2.1.24. Geometry
        25. 5.2.1.25. Geography
        26. 5.2.1.26. rowversion
        27. 5.2.1.27. uniqueidentifier
        28. 5.2.1.28. binary
        29. 5.2.1.29. varbinary
        30. 5.2.1.30. bit
        31. 5.2.1.31. xml
      2. 5.2.2. Program Data Types
        1. 5.2.2.1. cursor
        2. 5.2.2.2. table
        3. 5.2.2.3. sql_variant
    3. 5.3. Columns As More Than Simple Data Repositories
      1. 5.3.1. Default Values
      2. 5.3.2. Generating IDENTITY Values
      3. 5.3.3. The Use of NULL Values
    4. 5.4. Creating a Table in SQL Server Management Studio Express
    5. 5.5. Creating a Table Through Query Editor
    6. 5.6. Creating a Table Using a Template
    7. 5.7. Creating and Altering a Template
    8. 5.8. Altering a Table
    9. 5.9. Creating the Remaining Tables
    10. 5.10. Setting a Primary Key
    11. 5.11. Creating a Relationship
      1. 5.11.1. Check Existing Data on Creation
      2. 5.11.2. Enforce Foreign Key Constraints
      3. 5.11.3. Choosing Delete and Update Rules
    12. 5.12. Building a Relationship via T-SQL
    13. 5.13. Summary
  11. 6. Creating Indexes and Database Diagramming
    1. 6.1. What Is an Index?
      1. 6.1.1. Types of Indexes
        1. 6.1.1.1. Clustered
        2. 6.1.1.2. Nonclustered
        3. 6.1.1.3. Primary and Secondary XML
      2. 6.1.2. Uniqueness
    2. 6.2. Determining What Makes a Good Index
      1. 6.2.1. Using Low-Maintenance Columns
      2. 6.2.2. Primary and Foreign Keys
      3. 6.2.3. Finding Specific Records
      4. 6.2.4. Using Covering Indexes
      5. 6.2.5. Looking for a Range of Information
      6. 6.2.6. Keeping the Data in Order
    3. 6.3. Determining What Makes a Bad Index
      1. 6.3.1. Using Unsuitable Columns
      2. 6.3.2. Choosing Unsuitable Data
      3. 6.3.3. Including Too Many Columns
      4. 6.3.4. Including Too Few Records in the Table
    4. 6.4. Reviewing Your Indexes for Performance
    5. 6.5. Creating an Index
      1. 6.5.1. Creating an Index with the Table Designer
      2. 6.5.2. Indexes and Statistics
    6. 6.6. The CREATE INDEX Syntax
      1. 6.6.1. IGNORE_DUP_KEY
      2. 6.6.2. DROP_EXISTING
      3. 6.6.3. Creating an Index in Query Editor: Template
      4. 6.6.4. Creating an Index in Query Editor: SQL Code
    7. 6.7. Dropping an Index
    8. 6.8. Altering an Index in Query Editor
    9. 6.9. When an Index Does Not Exist
    10. 6.10. Diagramming the Database
      1. 6.10.1. Database Diagramming Basics
    11. 6.11. The SQL Server Express Database Diagram Tool
      1. 6.11.1. The Default Database Diagram
      2. 6.11.2. The Database Diagram Toolbar
    12. 6.12. Summary
  12. 7. Database Backup and Recovery
    1. 7.1. Transaction Logs
    2. 7.2. Backup Strategies
    3. 7.3. When Problems May Occur
    4. 7.4. Taking a Database Offline
    5. 7.5. Backing Up the Data
      1. 7.5.1. Backing Up the Database Using T-SQL
      2. 7.5.2. Transaction Log Backup Using T-SQL
    6. 7.6. Restoring a Database
      1. 7.6.1. Restoring Using SQL Server Management Studio Express
      2. 7.6.2. Restoring Using T-SQL
    7. 7.7. Detaching and Attaching a Database
      1. 7.7.1. Detaching and Attaching Using SQL Server Management Studio Express
      2. 7.7.2. Detaching and Attaching Using T-SQL
    8. 7.8. Producing SQL Script for the Database
    9. 7.9. Maintaining Your Database
    10. 7.10. Summary
  13. 8. Inserting Data and Transactions
    1. 8.1. The T-SQL INSERT Statement Syntax
    2. 8.2. INSERT SQL Statement
      1. 8.2.1. Default Values
      2. 8.2.2. NULL Values
    3. 8.3. DBCC CHECKIDENT
    4. 8.4. Column Constraints
    5. 8.5. Inserting Several Rows at Once
    6. 8.6. Transactions
      1. 8.6.1. BEGIN TRAN
      2. 8.6.2. COMMIT TRAN
      3. 8.6.3. ROLLBACK TRAN
      4. 8.6.4. Locking Data
      5. 8.6.5. Nested Transactions
    7. 8.7. Inserting Data via Transactions
    8. 8.8. Summary
  14. 9. Selecting, Updating, and Deleting Data
    1. 9.1. Retrieving Data
    2. 9.2. Using SSMSE to Retrieve Data
    3. 9.3. The SELECT Statement
    4. 9.4. Naming the Columns
    5. 9.5. First Searches
    6. 9.6. Varying the Output Display
    7. 9.7. Limiting a Search: The Use of WHERE
      1. 9.7.1. SET ROWCOUNT n
      2. 9.7.2. TOP n
      3. 9.7.3. TOP n PERCENT
    8. 9.8. String Functions
    9. 9.9. Order! Order!
    10. 9.10. The LIKE Operator
    11. 9.11. Creating Data: SELECT INTO
    12. 9.12. Who Can Add, Delete, and Select Data?
    13. 9.13. Updating Data
      1. 9.13.1. The UPDATE Statement
      2. 9.13.2. Updating Data Within Query Editor
    14. 9.14. Using Transactions
    15. 9.15. Deleting Data
      1. 9.15.1. DELETE Syntax
      2. 9.15.2. Using the DELETE Statement
    16. 9.16. Truncating a Table
    17. 9.17. Dropping a Table
    18. 9.18. Summary
  15. 10. Building a View
    1. 10.1. Why a View?
    2. 10.2. Using Views for Security
    3. 10.3. Encrypting View Definitions
    4. 10.4. Creating a View Using SSMSE
    5. 10.5. Creating a View Using a View
    6. 10.6. Creating a View Using CREATE VIEW Syntax
    7. 10.7. Creating a View Using a Query Editor Pane
    8. 10.8. Creating a View Using SCHEMABINDING
    9. 10.9. Indexing a View
    10. 10.10. Summary
  16. 11. Stored Procedures and Functions
    1. 11.1. What Is a Stored Procedure?
    2. 11.2. CREATE PROCEDURE Syntax
    3. 11.3. Returning a Set of Rows
    4. 11.4. Creating a Stored Procedure: SSMSE
    5. 11.5. Different Methods of Execution
      1. 11.5.1. No EXEC
      2. 11.5.2. With EXEC
    6. 11.6. Using RETURN
    7. 11.7. Controlling the Flow
      1. 11.7.1. IF...ELSE
      2. 11.7.2. BEGIN...END
      3. 11.7.3. WHILE...BREAK Statement
      4. 11.7.4. CASE Statement
    8. 11.8. Bringing It All Together
    9. 11.9. User-Defined Functions
      1. 11.9.1. Scalar Functions
      2. 11.9.2. Table-Valued Functions
      3. 11.9.3. Considerations When Building Functions
    10. 11.10. Summary
  17. 12. T-SQL Essentials
    1. 12.1. Using More Than One Table
    2. 12.2. Variables
    3. 12.3. Temporary Tables
    4. 12.4. Aggregations
      1. 12.4.1. COUNT/COUNT_BIG
      2. 12.4.2. SUM
      3. 12.4.3. MAX/MIN
      4. 12.4.4. AVG
    5. 12.5. Grouping Data
    6. 12.6. HAVING
    7. 12.7. Distinct Values
    8. 12.8. Functions
      1. 12.8.1. Date and Time
        1. 12.8.1.1. DATEADD()
        2. 12.8.1.2. DATEDIFF()
        3. 12.8.1.3. DATENAME()
        4. 12.8.1.4. DATEPART()
        5. 12.8.1.5. GETDATE()/SYSDATETIME
      2. 12.8.2. String
        1. 12.8.2.1. ASCII()
        2. 12.8.2.2. CHAR()
        3. 12.8.2.3. LEFT()
        4. 12.8.2.4. LOWER()
        5. 12.8.2.5. LTRIM()
        6. 12.8.2.6. RIGHT()
        7. 12.8.2.7. RTRIM()
        8. 12.8.2.8. STR()
        9. 12.8.2.9. SUBSTRING()
        10. 12.8.2.10. UPPER()
      3. 12.8.3. System Functions
        1. 12.8.3.1. CASE WHEN...THEN...ELSE...END
        2. 12.8.3.2. CAST()/CONVERT()
        3. 12.8.3.3. ISDATE()
        4. 12.8.3.4. ISNULL()
        5. 12.8.3.5. ISNUMERIC()
    9. 12.9. RAISERROR
    10. 12.10. Error Handling
    11. 12.11. @@ERROR
    12. 12.12. TRY...CATCH
    13. 12.13. Summary
  18. 13. Advanced T-SQL
    1. 13.1. Subqueries
      1. 13.1.1. IN
      2. 13.1.2. EXISTS
      3. 13.1.3. Tidying Up the Loose End
    2. 13.2. The APPLY Operator
      1. 13.2.1. CROSS APPLY
      2. 13.2.2. OUTER APPLY
    3. 13.3. Common Table Expressions
      1. 13.3.1. Recursive CTE
    4. 13.4. Pivoting Data
      1. 13.4.1. PIVOT
      2. 13.4.2. UNPIVOT
    5. 13.5. Ranking Functions
      1. 13.5.1. ROW_NUMBER
      2. 13.5.2. RANK
      3. 13.5.3. DENSE_RANK
      4. 13.5.4. NTILE
    6. 13.6. Query Plans
    7. 13.7. Missing Indexes
    8. 13.8. PowerShell Within SQL Server
    9. 13.9. Summary
  19. 14. Triggers
    1. 14.1. What Is a Trigger?
    2. 14.2. The DML Trigger
    3. 14.3. CREATE TRIGGER Syntax for DML Triggers
    4. 14.4. Why Not Use a Constraint?
    5. 14.5. Deleted and Inserted Logical Tables
    6. 14.6. Creating a DML Trigger
    7. 14.7. Checking Specific Columns
      1. 14.7.1. Using UPDATE()
      2. 14.7.2. Using COLUMNS_UPDATED()
    8. 14.8. DDL Triggers
      1. 14.8.1. DDL Database-Level Events
        1. 14.8.1.1. Database-Scoped Events
        2. 14.8.1.2. DDL Statements with Server Scope
    9. 14.9. Dropping a DDL Trigger
    10. 14.10. EVENTDATA()
    11. 14.11. Summary
  20. 15. SQL Server 2008 Reporting Services
    1. 15.1. Reporting Services Architecture
    2. 15.2. Configuring Reporting Services
    3. 15.3. Building Your First Report Using Report Designer
    4. 15.4. Summary