Microsoft SQL SERVER 2005数据库基础 由入门到精通 英文PDF电子书下载

Part Ⅰ Introduction to Database Development with Microsoft SQL Server 2005 3

1 Introducing Database Development with Microsoft SQL Server 2005 3

The Process of Storing and Managing Data 3

Introducing Database Systems 4

Understanding the Requirements of a Database System 5

Defining the Architecture of a Database Application 7

Using a Monolithic Application with Data Embedded in the Application Code 7

Using a Monolithic Application with Data Stored in an External File 8

Using a Database Application with Data Managed by a Database Server 9

Using an Application with a Generic Data Access Layer 9

Using an Application with Separated Presentation, Business, and Data Access Layers 10

Using a Complex Application with Multiple Options for Each Layer 12

Conclusion 13

2 Installing and Setting Up Your Microsoft SQL Server 2005 Development Environment 15

Using a Virtual PC 15

Operating System Requirements for Development of SQL Server Database Applications 15

Recommended Productivity Tools 16

Using Performance Monitor 16

Using Network Monitor 16

Using Upgrade Advisor 17

Recommended Development Tools 17

Microsoft SQL Server 2005 Editions 18

Microsoft SQL Server Express Edition 18

Microsoft SQL Server Workgroup Edition 18

Microsoft SQL Server Standard Edition 18

Microsoft SQL Server Enterprise Edition 18

Installing SQL Server 2005 19

Using the Surface Area Configuration Tool 27

Conclusion 29

Chapter 2 Quick Reference 29

3 Reviewing Microsoft SQL Server 2005 Management Tools 31

Introduction 31

Using SQL Server Books Online 31

Using SQL Server Configuration Manager 32

Using the SQL Server 2005 Services Node 33

Using the SQL Server 2005 Network Configuration Node 34

Using the SQL Native Client Configuration Node 37

Using SQL Server Surface Area Configuration 39

Using Surface Area Configuration For Services And Connections 40

Using Surface Area Configuration For Features 41

Sac Utility 41

Using SQL Server Management Studio 42

Administering Servers with SQL Server Management Studio 42

Using Object Explorer in SQL Server Management Studio 47

Writing Scripts in SQL Server Management Studio 53

Writing Solutions and Projects in SQL Server Management Studio 56

Using SQL Server Management Studio Templates 57

Using SQL Server Profiler 59

Using the SQL Server Profiler Tool and Creating a Trace 59

When Should You Use SQL Server Profiler? 62

Accessing Event Types 62

Running SQL Server Profiler and Performance Monitor Together 63

Viewing Deadlocks in SQL Server Profiler 64

Using Database Engine Tuning Advisor 65

Analyzing Database Engine Tuning Advisor 65

Working with Database Engine Tuning Advisor 65

Managing Database Engine Tuning Advisor Sessions 68

Using SQLCmd 68

Working with the SQLCmd Utility 69

Executing Script Files 69

Conclusion 70

Chapter 3 Quick Reference 70

Part Ⅱ How to Create a Microsoft SQL Server 2005 Database 73

4 Gathering and Understanding Business Requirements before Creating Database Objects 73

Understanding Business Processes and User Interaction Requirements 73

Defining the Business Problem 74

Capturing Requirements 74

Writing Requirements 76

Understanding Business Data and Its Lifetime 76

Architecture and Operational Requirements 77

Availability 77

Predicting the Volume of Information to Store and Manage and Predicting Database Utilization 83

Using Transaction Cost Analysis 83

Conclusion 90

Chapter 4 Quick Reference 90

Designing a Database to Solve Business Needs 91

Designing a Database Conceptually 91

Validating Business Requirements through Conceptual Models 92

Approving the Model 98

Designing a Database Logically to Leverage the Relational Engine 98

Creating Columns to Capture Object Attributes 99

Validating the Data 108

Physically Creating a Database 111

Selecting an Appropriate Storage Design for a Database 111

Creating a Database 112

Designing Database Schemas to Logically Group Database Objects 114

Creating Tables to Implement a Design 115

Conclusion 117

Chapter 5 Quick Reference 117

Part Ⅲ How to Query Data from Microsoft SQL Server 2005 121

6 Reading Microsoft SQL Server 2005 Data from Client Applications 121

Introducing Microsoft Data Access Components 121

Avoiding Deprecated MDAC Components 121

Outlining the MDAC Architecture 122

Understanding Open Database Connectivity (ODBC) 123

Understanding OLE DB and ADO 126

Understanding ADO.NET 129

Introducing the .NET SQL Server Data Provider 131

Using the SqlConnection Class 131

Using the SqlCommand Class 132

