Pro SQL Server Relational Database Design and Implementation, Fifth Edition

Pro SQL Server Relational Database Design and Implementation, Fifth Edition

by Louis Davidson, Jessica Moss, Foreword by Kalen Delaney
Released December 2016
Publisher(s): Apress
ISBN: 9781484219720

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

Learn effective and scalable database design techniques in a SQL Server 2016 and higher environment. This book is revised to cover in-memory online transaction processing, temporal data storage, row-level security, durability enhancements, and other design-related features that are new or changed in SQL Server 2016.

Designing an effective and scalable database using SQL Server is a task requiring skills that have been around for forty years coupled with technology that is constantly changing. Pro SQL Server Relational Database Design and Implementation covers everything from design logic that business users will understand, all the way to the physical implementation of design in a SQL Server database. Grounded in best practices and a solid understanding of the underlying theory, Louis Davidson shows how to "get it right" in SQL Server database design and lay a solid groundwork for the future use of valuable business data.

The pace of change in relational database management systems has been tremendous these past few years. Whereas in the past it was enough to think about optimizing data residing on spinning hard drives, today one also must consider solid-state storage as well as data that are constantly held in memory and never written to disk at all except as a backup. Furthermore, there is a trend toward hybrid cloud and on-premise database configurations as well a move toward preconfigured appliances. Pro SQL Server Relational Database Design and Implementation guides in the understanding of these massive changes and in their application toward sound database design.

  • Gives a solid foundation in best practices and relational theory
  • Covers the latest implementation features in SQL Server 2016
  • Helps you master in-memory OLTP and use it effectively
  • Takes you from conceptual design to an effective, physical implementation

What You Will Learn

  • Develop conceptual models of client data using interviews and client documentation
  • Recognize and apply common database design patterns
  • Normalize data models to enhance scalability and the long term use of valuable data
  • Translate conceptual models into high–performing SQL Server databases
  • Secure and protect data integrity as part of meeting regulatory requirements
  • Create effective indexing to speed query performance

Who This Book Is For

Programmers and database administrators of all types who want to use SQL Server to store data. The book is especially useful to those wanting to learn the very latest design features in SQL Server 2016, features that include an improved approach to in-memory OLTP, durability enhancements, temporal data support, and more. Chapters on fundamental concepts, the language of database modeling, SQL implementation, and of course, the normalization process, lay a solid groundwork for readers who are just entering the field of database design. More advanced chapters serve the seasoned veteran by tackling the very latest in physical implementation features that SQL Server has to offer. The book has been carefully revised to cover all the design-related features that are new in SQL Server 2016.

