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

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

0
45
lượt xem
9

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

Mô tả tài liệu

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

Chủ đề:

Bình luận(0)

Lưu

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

1. Introduction xxix c Chapter 25, “Microsoft Reporting Services,” describes the Microsoft enterprise reporting solution. This component is used to design and deploy reports. This chapter discusses the development environment that you use to design and create reports, and shows you different ways to deliver a designed and deployed report. c Chapter 26, “Optimizing Techniques for Business Intelligence,” describes two of several specific optimization techniques that can be used especially in the area of business intelligence: data partitioning and star schema join optimization. The data partitioning technique called range partitioning is described. (This partitioning type is entirely transparent to the application.) In relation to star schema join optimization, the role of bitmap filters in the optimization of schema joins is explained. Part V, “Beyond Relational Data,” is dedicated to two “nonrelational” topics, XML and spatial data, because SQL Server, as a data platform, doesn’t have to handle only relational data. The following chapters are included in this part: c Chapter 27, “Overview of XML,” gives you an overview of XML documents, DTDs, and XML Schema. A running example is used to demonstrate how XML documents can be validated using either a DTD or XML Schema. c Chapter 28, “SQL Server and XML,” discusses SQLXML, Microsoft’s set of data types and functions that supports XML in SQL Server 2008, bridging the gap between XML and relational data. The beginning of the chapter introduces the standardized data type XML and explains how stored XML documents can be retrieved. After that, the presentation of relational data as XML documents is discussed in detail. c Chapter 29, “Introduction to Spatial Data,” discusses spatial data and two different data types (GEOMETRY and GEOGRAPHY) that can be used to create such data. Several different standardized functions in relation to spatial data are also shown. Almost all chapters include at their end numerous exercises that you can use to improve your knowledge concerning the chapter’s content. All solutions to the given exercises can be found either at McGraw-Hill Professional’s web site (www .mhprofessional.com) or my own home page (www.fh-rosenheim.de/~petkovic).
2. xxx 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 Changes from the Previous Edition If you are familiar with the previous edition of this book, Microsoft SQL Server 2005: A Beginner’s Guide, you should be aware that I have made significant changes in this edition. To make the book easier to use, I separated some topics and described them in totally new chapters. (For instance, Chapter 20 is an entirely new chapter and describes the query optimizer in depth. In the previous edition, this topic was described lightly, together with indices, in Chapter 9.) The following table gives you an outline of significant structural changes in the book (minor changes aren’t listed). Chapter 2 The entire chapter concerning all facets of SQL Server installation (previously Chapter 17) has been moved to the beginning of the book Chapter 6 The description of the SELECT statement is now streamlined and described in one chapter (The number of examples is reduced, but there are still 77 examples and 30 exercises ) Chapter 9 The system catalog is described earlier in this edition than in the prior edition (previously Chapter 11) The reason is that the general discussion of dynamic management views (DMVs) is given in Chapter 9, while the description of specific DMVs starts in Chapter 10 and continues throughout the rest of the book Chapter 10 This chapter is now exclusively dedicated to indices Chapter 13 Chapter 13 doesn’t describe transactions only, as in the prior edition, but handles the entire topic of concurrency control (For example, the section “Concurrency Models” explains in detail both models supported by Database Engine ) Chapter 14 Triggers are now described after concurrency control because they use a mechanism, row versioning, that is related to the latter topic and thus described in Chapter 13 Chapter 16 This chapter is entirely new and describes two components related to management of server instances and maintenance of databases Chapter 20 This chapter is entirely new and describes in detail the query optimizer of Database Engine (This component was described in the previous book in Chapter 9 together with indices ) Chapter 26 This chapter is partly new, and describes data partitioning and star schema join optimization Chapter 29 This is an entirely new chapter, with spatial data as the topic Differences Between SQL and Transact-SQL Syntax Transact-SQL, SQL Server’s relational database language, has several nonstandardized properties that generally are not known to people who are familiar with SQL only: c Whereas the semicolon (;) is used in SQL to separate two SQL statements in a statement group (and you will generally get an error message if you do not include the semicolon), in Transact-SQL, use of semicolons is optional.
3. Introduction xxxi c Transact-SQL uses the GO statement. This nonstandardized statement is generally used to separate statement groups from each other, whereas some Transact-SQL statements (such as CREATE TABLE, CREATE INDEX, and so on) must be the only statement in the group. The extended syntax of this statement, GO n (where n = 1, 2, 3,…), means that the corresponding statement group will be executed n times. c The USE statement, which is used very often in this book, changes the database context to the specified database. For example, the statement USE sample means that the statements that follow are related to the sample database. Working with the Sample Databases In contrast to its predecessor, this edition uses several sample databases: c This book’s own sample database c Microsoft’s AdventureWorks database c Microsoft’s AdventureWorksDW database An introductory book like this requires a sample database that can be easily understood by each reader. For this reason, I used a very simple concept for my own sample database: it has only four tables with several rows each. On the other hand, its logic is complex enough to demonstrate the hundreds of examples included in the text of the book. The sample database that you will use in this book represents a company with departments and employees. Each employee belongs to exactly one department, which itself has one or more employees. Jobs of employees center around projects: each employee works at the same time for one or more projects, and each project engages one or more employees. The tables of the sample database are shown here. The department table: dept_no dept_name location d1 Research Dallas d2 Accounting Seattle d3 Marketing Dallas
4. xxxii 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 The employee table: emp_no emp_fname emp_lname dept_no 25348 Matthew Smith d3 10102 Ann Jones d3 18316 John Barrimore d1 29346 James James d2 9031 Elsa Bertoni d2 2581 Elke Hansel d2 28559 Sybill Moser d1 The project table: project_no Project_name budget p1 Apollo 120000 p2 Gemini 95000 p3 Mercury 185600 The works_on table: emp_no project_no job enter_date 10102 p1 Analyst 2006 10 1 10102 p3 Manager 2008 1 1 25348 p2 Clerk 2007 2 15 18316 p2 NULL 2007 6 1 29346 p2 NULL 2006 12 15 2581 p3 Analyst 2007 10 15 9031 p1 Manager 2007 4 15 28559 p1 NULL 2007 8 1 28559 p2 Clerk 2008 2 1 9031 p3 Clerk 2006 11 15 29346 p1 Clerk 2007 1 4
5. Introduction xxxiii You can download the sample database from McGraw-Hill Professional’s web site (www.mhprofessional.com) or my own home page (www.fh-rosenheim.de/~petkovic). Also, you can download all the examples in the book from my home page. Although the sample database can be used for many of the examples in this book, for some examples, tables with lot of rows are necessary (to show optimization features, for instance). For this reason, two Microsoft sample databases—AdventureWorks and AdventureWorksDW—are also used. Both of them can be found at the Microsoft CodePlex web site www.codeplex.com/MSFTDBProdSamples.
12. 6 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 the database without having to make any changes to the database application programs. For example, if the structure of an object named PERSON exists in the database system and you want to add an attribute to PERSON (say the address), you have to modify only the logical structure of the database, and not the existing application programs. Query Optimization Every database system contains a subcomponent called an optimizer that considers a variety of possible execution strategies for querying the data and then selects the most efficient one. The selected strategy is called the execution plan of the query. The optimizer makes its decisions using considerations such as how big the tables are that are involved in the query, what indices exist, and what Boolean operator (AND, OR, or NOT) is used in the WHERE clause. (This topic is discussed in detail in Chapter 20.) Data Integrity One of the tasks of a database system is to identify logically inconsistent data and reject its storage in a database. (The date February 30 and the time 5:77:00 P.M. are two examples of such data.) Additionally, most real-life problems that are implemented using database systems have integrity constraints that must hold true for the data. (One example of an integrity constraint might be the company’s employee number, which must be a five-digit integer.) The task of maintaining integrity can be handled by the user in application programs or by the DBMS. As much as possible, this task should be handled by the DBMS. (Data integrity is discussed in two chapters of this book: declarative integrity in Chapter 5 and procedural integrity in Chapter 14.) Concurrency Control A database system is a multiuser software system, meaning that many user applications access a database at the same time. Therefore, each database system must have some kind of control mechanism to ensure that several applications that are trying to update the same data do so in some controlled way. The following is an example of a problem that can arise if a database system does not contain such control mechanisms: 1. The owners of bank account 4711 at bank X have an account balance of $2,000. 2. The two joint owners of this bank account, Mrs. A and Mr. B, go to two different bank tellers, and each withdraws$1,000 at the same time. 3. After these transactions, the amount of money in bank account 4711 should be $0 and not$1,000. All database systems have the necessary mechanisms to handle cases like this example. Concurrency control is discussed in detail in Chapter 13.