McGraw-Hill - Microsoft SQL Server 2008_ A Beginner_s Guide (2008)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:30

0
43
lượt xem
19
download

McGraw-Hill - Microsoft SQL Server 2008_ A Beginner_s Guide (2008)01

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

McGraw-Hill - Microsoft SQL Server 2008_ A Beginner_s Guide (2008)01

Chủ đề:
Lưu

Nội dung Text: McGraw-Hill - Microsoft SQL Server 2008_ A Beginner_s Guide (2008)01

  1. Microsoft® SQL Server™ 2008 A Beginner’s guide
  2. About the Author Dušan Petković is a professor in the Department of Computer Science at the Polytechnic in Rosenheim, Germany. He is the author of SQL Server 7: A Beginner’s Guide, SQL Server 2000: A Beginner’s Guide, and Microsoft SQL Server 2005: A Beginner’s Guide, and is a frequent contributor to SQL Server Magazine. About the Technical Editor Todd Meister has been using Microsoft technologies for over ten years. He’s been a technical editor on over 40 titles ranging from SQL Server to the .NET Framework. In addition, he is an assistant director for computing services at Ball State University in Muncie, Indiana. He lives with his wife, Kimberly, and their four children in central Indiana. Contact Todd at tmeister@sycamoresolutions.com. Copyright © 2008 by The McGraw-Hill Companies. Click here for terms of use.
  3. Microsoft® SQL Server™ 2008 A Beginner’s guide Dušan Petkovic ´ New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
  4. Copyright © 2008 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of America. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. 0-07-154639-1 The material in this eBook also appears in the print version of this title: 0-07-154638-3. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate train- ing programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904- 4069. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. DOI: 10.1036/0071546383
  5. Professional Want to learn more? We hope you enjoy this McGraw-Hill eBook! If you’d like more information about this book, its author, or related books and websites, please click here.
  6. Contents at a Glance Part I Basic Concepts and Installation Chapter 1 Relational Database Systems—An Introduction 3 Chapter 2 Planning the Installation and Installing SQL Server 21 Chapter 3 SQL Server Management Studio 39 Part II Transact-SQL Language Chapter 4 SQL Components 67 Chapter 5 Data Definition Language 91 Chapter 6 Queries 125 Chapter 7 Modification of a Table’s Contents 199 Chapter 8 Stored Procedures and User-Defined Functions 215 Chapter 9 System Catalog 243 Chapter 10 Indices 257 Chapter 11 Views 279 Chapter 12 Security System of Database Engine 301 Chapter 13 Concurrency Control 343 Chapter 14 Triggers 367 Part III SQL Server: System Administration Chapter 15 System Environment of Database Engine 389 Chapter 16 Managing Instances and Maintaining Databases 405 Chapter 17 Backup and Recovery 413 Chapter 18 Automating System Administration Tasks 447 v
  7. vi M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Chapter 19 Data Replication 465 Chapter 20 Query Optimizer 485 Chapter 21 Performance Tuning 517 Part IV SQL Server and Business Intelligence Chapter 22 Business Intelligence—An Introduction 549 Chapter 23 Microsoft Analysis Services 565 Chapter 24 Business Intelligence and Transact-SQL 579 Chapter 25 Microsoft Reporting Services 605 Chapter 26 Optimizing Techniques for Business Intelligence 623 Part V Beyond Relational Data Chapter 27 Overview of XML 641 Chapter 28 SQL Server and XML 651 Chapter 29 Introduction to Spatial Data 673 Index 683
  8. For more information about this title, click here Contents Acknowledgments xxi Introduction xxiii Part I Basic Concepts and Installation Chapter 1 Relational Database Systems—An Introduction 3 Database Systems: An Overview 4 Variety of User Interfaces 5 Physical Data Independence 5 Logical Data Independence 5 Query Optimization 6 Data Integrity 6 Concurrency Control 6 Backup and Recovery 7 Database Security 7 Relational Database Systems 7 Working with the Book’s Sample Database 8 SQL: A Relational Database Language 11 Database Design 11 Normal Forms 13 Entity-Relationship Model 15 Syntax Conventions 17 Conclusion 18 Exercises 18 Chapter 2 Planning the Installation and Installing SQL Server 21 Planning the Installation 22 Purpose of the SQL Server System 22 Hardware and Network Requirements 23 SQL Server Editions 24 Installation Recommendations 25 vii
  9. viii M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Installing SQL Server 27 Before Starting the Setup Program 28 Starting the Setup Program and Installing Prerequisites 28 Installing SQL Server Components 28 Starting and Stopping an Instance of Database Engine 37 Dedicated Connection to an Instance of Database Engine 38 Conclusion 38 Chapter 3 SQL Server Management Studio 39 SQL Server Program Group and Books Online 40 Introduction to SQL Server Management Studio 41 Connecting to a Server 42 Registered Servers 44 Object Explorer 44 Organizing and Navigating Management Studio’s Panes 45 Using Management Studio with Database Engine 46 Administering Database Servers 46 Managing Databases Using Object Explorer 49 Authoring Activities Using SQL Server Management Studio 57 Conclusion 63 Exercises 63 Part II Transact-SQL Language Chapter 4 SQL Components 67 SQL’s Basic Objects 68 Literal Values 68 Delimiters 69 Comments 70 Identifiers 70 Reserved Keywords 70 Data Types 71 Numeric Data Types 71 Character Data Types 72 Temporal Data Types 72 Miscellaneous Data Types 74 DECIMAL with VARDECIMAL Storage Format 78
  10. Contents ix Transact-SQL Functions 79 Aggregate Functions 79 Scalar Functions 80 Scalar Operators 87 Global Variables 87 NULL Values 88 Conclusion 89 Exercises 89 Chapter 5 Data Definition Language 91 Creating Database Objects 92 Creation of a Database 92 CREATE TABLE: A Basic Form 97 CREATE TABLE and Declarative Integrity Constraints 99 Referential Integrity 105 Creating Other Database Objects 109 Integrity Constraints and Domains 111 Modifying Database Objects 113 Altering a Database 114 Altering a Table 116 Removing Database Objects 120 Conclusion 121 Exercises 122 Chapter 6 Queries 125 SELECT Statement: A Basic Form and the WHERE Clause 126 WHERE Clause 127 Subqueries 142 Subqueries and Comparison Operators 143 Subqueries and the IN Operator 144 Subqueries and ANY and ALL Operators 145 SELECT Statement: Other Clauses and Functions 147 GROUP BY Clause 147 Aggregate Functions 149 HAVING Clause 155 ORDER BY Clause 156 SELECT Statement and IDENTITY Property 158 Set Operators 160
  11. x M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e CASE Expressions 164 COMPUTE Clause 166 Temporary Tables 168 Join Operator 169 Two Syntax Forms to Implement Joins 170 Natural Join 171 Cartesian Product 177 Outer Join 177 Further Forms of Join Operations 180 Correlated Subqueries 183 Subqueries and the EXISTS Function 184 Should You Use Joins or Subqueries? 185 Table Expressions 186 Derived Tables 187 Common Table Expressions 188 Conclusion 195 Exercises 195 Chapter 7 Modification of a Table’s Contents 199 INSERT Statement 200 Inserting a Single Row 200 Inserting Multiple Rows 203 Table Value Constructors and INSERT 205 UPDATE Statement 205 DELETE Statement 208 TRUNCATE TABLE Statement 209 The OUTPUT Clause 210 MERGE Statement 211 Conclusion 213 Exercises 213 Chapter 8 Stored Procedures and User-Defined Functions 215 Procedural Extensions 216 Block of Statements 216 IF Statement 217 WHILE Statement 218 Local Variables 219 Miscellaneous Procedural Statements 220 Handling Events with TRY and CATCH Statements 221
  12. Contents xi Stored Procedures 222 Creation and Execution of Stored Procedures 223 Stored Procedures and CLR 228 User-Defined Functions 233 Creation and Execution of User-Defined Functions 233 User-Defined Functions and CLR 240 Conclusion 242 Exercises 242 Chapter 9 System Catalog 243 Introduction to the System Catalog 244 General Interfaces 246 Catalog Views 246 Dynamic Management Views and Functions 249 Information Schema 250 Proprietary Interfaces 252 System Procedures 252 System Functions 253 Property Functions 254 Conclusion 255 Exercises 255 Chapter 10 Indices 257 Introduction 258 Clustered Indices 260 Nonclustered Indices 261 Transact-SQL and Indices 262 Creating Indices 262 Obtaining Index Fragmentation Information 266 Editing Index Information 267 Altering Indices 268 Removing and Renaming Indices 270 Guidelines for Creating and Using Indices 271 Indices and Conditions in the WHERE Clause 271 Indices and Join Operator 272 Covering Index 272 Indices on Computed Columns 273 Virtual Computed Columns 274 Persistent Computed Columns 274
  13. xii M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Conclusion 275 Exercises 276 Chapter 11 Views 279 DDL Statements and Views 280 Creating a View 280 Altering and Removing Views 284 Editing Information Concerning Views 285 DML Statements and Views 286 View Retrieval 286 INSERT Statement and a View 287 UPDATE Statement and a View 289 DELETE Statement and a View 292 Indexed Views 293 Creating an Indexed View 293 Modifying the Structure of an Indexed View 295 Editing Information Concerning Indexed Views 296 Benefits of Indexed Views 297 Conclusion 298 Exercises 299 Chapter 12 Security System of Database Engine 301 Authentication 303 Implementing an Authentication Mode 304 Encrypting Data 304 Setting Up System Security Using DDL 309 Managing Logins Using Management Studio 311 Schemas 312 User-Schema Separation 312 DDL Schema-Related Statements 313 Database Security 315 Setting Up Database User Accounts 316 Default Database Schemas 318 Roles 318 Fixed Server Roles 319 Fixed Database Roles 321 Application Roles 322 User-Defined Database Roles 324
  14. Contents xiii Authorization 326 GRANT Statement 327 DENY Statement 331 REVOKE Statement 332 Managing Permissions Using Management Studio 333 Tracking Changes 334 Data Security and Views 338 Conclusion 339 Exercises 340 Chapter 13 Concurrency Control 343 Concurrency Models 344 Transactions 345 Properties of Transactions 346 Transact-SQL Statements and Transactions 347 Transaction Log 350 Locking 351 Lock Modes 352 Lock Granularity 354 Lock Escalation 355 Affecting Locks 356 Displaying Lock Information 357 Deadlock 358 Isolation Levels 359 Concurrency Problems 359 Database Engine and Isolation Levels 360 Row Versioning 362 READ COMMITTED SNAPSHOT Isolation Level 363 SNAPSHOT Isolation Level 364 Conclusion 364 Exercises 365 Chapter 14 Triggers 367 Introduction 368 Creating a DML Trigger 368 Modifying a Trigger’s Structure 369 Using Deleted and Inserted Tables 370
  15. xiv M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Application Areas for DML Triggers 371 AFTER Triggers 371 INSTEAD OF Triggers 375 First and Last Triggers 376 DDL Triggers 378 Database-Level Triggers 379 Server-Level Triggers 379 Triggers and CLR 380 Conclusion 385 Exercises 385 Part III SQL Server: System Administration Chapter 15 System Environment of Database Engine 389 System Databases 390 master Database 390 model Database 390 tempdb Database 391 msdb Database 392 Disk Storage 392 Properties of Data Pages 393 Types of Data Pages 396 Parallel Processing of Tasks 398 Utilities and the DBCC Command 399 bcp Utility 399 sqlcmd Utility 400 DBCC Command 402 Conclusion 404 Exercises 404 Chapter 16 Managing Instances and Maintaining Databases 405 Declarative Management Framework 406 Key Terms 406 Using DMF 406 Maintenance Plan Wizard 409 Conclusion 411 Exercises 411
  16. Contents xv Chapter 17 Backup and Recovery 413 Backup Methods 415 Full Database Backup 415 Differential Backup 415 Transaction Log Backup 415 File or Filegroup Backup 417 Performing Backup 417 Backing Up Using Transact-SQL Statements 418 Backing Up Using Management Studio 421 Determining Which Databases to Back Up 424 Performing Database Recovery 426 Automatic Recovery 426 Manual Recovery 426 Recovery Models 435 High Availability 438 Using a Standby Server 439 Using RAID Technology 440 Failover Clustering 441 Database Mirroring 442 Log Shipping 443 Conclusion 444 Exercises 445 Chapter 18 Automating System Administration Tasks 447 Running and Configuring SQL Server Agent 449 Creating Jobs and Operators 450 Creating a Job and Its Steps 450 Creating a Job Schedule 453 Notifying Operators about the Job Status 454 Viewing the Job History Log 455 Alerts 457 Error Messages 457 SQL Server Agent Error Log 458 Windows Application Log 459 Defining Alerts to Handle Errors 459 Conclusion 463 Exercises 464
  17. xvi M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Chapter 19 Data Replication 465 Distributed Data 466 Methods for Distributing Data 466 Choosing a Data Distribution Method 467 Replication—An Overview 468 Publishers, Distributors, and Subscribers 468 Publications and Articles 470 Replication Types 471 Replication Models 476 Managing Replication 478 Configuring the Distribution and Publication Servers 478 Setting Up Publications 480 Configuring Subscription Servers 480 Conclusion 483 Exercises 483 Chapter 20 Query Optimizer 485 Phases of Query Processing 486 How Query Optimization Works 487 Query Analysis 488 Index Selection 488 Join Order Selection 492 Join Processing Techniques 492 Tools for Editing the Optimizer Strategy 494 SET Statement 495 Management Studio and Graphical Execution Plans 499 Examples of Execution Plans 500 Dynamic Management Views and Query Optimizer 504 Optimization Hints 507 Why to Use Optimization Hints 507 Types of Optimization Hints 508 Conclusion 515 Chapter 21 Performance Tuning 517 Factors that Affect Performance 518 Database Applications and Performance 519 Database Engine and Performance 521 System Resources and Performance 522
  18. Contents xvii Monitoring Performance 526 Performance Monitor: An Overview 526 Monitoring the CPU 528 Monitoring Memory 529 Monitoring the Disk System 531 Monitoring the Network Interface 532 Choosing the Right Tool 533 SQL Server Profiler 534 Database Engine Tuning Advisor 535 SQL Server 2008 Performance Tools 542 Performance Data Collector 542 Resource Governor 543 Conclusion 545 Exercises 545 Part IV SQL Server and Business Intelligence Chapter 22 Business Intelligence—An Introduction 549 Online Transaction Processing vs Business Intelligence 550 Online Transaction Processing 550 Business Intelligence Systems 551 Data Warehouses and Data Marts 552 Data Warehouse Design Using Dimensional Model 554 Cubes and Their Architectures 558 Aggregations 559 How Much to Aggregate? 559 Physical Storage of a Cube 561 Data Access 562 Conclusion 563 Exercises 563 Chapter 23 Microsoft Analysis Services 565 BI Development Studio 566 Creating a New Project 567 Creating a Data Source 568 Creating a Data Source View 569
  19. xviii M i c r o s o f t S Q L S e r v e r 2 0 0 8 : A B e g i n n e r ’s G u i d e Analysis Services and Cubes 571 Creating a Cube 573 Designing Storage Aggregation and Processing the Cube 573 Browsing a Cube 576 Conclusion 577 Chapter 24 Business Intelligence and Transact-SQL 579 Introduction to SQL/OLAP 580 Window Construct 581 Extensions of GROUP BY 587 CUBE Operator 587 ROLLUP Operator 589 GROUPING Function 590 Grouping SETS Operator 591 OLAP Query Functions 593 Ranking Functions 593 Statistical Aggregate Functions 596 Nonstandard Analytic Functions 597 TOP Clause 597 NTILE Function 600 PIVOT and UNPIVOT Operators 601 Conclusion 603 Exercises 603 Chapter 25 Microsoft Reporting Services 605 Introduction to Microsoft Reporting Services 606 Report Manager 606 Report Server 607 Report Catalog 608 Creating Reports 608 Creating Reports with the Report Server Project Wizard 610 Creating Parameterized Reports 617 Processing and Managing Reports 618 Accessing and Delivering Reports 619 On-Demand Reports 619 Report Subscription 620 Conclusion 621 Exercises 622
Đồng bộ tài khoản