Apply Your Knowledge


Exercises

10.1 Creating a Nonclustered Index on a New Column with the Enterprise Manager

This exercise offers practice in creating a nonclustered index using the SQL Server Enterprise Manager. Before creating an index, an existing table has to be available. This exercise uses the Authors table.

Estimated Time: 10 Minutes

  1. Open the SQL Server Enterprise Manager; expand a server group and then expand a server.

  2. Expand Databases, expand the Pubs database, and then select Tables.

  3. Right-click Authors; click on Design Table.

  4. On the toolbar, click the Table and Index Properties button, which has a picture of a hand pointing to a table.

  5. Select the Indexes/Keys table to change specific properties pertaining to indexes.

  6. On the Indexes/Keys tab, click New.

  7. In the Column Name list, you have to click the column or columns that are part of the index. For this exercise, select au_id for the column name.

  8. Select the Create UNIQUE check box to make this index a unique constraint.

  9. In Fillfactor , you have to specify how full SQL Server should make the leaf level of each index page during index creation. For this exercise, specify 60%.

  10. To save changes, click the Save button on the toolbar.

  11. Close the dialog box.

10.2 Creating a Clustered Index Using Code

You saw in Exercise 10.1 just how simple it was to create an index using the Enterprise Manager. In this exercise, you will also examine index creation, this time using code.

Estimated Time: 10 Minutes

  1. Open the Query Analyzer by selecting Query Analyzer from the Start menu.

  2. Create a new index on a new table. To do this, first create a new table in the Pubs database. In the code pane, type in the following:

     use Pubs CREATE TABLE IndexPractice (YourName char(32),  Age integer,  BirthDate integer) 
  3. After creating a new table, you have to create a new clustered index. To do this, code in the following:

     Use Pubs CREATE CLUSTERED INDEX age_index    ON IndexPractice (age) 
  4. To see your new index, open the Query Analyzer, expand the Pubs database in the Object Browser, and then expand User Tables. Expand IndexPractice and then expand Indexes. If you followed the steps properly, you should see age_index listed below Indexes.

10.3 Creating an Indexed View

This exercise requires you to optimize a view by creating an index on it.

Estimated Time: 10 Minutes

  1. Open the Query Analyzer. To do this, select the Query Analyzer from the Start menu.

  2. Type the following in the code pane to configure session-level settings needed when creating an indexed view:

     Use Pubs SET ANSI_PADDING,CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ARITHABORT,ANSI_NULLS,QUOTED_IDENTIFIER ON SET NUMERIC_ROUNDABORT OFF GO 
  3. Now you actually create the view. To create the view with the WITH SCHEMABINDING option set, copy out the following code:

     CREATE VIEW ExerciseView WITH SCHEMABINDING AS SELECT Au_fname, Au_id, Au_lname FROM dbo.Authors GO 
  4. Now you have to create a unique clustered index on the view. To do this, copy out the following code snippet:

     CREATE UNIQUE CLUSTERED Index ExerciseIndex ON ExerciseView (au_id) Go 
  5. To see the indexed view you have just created, open the Query Analyzer and then the Object browser.

  6. Expand Pubs and then expand Views.

  7. Click ExerciseView and then expand Indexes. You should see ExerciseIndex listed under Indexes.

Review Questions

1:

Briefly describe clustered and nonclustered indexes.

A1:

Clustered indexes are implemented so that the logical order of the key values determines the physical order of the corresponding rows in a table. Nonclustered indexes are indexes, similar to those in the back of a book, in which the logical order of the index is different than the physical, stored order of the rows on disk. See "Clustered Indexes" and "Examining Nonclustered Indexes."

2:

What does FILLFACTOR specify? In what cases would an implementation benefit from its use?

A2:

The FILLFACTOR option specifies how full SQL Server should make each page when it creates a new index using existing data. See "Exploring FILLFACTOR."

3:

What is a nonclustered covering index?

A3:

A covering index is a nonclustered index that is built upon all the columns needed to satisfy a SQL query, both in the selection criteria and the WHERE clause. See "Nonclustered Covering Indexes." It must cover all columns referred to in the query from that table, including group by , having , compute , compute by , and so on.