Table of contents

  1. Cover
  2. Title
  3. Copyright
  4. Dedication
  5. Contents at a Glance
  6. Contents
  7. Foreword
  8. About the Authors
  9. About the Technical Reviewers
  10. Acknowledgments
  11. Introduction
  12. Chapter 1: The Fundamentals
    1. Taking a Brief Jaunt Through History
      1. Introducing Codd’s Rules for an RDBMS
      2. Nodding at SQL Standards
    2. Recognizing Relational Data Structures
      1. Introducing Databases and Schemas
      2. Understanding Tables, Rows, and Columns
      3. Working with Missing Values (NULLs)
      4. Defining Domains
      5. Storing Metadata
      6. Defining Uniqueness Constraints (Keys)
    3. Understanding Relationships
      1. Working with Binary Relationships
      2. Working with Nonbinary Relationships
    4. Understanding Functional Dependencies
      1. Understanding Functional Dependencies
      2. Finding Determinants
    5. Relational Programming
    6. Outlining the Database-Specific Project Phases
      1. Conceptual Phase
      2. Logical Phase
      3. Physical
      4. Engine Adjustment Phase
    7. Summary
  13. Chapter 2: Introduction to Requirements
    1. Documenting Requirements
    2. Gathering Requirements
      1. Interviewing Clients
      2. Asking the Right Questions
    3. Utilizing Other Types of Documentation
      1. Early Project Documentation
      2. Contracts or Client Work Orders
      3. Level of Service Agreement
      4. Audit Plans
      5. Prototypes
    4. Following Best Practices
    5. Summary
  14. Chapter 3: The Language of Data Modeling
    1. Introducing Data Modeling
    2. Entities
    3. Attributes
      1. Primary Keys
      2. Alternate Keys
      3. Foreign Keys
      4. Domains
    4. Relationships
      1. Identifying Relationships
      2. Nonidentifying Relationships
      3. Role Names
      4. Relationship Cardinality
      5. Verb Phrases (Relationship Names)
    5. Descriptive Information
    6. Alternative Modeling Methodologies
      1. Information Engineering
      2. Chen ERD
  15. Chapter 4: Conceptual and Logical Data Model Production
    1. Example Scenario
    2. Building the Conceptual Model
      1. Identifying Entities
      2. Identifying Relationships Between Entities
      3. Testing the Conceptual Model
    3. Building the Logical Model
      1. Identifying Attributes and Domains
      2. Identifying Business Rules
      3. Identifying Fundamental Processes
      4. Finalizing the Logical Model
    4. Best Practices
    5. Summary
  16. Chapter 5: Normalization
    1. The Process of Normalization
    2. Table and Column Shape
      1. All Columns Must Be Atomic
      2. All Rows Must Contain the Same Number of Values
      3. All Rows Must Be Different
      4. Clues That an Existing Design Is Not in First Normal Form
    3. Relationships Between Columns
      1. BCNF Defined
      2. Partial Key Dependency
      3. Entire Key Dependency
      4. Surrogate Keys Effect on Dependency
      5. Dependency Between Rows
      6. Clues That Your Database Is Not in BCNF
      7. Positional Meaning
    4. Tables with Multiple Meanings
      1. Fourth Normal Form: Independent Multivalued Dependencies
      2. Fifth Normal Form
    5. Denormalization
    6. Best Practices
    7. Summary
    8. The Story of the Book So Far
  17. Chapter 6: Physical Model Implementation Case Study
    1. Choosing a Physical Model for Your Tables
    2. Choosing Names
      1. Table Naming
      2. Naming Columns
      3. Model Name Adjustments
    3. Choosing Key Implementation
      1. Primary Key
      2. Alternate Keys
    4. Determining Domain Implementation
      1. Enforce Domain in the Column, or With a Table?
      2. Choosing the Datatype
      3. Setting Nullability
      4. Choosing the Collation
    5. Setting Up Schemas
    6. Adding Implementation Columns
    7. Using DDL to Create the Database
      1. Creating the Basic Table Structures
      2. Adding Uniqueness Constraints
      3. Building DEFAULT Constraints
      4. Adding Relationships (Foreign Keys)
      5. Adding Basic CHECK Constraints
      6. Triggers to Maintain Automatic Values
      7. Documenting Your Database
      8. Viewing the Basic System Metadata
    8. Unit Testing Your Structures
    9. Best Practices
    10. Deployment Lifecycle
    11. Summary
  18. Chapter 7: Expanding Data Protection with Check Constraints and Triggers
    1. Check Constraints
      1. CHECK Constraints Based on Simple Expressions
      2. CHECK Constraints Using Functions
      3. Enhancing Errors Caused by Constraints
    2. DML Triggers
      1. AFTER Triggers
      2. Relationships That Span Databases
      3. INSTEAD OF Triggers
    3. Dealing with Trigger and Constraint Errors
    4. Best Practices
    5. Summary
  19. Chapter 8: Patterns and Anti-Patterns
    1. Desirable Patterns
      1. Uniqueness
      2. Data-Driven Design
      3. Historical/Temporal Data
      4. Hierarchies
      5. Images, Documents, and Other Files, Oh My!
      6. Generalization
      7. Storing User-Specified Data
    2. Anti-Patterns
      1. Undecipherable Data
      2. One-Size-Fits-All Key Domain
      3. Generic Key References
      4. Overusing Unstructured Data
    3. Summary
  20. Chapter 9: Database Security and Security Patterns
    1. Database Access
      1. Guidelines for Host Server Security Configuration
      2. Principals and Securables
      3. Connecting to the Server
      4. Impersonation
    2. Database Object Securables
      1. Grantable Permissions
      2. Roles
      3. Schemas
    3. Row-Level Security
      1. Using Specific-Purpose Views to Provide Row-Level Security
      2. Using the Row-Level Security Feature
      3. Using Data-Driven Row-Level Security
    4. Controlling Access to Data via T-SQL–Coded Objects
      1. Stored Procedures and Scalar Functions
      2. Impersonation Within Objects
      3. Views and Table-Valued Functions
    5. Crossing Database Lines
      1. Using Cross-Database Chaining
      2. Using Impersonation to Cross Database Lines
      3. Using a Certificate-Based Trust
      4. Different Server (Distributed Queries)
    6. Obfuscating Data
      1. Encrypting Data
      2. Using Dynamic Data Masking to Hide Data from Users
    7. Auditing SQL Server Use
      1. Defining an Audit Specification
      2. Viewing the Audit Configuration
    8. Best Practices
    9. Summary
  21. Chapter 10: Index Structures and Application
    1. Indexing Overview
    2. Basic Index Structure
    3. On-Disk Indexes
      1. Clustered Indexes
      2. Nonclustered Indexes
      3. Structure
      4. Using the Nonclustered Index
      5. Using Unique Indexes
    4. Memory-Optimized Indexes
      1. In-Memory OLTP Tables
      2. Columnstore Indexes
    5. Common OLTP Patterns of Index Usage
      1. When to Cluster on Something Other Than the PRIMARY KEY
      2. Indexing Foreign Keys
      3. Indexed Views
      4. Compression
      5. Partitioning
    6. Indexing Dynamic Management View Queries
      1. Missing Indexes
      2. On-Disk Index Utilization Statistics
      3. Fragmentation
      4. In-Memory OLTP Index Stats
    7. Best Practices
    8. Summary
  22. Chapter 11: Matters of Concurrency
    1. OS and Hardware Concerns
    2. Transactions
      1. Transaction Overview
      2. Transaction Syntax
    3. SQL Server Concurrency Methods
      1. Isolation Levels
      2. Pessimistic Concurrency Enforcement
      3. Optimistic Concurrency Enforcement
    4. Coding for Asynchronous Contention
      1. Row-Based Change Detection
      2. Coding for Logical Unit of Work Change Detection
    5. Best Practices
    6. Summary
  23. Chapter 12: Reusable Standard Database Components
    1. Numbers Table
      1. Determining the Contents of a String
      2. Finding Gaps in a Sequence of Numbers
      3. Separating Comma-Delimited Items
    2. Calendar Table
    3. Utility Objects
      1. Monitoring Objects
      2. Extended DDL Utilities
    4. Logging Objects
    5. Other Possibilities…
    6. Summary
  24. Chapter 13: Architecting Your System
    1. Choosing the Engine for Your Needs
    2. Ad Hoc SQL
      1. Advantages
      2. Pitfalls
    3. Stored Procedures
      1. Advantages
      2. Pitfalls
    4. Stored Procedure or Ad Hoc?
    5. T-SQL and the CLR
    6. Best Practices
    7. Summary
  25. Chapter 14: Reporting Design
    1. Reporting Styles
      1. Analytical Reporting
      2. Aggregation Reporting
      3. Operational Reporting
    2. Requirements-Gathering Process
    3. Dimensional Modeling for Analytical Reporting
      1. Dimensions
      2. Facts
    4. Analytical Querying
      1. Queries
      2. Indexing
    5. Summary Modeling for Aggregation Reporting
      1. Initial Summary Table
      2. Additional Summary Tables
    6. Aggregation Querying
      1. Queries
      2. Indexing
    7. Modeling (or Lack Thereof) for Operational Reporting
      1. Sample Operational Data Model
      2. In-Memory OLTP
    8. Operational Querying
      1. Queries
      2. Indexing
    9. Summary
  26. Appendix A: Scalar Datatype Reference
    1. Precise Numeric Data
      1. Integer Values
      2. Decimal Values
      3. Approximate Numeric Data
    2. Date and Time Data
      1. date
      2. time [(precision)]
      3. datetime2 [(precision)]
      4. datetimeoffset [(precision)]
      5. smalldatetime
      6. datetime
      7. Discussion on All Date Types
    3. Character Strings
      1. char[(length)]
      2. varchar[(length)]
      3. varchar(max)
      4. text
      5. Unicode Character Strings: nchar, nvarchar, nvarchar(max), ntext
    4. Binary Data
      1. binary[(length)]
      2. varbinary[(length)]
      3. varbinary(max)
      4. image
    5. Other Datatypes
      1. bit
      2. rowversion (aka timestamp)
      3. uniqueidentifier
      4. cursor
      5. table
      6. sql_variant
    6. Not Simply Scalar Datatypes
  27. Index

Product information

  • Title: Pro SQL Server Relational Database Design and Implementation, Fifth Edition
  • Author(s): Louis Davidson, Jessica Moss, Foreword by Kalen Delaney
  • Release date: December 2016
  • Publisher(s): Apress
  • ISBN: 9781484219720