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