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