You are previewing The Red Gate Guide to SQL Server Team-based Development.
O'Reilly logo
The Red Gate Guide to SQL Server Team-based Development

Book Description

This book shows how to use of mixture of home-grown scripts, native SQL Server tools, and tools from the Red Gate SQL Toolbelt, to successfully develop database applications in a team environment, and make database development as similar as possible to "normal" development.

Table of Contents

  1. Copyright
  2. About the Authors
    1. Phil Factor
    2. Grant Fritchey
    3. Alex Kuznetsov
    4. Mladen Prajdić
    5. Peter Larsson (technical reviewer)
    6. Roger Hart (additional material)
    7. Allen White (additional material)
  3. Introduction
    1. Code examples
  4. 1. Writing Readable SQL
    1. Why Adopt a Standard?
    2. Object Naming Conventions
      1. Tibbling
      2. Pluralizing
      3. Abbreviating (or abrvtng)
      4. [Escaping]
      5. Restricting
      6. A guide to sensible object names
    3. Code Layout
      1. Line-breaks
      2. Indenting
      3. Formatting lists
      4. Punctuation
      5. Capitalization
      6. Getting off the fence…
    4. Summary
  5. 2. Documenting your Database
    1. Why Bother to Document Databases?
    2. Where the Documentation Should Be Held
    3. What Should Be In the Documentation?
    4. How Should the Documentation Be Published?
    5. What Standards Exist?
      1. XMLDOCS
      2. YAML and JSON
    6. How Headers are Stored in the Database
      1. Extended properties
    7. Get Information Out of Headers
    8. Publishing the Documentation
    9. Summary
  6. 3. Change Management and Source Control
    1. The Challenges of Team-based Development
    2. Environments
      1. Development environments
        1. Sandbox (local server)
        2. Common server
      2. Testing, staging and production environments
        1. Integration server
        2. Continuous integration server
        3. Quality Assurance
        4. Performance testing
        5. User Acceptance Testing
        6. Staging
        7. Production
    3. Source Control
      1. Source control features
        1. Versioning databases
        2. Optimistic versus pessimistic source control
      2. Source control systems
      3. Database objects in source control
      4. Getting your database objects into source control
        1. Scripting a database using PowerShell/SMO
        2. Red Gate SQL Source Control
        3. Red Gate SQL Compare
        4. Visual Studio Team System Database Edition
        5. SSMS
      5. Managing data in source control
        1. Authoritative source database
        2. Data movement tools
          1. Red Gate SQL Data Compare
          2. Visual Studio data comparison
    4. Summary
  7. 4. Managing Deployments
    1. Deployment Schemes
      1. Visual Studio 2010 Premium tools
        1. Sandbox
        2. Shared development
        3. Staging and production
      2. Red Gate SQL Source Control
        1. Sandbox
        2. Shared development environment
        3. Staging and production
    2. Automating Builds for Continuous Integration
      1. What is continuous integration?
      2. Example: deploying to test
      3. Creating test data
      4. Automation with MSBuild, NAnt, and PowerShell
        1. Using MSBuild
        2. Using NAnt
        3. Using PowerShell
      5. Automation with CruiseControl
    3. Summary
  8. 5. Testing Databases
    1. Why Test a Database?
    2. Essential Types of Database Testing
      1. Black-box and white-box testing
      2. Unit testing
      3. Integration and acceptance testing
      4. Stress testing databases
      5. Error testing
    3. Essentials for Successful Database Testing
      1. The right attitude
      2. A test lab
      3. Source control
      4. Database schema change management
      5. Semi- or fully-automated deployment
      6. A testing tool
      7. A data generation tool
    4. How to Test Databases
      1. Reverting the database state
        1. Use transactions
        2. Use the NDbUnit framework
        3. Do a backup/restore
        4. Use database snapshots
      2. Simplifying unit tests
      3. Testing existing databases
    5. Unit Testing Examples: Testing Data and Schema Validity
      1. Testing the database interface
      2. Testing the database schema
      3. Testing tables, views, and UDFs
      4. Testing stored procedures
      5. Testing authentication and authorization
    6. Summary
  9. 6. Reusing T-SQL Code
    1. The Dangers of Copy-and-Paste
    2. How Reusing Code Improves its Robustness
    3. Wrapping SELECTs in Views
    4. Reusing Parameterized Queries: Stored Procedures versus Inline UDFs
    5. Scalar UDFs and Performance
    6. Multi-Statement Table-Valued UDFs
    7. Reusing Business Logic: Stored Procedure, Trigger, Constraint or Index?
      1. Use constraints where possible
      2. Turn to triggers when constraints are not practical
      3. Unique filtered indexes (SQL Server 2008 only)
    8. Summary
  10. 7. Maintaining a Code Library
    1. Coding for Reuse
      1. Code comments
      2. Parameter naming
      3. Unit tests
    2. Storing Script Libraries
      1. Source control
      2. A single file or individual files?
    3. Tools for Creating and Managing Code Libraries
      1. SQL Server Management Studio
      2. Text editors
      3. Wikis
      4. SQL Prompt
    4. Summary
  11. 8. Exploring your Database Schema
    1. Building a Snippet Library
    2. Interrogating Information Schema and Catalog Views
    3. Searching Structural Metadata in Schema-scoped Objects within a Database
      1. Tables with no primary keys
      2. Tables with no referential constraints
      3. Tables with no indexes
      4. A one-stop view of your table structures
      5. How many of each object…
      6. Too many indexes…
      7. Seeking out troublesome triggers
      8. What objects have been recently modified?
      9. Querying the documentation in extended properties
      10. Object permissions and owners
    4. Searching All Your Databases
    5. Investigating Foreign Key Relationships
    6. Interrogating Object Dependencies
      1. Finding the closest relations
      2. Finding the dependency chain
    7. Summary
  12. 9. Searching DDL and Build Scripts
    1. Searching Within the DDL
      1. Why isn't it in SSMS?
      2. So how do you do it?
        1. Get the free tool: SQL Search
        2. Searching the entire build script
        3. The "Help" system procedures: a toe in the water
        4. Going standard: using Information_Schema
        5. Learning patience: using SMO
        6. Using the object catalog views
        7. Using System Tables (pre-SQL 2005)
    2. Using SSMS to Explore Table Metadata
      1. SSMS shortcut keys
      2. Useful shortcut queries
      3. Useful shortcut stored procedures
    3. Generating Build Scripts
    4. Summary
  13. 10. Automating CRUD
    1. First, Document Your Code
    2. Automatically Generating Stored Procedure Calls
    3. Automating the Simple Update Statement
    4. Generating Code Templates for Table-Valued Functions
    5. Automatically Generating Simple INSERT Statements
    6. Summary
  14. 11. SQL Refactoring
    1. Why Refactor SQL?
    2. Requirements for Successful SQL Refactoring
      1. A set-based mindset
      2. Consistent naming conventions
      3. Thorough testing
      4. A database abstraction layer
    3. Where to Start?
    4. SQL Refactoring in Action: Tackling Common Anti-Patterns
      1. Using functions on columns in the WHERE clause
      2. The "SELECT *" anti-pattern
        1. Client problems when reading data
        2. View refreshing problem
        3. Random I/O access when filtering on non-clustered indexes
      3. Huge, many-parameter stored procedures
      4. The "one subquery per condition" anti-pattern
      5. The "cursor is the only way" anti-pattern
      6. Using data types that are too large
      7. The "data in code" anti-pattern
    5. Summary