4:

How would you go about rebuilding indexes?

A4:

To rebuild indexes, you should use DBCC DBREINDEX because it allows all indexes to be rebuilt in a single shot. See "Rebuilding Indexes" for further explanation.

5:

What are indexed views?

A5:

Indexed views enable views to be stored in a database's physical storage after an index is created. See "Indexed Views" for further study.

6:

Why do index statistics need to be updated? How do you update them?

A6:

Index statistics need to be up to date for the optimizer to decide upon the fastest route of access. To update them, use the UPDATE STATISTICS statement. See "Updating Statistics" for more detail.

Exam Questions

1:

You are working for a small manufacturing company that has been operating well for some time. Lately the end users have been reporting that when performing queries against information on customers, the system is growing increasingly slow. After examining the system, you determine that the table definition has recently been altered . You want the response time to improve and be similar to what it was before the change. How would you fix the problem?

  1. Run a DBCC DBREINDEX .

  2. Drop and re-create the table's clustered index.

  3. Drop and re-create all indexes.

  4. Update the index statistics.

  5. Stop and restart the server.

A1:

D. Because the table structure has recently been altered, it is a good possibility that this change has caused the indexing information to become unstable or that statistics affecting the index have not been updated. If you restart the service, SQL Server should then update the statistical information accordingly . After the restart, you may want to ensure that all statistics are intact. Also consider index fragmentation as a possible source to the problem. For more information, see the section "Indexing and Statistics."

2:

Your company has a table named Products that is dedicated to its goods. A month ago, you added three nonclustered indexes to the table named NC1_Pro , NC2_Pro , and NC3_Pro . You also added a clustered index named C1_Pro on the Primary Key named Prod_ID . You monitor the performance on the indexes and notice that the indexes are not as efficient as before. You decide to rebuild each index in the table. Which method should you use to rebuild all indexes in the fastest and most efficient way?

  1. DBCC DBREINDEX (Products).

  2. Create clustered index with drop-existing; create nonclustered index with drop-existing.

  3. Delete all indexes and then re-create them.

  4. DBCC DBREINDEX (NC1_Pro, NC2_Pro, NC3_Pro, C1_Pro).

  5. Update the index statistics.

A2:

A. DBCC DBREINDEX . Answer B is wrong because this would be more time-consuming than DBCC DBREINDEX . You would have to individually rebuild all indexes. Answer C is not correct because deleting a clustered index and then re-creating it means deleting and re-creating all nonclustered indexes. Also, the process would have to involve two separate steps. For more details, refer to the section titled "Index Maintenance."

3:

You have a SQL Server database implemented in a library that stores library-specific information. The description of each title that is present in the library is stored in the Titles table. The Description column is implemented as data type text . A full-text index exists for all columns in the Titles table. You want to search for a title that includes the phrase Programming SQL . Which query should you execute to return the required results?

  1. SELECT * FROM Titles WHERE Description like '_Programming SQL_%'

  2. SELECT * FROM Titles WHERE Description like '%Programming SQL%'

  3. SELECT * FROM Titles WHERE CONTAINS (Titles, Description, 'Programming SQL')

  4. SELECT * FROM Titles WHERE FREETEXT (Description, 'Programming SQL')

A3:

D. Answer A is incorrect because the LIKE keyword cannot search on text-based columns. Answer B is wrong for the same reason. Answer C is wrong because the syntax for CONTAINS is wrong. Answer D is correct because it uses the correct method of searching, using FREETEXT . See the section "Creating Full Text Indexes" for more information.

4:

You have a table that is defined as follows :

 CREATE TABLE Books ( Book_ID char, Description Text, Price Integer, Author char(32)) 

You write the following:

 SELECT * FROM Books WHERE CONTAINS (Description, 'Server') 

You know for a fact that there are matching rows, but you receive an empty resultset when you try to execute the query. What should you do?

  1. Ensure that there is a nonunique index on Description column.

  2. Create a unique clustered index on the Description column.

  3. Create a FULLTEXT catalog for the Books table and then populate it.

  4. Use the sp_fulltext_populate stored procedure.

