MASTERING SQL SERVER 2000- P2

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

0
35
lượt xem
4
download

MASTERING SQL SERVER 2000- P2

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

Tham khảo tài liệu 'mastering sql server 2000- p2', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:
Lưu

Nội dung Text: MASTERING SQL SERVER 2000- P2

  1. 20 CHAPTER 1 • INTRODUCTION TO SQL SERVER 2000 There are a few things to note about this code that will give you a sense of the flex- ibility of the ADO Recordset object: • Setting the CursorLocation property of the recordset to adUseClient tells ADO to cache the records locally after they’re retrieved. This allows more efficient processing and enables some advanced ADO methods. • You can specify that you want a static recordset (one that doesn’t reflect changes from other users) as well as the type of locking (in this case, optimistic locking) to use when you open the recordset. • A recordset is a collection of fields, each with a name and value. • The recordset supports a number of properties, including an EOF property that’s true at the end of the recordset, and a number of methods, including a MoveNext method that moves the cursor to the next record. • To be neat, you can close your ADO objects and set them equal to Nothing to explicitly free the memory they’re consuming. Figure 1.11 shows the results of running this procedure. FIGURE 1.11 Data from a Recordset object Editing Data ADO also makes it simple to edit data: You can add new records, delete existing records, or modify the data stored in existing records by calling appropriate methods of a recordset. For example, here’s some code to modify the recordset we just created: Private Sub cmdModify_Click() ‘ Demonstrate recordset modification Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset ‘ Open a connection cnn.Open “Provider=SQLOLEDB.1; “ & _ “Data Source = (local);” & _ “User ID = sa;” & _
  2. TOUR FOR DEVELOPERS 21 “Initial Catalog = Northwind” PA R T I ‘ Open a recordset on a table rst.CursorLocation = adUseClient rst.Open “Shippers”, cnn, adOpenStatic, _ adLockOptimistic Introducing SQL ‘ Add a record rst.AddNew Server rst.Fields(“CompanyName”) = “New Shipper” rst.Fields(“Phone”) = “(509)-555-1212” rst.Update ‘ Modify the record just added rst.MoveLast rst(“Phone”) = “509-666-1212” rst.Update ‘ Delete the record we’ve been playing with rst.MoveLast rst.Delete ‘ Tidy up rst.Close cnn.Close Set rst = Nothing Set cnn = Nothing End Sub You can see the recordset methods in action here: • The AddNew method prepares a new row to be added to the recordset. • The Update method saves a new row or changes to data in an existing row. • The Delete method deletes the current row. NOTE In this case, you’re assured that the new row will be the last row in the recordset because the recordset is based on a table that includes an Identity field. The server auto- matically assigns ID numbers in increasing order to this field. You’ll learn about Identity fields in Chapter 11.
  3. 22 CHAPTER 1 • INTRODUCTION TO SQL SERVER 2000 Displaying Data on a Web Page These days, the Internet is everywhere—and where there’s no Internet, there are cor- porate intranets. It’s probably inevitable that any developer today will be asked to make data available via a Web page. There are many ways to do this, of course. You can run client-side VBScript code that connects to a remote server. You can create ASP pages that use ADO objects directly on a server to create raw HTML to send to clients. You can also write queries that directly return XML, which some browsers can display. For now, let’s look at the simplest possible case: using the tools that SQL Server supplies to publish data directly to a Web page. From Enterprise Manager, you can choose Tools ➣ Wizards and launch the Web Assistant Wizard. (How can something be both an assistant and a Wizard? We don’t know; we didn’t name it.) This Wizard creates a set of SQL Server tasks that create and update a Web page based on the data you choose. Using the Wizard is a simple process: 1. Choose the database that holds the data that you wish to publish. 2. Assign a name to the Web page and choose a table, SQL statement, or stored procedure to supply the data. 3. Select the columns from your data that you wish to publish. 4. Decide which rows to publish. 5. Select an update frequency. As Figure 1.12 shows, this step is very flexible. 6. Choose a filename for the Web page. 7. Supply information on formatting your Web page. 8. Select a list of hyperlinks for the page to include. 9. Decide whether to return all the data or chunks of data. When you’re finished making choices, click Finish—the Wizard does the rest. Fig- ure 1.13 shows a Web page generated by the Web Assistant Wizard.
  4. TOUR FOR DEVELOPERS 23 FIGURE 1.12 PA R T The Web Assistant I Wizard at work Introducing SQL Server FIGURE 1.13 The finished Web page
  5. 24 CHAPTER 1 • INTRODUCTION TO SQL SERVER 2000 NOTE You’ll learn more about using SQL Server data with the Internet in Chapters 21 and 22. Tour for Users Some of you aren’t DBAs or developers, just users of data stored in SQL Server data- bases. That’s OK—there’s plenty in the product (and in this book) for you too. In fact, we suspect that, increasingly, more people are going to be combination DBA/developer/users in the future, now that Microsoft has released a desktop version of SQL Server that runs under Windows 95 or Windows 98. In addition to the desk- top version, which includes the management tools, there’s also the Microsoft Data- base Engine (MSDE), which is SQL Server without any of the user interface. MSDE is shipped with other Microsoft products such as Microsoft Office or Microsoft Visual Studio. So, in this section, we’ll examine the available tools to use when you just want to get to your data. First on the list is Query Analyzer, a tool that ships with SQL Server. However, we also want to highlight Microsoft Access 2000, part of the Office 2000 suite of products, for its easy connectivity to SQL Server data. Opening Query Analyzer For ad hoc queries (that is, queries that haven’t been saved to a database), the tool that ships with SQL Server 2000 is Query Analyzer. You can launch this tool by choosing Programs ➣ Microsoft SQL Server ➣ Query Analyzer from the Start menu. You can use SQL Server setup to install Query Analyzer on a computer that doesn’t have SQL Server itself installed, so that Query Analyzer can be used from anywhere on the network. When you launch Query Analyzer, you’ll be prompted to enter the name of a SQL Server and your authentication information. After that, the program will open with a blank query window. Figure 1.14 shows the basic Query Analyzer interface. In this case, one query was executed, and a new window was opened to execute a second query. The Object Browser, to the right of the Query Analyzer workspace, provides easy access to the names of all your SQL Server objects. As you can see, Query Ana- lyzer can display multiple results at any time.
  6. TOUR FOR USERS 25 FIGURE 1.14 PA R T Query Analyzer I Introducing SQL Server Query Analyzer can show you the results of any Transact-SQL statement (Transact- SQL, or T-SQL, is the language of SQL Server). For example, you might try executing this statement in the Northwind sample database: SELECT CompanyName, Country FROM Customers WHERE CustomerID > ‘MMMMM’ ORDER BY Country Even if you don’t know SQL, you can probably guess what this statement does. It returns the CompanyName and Country fields from the Customers table for all cus- tomers whose CustomerID is greater than (that is, later in the alphabet than) “MMMMM”. The results are sorted by the customer’s country. Although SQL is a spe- cialized language, by and large, you can read SQL as plain English and get the idea. NOTE You’ll learn a lot more about SQL in Chapters 5 through 8. Appendix A contains a summary of important Transact-SQL statements.
  7. 26 CHAPTER 1 • INTRODUCTION TO SQL SERVER 2000 Other Query Analyzer Features Query Analyzer is a pretty flexible tool. Some of the other actions you can do from this interface include: • Saving queries to text files and reloading them later • Viewing results in either a grid or plain text • Checking the syntax of a query without executing it • Analyzing the indexes in a database to determine whether a particular query would be helped by additional indexes • Showing the execution plan for a query The last point—showing the execution plan for a query—is worth discussing. The execution plan for a query is the set of steps that SQL Server will follow to get you the information for which you’ve asked. For example, in the SELECT query in the previ- ous section, SQL Server will first find all the rows desired using the index on the Cus- tomerID field and then sort them in the desired order. For more complex queries, an execution plan might have dozens of steps. If you get really familiar with SQL, you can sometimes use optimizer hints in your queries to change the execution plan and make it faster for your particular data. WARN I NG Don’t change the execution plan if you don’t know what you’re doing. SQL Server 2000 does a good job of optimizing queries all by itself. Connecting Access 2000 to SQL Server Although Query Analyzer is a useful tool, it’s not all that user-friendly. You need to understand SQL to do much of anything with Query Analyzer. Wouldn’t it be nice to just view your SQL Server data through a more friendly interface? Well, if you’re familiar with Microsoft Access and you have Access 2000, you can do just that. Access 2000 includes a new type of database called an Access project. An Access project includes all of the familiar Access user-interface tools such as forms and reports. However, instead of storing its data in a Jet database, it stores its data in a Microsoft SQL Server database. In fact, Access 2000 even comes with a desktop ver- sion of SQL Server, the Microsoft Database Engine (MSDE).
  8. TOUR FOR USERS 27 You can also create an Access project that shows data from an existing SQL Server PA R T database. To do so, follow these steps: I 1. Launch Access 2000. 2. Choose Create a New Database Using Access Database Wizards, Pages and Pro- jects from the opening dialog box. Introducing SQL 3. Choose the General tab in the New dialog box. 4. Choose the icon for Project (Existing Database) and click OK. 5. Assign a name to your project and click Create. Server 6. Enter your SQL Server name, authentication information, and database name in the Data Link Properties dialog box, and click OK. That’s all there is to it. As Figure 1.15 shows, the result of following these steps with the sample Northwind database is the creation of an Access project showing your SQL Server data. In the figure, we’ve opened up one of the SQL Server tables to show the data. FIGURE 1.15 An Access 2000 project
  9. 28 CHAPTER 1 • INTRODUCTION TO SQL SERVER 2000 Editing Data in Access 2000 Once you’ve created an Access project tied to your SQL Server database, all of the Access 2000 tools are available to use. For example, suppose you want to view and edit your Customer data in a friendly format. It’s easy to do using the Access Form Wizard: 1. Select a table in the Database Window (for example, Customers). 2. Select Insert ➣ Form from the Access menus. 3. Choose Autoform (Columnar) and click OK. The result will be a form similar to the one shown in Figure 1.16. FIGURE 1.16 SQL Server data in an Access form From this form, you can perform all of the basic data operations: • Entering new customers • Editing existing customers • Deleting customers that you no longer need WARN I NG You’re still limited by the way your SQL Server is set up. In particular, if your DBA has used SQL Server security to prevent you from modifying data, you won’t be able to do so through an Access project. However, if you’re your own DBA, working on a single-user version of SQL Server, this shouldn’t be a problem.
  10. SUMMARY 29 Similarly, you can use the Access report Wizards to create summaries and lists of PA R T your SQL Server data in a format that’s easy to print. When you create user-interface I objects such as reports in an Access project, the user-interface objects themselves are stored in the .ADP file created by Access. All of the data objects, such as views or tables, remain on the server. Introducing SQL NOTE For much more information about Access projects, see Access 2000 Developer’s Handbook, Volume 2: Enterprise Edition (by Paul Litwin, Ken Getz, and Mike Gilbert, ISBN Server 0-7821-2372-4, Sybex 2000). Summary SQL Server isn’t everything to everybody, but in the current release, it certainly has something for almost every computer user. The range of SQL Server goes from simple customer databases intended for a single user all the way to terabytes (a terabyte is one trillion characters) of data in cases such as Microsoft’s TerraServer (http://www .terraserver.microsoft.com). In the rest of this book, you’ll learn about various aspects of SQL Server: • Part 1 will teach you basic SQL Server and database concepts. • Part 2 will teach you Transact-SQL. • Part 3 examines the basic SQL Server objects in more detail. • Part 4 covers administrative tasks. • Part 5 reviews the developer tools that ship with SQL Server. • Part 6 deals with SQL Server data on the Web. • Part 7 introduces some advanced concepts. Ready to start? Good! The next chapter will teach you basic database concepts.
  11. This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  12. CHAPTER 2 Overview of Database Concepts F E AT U R I N G : Databases 32 Tables 36 Views 44 Stored Procedures 52 Ownership and Security 55 Jobs, Alerts, and Operators 56 Replication 57 Application Programming Interfaces 58 Summary 59
  13. B efore we get started with Microsoft SQL Server, we want to step back for a few moments and discuss the basic ideas of database technology. Depending on your experience, you might already know everything in this chapter, in which case you can just skim it to make sure the terminology we use is the terminology with which you’re familiar. On the other hand, if you’ve never worked with a database before, this will be your introduction to the basic concepts of the field. What’s stored in a database, anyhow? What can you do with a database? We’ll try to answer those questions here in a very broad fashion. You might want to read this chap- ter now to get an overview, and then refer back to it as necessary to refresh your mem- ory on the big picture when you read about the details later in the book. All of the concepts in this chapter will be discussed later in the book in the context of SQL Server. For example, one of the first things we’ll introduce in this chapter is the notion of a database table. All of Chapter 11 is devoted to tables as implemented by SQL Server. So while you read the current chapter, if you want to know the mechanics of working with a particular piece of your database, you can follow the ref- erences forward to the specific chapters. For now, we’ll start with a general overview. Databases A database is a place to store data. Suppose you’re running a small business and you want to store all of the data that pertains to that business. Your data is not just a big heap of disparate facts (or at least, it shouldn’t be a big heap if you want to be able to find things). The facts are naturally organized into a hierarchy. For example, consider a single fact: A particular employee of your company was hired on October 17, 1993. By placing that fact together with other facts, you can organize your database at four levels: • The hire date of the employee • All of the important facts about that employee • All of the important facts about all employees • All of the important facts about your entire business In database terms, you refer to these four levels of organization by four special terms: • The field holds an individual fact. • The record holds all facts about an entity. • The table holds all facts about a group of similar entities. • The database holds all facts about all the entities in a connected whole.
  14. DATABASES 33 Strictly speaking, if a database allows for storing records, fields, and tables, that’s PA R T all it really needs to keep track of. Some simple databases go no further than this. I However, many database manufacturers add storage for additional things to their database. Microsoft SQL Server in particular stores many things in the database other than data. As you read through this chapter, you’ll encounter these other things (such as views or stored procedures), which are collectively called database objects. Introducing SQL But first, you should know more about types of databases. Specifically, there are three topics you’ll frequently run across in the database world: • File-server versus client-server databases Server • Relational databases • OLTP versus OLAP databases NOTE For more information on the mechanics of creating and managing SQL Server databases, refer to Chapter 10. File-Server and Client-Server Databases One important distinction is that between file-server and client-server databases. These two terms refer to fundamentally different ways of working with data. In a file-server database, the data is stored in a file, and individual users of the data take what they need directly from the file. When there is a change to be made, the application opens the file and writes new data. When existing data is needed for dis- play, the application opens the file and reads the data. If there are 20 different users for a database, all 20 users are reading from and writing to the same file. In a client-server database, by contrast, the data is still stored in a file, but all access to the file is controlled by a single master program (the server). When an application wants to make use of existing data, this application (the client) sends a request to the server. The server finds the proper data and sends it back. When an application wants to write new data to the database, it sends the data to the server, which does the actual writing. Only a single program reads and writes from the data files. Typically, databases aimed at a single-user desktop (such as Microsoft Access or Microsoft FoxPro) are file-server databases. Databases that are aimed at departmental, company, or enterprise users (such as Oracle, Informix, or Microsoft SQL Server) are client-server databases. Client-server databases have several important advantages in large-scale use. These include: • Because only a single program is reading and writing data, there is less chance of accidental changes or crashes destroying vital data.
  15. 34 CHAPTER 2 • OVERVIEW OF DATABASE CONCEPTS • The single server program can act as a gatekeeper for all clients, making the cre- ation and enforcement of a security policy easier. • Because only requests and results flow across the wire, client-server databases make more efficient use of network bandwidth than file-server databases. • Because all the reading and writing is being done by a single computer, it’s easier to increase database performance by upgrading that one computer. • Client-server databases also tend to offer features that protect your data, such as logging transactions and recovery from disk or network errors. Strictly speaking, these features could be offered by file-server databases as well, but in practice, they’re found only in the more expensive client-server market. Relational Databases A relational database is one that stores your data in multiple places called tables, while also keeping track of how those tables are related to one another. Sometimes you’ll see the term RDBMS, which stands for Relational Database Management System, used for a relational database. For example, consider a database that’s used to keep track of students in a college. You might want to collect information about students, courses, and instructors. Each of these would be stored as a single table, which would have names: • Students • Courses • Instructors In addition, the RDBMS would also keep track of the facts relating these tables to each other. For example, each student could be enrolled in one or more courses, and each instructor could teach one or more courses. NOTE SQL Server is a relational database. OLTP and OLAP Databases Another important distinction is that between online transaction processing (OLTP) and online analytical processing (OLAP) databases. The distinction is not as clear-cut as that between file-server and client-server. In fact, most databases will be used as both OLTP and OLAP products during their lifetime.
  16. DATABASES 35 OLTP refers to a usage pattern involving rapid insertion, deletion, and updating of PA R T data. This is typical of many applications. For example, suppose you’re running a I travel agency and have 20 agents all updating a database of customer trip informa- tion. This would be a typical OLTP application. The ability to quickly locate and change data is of paramount importance to avoid the database becoming a bottleneck for the entire operation. Introducing SQL On the other hand, suppose you’re the manager of the travel agency. You might be interested in seeing summary information from many bookings. Perhaps there’s a pat- tern where women travel more to Greece and men more to Spain; knowing this could Server enable you to better target your advertising to appropriate periodicals. Such analysis, involving summaries of all or most of the data in a database, is the hallmark of OLAP applications. It’s very difficult for a server to be efficient for both OLTP and OLAP applications. The data structures that are appropriate for fast updating are suboptimal for aggregate querying. Microsoft solves this problem by shipping two servers together. The first, Microsoft SQL Server 2000, is mainly an OLTP server. It can perform summary queries, but it’s not optimized for them. That’s the job of the second program, Microsoft SQL Server 2000 Analysis Services. This second program ships with every copy of SQL Server and is designed to build efficient structures for OLAP applications to use. NOTE You’ll learn more about Microsoft SQL Server 2000 Analysis Services in Chapter 28. Transaction Logs Another feature commonly found in client-server databases is the transaction log. This is a separate file (or other distinct storage area) where the database server keeps track of the operations it is performing. For example, suppose you add a new record to a table. Before it adds the record to the table, the database server will make an entry in the transaction log that says, essentially, “About to add this record to the table,” along with the data from the record. Only after the transaction log entry has been saved does the server actually save the change to the database. Transaction logs are an important part of protecting your data. By keeping track of operations in a log, the database server makes it possible to recover from a wide range of disasters. For example, suppose that the hard drive that stores your database fails. If you’ve kept backups, and if the transaction log is stored on a separate hard drive (both worthwhile precautions), you can easily recover the data by first restoring the backup and then telling the server to reapply all the changes that were noted in the transaction log after the backup was made.
  17. 36 CHAPTER 2 • OVERVIEW OF DATABASE CONCEPTS Tables Tables are the objects that actually store your data. One of the basic guidelines for databases is that each table should store information on a particular entity. This is what’s known as a normalization rule. You’ll learn much more about normalization in Chapter 4. Figure 2.1 shows a table of information about employees. In this particular case, the table is stored on a Microsoft SQL Server, and the screenshot was taken inside of SQL Enterprise Manager, one of the utilities that ships as a part of SQL Server (you’ll learn more about SQL Enterprise Manager in Chapter 9). FIGURE 2.1 A table about employees Much of the work you do with a database will revolve around tables. There are four basic operations that every database supports: • Adding information to a table • Updating information that already exists in a table • Deleting information from a table • Viewing information contained in a table Generally speaking, you’ll perform these operations by executing SQL statements. SQL stands for Structured Query Language, a standard computer language for working with the contents of a database. You’ll learn more about SQL later in this chapter and throughout this book.
  18. TABLES 37 Records, Fields, and Values PA R T Every table is made up of records and fields. A record is all of the information about one I of the entities within a table. A field is a particular piece of information stored in a table. For example, referring back to Figure 2.1, the first record is all of the information for the employee named Nancy Davolio, Employee ID 1. Some of this information is Introducing SQL listed in the figure, while the rest is off to the right and not visible. On the other hand, there’s also the EmployeeID field, which has the values 1 through 9 for the records in this particular table. Server Depending on what you’re doing, it is sometimes convenient to manipulate records, and sometimes fields. For example, if you want to know everything stored in a database about a particular employee, you’d retrieve that employee’s record from the appropriate table. However, if you want to know the dates of birth of all your employees, you’d need to inspect the contents of the BirthDate field for all records in the same table. WARN I NG Note the ambiguous nature of the term field. Sometimes it refers to an individual piece of information; sometimes it refers to every piece of similar information within a table. When the meaning isn’t clear from context, we’ll refer to these as a field in a record and a field in a table if it’s necessary to differentiate between them. When you inspect a particular field in a particular record, what you see is the value of that field in that record. For example, the value of the first field in the first record in this table is the number 1. Rows and Columns You’ll also find records and fields referred to as table rows and columns. It’s easy to see why this is if you look at Figure 2.1. Database tables are traditionally displayed on a grid, with the fields running across and the records running down. So you might refer to the row in the table for Nancy Davolio, or the column containing information on last names. The terms are completely equivalent, and there’s seldom a reason for pre- ferring one set to the other. The SQL Server documentation usually uses row and col- umn, but much general database literature is written in terms of records and fields instead.
  19. 38 CHAPTER 2 • OVERVIEW OF DATABASE CONCEPTS Null Values As we mentioned above, a value is the actual data stored in a particular field of a par- ticular record. But what happens when there is no data? Consider, for example, a database that records customer information. One of the things that you’d like to keep track of is the fax number for each customer. However, some customers won’t have fax numbers. Or perhaps they have a fax, but you don’t know the number. Figure 2.2 shows a SQL Server table illustrating this. The highlighted customer, Antonio Moreno Taqueria, doesn’t have information stored for their fax number in this database. FIGURE 2.2 Customer with no fax number As you can see in the figure, the answer to this problem is something displayed as . This is SQL Server’s way of displaying a null value. A null value represents the absence of information. You can think of it as a placeholder value in a table; it’s the database’s way of telling you that it doesn’t know what data belongs in that field. Because nulls represent missing information, they cause what is sometimes called null propagation. If you use a field with a null value in a calculation, the result will always be null. For example, you might calculate a line item total by multiplying quantity times unit price. If the quantity for a particular record is null, the answer will also be null. If you don’t know how many you’re buying, you can’t know what the total cost will be, either.
  20. TABLES 39 Field Properties PA R T Not all fields are created equal. That’s obvious if you stop to think about it for a I moment: Phone numbers look different from birth dates, which in turn look different from last names. A full-featured database such as SQL Server lets you capture these dif- ferences by specifying field properties. Introducing SQL Figure 2.3 shows a different way of looking at the Employees table in a SQL Server database. This view shows the schema information for the table, rather than the data that the table contains. The schema of a database is a way of referring to all of the Server design information that constrains what can be stored in that database. FIGURE 2.3 Design view of the Employees table
Đồng bộ tài khoản