Using the SqlDataReader Class 133

Using the SqlDataAdapter Class 133

Using the TableAdapter Class 136

Introducing SQL Native Client 138

Using the XML Datatype 139

Using Multiple Active Result Sets (MARS) 139

Using Query Notification 140

Conclusion 142

Chapter 6 Quick Reference 142

7 Selecting the Data You Need 143

Selecting Data from a Single Table 143

Using AND and OR Operators 145

Comparing NULL Values 146

Using the CASE Statement 147

Using Search Arguments 148

Selecting Data from Multiple Tables 149

Using Aliases 150

Using the INNER JOIN Syntax 151

Using More than Two Tables 151

Using LEFT JOIN 152

Using RIGHT JOIN 154

Using FULL JOIN 155

Reading Single Values 155

Using System-Supplied Scalar Functions 156

Designing and Using Scalar UDFs 163

Designing and Using Stored Procedures to Retrieve Scalar Values 164

Reading Relational and XML Data 165

Viewing XML Results in SQL Server Management Studio 166

Converting Relational Data to XML Format 167

Converting XML Data to Relational Format 172

Querying XML and Relational Data Using XQuery 174

Sorting Data 176

Conclusion 178

Chapter 7 Quick Reference 178

8 Creating Views to Encapsulate Queries 179

Selecting Data from a Single View 179

Creating a View 179

Modifying a View Definition 183

Updating Data through a View 184

Partitioned Views 185

Mixing Data from Views and Tables 186

Working with Views within Client Applications 186

Conclusion 188

Chapter 8 Quick Reference 188

9 Retrieving Data Using Programmable Objects 191

Introduction 191

Working through a Simple Problem 192

Understanding Scalar UDFs 194

Retrieving Result Sets 197

Updating Data 203

Common Language Runtime UDFs and Procedures 207

Working with Statistical Calculations 208

Working with CLR Stored Procedures 216

Conclusion 217

Chapter 9 Quick Reference 218

Part Ⅳ How to Modify Data in Microsoft SQL Server 2005 221

10 Inserting Data in Microsoft SQL Server 2005 221

Using the INSERT Statement 221

Creating an INSERT Sentence with SQL Server Management Studio 221

Using Special Values for Row Insertion 223

Using Other Forms of the INSERT Statement 224

Inserting Data through Views 225

Using the WITH Clause 226

Using INSTEAD OF INSERT Triggers on Views 227

Importing Data into SQL Server 2005 228

Using the BCP Utility 228

Using the BULK INSERT Command 230

Using Integration Services to Insert Data 231

Creating Your Own Package 236

Encapsulating Insert Operations in Stored Procedures 241

Triggering Actions Automatically when Inserting Data 243

Dealing with Errors when Inserting Data 245

Managing the Error 247

Inserting Data from ADO.NET 249

Conclusion 250

Chapter 10 Quick Reference 250

11 Deleting Data from Microsoft SQL Server 2005 253

Using the DELETE Statement 253

Creating a DELETE Sentence with SQL Server Management Studio 253

Defining the WHERE Condition 254

Using Relationships to Perform Deletions 255

Deleting Data through Views 256

Using INSTEAD OF DELETE Triggers on Views 257

Encapsulating Delete Operations in Stored Procedures 258

Implementing Pessimistic Concurrency for Delete Operations 259

Implementing Optimistic Concurrency for Delete Operations 261

Triggering Actions Automatically when Deleting Data 265

Dealing with Errors 266

Data Does Not Exist 266

Data to Be Deleted Is Related to Other Tables 267

Other Errors 268

Deleting Data from ADONET 268

Managing Errors during the Delete Process 270

Conclusion 271

Chapter 11 Quick Reference 272

12 Updating Data from Microsoft SQL Server 2005 273

Using the UPDATE Statement 273

Creating an UPDATE Sentence with SQL Server Management Studio 273

Defining the WHERE Condition 274

Assigning New Values to Columns 274

Using Arithmetic to Update Information 275

Changing the Content of a Large(MAX)Column 276

Changing the Content of a Large(BLOB)Column 276

Updating Data through Views 276

Using INSTEAD OF UPDATE Triggers on Views 277

Encapsulating Update Operations in Stored Procedures 279

Implementing Pessimistic and Optimistic Concurrency for Update Operations 281

Triggering Actions Automatically when Updating Data 282

Dealing with Errors 283

Assigned Value Is Higher than the Field Datatype Limit 284

Column Value Violates Referential Integrity 284

Updating Data from ADONET 284

Using Datasets and Table Adapters 285

Using Stored Procedures with Table Adapters 289

Conclusion 296

Chapter 12 Quick Reference 296

Index 297