17. Establish and use indexes
What is an index?
Index concept
Establish an index
Re-establish index
Use index
Design effective index
Summary of this chapter
For the database designer, the index is one of the most useful tools. Index is a auxiliary structure that allows you to enhance query efficiency, which reduces the number of I / O motions necessary for retrieval query data. In other words, the index allows the Microsoft SQL Server 2000 to find information with fewer I / O operations to reduce the amount of search information. When you use the index of the database data sheet to retrieve the data column, SQL Server can quickly determine the location of the data stored and immediately capture the information. Therefore, the index of the database data sheet is quite similar to the index used to book - both provide fast capture, especially when we face a lot of information.
In this chapter, you will learn the basic concepts of indexes, including how to build an index and which different types of indexes available in SQL Server. You will also learn when you should use the index, when you don't use the index - because the index is not uncomfortable tool, in some cases, use the index but may be harmful to the system.
What is an index? As mentioned earlier, the index is the auxiliary data structure used by SQL Server to access the data. According to the type of its type, the index may be stored with the information, or it may be stored separately from the data. Without its type, it is basically the same, and this is also the part you will learn in this section. In a system that is not indexed, all data retrieved will be done by scanned data sheets. In the operation of the scanned data sheet, all the information needs to be read and the data of the query is compared with each other. Generally speaking, we will try to avoid data sheet scan - because this operation can cause an amazing I / O number, especially when scanning a huge amount of information, it may have a lot of time and eat a considerable system. Resource. If you use an index, you can decrease the number of I / O operations, enhance the speed of data access, and release system resources to other jobs as soon as possible. The database index is organized in a Type B tree structure. Each page in the index is referred to as an index paging or an index node. The index structure begins by the root node of the top layer. Root Node indicates the starting point of the index; when you need to find information, it is the data that is first accessed. The root node also includes some index data columns; these index data columns contain one key value and a indicator called the intermediary node points to another index page, as shown in Figure 17-1. This configuration is necessary, because in an average size data sheet, the index may be composed of thousands of index paging. SQL Server can "enlarge" the information you want by starting by the root node and browse the intermediary node.
Figure 17-1 root node and intermediary node
When we use the index in the book, the method used may be very similar to the following: assuming the index is a directory page that contains a page containing letters such as "A", "B", "C". Start. Then turn to "a" this page, and it will find the page number where "AA-AB", "AC-AD", "AE-AF" is located. Enter "AA", which is also the page number where "AAA-AAB", "AAC-AAD", "AAD-AAF", etc. is listed. By this arrangement, you can quickly find the information you want, not to swipe all the book. The index is used as the first page with a root node, which is quite similar to the above. As the root node, each intermediary node contains an index paging, and the paging contains an index funding column. Each index assembly column points to other intermediary nodes or branch nodes, as shown in Figure 17-2. The branch node consists of the bottom of the index. Unlike the root node, each intermediary node also includes a link list that connects to the same layer intermediary node. In other words, this node knows the neighboring node and the lower level node. As the name, the so-called "B tree" means that its type is like a tree branches after the root node expands the intermediary node. The intermediary node of the same layer in each set of tree is referred to as the index hierarchy, as shown in Figure 17-3. The number of I / O operations required to reach the branch node (the bottom of the tree structure) should be determined according to the number of the index level. If the data containing the database is rare, the root node can directly point to the branch node, the index will not need to include any intermediary node (of course, this situation is unlikely). Figure 17-2 shows the tree of the mediation node and the branch node
Figure 17-3 Index Level
In a non-plenary index, the branch node contains a key value, and a data column identification code (Row ID) that points to the data column in the data sheet, or a clustered index key value - if the data table is A clustered index. In the cluster index, the information itself is in the branch node (the cluster and non-cluster index will be explained later in this chapter. " The number of data columns in the branch node should be used to see the size of the items referenced when indexing. In the cluster index, it is necessary to depend on the size of the information.
Description
The data column identification code is an indicator that SQL Server is automatically established by the file number, the subtime code, and the data column number. With the information column identification code, you only need an additional I / O operation to take information. Because you know which pagination is to be captured, and SQL Server knows the location where the page is located, the page can be read into the memory as long as a single I / O operation can be read. This simplification of this retrieval is also the main reason why the index is more efficient and enhanced.
It is necessary to pay special attention, because the index is established in sorting, and the change of any information may result in an additional burden. For example, if the insertion action causes a new data column to be inserted into a full branch node, SQL Server must move out of space to new information columns. This move must be approached to the other paging in the branch node. This information is moved as a pagination separation (Page Split). Page separation causes a series of separations of the index tree. Through a cautious set filling factor (Fill Factor) can avoid paging separation, we will introduce the fill factor in this chapter.
Index concept Now you have already had a basic understanding of the index structure, then let us see some concepts related to the index. In this section, you will learn index keys, index uniqueness, and types of indexes. The index key index key (index key) indicates a data line or several data used to generate an index. The index key is a value that allows you to quickly find the information column, which contains the information you need. The index key is like an index item in the book, which can point to a specific topic in the text. To use an index to get a data sheet, you must include an index key value in the SQL stateful WHERE clause, but also to see an index is a simple index or a composite index. Simple Index Simple Indexing is an index of a single data line, as shown in Figure 17-4. If you have the efficiency of the index, the data must be referenced in the SQL statement where clause. Figure 17-4 Simple index
According to the data stored in the data, the number of SQL statements such as the uniqueness item in the data line, and sometimes use simple indexes can be efficient, but some cases may use the composite index. Will be appropriate. For example, if you index a contact with a thousand name and address, the State data should not be a simple index candidate key because there may be many identical items in the same state. However, if street and city data line is added to the index, it makes it a composite index, which allows each item to be almost unique. This method is very useful - if your query is to find the information listed in the address. Composite index composite indexes are indexes defined more than one data line, as shown in Figure 17-5. The composite index can be accessed with a multi-index key. In SQL Server 2000, the index can contain up to 16 data lines, and the total length of key value cannot exceed 900 positions.
Figure 17-5 Composite index
When you use a composite index to query, you don't need to put all index keys in the SQL stateful WHERE clause, but use more than one index key will be relatively smart. For example, it is assumed that the index is established by the three data lines A, B, and C in the data sheet, which can be accessed using SQL statement including (A and B AND C) or (a and B) or A. index. Of course, use the most stringent WHERE clause, such as containing A and B and C, which is more efficient. It can retrieve less information columns from the database because it is more clear when it is verified. If you use A and B or only A, you will start an operation of index scanning. An index scan will occur when more than one index item meets the search criteria. In the index scan, the node of the index will be scanned to obtain multiple data sets. In addition, the index only covers the selected value. For example, if the index is established by the three data lines of A, B, and C, the query specifies A, all data columns that satisfy a value will be returned, including all B and C values. Since the basic data used in the index is arranged in a digital arrangement, the SQL Server query optimizer can determine the index paging range of the data you need. Once you know the start page and end page, all paging containing the data value will be retrieved, and the information will be scanned to select the information you need.
Real World CustomerLocation Data Sheet assumes that we have a list of information, which contain information about our company's customer address. The index is established by State, Country, and City data lines and stored in the order of State, Country, and City in the Type B tree structure. If a query specifies the value of State in the WHERE clause, the function of the index will be used. Since Country and City's values are not specified in the query, the index will return to the data column of all the State data set in the index funds. The index scan will be used first to retrieve the range of index paging, and then quote information pagination according to the value of the State Data. Index paging will be sequentially read, and the way the information table scans the access data paging. Note If you want to use an index, you only use at least one index key in the WHERE clause of the SQL query, the index will work. With a just example, if you only have the name or phone in the WHERE clause of the query, you will not use the functionality of the index.
In most cases, the index scan is very efficient. However, if more than 20% of the data columns will be accessed in the information list, the data sheet scan of all the information columns in the read data sheet will be more efficient. Index utilization query efficiency, you have to treat you how to use indexes (this chapter will later use index> one section) and we will follow the uniqueness of the index.
Uniqueness of indexes
You can define SQL Server's indexes as unique indexes or non-only indexes. In unique index, the value of each index key must be unique. Nonunique Index, allows the value of the index key to be repeated. The effectiveness or efficiency of non-unique indexes should be derived from the selectivity of the index.
Single index
Each index key of the unique index contains only one list - in other words, the index key value will not be twice in the data sheet. The only index is very efficient because they guarantee that the information you need to get queries requires only one additional I / O operation. SQL Server will force a uniqueness of one or several data lines to establish an index key. SQL Server will not allow duplicate key values to be inserted in the database. If you try to do this, the error message will be sent back. When you create a Primary Key condition constraint or a UNIQUE condition constraint in the data sheet, SQL Server will establish unique indexes. Primary Key Condition Constraints and UNIQUE Conditions Constraints have been discussed in Chapter 16.
When the data itself is unique, the index can naturally establish its uniqueness. However, if the information contained in the information line is not unique, you can still use a composite index to build a unique index. For example, the Last Name data line may not be unique, but if you are combined with the First Name and Middle Name Data, you can build a unique index on the data sheet.
Description
If you insert a data column in the data sheet, the unique index will have a repeated index key value, which will fail.
Non-unique index
Non-only indexed mode of operation is nothing wrong with the uniqueness index (except that the repetition value can be included in the branch node). All repetition values will be captured as long as the reference specified in the SELECT statement is compliant.
Non-only indexes are not as efficient as unique indexes because it requires additional programs (additional I / O operations) to retrieve the information required for queries. However, some applications need to be used to use repeated key values, which may not be able to establish a unique index. In such cases, non-unique indexes are at least better than no indexes.
There are two types of type B tree-like structures indexes: Clustered Indexes and Nonclustered Indexes. The cluster indexs stores the information columns of actual information in their branch nodes. Non-clustered indexes are an auxiliary structure that refers to the data location in the data sheet. In this section, we will look at the different places between the two index types. We will also introduce full-text index in this section, which is actually more like a directory instead of index. The cluster index is as described above, and the cluster index is a B-type tree-like structure index that stores the actual data column of the data sheet in the order of the branch node, as shown in Figures 17-6. This system provides some advantages and disadvantages. Figure 17-6 Cluster index
Since the information of the cluster index is stored on a branch node, it is possible to obtain information once it reaches the boss node, which means that as long as the minority I / O operation can be obtained. The reduction in any number of operations can make a single operation to produce higher performance, and improve the overall performance of the system. Another advantage of a cluster index is that the information will be captured in the order of index sorting. For example, if there is now a cluster index is created by State, Country, the city data line, and the query selects all the value of all state? Texas, the output of the output will be sorted when the country and city data line are defined during indexing Sort by means. If the application is considered to be in design time, this feature allows you to avoid unnecessary sorting operations. For example, if you have determined the sorting mode needs to be required and used in the index, you can use the clustered index to allow you to perform the job after you get the information. One disadvantage to use the Congs index is that the access data table often needs to shuttle the entire index, so that the system will cause an additional burden. SQL Server Acquires Search Corps from the root node when you reach the branch node containing the information. If many dividend nodes are established due to the number of data, the number of index hieles required for these branch nodes is also quite, SQL Server will need more I / O operations from the search process of the root node to the boy node. . Because practical information is stored in the Congye Index, you can only create a clustered index on a data sheet. On the other hand, you can build less than a non-clustered index at the top of the cluster data sheet. (The cluster data sheet is only a data sheet with a clustered index.) You should use the most frequently accessible index keys to establish a clustered index, so that you can save information in the best time through the cluster, and provide the best Efficiency. Non-clustered indexes are different from clustered indexes, and non-cluster indexes do not contain practical data in their branch nodes. The branch nodes contain one of the positions of the two information columns. First, if there is no clustered index on the data sheet, the branch node of the non-incompatant index of this information sheet is stored in the information column identification code, as shown in Figures 17-7. Each data column identification code points to the information column where the actual data exists in the data sheet. The values constituting the information column identification code include the data archive identification code, the page number, and the data column number in the paging. This value accurately indicates the location stored in order to quickly access actual information.
Figure 17-7 Non-clustered index on information sheets without cluster index
If there is a clustered index on the data sheet, the boss node of non-clustered index will contain the cluster index key value of this information, as shown in Figure 17-8. When reaching the branch node of the nonset index, the clustered index key value can be found, and then it is used to continue searching in the cluster index until the information is found in the branch node indexed in the cluster.
Figure 17-8 Non-clustered index on a data sheet with a clustered index
It has been said before, and each data table can only have a clustered index. You can build 249 non-clustered indexes on each data sheet, but it is not a smart approach (this chapter will explain this later). It is common to have several non-cluster indexes on different data on the data sheet. The WHERE clause uses which index is determined by the query optimer.
Full text search index
As mentioned earlier, SQL Server's full text search index is in fact like a directory instead of an index, and is not a T-tree structure. The full text search index allows you to search for information on groups of keywords. The full text search index is part of Microsoft Search Services and is widely used in Web website search engines and other text-based operations. Unlike the Type B tree structure index, the full text retrieval index stores outside the database, but is maintained by the database. Since it is stored outside, the index can maintain its structure itself. Perform a full text search index with the following restrictions:
A full-text retrieval index must include a line of lines, and the information line is unique in each column in the data sheet. The full text search index must also include one or more character data lines in the data sheet. Each piece of information can only be allowed to have a full-text search index.
The full-text search index is not like automatic update as the B tree structure index. In the B type tree structure index, the insertion, update or deletion of the data sheet will update the index. However, in the full-text search index, these operations on the data sheet will not be automatically updated, and you must update in a schedule or manual way. The full text search index has many rich features that cannot be found in the Type B tree structure index. Since this index is designed as a text search engine, it supports more features than the general text. You can use a full-text search index to search for single words, a word, a single word or a single collection, or search similar single words. In this chapter, you will learn how to build a full-text index. Establishing an index establishment index is not difficult. Establishing a cluster and non-assembly index is not large, and the elves provided by Enterprise Manager can be used to establish the SQL command CREATE INDEX. In this section, you will learn how to use these two methods to build an index, and learn related issues that fill the factor, and how to use the pre-depreciation index.
Description Although the wizard is easier to use, if your job needs to be repeated or have to create multiple similar database, you will find a tool for the instruction file. The instruction file allows you to create program files and can be executed repeatedly.
Use the establishment index wizard. If you intend to establish an index on the data sheet, the data sheet must be in the database. You can use the Establishing Index Elf to build a cluster or non-clustered index on the information sheet, follow these steps:
Open Enterprise Manager, press the Execute Springs for the Tool Column to display the Select Elf Dialogue. In this example, we will use the Northwind database. Expand the database, select the establishment of an index wizard, and then determine. Welcome to establish an index wizard screen, as shown in Figure 17-9. Note that the name of the server you choose and the database will appear in the title column.
Figure 17-9 "Welcome to the Establishment of Index Elf"
Press Next to display the selection database and data sheet screen, as shown in Figure 17-10. Here you can specify the database data sheet you want to establish an index. The preset repository is the database you have selected when you open the elves. There will also be a list for you to choose. Press Next to continue to the current index information picture, as shown in Figure 17-11. This example uses a Customers data sheet because it contains a lot of data columns. As you can see, several index have been established on the Customers Data Sheet, including a clustered index and four non-cluster indexes. Remember, only one clustered index can be established on a data sheet. The index that has been established on all Customers data sheets is a simple index (only one data line) and each index is built on different data lines. When Query Optimizer analyzes a query and selects the query execution scenario, it will determine which index to use in accordance with the currently available index and WHERE clauses.
Figure 17-10 Select the database and information sheet screen
Figure 17-11 Current index information picture
Press the next step to display the selection line screen, as shown in Figure 17-12. This picture allows you to select the data line to be included in the index. You don't have to worry about the order of the information - you can still change them later. Figure 17-12 "Selection Data Line" screen
To specify the data line intended to be included in the index, you can select the Nuclear Note on the right side of the data line. In this example, we will establish a composite index on the CompanyName, ContactName, and Region data line. Press Next to display the specified index option screen, as shown in Figure 17-13. This picture allows you to set some important options to decide what indexes to build. You can choose the category index to get the cube, so that the new index becomes a clustered index. In this example, the verified block used to establish a clustered index is unpubable because the Customers data has established a clustered index. You can also choose to make this unique index option, so that this index is unique index and is not unique index. You can also specify the fill factor to optimize or use a fixed ratio. Since the data columns in the index are stored in its order, SQL Server may sometimes need to move the data to maintain its order. Filling the factor option allows you to specify that the newly established index is filling to what extent, you should reserve the space to make future insertion movements can be used. The preset fill factor (if you choose to optimize it is a preset value) is 0, that is, specifying the branch node but reserves some spaces in the higher level of the index node. For detailed situations of filling factors, see this chapter.
Figure 17-13 Specifying an index option screen
Select your index option and press step to display the establishment index wizard screen, as shown in Figure 17-14. In this picture, you can rearrange the order of information that make up the index. Select the information you want to move, press or move until the data line reaches the location you want. You can also specify the name of the index in this picture.
Figure 17-14 "Completing the Establish Index Elf"
The order of composite index is quite important. SQL statements must take advantage of the advantages of the index, and the statement must be an important part of the index in the stateful WHERE clause. Figure 17-15 shows the same picture, but the index has renamed CustomrareaIndex, the order of the data line is also rearranged as Region, CompanyName, and ContactName.
Figure 17-15 Sequence in rearranging the data line in the index
Since the index data is arranged in this order, the SQL statement that uses this index must contain the Region Data in its WHERE clause, as Region is the main data line. Of course, statements can also be used in the WHERE clause, or even region, companyName, and CompanyName are used. If you use all three values in the WHERE clause, you will achieve the best performance because the least I / O operation is performed. The order in which the data line name in the WHERE clause does not have any effect. Once you have satisfied the order of the information line, press the index of the index will start establishment. The process of indexing creation may be a few seconds or may be a few hours, the length of the time is quantity, system performance, disk performance, and system memory size. To establish an index on the data sheet, SQL Server must read all the information on the data sheet, so the growth rate of time consumption is quite large.
Note If you create a unique index, the index key finds a duplicate value, the index settler will fail.
Use the establishment of an index elf to establish an index is quite simple, but there will be some shortcomings during the establishment process. Especially when you use the establishment index wizard, it will not leave any information related to your job, so each time you want to create another index, you must make it again from the head until the program will be done from the head until the program. Relatively, if you use an index, you can use the instruction code file, you can use it repeatedly. If you plan to rebuild the database, you must establish an index wizard again for each index in the database. However, after you build an index, you can use Enterprise Manager to generate SQL instruction codes. Use Transact-SQL if you use Transact-SQL (T-SQL) to establish an index, you can store the command into a command code and repeatedly used again, you can also modify the index created instruction code to establish additional indexes. In addition, this approach also allows you to be more flexible when establishing an index because there are more quotes available. To build an index using this technology, simply store the T-SQL command into a file and use the following syntax to read the file into osql: OSQL -UUSERNAME -PPASSWORD This command assumes that your file name is CREATE_INDEX.SQL. You can also use Query Analyzer to perform the instruction code. (For information on this program, please refer to Chapter 13.) To create an index with T-SQL, you must use the CREATE INDEX command. The syntax of CREATE INDEX is as follows: Create [unique] [clustered | nonclustered] INDEX INDEX_NAME ON TABLE_NAME ( Column_name [, Column_Name, Column_name, ...] ) [With options] [On filegroup_name] The value in the brackets is selective. You can establish indexes as uniqueness or non-unique indexes, clusters, or nonset consolidations, using one or more data lines, and you can use the selective quotes listed in Table 17-1. You can also selectively specify the archive group group stored in the index. Table 17-1 Selective quotes used in create index commands Quoter Description PAD_INDEX with the Fill_Factor extortion, specifies the space that should reserve at a higher layer node other than the branch node, not only in the branch node. Fill_Factor? Number Specifies the extent of each branch node filled with percentage (from 0 to 100). Ignore_dup_key When trying to insert a repetition value for a uniqueness index, specify ignore its insertion action and issue a warning. If IGNORE_DUP_KEY is not specified, the entire insert will be restored. DROP_EXISTING Specifies the existing same name index that should be removed and rebuilt the index. This quoter is useful when you re-establish the Congye index on a data sheet that has been indexed with a non-uncomposed index. Statistics_noreCompute specifies that statistics will not be recalculated. This option is not recommended because the execution scenario is based on an outdated material, which may not be an optimized state. This quotes are only used when you plan to manually update the statistics. Related Information For more information on these selective quotes, you can use the "Line Book" index Create Index, and select Create Index in the found topic dialog (Transact-SQL Reference). When establishing an index, it is a good choice to use T-SQL compared to the establishment of an index wizard. Although it is more difficult to use T-SQL, it will find that the use of T-SQL to create multiple indexes than the establishment index wizard is simple. Using Filled Factor Avoiding Page Separation When you have an indexed data table, the index paging must be updated when there is an update or insertion. Index paging is a link to each other. Each cable page has two indicators, one pointing the next page and another pointing forward one page. When the index palette is filled, the index will cause changes to the indicator chain, as new pactions must be inserted between the two pairs, and this process is called index page split, so new information Can be placed in the correct location in the index chain. SQL Server will move the already existing paging (the page of the new information must be placed) to the new index paging. The two pactions that are originally directed to each other must now point to new pactions, and new paging must also point to the original two pages (forward and backward). Now the new paging in the point in the chain is correct, but the order of index paging in the database is naturally in the order. Since the index information leverages constantly adds the index (if the update or insertion occurs), the index paging is limited, and more and more index paging will be filled. When this happens, the new paging must find additional space. To establish more space, SQL Server must repeatedly execute index page dividers, resulting in increasing system loads, because these work requires more CPU usage and additional I / O operations. This will also cause an index of a piece. The index data is spread throughout the database, and the effectiveness is thus incomparable. Figure 17-16 Index paging separation To reduce the problem of paging separation and snipping, it is to adjust the filling factor of the index node. When you establish an index, fill the fill factor specify the percentage of the node filled with your additional index information column. You can use the T-SQL statement crete index's Fill_Factor option to specify an index filling factor, just as before. If the Fill factor is not specified in the CREATE INDEX command, the system will use the preset value. The preset value can be set using the sp_configure extract Fill Factor. When you install SQL Server, the preset value is set to 0. Note Only when it is created when establishing an index, it will affect the index. When the index has been established, it will not produce any effect. Filling the value of the factor (ranges from 0 to 100) specifies the percentage ratio of the index paging being filled. It is more special when the value is 0. When the filling factor is specified as 0, the branch node will be completely filled, but the intermediary node will reserve some spaces with the root node. This value is SQL Server installation preset value and usually does not have any problems. If the index is established, the fill is specified by 100, and all index nodes will be completely filled. This value is the best choice for any update action without any new information insertion. The indexed boss nodes and higher-level nodes will be completely filled, and any insert will be separated from page. This setting is most ideal for reading the information table, even if there is information, it will be deleted, because the data deletion does not cause a pages separation. Lower filling factors will reserve a number of spaces to insert, but maybe more extra spaces to establish an index. Unless you perform a fixed insertion action on the database, it is usually not recommended to use a lower filling factor setting value. If too much pagination separation occurs, try to reduce the set value of the fill factor and rebuild the index, see if the number of paging separation will be reduced. Using the Page Splits / Sec to Using the Efficiency Monitor to find out how many pags separated per second. This counter can be found in the SQL Server: Access Methods object. If the paging separation has been caused, and your index has also become severely fragmented, the solution is to rebuild your index. A sniffer occurs only when you use the filling factor to reserve space for the index paging. These spaces will be filled sooner or later. For more information on this, please refer to this chapter later Expand the database in the Select Elf dialogue. Select the full text search index wizard, press to determine. Welcome to the SQL Server full-text search index wizard screen, as shown in Figure 17-17. Figure 17-17 "Welcome to the SQL Server Full Text Search Index Elf" Press the next step to enter the selection database screen. We choose Northwind as an example. (Instead If you have selected the database before pressing the Wizard button, this screen will not appear.) Press Next to display the selection table screen, as shown in Figure 17-18. Let's choose a Customers Data Sheet and press Next. Select the index dialog box appears. This elves need you to choose an existing uniqueness index to use with full-text search operations. The Customers Data Sheet is currently only available in PK_CUSTOMERS. Figure 17-18 "Select Information Table" screen Press the next step to display the selection list information line screen. Here you can choose to apply for information on the full text search query. Figure 17-19 shows a case where several data is selected. Figure 17-19 Selecting the Data Sheet Data Line screen, the case of several data lines have been selected Press Next to display the full text search directory screen, as shown in Figure 17-20. This screen allows you to choose to use an existing index directory (if you already have any words) or create a new directory. If you intend to establish a new directory, make sure the directory is placed in the I / O system supported position and enter a name sufficient to describe this directory in the name text block. Figure 17-20 Choose a full text search directory screen Press Next to display the selection or establish an extension schedule (selective) screen, as shown in Figure 17-21. The full text search index is different from the B tree index, which does not update it when the data is inserted. The scheduling function allows you to specify an index every time you have an update action. Here you can choose an existing schedule (if any), or create a new schedule to extend the directory, the new schedule can be found, or to retrieve directory each full-text (a directory You can include multiple data sheets that enable full-text retrieval indexes) and will otherwise it is to choose not use. If you intend to establish a schedule, you can choose to completely expand or increment extensions. Fully extended means that the system will establish an index item for all data columns (or all of the data in the directory) (if they have index items, they will be rebuilt). Fully expand usually only occurs when the directory is established. Incremental expansion is only to update their index items only in the data sheets modified in the data sheet. To perform incrementation expansion, there must be a TimeStamp data line in the data sheet. If not, full expansion will be performed. Figure 17-21 Select or establish an extended schedule (selective) screen From here, you can continue the elves or choose a schedule. If you press the next step without building any extended schedule, the full text retrieval index will only be established once at the end of the sprite (rather than re-establishing every other time). Note Because full-text retrieval index does not stop updating when the database change occurs, you must periodically update it. The scheduling function allows you to make the update action of the full-text retrieval index can be automatically executed according to the time. Once the schedule is established, the index will be updated according to the setting of the schedule. Press Next to display the SQL Server full-text search index wizard screen, as shown in Figure 17-22. Click to complete, the full text search index wizard will create a full-text search index directory. If you have developed an updated schedule, the schedule will begin. Once a directory is established, you can start using the full-text search index. Figure 17-22 "Complete SQL Server Full Text Search Index Elf" Establish a full-text search index using a pre-stored program You can also use the pre-depreciation program to establish a full-text search index. This is only a summary introduction to how to use a pre-deployment to establish a full-text search index, please refer to SQL Server 2000 online book. Call SP_FULLTEXT_DATABASE pre-depreciation, using the Enable extract to enable full-text search indexes in SQL Server. Call sp_fulltext_catalog, use the CREATE rating to establish a directory. Call sp_fulltext_table to establish a corresponding connection to the data sheet / index. This pre-depiction must call with the CREATE quotes and must provide a full-text search index to use the data table name and the name of the unique index. Call sp_fulttext_column to add a number of data used to retrieve indexes in full text. This pre-depoxing program must be executed in the add option and the data line name intend to join the directory, and each of the indexes must be executed once. Call sp_fultext_table again. This pre-depicting program call needs to use the Activate quotes to launch a directory containing the data sheet. Call SP_FULLTEXT_CATALOG again, this time you need to use the start_full quotes to start the full expansion of the directory, and the full-text retrieval index will take each data column of each data sheet associated with the directory. Use the pre-depreciation program to establish a full-text search index, which is complicated to establish a T-SQL command to establish a T-tree structure index. However, if you must establish a lot of full text retrieval index directory, it is best to design a command code to perform this task, although it is a bit trouble, it is quite worth it. Using the full text search index Once the full-text retrieval index is established, it is easy to use its function. You can specify T-SQL keywords that use full-text search index: contains and freetext. If you do not use the full-text search index, use SQL search strings, the following statements are typical ways you will use. As you can see, the WHERE clause of the query will use like keyword: select * from customs where contactname Like '% pete%' This statement may not be able to get the results you want. To use more convenient query, and use the full text search index, you can use the Contains. Contains The words must contain the data line name and the specific text you want to retrieved: Select * from customers where Contains (ContactName, '"pete") Contains The word will use full-text search index to find text strings that contain specific text, such as "Peter" or "Petey". You can also use the FreeText keyword to search the full text search index. Like Contains, Freetext is used in the WHERE clause. FreeText can be used to find single words (or related single words), which match the single-word bullets you specified in FreeEtext, but the single word that is not necessarily exactly the same as the single words you specified in FreeEtext. The way to use Freetext in SQL statements is as follows: Select CategoryName from Categories Where Fretext (Description, 'Sweets Candy Bread') This query will find some names that contain words like "SWEETENED", "Candied" or "BREADS". Renovating the index SQL Server will retain statistics for each index, which describes its uniqueness or selectivity, and discrete to index key values. SQL Server query optimizer then uses these statistics to determine which index that uses to meet the needs of specific queries. Under the preset condition, the index statistics will be updated regularly. However, since the paging separation actually dispersed the index paging in the database, the index may become extremely fragmented after a period of time, the result is the deterioration of the performance of the system. Indexing may also become unbalanced, that is, some part of the index tree has more filled index pagings compared to other parts. You can re-set the index to reply to its balance and continuity. In addition, the statistics will also be rebuilt when re-establishing an index. But please pay special attention, do not need to remove the index and rebuild index first. We will explain this problem later. Description In the preset condition, the index statistics will be automatically updated, but you can close this feature using the sp_autostats pre-depression. Another problem is that the index will have a index hierarchy that is more than appropriate demands. More index hierarchies mean that each index query requires more I / O operations. You can re-establish the index to reduce the number of index hierarchies, and thus reduce the I / O operand required for all index queries. A method of re-establishing an index is to first remove the index, and then re-establish the index. For a smaller data sheet, this way is barely acceptable. However, if it is processed is a medium or large-scale data sheet, this method should not be used. It is best to choose to reconstruct the index in the way you want to introduce this section. This method does not need to remove the re-establish index. There are several reasons to support this option. When we establish a non-plenary index on the clustered data sheet, non-clustered indexes are based on the cluster index. If we remove the clustered index, the non-cluster index must be rebuilt once, because it is originally used as a written index index that is not present on the data sheet. Suppose we followed the new clustered index on the information sheet, and the non-clustered index must be rebuilt second! In other words, if you first remove the re-establishing index index, you must rebuild the non-plenary index: once in the clustered index removal, at a time, occurs at the cluster index reconstruction. If you use another method to re-establish a clustered index, non-clustered index will only need to rebuild once. To rebuild an index, you don't need to remove re-establishment, you can use create index ... Drop_existing or use DBCC DBREINDEX. Both options can be a step to re-establish the index and tell SQL Server to retest the existing index. Using these methods allows you to avoid deletion and reconstruction of nonset index when resetting the cluster index. These methods of one step will also use the order in which the data is arranged in the current index, which does not need to be reordered. CREATE INDEX ... DROP_EXISTING is used to reconstruct an index at the last time in the data sheet. DBCC DBREINDEX is used with the database name and information table name, which can rebuild all indexes on the table without having to perform individual commands for each index. The syntax and options of these two commands are for reference to the line series. Update Index Statistics If you don't have time or resources to rebuild an index, you can only update statistics separately. The results obtained by this technology may not be as effective as re-establishing the index, because the index may have been broken, and the resulting problem is far more complicated than statistics. Here, it is also assumed that you have automatically updated the statistics in SQL Server (otherwise, your statistics will be periodically updated anyway). You can use the update statistics command to manually update the index statistics, the syntax is as follows: Update statistics Table_name [index_name | (statistics_name [, statistics_name, ...]]] [With [Fullscan | Sample Number {Percent | Rows}] [All | columns | index] [NOREC MOMPUTE] ] The value in the middle brackets is selective, and the only one must be the Table_Name. The selective core is illustrated in Table 17-2. If your system must handle a lot of insertion, update,, and delete jobs, you should re-build an index to avoid efficiency as well as the aforementioned situation. If you cannot re-build an index, at least regular update statistics should be updated. Table 17-2 Selective quotes of the update statistics command Quoter Description INDEX_NAME Specifies the index to recalculate the statistics. In the preset situation, all indexes in the data sheet are recalculated. If index_name is specified, only the statistics of the index will be recalculated. Statistics_name allows you to specify that statistics should be recalculated. If this value is not specified, all statistics will be recalculated. Fullscan Specifies all the information columns in the read data table to collect statistics. Using this quota is the best way to collect statistics, but it also consumes the most system resources and time. Sample Number Percent | Rows Specifies how much information is specified as a basis, and you can provide data column or percentage. Under the preset situation, SQL Server will decide to sample the number of data to be sampled. This option cannot be coexist with the fullscan option. All | Column | Index Specifies all statistics, or data line statistics, or only index statistics. NoreCompute specifies that statistics will not be recalculated in the future. To re-open the function of automatically recalculate the statistics, execute this command again and do not use the NoreCompute option, or execute sp_autostats pre-depreciation. Using Index Now you have learned how to build an index, let's take a look at how to use the index. Even if the index already exists, it does not mean that SQL Server will use it. Whether an index will be used by SQL Server to see indexes and SQL statements. Also, if there is a plurality of indexes, SQL Server may have to choose which index to use. In this section, you will see how SQL Server selects an index, and you will learn how to use the index prompt to specify which index you use. You will also learn how to use Query Analyzer to view a query execution plan. Using Tips When the SQL Server query optimizer generates a query execution plan, it selects an index that provides the best performance - usually this index will use the least I / O operation and retrieve the least amount of information. Although query optimists typically choose the most efficient query to perform planning and access paths, if you know more about the information, you can make better than the query optimizer. decision. For example, suppose you want to retrieve personal information about "Smith" on a list of data sheets. Index statistics are established in accordance with this information, and assume that it appears three times average each name in the data line. This information means that the data is considerable in a very good choice; however, you know that "Smith" is much higher than the average. If you know how to indicate more efficient SQL jobs, you can use the prompt. Tip (hint) is a simple suggestion you give the query optimizer, specifying it does not need to automatically select. There are several types of prompts available - including linkage tips, query tips, and information sheet tips - but we will focus on the information sheet. The data sheet reminds you to specify how to access the information sheet (other types of prompts are introduced in Chapter 35). The data sheet is available to specify the following information: Data table scan In some cases, you may find that a data sheet scan is more efficient than a index query or index scan. When the index scanner is required to retrieve more than 20% of the data column in the table, the data table is more efficient - for example, 30% of the data table is "Smith". Which index to use You can specify a specific index as the only index to consider. You may not know which index will be selected without prompting, but you don't think it's best to use this index. In which set of indexes you can select You can recommend several indexes for query optimizer, which use (ignore repetition). This option will be useful when you know that an index collection can work well. Locking Method When querying the optimizer, you can tell it which type of lock to use from a specific information table. If you instinct to the wrong locked type may be selected on this information table, you can specify the query optimizer to lock, panelock lock, or data table locks. Let us look at how to use the prompt to specify the index that should be used - is also an index prompt. The following example shows the case where the index prompt is used in the T-SQL statement (the query uses the region index): SELECT * From customers with (Index (Region)) Where region = 'or' and city = 'portland' Note that the index prompt is posated behind the whistle. If you want to specify multiple indexes to SQL Server, list them in the T-SQL statement, the way is as follows: SELECT * From customers with (Index (INDEX (Region, City, CompanyNAME)) Where region = 'or' and city = 'portland' The core number of the index prompt can be an index name (just as just seen) or an index ID. Some special situations when using the index identification code in the prompt, as shown in the following table. Tip Sales INDEX (0) Use in a cluster data table (there is a cluster index on this information table) to enforce a clustered index scan index (1) to force a clustered index sweep or search on a cluster data table. Index (0) uses on a non-clustered data sheet (there is no cluster index on this data sheet) to enforce a data sheet scan index (1) use in a non-assembly information table as an error occurs. You can use SQL Server Query Analyzer to perform your query to view the results of the prompt. Using Query Analyzer in Chapter 13, you have learned that Query Analyzer is a very useful tool in SQL Server 2000. Then we have to use this tool again to see which index does it display out the query execution plan. Query Analyzer can also be used in other tasks, as follows: Execute SQL query You can use a GUI that is easy to use to perform SQL statements and review its results. Analysis of the syntax of the query analyzes the SQL statement in the pre-execution, you can discover errors and correct it. Displaying the estimated execution plan The implementation plan is performed through the display, you can see how different queries affect the execution consumption. This is very valuable in optimization SQL statements because you allow you to rewrite your SQL narrative and see if its execution cost changes. Perform index analysis index analysis can tell you whether you use an index to increase the execution of a query. To experiment with Query Analyzer, load the following T-SQL statements into the Query Analyzer: SELECT * From customer Where region = 'or' and city = 'portland' Now please check the estimated execution plan in the query drop-down menu. In Figure 17-23, you can see the situation that has been indexed with the city. Figure 17-23 Using the estimated execution plan using a city index when there is no prompt Now let's add a prompt to indicate that SQL Server uses the Region index. The query is as follows: SELECT * From customers with (Index (Region)) Where region = 'or' and city = 'portland' The estimated execution plan for this query is shown in Figure 17-24. Note that the Region index is now used. Figure 17-24 Using the prompt to specify an estimate of the estimation of the Region index When executing SQL statement, SQL Server Query Analyzer is a quite useful and convenient tool that not only provides a GUI, but also allows you to analyze SQL statements from or other aspects. For operations that can be performed using the command code, you can save your work in Query Analyzer in Query Analyzer in Query Analyzer in Query Analyzer. Design effective index index efficiency - is also the maximum efficiency and efficiency - is determined by indexing and using the SQL statement of the index. The light is to establish an index is not enough to play an index, and your SQL statement must also grasp the focus of the index. The index will only be useful when there is one or more index keys in the WHERE clause in SQL. In this section, you will learn a good index attribute, as well as the best and worst timing of the index. The characteristics of the effective index As we see, a good index allows you to solve the problem with less I / O operations and system resources when you get information, and do not need to use information table scans. . Since the index scan needs an inspection of the index tree to find an individual value, use indexes when you get a lot of information, it will not be more efficient. Note If you query access to 2 0% of the data sheet, the data sheet scan will be more efficient than using the index. A valid index only captures a small amount of information - in fact, most queries can end only using a small amount of information. To do more efficient, index must be designed to have good selectivity. The selectivity of the index is based on the number of information involved in each index key value. A selective index, each index key value has multiple data columns; selective indexes, each index key value has only several or one data column. Unique index has the highest selectivity. The selective store of the index is stored in the index discrete statistics. You can use the DBCC Show_statistics command to view the selectivity of the index. The query optimizer is more likely to use a good selectivity index. You can use multiple data rows to create a composite index to enhance the selectivity of the index. Data lines in the composite index can be combined to form an index having good selectivity. Although the unique index provides the best selectivity, please make sure the selected index type is really suitable for your data model. For example, if there are many projects in your Customers table, its Last Name is "Smith", you may not be able to build a unique index on the Last Name, but you can still find the usefulness of the index. When using an index is used in the following work: Specify a query for a specific search standard. These queries only need to retrieve a small number of information columns that meet the designated standards. Specify a query of a numerical range. These queries should also only retrieve a small amount of information. Searching in the connection. Commonly used to make a line of information on the key than the key is a good candidate key. Search for information in a specific order. If the data of the result set must be sorted in the order of the set index, the sorting action does not need to be executed, because the result rally is presented before the transmission. For example, if the cluster index is above the LastName and FirstName data, the application requires the order of LastName and FirstName, no need to add the ORDER BY clause. When the data table must perform a large amount of insertion, update, or delete actions, the index should be used more carefully, because each job that will change the information needs to update the index paging. Index Guidance Principles You should follow the following index guidelines to enhance the performance and efficiency of the system: moderately use indexes There are some indexes that may be quite good, but if the index is too much, it may be harmful to system performance. Since the system must maintain an index, each execution insert, update, or delete operations on the data sheet, the index needs to be updated. If this type of operation is very much, the system burden of maintenance index will be quite heavy. Don't use the index of small data sheets. If the information sheet is not large (all about hundreds of information columns), it is sometimes efficient to use the information table to scan. The additional burden caused by the index is not worth it compared to the benefits of it. Use less index key information to obtain good selective data, the smaller, but not to sacrifice selectivity as the price. The index of less information is called Narrow Index, and the index of many key value data is called Wide Index. Narrow ratios ratio of width indexes take less space. Use the cover query to cover the query (Covering Query) is a query method that contains all the information you want to query in the index key, that is, all index keys are also all selected data lines. In the inquiry, only the index is accessed, and the information table itself is skipped. Covering Index is an index that contains all data sheet data lines. For example, if the index is established on the A, B and C data lines, and the SELECT statement is only queried from these information lines, then this index is only needed. This chapter summarizes the use of indexes is a good way to improve the efficiency of the database.
