MASTERING SQL SERVER 2000- P6

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

0
32
lượt xem
4
download

MASTERING SQL SERVER 2000- P6

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- p6', 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- P6

  1. 220 CHAPTER 6 • SELECT QUERIES 10. On the Start Copying Files screen, click Next to complete the installation. 11. After setup has completed copying files, click Finish. After the short task of installation has been completed, you are ready to configure Full-Text Search for use. The first thing you need to do is create a full-text index. Full- text indexes are created with SQL Server tools, such as Enterprise Manager, but they are maintained by the Microsoft Search Service and stored on the disk as files separate from the database. To keep the full-text indexes organized, they are stored in catalogs in the database. You can create as many catalogs in your databases as you like to orga- nize your indexes, but these catalogs cannot span databases. When a full-text index is first created, it is completely useless. Because these indexes are maintained by the Microsoft Search Service, you must specifically instruct the Search Service to fill the index with information about the text fields that you want to search. This filling of the full-text indexes is called populating the index. As your data changes over time, you will need to tell the Search Service to rebuild your full-text indexes to match your data—this process is called repopulating. In the following steps, you will create a catalog and index for the Employees table in the Northwind database. Employees was chosen because it has a text column in it (actually it’s ntext, which is Unicode as opposed to standard ANSI text). Here’s how to create the index and catalog: 1. While still in Enterprise Manager, click the Northwind icon and from the Tools menu select Full-Text Indexing. 2. On the first screen of the Full-Text Indexing Wizard, click Next.
  2. FULL-TEXT SEARCHING 221 PA R T II 3. On the second screen, you must select a table to index. Here, pick [dbo].[Employees] because it has a text column and click Next. Transact-SQL 4. Each table on which you create a full-text index must already have a unique index associated with it for Full-Text to work. In this instance, select the default PK_Employees index and click Next.
  3. 222 CHAPTER 6 • SELECT QUERIES 5. On the next screen, you are asked which column you want to full-text index. Because Notes is your ntext column, select it here by checking the box next to it and click Next.
  4. FULL-TEXT SEARCHING 223 6. On the next screen, you are asked in which catalog you would like to store this new index. You’ll need to create a new one here, because there are none avail- able. In the Name field, enter Northwind Catalog and click Next. PA R T II 7. On the next screen, you are asked to create a schedule for automatically repopu- Transact-SQL lating the full-text index. If your data is frequently updated, you will want to do this more often, maybe once a day. If it is read more often than it is changed, you should repopulate less frequently. You can schedule population for a single table or an entire catalog at a time. Here, you will set repopulation to happen just once for the entire catalog by clicking the New Catalog Schedule button. 8. On the New Schedule Properties screen, enter Populate Northwind and click OK.
  5. 224 CHAPTER 6 • SELECT QUERIES 9. When you are taken back to the Full-Text Indexing Wizard, click Next. 10. On the final screen of the Wizard, you are given a summary of the choices you have made. Click Finish to create the index. To use your new full-text index, you will need to populate it for the first time. Here’s how: 1. In Enterprise Manager, expand Northwind and select Full-Text Catalogs.
  6. FULL-TEXT SEARCHING 225 2. In the contents pane (on the right), right-click the Northwind Catalog and move to Start Population. 3. From Start Population, select Full Population. With a new, fully populated full-text index in place, you are ready to unleash the power of the full-text search. To do that, you need to know how to modify your SELECT query to work with the Microsoft Search Service that scans your new index. Let’s look at some new clauses for full-text search. Performing Full-Text Searches The nice thing about performing a full-text search is that you already know how to do it, or at least you are very close. Full-text searches are just SELECT queries that use full-text operators. Four operators are used to search through a full-text index: CONTAINS and CONTAINSTABLE: These can be used to get exact or not- so-exact words or phrases from text columns. Not-so-exact means that if you PA R T look for cook, you could also find cooks, cooked, cooking, etc. II FREETEXT and FREETEXTTABLE: These are less precise than CON- TAINS; they return the meaning of phrases in the search string. For example, if you search for the string “SQL is a database server”, you would receive results containing the words SQL, database, server, and any derivative thereof. Transact-SQL The difference between CONTAINS/FREETEXT and CONTAINSTABLE/FREETEXT- TABLE is that the latter don’t return a normal result set. Instead, they create a whole new table for you to search through. These operators are generally used in complex queries that require you to join the original table with the newly created table that came from the CONTAINSTABLE/FREETEXTTABLE query. To see how to use the CONTAINS/FREETEXT operators, let’s execute some queries: 1. Open Query Analyzer and log in using Windows NT Authentication. 2. Execute the following code: USE Northwind SELECT notes FROM Employees WHERE CONTAINS (Notes, ‘”French”’)
  7. 226 CHAPTER 6 • SELECT QUERIES 3. In the result set, notice that each record returned contains the word French. Now execute the following code to test FREETEXT: USE Northwind SELECT notes FROM Employees WHERE FREETEXT (Notes, ‘“selling peace”’) 4. In the result set, notice that each record contains either selling or peace in some form.
  8. FULL-TEXT SEARCHING 227 PA R T II The FREETEXTTABLE and CONTAINSTABLE operators function quite a bit differ- ently from their counterparts. These two operators look through the full-text indexes Transact-SQL and create a brand-new table with two columns: key and rank. The key column tells you the record number of the record that matches your query, so if record number 3 in the queried table matches your query, the key column would simply say 3. The rank column tells you how closely the record matches your query: 1000 indicates an exact match, and 1 indicates a low chance of a match. You can use the new table that is created by FREETEXTTABLE in a JOIN to see how closely each record in your table matches your query. For example, if you want to know who in your company speaks French and German, you could use the following query (also see Figure 6.21): USE Northwind SELECT new.[key], new.rank, emp.lastname, emp.firstname, emp.notes FROM employees AS emp INNER JOIN FREETEXTTABLE(employees, notes, ‘German French’) AS new ON emp.employeeid = new.[key] ORDER BY new.rank DESC
  9. 228 CHAPTER 6 • SELECT QUERIES FIGURE 6.21 FREETEXTTABLE generates a completely new table with a rank column. Let’s examine the result set that comes from this query, as displayed in Figure 6.21. First you told SQL to select the key and rank columns from the table that the FREE- TEXTTABLE operator creates. The key column tells you the record number of the matching record, and the rank column tells you how closely that record matches. Take a look at the first record: The key is 2, which means that the record number is 2 (literally, it is the second record in the table). The rank column is 174—this is the highest matching record in the table. Now read the notes column and notice that it has both German and French. The same is true of the second record in the result set— both German and French are mentioned. In the third record of the result set, you will notice that the rank is 0, which means that it has a very low chance of containing the data you want. In fact, if you look at the notes column for the third record of the result set, you will see that only French is mentioned, not German. The same is true of the other records as well, each having no chance of meeting your requirements. These full-text search queries can be very powerful tools for locating data in large text columns, but they are valueless if you don’t maintain them. Let’s see what it takes to administer your newfound indexes.
  10. FULL-TEXT SEARCHING 229 Administering Full-Text Search There is not a lot of work involved in administering Full-Text Search. The most impor- tant thing to remember is the repopulation of the full-text indexes, and that can be scheduled when you first create the catalog. However, if you underestimate the fre- quency of data updates, you may need to change that schedule. To change the repop- ulation schedule, follow these steps: 1. In Enterprise Manager, expand the database containing the full-text catalog you want to modify. In this instance, it is Northwind. 2. Click the Full-Text Catalog icon. 3. In the contents (right) pane, right-click the Northwind Catalog icon and select Schedules. PA R T II Transact-SQL 4. In the Schedules dialog box that pops up, select the schedule to change and click Edit, then select Recurring.
  11. 230 CHAPTER 6 • SELECT QUERIES 5. Click the Change button and select Daily, and then click OK. 6. Click OK on each screen until you are returned to Enterprise Manager. If you have just made a massive amount of changes, such as a bulk insert, to a table, you may not have time to wait for the scheduled repopulation of the index. You can force repopulation by right-clicking the catalog and selecting Start Popula- tion, and then selecting either Full or Incremental Population. A full population will rebuild the entire full-text index, and an incremental population will update only the changes to the index since the last repopulation.
  12. LINKED SERVER QUERIES 231 The only other administrative activity you need to engage in for Full-Text Search is backing up the indexes themselves. Although full-text indexes are managed through Enterprise Manager, they are not actually part of the SQL Server database structure. In fact, they are stored outside of SQL Server in an entirely separate directory, which is managed by the Microsoft Search Service. To back these indexes up, you need to remember to stop the Microsoft Search Service and include the MSSQL2000\DATA direc- tory in your Windows NT backup strategy. Using all of the tools we have discussed thus far, you can get any data you want out of your server. However, many companies have data spread across many servers. To get to that multiserver data, you need to link your servers and perform linked server queries. Linked Server Queries PA R T A growing number of companies have more than one server from which they need to II extract data to formulate reports. With all of the queries you have seen thus far, this task would be very difficult, because all of these SELECT queries are designed to work with only one server at a time. To get data from multiple servers with standard query methods, you would need to execute SELECT queries on each server and then manu- ally try to combine the results into something meaningful. To ease the process of get- Transact-SQL ting result sets that comprise data from multiple servers, there are linked server queries (also known as distributed or heterogeneous queries). When you perform a query using Query Analyzer, you are asked to log in every time. The process of linking servers allows one SQL Server to log in to another data- base server, just the way you log in with Query Analyzer. This allows SQL Server to perform queries on the remote server on behalf of the end user. The database server in question does not even have to be SQL Server, which means that you can query an Access or Oracle database with this type of query. Two different types of linked server queries are at your disposal: ad hoc and permanent. If you are going to use a particular linked server query infrequently, you should use ad hoc linked server queries. The ad hoc queries do not take up space in your data- base, and they are simple to write. The code to perform an ad hoc linked server query involves using the OPENROWSET command. OPENROWSET creates a new temporary table from a foreign database that can be searched by a standard SELECT statement. For example, code to run an ad hoc query against the Access version of Northwind looks as follows: SELECT Access.* FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
  13. 232 CHAPTER 6 • SELECT QUERIES ‘c:\MSOffice\Access\Samples\northwind.mdb’;’admin’;’mypwd’, Orders) AS Access GO The syntax for OPENROWSET is as follows: OPENROWSET(‘provider_name’,’data_source’,’user_name’,’password’,object) This code signifies that you have selected all records from the Orders table of the Microsoft Access version of the Northwind database. If you need to execute a linked server query on a more frequent basis, OPEN- ROWSET is not going to work for you. For frequent linked server queries, you will need to permanently link your server with the sp_addlinkedserver stored procedure. This stored procedure will allow the local server (where the user logs on) to log on to the remote server and stay logged on. With OPENROWSET, the link is disconnected every time the query is finished. To link a SQL Server named Washington, for example, you would use the following: sp_addlinkedserver ‘Washington’, ‘SQL Server’ To query the Northwind database on the Washington SQL Server machine, all you need to do is add it to your SELECT query, as follows: SELECT * FROM Washington.Northwind..Employees Linking to a non-SQL server is just as easy—it just requires a little more typing. Here’s how to link to the Northwind database on an Access machine named Market- ing on a more permanent basis: sp_addlinkedserver ‘Marketing’,’Microsoft.Jet.OLEDB.4.0’, ‘OLE DB Provider for Jet’, ‘C:\MSOffice\Access\Samples\Northwind.mdb’ To query the newly linked Access database, all you need to do is use the following: SELECT * FROM Marketing.Northwind..Employees Summary That was a lot of information—but rest assured that you will use everything you have read here at some point in your illustrious career as a SQL Server guru. The first thing you learned here was how to use a basic SELECT query to retrieve data from a single table in your database. After examining the result sets from the basic queries, you dis- covered that there is just too much information displayed, so you learned how to use WHERE to limit what is returned in the result set. Next, because most databases have more than one table in them, you learned how to use JOINs to combine the information from multiple tables in a single result set.
  14. SUMMARY 233 Then, you figured out that the result sets are not in any particular order when they are displayed, so you learned how to bestow organization upon them using the ORDER BY clause. Even with ORDER BY, though, your result sets still didn’t look enough like reports to be easily read, so you went through the process of adding summary and detailed information using GROUP BY with the HAVING, ROLLUP, and CUBE operators. COMPUTE and COMPUTE BY were then used to generate the same detailed and summary information, just in a slightly different format. After that, you learned the proper use of TOP N to retrieve the top percentage of a group of values, such as the top 5% of salespeople in a company. Afterward, you found that Full-Text Search could greatly enhance SELECT queries by allowing you to find words or phrases in your text fields. Finally, you discovered the power of the linked server query, which allows you to access data from more than one server at a time during the same query. With SELECT queries under your belt, you are ready to move on to action queries. PA R T II Transact-SQL
  15. This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
  16. CHAPTER 7 Action Queries F E AT U R I N G : What Are Action Queries? 236 Delete Queries 237 Update Queries 242 Insert Queries 257 Summary 263
  17. A s you saw in Chapter 6, SELECT queries allow you to retrieve the data from your database in a flexible manner. However, there’s more to using a data- base than just retrieving existing data. There are three other fundamental operations you need to be able to perform: • Deleting existing data from tables • Making changes to existing data in tables • Inserting new data in tables Fortunately, the T-SQL language provides a mechanism to accomplish all of these tasks. That mechanism is the action query, and in this chapter, you’ll learn how to construct and use action queries to perform these three fundamental operations. What Are Action Queries? Action queries are SQL statements that modify one or more records in an existing table. These statements include: • DELETE statements, which can delete individual records or even every record in a table • TRUNCATE TABLE statements, which delete every record in a table • UPDATE statements, which can make changes to one or more columns within one or more records in a table • UPDATETEXT statements, which can make changes to text or image columns • WRITETEXT statements, which can insert new values in text or image columns • INSERT statements, which can insert one or more rows into an existing table • SELECT INTO statements, which can create an entire new table from existing data In the rest of this chapter, we’ll explain the syntax of each of these seven types of statements and show how you can use them in your own applications. N OTE Action queries work on existing tables. To create a new table, you can use a CREATE TABLE statement; to completely destroy a table, you use a DROP TABLE statement. You’ll learn about creating and dropping tables in Chapter 11.
  18. DELETE QUERIES 237 Delete Queries There are two different statements that you can use to delete records from an existing table. DELETE statements are the more flexible of the two and allow you to specify exactly which records you wish to delete. When you want to delete every record in a table, you’ll find that TRUNCATE TABLE is faster and uses fewer system resources. Syntax of DELETE The DELETE statement has a number of options, but the basic syntax is fairly straight- forward: DELETE [FROM] { PA R T table_name [WITH (table_hint […n]]) | view_name II | OPENQUERY | OPENROWSET | OPENDATASOURCE } [FROM table_source] [WHERE search_conditions] [OPTION query_hints] Transact-SQL Taken piece by piece, here’s what’s in a DELETE statement: • The DELETE keyword identifies the statement. • The optional FROM keyword can be used if you think it makes the SQL more understandable. • You have to specify either a table name, a view name, or the results of an OPEN- QUERY, OPENROWSET, or OPENDATASOUCE function as the source for the rows to delete. OPENQUERY, OPENROWSET, and OPENDATASOURCE are dis- cussed in Chapter 8. • The optional WITH clause can be used to provide optimizer hints for the table. Optimizer hints are also discussed in Chapter 8. • The FROM clause has the same syntax and options as the FROM clause in a SELECT statement, which you’ve already seen in Chapter 6. • The WHERE clause has the same syntax and options as the WHERE clause in a SELECT statement. • The OPTION clause can be used to provide further hints, which are also dis- cussed in Chapter 8.
  19. 238 CHAPTER 7 • ACTION QUERIES Overall, the DELETE statement is very similar to the SELECT statement. In fact, as long as a SELECT statement doesn’t contain any aggregate functions, you can create a DELETE statement to delete the corresponding rows simply by replacing the SELECT keyword with the DELETE keyword. Limitations of DELETE If a DELETE statement uses a view rather than a table as the source for the rows to be deleted, that view must be an updateable view. Updateable views have no aggregate functions or calculated columns. In addition, a view in a DELETE statement must contain precisely one table in its FROM clause (the FROM clause used to create the view, not the FROM clause in the DELETE statement). NOTE For more on updateable views, see Chapter 13. If you omit the WHERE clause from a DELETE statement, the statement will delete all of the rows in the target table. If you include a WHERE clause, the statement deletes only the rows that the WHERE clause selects. A DELETE statement cannot remove rows from a table on the nullable side of an outer join. For example, consider a DELETE statement with the following FROM clause: FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID In this case, the Orders table is nullable. That is, the columns from that table will contain null values for rows corresponding to Customers who have not placed an order. In this case, the DELETE statement cannot be used to delete rows from the Orders table, only from the Customers table. If a DELETE statement attempts to violate a trigger or a referential integrity con- straint, the statement will fail. Even if only one row from a set of rows being deleted violates the constraint, the statement is cancelled, SQL Server returns an error, and no rows will be deleted. If you execute a DELETE statement on a table that has an INSTEAD OF DELETE trigger defined, the DELETE statement itself will not be executed. Instead, the actions in the trigger will be executed for each row in the table that would have been deleted. You’ll learn about triggers in Chapter 15.
  20. DELETE QUERIES 239 Examples of DELETE The simplest possible DELETE statement just deletes all the rows from the target table: DELETE authors WARN ING If you try this or any of the other SQL statements in this section, you will destroy rows in your database. For safe experimentation, make copies of tables and prac- tice your DELETE statement syntax on those copies. Optionally, if you’d like the SQL statement to be a bit more readable, you can include the FROM keyword: DELETE FROM authors To delete a single row, you need to include a WHERE clause that specifies that par- PA R T ticular row: II DELETE FROM authors WHERE au_fname = ‘Dean’ Or, with a less restrictive WHERE clause, you can delete multiple rows, but less than the entire table: Transact-SQL DELETE FROM authors WHERE phone LIKE ‘415%’ WARN I NG To check that a DELETE statement will delete the rows you intend it to delete, you might want to use SQL Query Analyzer to examine the results of the corre- sponding SELECT statement (SELECT * FROM authors WHERE phone LIKE '415%' in the case above). You can also delete rows from one table based on rows from another table by using the second FROM clause. Consider the case where you have Customers and Orders joined on a common CustomerID field. In this case, you could delete all of the Orders for Customers who are in France with the following statement: DELETE FROM Orders FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID WHERE Customers.Country = ‘France’
Đồng bộ tài khoản