| Introduction |
|
xxi | |
| Assessment Test |
|
xxx | |
|
Designing a Database Solution |
|
|
1 | (46) |
|
Designing a Logical Database |
|
|
2 | (6) |
|
Understanding Normalization |
|
|
3 | (3) |
|
Understanding Denormalization |
|
|
6 | (2) |
|
Designing the Physical Entities |
|
|
8 | (23) |
|
|
|
9 | (10) |
|
|
|
19 | (9) |
|
Designing Entity Relationships |
|
|
28 | (3) |
|
|
|
31 | (1) |
|
|
|
32 | (1) |
|
|
|
33 | (11) |
|
Answers to Review Questions |
|
|
44 | (3) |
|
Designing Database Objects |
|
|
47 | (84) |
|
Designing the Objects That Define Data |
|
|
48 | (13) |
|
Creating Partitioned Tables |
|
|
48 | (5) |
|
|
|
53 | (8) |
|
Designing Objects That Retrieve Data and Extend Functionality |
|
|
61 | (22) |
|
|
|
63 | (4) |
|
|
|
67 | (4) |
|
Designing T-SQL User-Defined Functions |
|
|
71 | (6) |
|
Designing CLR User-Defined Functions |
|
|
77 | (2) |
|
Designing CLR User-Defined Aggregates |
|
|
79 | (1) |
|
Designing Stored Procedures |
|
|
80 | (3) |
|
Designing Objects That Perform Actions |
|
|
83 | (37) |
|
|
|
84 | (6) |
|
|
|
90 | (5) |
|
|
|
95 | (9) |
|
|
|
104 | (16) |
|
|
|
120 | (1) |
|
|
|
120 | (1) |
|
|
|
121 | (7) |
|
Answers to Review Questions |
|
|
128 | (3) |
|
Performance Tuning a Database Solution |
|
|
131 | (52) |
|
Establishing Performance Objectives |
|
|
132 | (7) |
|
Monitoring SQL Server Performance |
|
|
133 | (1) |
|
|
|
133 | (4) |
|
Understanding the Factors Affecting SQL Server Performance |
|
|
137 | (2) |
|
Evaluating the Tools for Monitoring Performance |
|
|
139 | (26) |
|
|
|
139 | (2) |
|
|
|
141 | (17) |
|
Choosing a Monitoring Tool |
|
|
158 | (1) |
|
Correlating a Trace with Windows Performance Log Data Using SQL Profiler |
|
|
159 | (6) |
|
Detecting and Responding to Performance Problems |
|
|
165 | (10) |
|
Troubleshooting Memory Problems |
|
|
166 | (3) |
|
Troubleshooting CPU Problems |
|
|
169 | (3) |
|
Troubleshooting I/O Bottlenecks |
|
|
172 | (1) |
|
Troubleshooting tempdb Problems |
|
|
173 | (1) |
|
Troubleshooting Poorly Running Queries |
|
|
174 | (1) |
|
|
|
175 | (1) |
|
|
|
176 | (1) |
|
|
|
177 | (4) |
|
Answers to Review Questions |
|
|
181 | (2) |
|
Securing a Database Solution |
|
|
183 | (80) |
|
Designing an Application Solution to Support Security |
|
|
184 | (24) |
|
A Brief History of SQL Server Security |
|
|
185 | (4) |
|
Securing the SQL Server Solution |
|
|
189 | (3) |
|
Designing and Implementing Application Security |
|
|
192 | (1) |
|
Designing the Database to Enable Auditing |
|
|
193 | (15) |
|
Designing Database Security |
|
|
208 | (45) |
|
Defining the Security Model |
|
|
210 | (2) |
|
Defining Login Access Requirements |
|
|
212 | (2) |
|
Defining Database Access Requirements |
|
|
214 | (14) |
|
Specifying Database Object Security Permissions |
|
|
228 | (5) |
|
Specifying Database Objects Used to Maintain Security |
|
|
233 | (4) |
|
Defining Schemas to Manage Object Ownership |
|
|
237 | (3) |
|
Designing an Execution Context Strategy |
|
|
240 | (13) |
|
|
|
253 | (1) |
|
|
|
253 | (2) |
|
|
|
255 | (5) |
|
Answers to Review Questions |
|
|
260 | (3) |
|
Designing Database Testing and Code Management Procedures |
|
|
263 | (38) |
|
Designing a Unit Test Plan for a Database |
|
|
264 | (8) |
|
Assessing Unit Test Components |
|
|
265 | (2) |
|
Developing Appropriate Performance Profiles |
|
|
267 | (1) |
|
Testing Performance Profiles |
|
|
268 | (1) |
|
Designing Tests for Query Performance |
|
|
269 | (3) |
|
Creating a Plan for Deploying a Database |
|
|
272 | (15) |
|
Selecting an Appropriate Deployment Technique |
|
|
272 | (13) |
|
Designing Database Deployment Scripts |
|
|
285 | (2) |
|
|
|
287 | (4) |
|
Understanding the Benefits of Source Control |
|
|
287 | (1) |
|
Implementing SCM Techniques |
|
|
288 | (3) |
|
|
|
291 | (1) |
|
|
|
291 | (1) |
|
|
|
292 | (6) |
|
Answers to Review Questions |
|
|
298 | (3) |
|
Designing a Web Services Solution |
|
|
301 | (46) |
|
``A Brief History of Distributed Application Development'' |
|
|
302 | (1) |
|
Introducing XML Web Services |
|
|
303 | (4) |
|
Using Native XML Web Services in SQL Server |
|
|
307 | (1) |
|
What's Going on Under the Hood? |
|
|
308 | (1) |
|
Building an XML Web Service Using SQL Server |
|
|
309 | (5) |
|
|
|
314 | (9) |
|
|
|
314 | (2) |
|
Building a Client Application Using Visual Studio 2005 |
|
|
316 | (7) |
|
Reviewing What You've Learned |
|
|
323 | (2) |
|
Creating and Configuring Native XML Web Services |
|
|
325 | (7) |
|
Using the Create Endpoint Statement |
|
|
325 | (3) |
|
Using the Alter Endpoint Statement |
|
|
328 | (1) |
|
Using the Drop Endpoint Statement |
|
|
328 | (1) |
|
Querying Endpoint Metadata |
|
|
328 | (1) |
|
Reserving a Namespace: Running Side-by-Side with IIS |
|
|
329 | (3) |
|
Implementing Security with Native XML Web Services |
|
|
332 | (3) |
|
Implementing Authorization |
|
|
332 | (2) |
|
Implementing Authentication |
|
|
334 | (1) |
|
Implementing Security Best Practices for Native XML Web Services |
|
|
335 | (1) |
|
Using Alternative Technologies for SQL Server and Web Services |
|
|
335 | (2) |
|
Native XML Web Services vs. SQLXML |
|
|
335 | (1) |
|
Native XML Web Services vs. ASMX Web Services |
|
|
336 | (1) |
|
Implementing Best Practices for Native XML Web Services |
|
|
337 | (2) |
|
|
|
339 | (1) |
|
|
|
339 | (1) |
|
|
|
340 | (4) |
|
Answers to Review Questions |
|
|
344 | (3) |
|
Designing Messaging Services for a Database Solution |
|
|
347 | (70) |
|
Understanding Service-Oriented Architecture |
|
|
349 | (1) |
|
Understanding the Components of SOAs |
|
|
349 | (1) |
|
Understanding SOA in the Microsoft World |
|
|
350 | (1) |
|
Designing Service Broker Solutions for Asynchronous Applications |
|
|
350 | (12) |
|
Understanding the Service Broker Conversation Architecture |
|
|
351 | (6) |
|
Putting It All Together: A Sample Service Broker Application |
|
|
357 | (5) |
|
Developing Applications for SQL Server Notification Services |
|
|
362 | (31) |
|
Understanding the Notification Services Application Architecture |
|
|
363 | (2) |
|
Building Notification Services Applications |
|
|
365 | (20) |
|
Configuring a Notification Services Instance |
|
|
385 | (3) |
|
Deploying Notification Services Instances |
|
|
388 | (4) |
|
Looking at Notification Services Sample Applications |
|
|
392 | (1) |
|
Using SQL Server Database Mail |
|
|
393 | (14) |
|
Understanding the Database Mail Architecture |
|
|
395 | (12) |
|
|
|
407 | (1) |
|
|
|
408 | (1) |
|
|
|
409 | (5) |
|
Answers to Review Questions |
|
|
414 | (3) |
|
Designing a Reporting Services Solution |
|
|
417 | (60) |
|
Understanding Your Options for Developing Reports |
|
|
419 | (1) |
|
Understanding the Report Model |
|
|
419 | (12) |
|
|
|
420 | (9) |
|
|
|
429 | (1) |
|
|
|
429 | (2) |
|
Using the SSRS Report Wizard to Design Reports |
|
|
431 | (6) |
|
Using Microsoft Visual Studio to Develop Reporting Services Reports |
|
|
437 | (8) |
|
Understanding Report Layout and Rendering Options |
|
|
445 | (10) |
|
Working with Report Data Regions |
|
|
445 | (1) |
|
|
|
446 | (4) |
|
Understanding How Reports Are Rendered at Runtime |
|
|
450 | (1) |
|
Controlling User Interaction within Reports |
|
|
451 | (1) |
|
Working with Graphical Report Elements |
|
|
452 | (1) |
|
Working with Report Style Elements |
|
|
452 | (2) |
|
Working with Nongraphical Report Elements |
|
|
454 | (1) |
|
Creating SSRS Reports Using the Microsoft Report Builder Utility |
|
|
455 | (3) |
|
Optimizing Report Execution |
|
|
458 | (5) |
|
|
|
460 | (1) |
|
Creating Report Snapshots |
|
|
461 | (2) |
|
|
|
463 | (1) |
|
|
|
464 | (3) |
|
Configuring Email Support |
|
|
465 | (1) |
|
|
|
465 | (2) |
|
|
|
467 | (1) |
|
|
|
467 | (2) |
|
|
|
469 | (5) |
|
Answers to Review Questions |
|
|
474 | (3) |
|
Designing Data Integration Solutions |
|
|
477 | (78) |
|
Using SQL Server Integration Services |
|
|
478 | (48) |
|
Understanding the Package Structure |
|
|
487 | (16) |
|
Understanding the SSIS Architecture |
|
|
503 | (3) |
|
Testing and Debugging SSIS Packages |
|
|
506 | (20) |
|
Using Alternate Data Integration Technologies |
|
|
526 | (20) |
|
Using the SQL Server Agent |
|
|
527 | (1) |
|
|
|
528 | (4) |
|
Using the Bulk Insert Statement |
|
|
532 | (2) |
|
|
|
534 | (4) |
|
Using Ad Hoc Distributed Queries |
|
|
538 | (3) |
|
|
|
541 | (5) |
|
|
|
546 | (1) |
|
|
|
546 | (1) |
|
|
|
547 | (5) |
|
Answers to Review Questions |
|
|
552 | (3) |
|
|
|
555 | (24) |
|
Case Study 1: United Nations |
|
|
557 | (2) |
|
|
|
557 | (1) |
|
|
|
558 | (1) |
|
|
|
558 | (1) |
|
|
|
559 | (3) |
|
Answers to Review Questions |
|
|
562 | (1) |
|
Case Study 2: Energy Trading |
|
|
563 | (2) |
|
|
|
563 | (1) |
|
|
|
563 | (1) |
|
|
|
564 | (1) |
|
|
|
565 | (2) |
|
Answers to Review Questions |
|
|
567 | (1) |
|
Case Study 3: Nigerian Census |
|
|
568 | (2) |
|
|
|
568 | (1) |
|
|
|
569 | (1) |
|
|
|
569 | (1) |
|
|
|
570 | (2) |
|
Answers to Review Questions |
|
|
572 | (1) |
|
Case Study 4: Shark Conservation |
|
|
573 | (2) |
|
|
|
573 | (1) |
|
|
|
574 | (1) |
|
|
|
574 | (1) |
|
|
|
575 | (2) |
|
Answers to Review Questions |
|
|
577 | (2) |
| Glossary |
|
579 | (14) |
| Index |
|
593 | |