A4:

C. To enable full-text searching, you must create a catalog for a table and then populate manually or schedule a job. Answer A is incorrect because creating a nonunique index does not help in any way whatsoever. B is incorrect because creating a unique clustered index does not enable full-text searching. D is incorrect because there is no such procedure as sp_fulltext_populate (but there is a stored procedure named sp_fulltext_catalog ). For more information, see "Creating Full Text Indexes."

5:

You have 50,000 records in a database file, and you know you want to add another 25,000 records in the next month. What FILLFACTOR should you specify to maximize performance? It should be mentioned that a new index is to be created and that you will change your FILLFACTOR ; you also want fast INPUT into the tables.

  1. 0 (default setting)

  2. 100

  3. 70

  4. 50

A5:

D. You know exactly how many new records are coming in. 25,000 is 50% of 50,000, so filling the page by 50% and leaving 50% free space for the remaining 50,000 records seems logical. For more details, see the section "Exploring FILLFACTOR."

6:

You have a table named Products that holds information pertaining to the products your company trades. You need to perform quality searches on the Description column so that you can find products needed using diverse methods . You decide using full-text searches is the best method and so enable full-text indexing in your table. You want to keep performance consistent with the full-text indexes so that they do not degrade, and you want to minimize overhead associated with their maintenance. What should you do?

  1. Expand the database to accommodate future growth of the full-text indexes.

  2. Use the Index Tuning Wizard.

  3. Repopulate using the Full-Text Indexing Wizard and specify Keep Performance Consistency.

  4. Schedule regular repopulates of the full-text indexes.

A6:

D. Unlike regular SQL indexes, full-text indexes are not automatically kept up to date as data is modified in the associated tables. Full-text indexes should be frequently updated to maintain performance. For more information, see "Creating Full Text Indexes."

7:

You are a database developer for a computer manufacturing company named Optima. For a limited time, Optima ships free software with the purchase of any desktop computer or notebook. The software titles, descriptions, values, and other information are located in the Software table. You configure full-text indexing on the Software_Description column that contains over 2000 rows and is located in the Software table. After executing a search using FREETEXT for the word Windows , you notice an empty resultset in the results pane. Why is this happening?

  1. The catalog is not populated .

  2. FREETEXT is not a valid keyword recognized by SQL Server 2000.

  3. FREETEXT is not allowed for columns that contain 2000 or more rows.

  4. You didn't create a nonclustered index.

A7:

A. Before executing full-text searches, you must create and populate a full-text catalog. A full-text catalog is the basis of the storage used for the indexes. Periodically these catalogs should be repopulated to ensure usefulness . Repopulation can be done by schedule or by administrative task. For more details, see "Creating Full Text Indexes."

8:

Which statements show the maximum number of clustered and nonclustered indexes allowed in a single table?

  1. Clustered 249 and nonclustered 149

  2. Clustered 249 and nonclustered 249

  3. Clustered 1 and nonclustered 249

  4. Clustered 1 and nonclustered unlimited

A8:

C. There can be only one clustered index per table and as many as 249 nonclustered indexes. For more details, refer to SQL Server Books OnLine.

9:

What is true about the WITH SCHEMABINDING argument of the CREATE INDEX statement? Choose all that apply.

  1. It must be specified to create an indexed view.

  2. It allows a view's name and other properties to be changed dynamically.

  3. It prevents the dropping and altering of tables participating in the indexed view.

  4. It has to be specified only when you are creating a unique clustered index on a text column.

A9:

A, C. The WITH SCHEMABINDING argument of the CREATE INDEX statement is needed when creating an indexed view. When WITH SCHEMABINDING is specified, tables participating in the indexed view are prevented from alteration and deletion. "Indexed View Requirements and Restrictions" provides additional information on use of the WITH SCHEMABINDING option.

10:

John has just been impressed with the power full-text searches can provide and how easy they are to implement. Before John actually upgrades to a full-text search, John wants to try out the full-text searching "dream" capabilities by just testing them on the Products table of his company's database. John would like to perform flexible searches on a text column in the Products table. Which tool helps him accomplish his task?

  1. Index Tuning Wizard

  2. Full-Text Searching Wizard

  3. Full-Text Indexing Wizard

  4. MSSearch Index Wizard

A10:

C. The Full-Text Indexing Wizard is a graphical tool that enables full-text searches on columns by easily and quickly creating full-text indexes and full-text catalogs. For more details, see "Creating Full Text Indexes."

11:

Jauna is a DBA who has received complaints from many users concerning the data retrieval and modification times on the Sales table. She knows that you are a developer and asks you to figure out a way to resolve the problem she is facing . Because you are a new developer, you happen to know indexes increase performance, and that's just about all you know about indexes. You imprudently index all twelve columns in the Sales table. Which of the following statements outline the consequence of using the numerous indexes you have just done?

  1. Numerous indexes make it difficult to modify data.

  2. Numerous indexes are not allowed to be built; indexes can be used only in smaller quantities .

  3. Numerous indexes result in a very short index lifespan.

  4. Numerous indexes decrease performance on queries that select data with the SELECT clause.

A11:

A. Indexes used in larger quantities often degrade the rate at which insertions, deletions, and some modifications to data occur. Nevertheless, indexes generally speed up data access in cases where the data in the table is sufficient to warrant indexing. For more information, see "Indexing to Improve Performance."

12:

David is a database implementer who works for a major car retailer that tracks information on the latest car models available by the various car manufacturers. The company currently searches for the latest car model description using a regular search engine that is not capable of performing such complex searches as full-text searches can perform. David needs to upgrade the company's searches to full-text searches. What must he do before creating a full-text index?

  1. End all wizards and programs using the MSSearch utility.

  2. Create an index using the Index Tuning Wizard.

  3. Run a custom setup to install Microsoft Full-Text Search Engine.

  4. He doesn't have to do anything.

A12:

A. Before creating a full-text index, it is required that you close all applications and wizards running or using the MSSearch utility. For more details, see "Creating Full Text Indexes."

13:

Which of the following statements is true when using full-text indexing? Choose all that apply.

  1. The column that you plan on indexing must not contain text if at all possible.

  2. The column that you index has to be made up of text data.

  3. Full-text indexes are not automatically updated and they reside in a storage space called a full-text catalog.

  4. There can be up to 200 catalogs and not more in a single server.

  5. Full-text searches are best performed on columns that hold integer values.

A13:

B, C. The column that you plan to index has to be made up of text data. After you create a full-text index, it is not automatically updated and it resides in a storage space called a full-text catalog. For more details, see "Creating Full Text Indexes."

14:

You are designing a database that will serve as a back end for several large web sites. The web sites themselves will communicate with each other and pass data back and forth using XML. You would like to control the data displayed on the user browser based on interactions with the user. In many cases columns and rows need to be eliminated based on the criteria supplied. You would like to minimize round trips to the server for data exchange purposes. What technology is the best to apply?

  1. Use a user-defined function with SCHEMABINDING set to the XML recordsets.

  2. Create an Indexed View of the XML recordset specifying only the columns needed and supply a WHERE condition based on the rows selected.

  3. Create standard views of SQL Server data and export the requested data using XML.

  4. Send data requests and updates directly from the client machine to the SQL Server using FOR XML and OPENXML options.

  5. Use HTML and an XML schema to provide the necessary view of the data.

A14:

E. SCHEMABINDING refers only to SQL Server objects specifically tables, views, and user-defined functions. An XML schema cannot be bound in this manner. XML resides in memory and is processed against its own internal set of rules, referred to as a schema. An XML schema interacts directly with the data to supply logic and display attributes on the user's browser. HTML does not have the required functionality. For more information, see the "Indexed View Requirements and Restrictions."

Suggested Readings and Resources
  1. SQL Server Books Online

    • Index Tuning Recommendations

    • Placing Indexes on Filegroups

    • Database Design Considerations

    • Creating and Maintaining Databases: Indexes



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net