Implementing Indexes and Views

 

1.  What does an index page in a clustered index consist of?

      The index entries are stored in index pages and are at the highest level.

2.  What does the index page at the lowest level of the index tree in non-clustered indexes consist of?

      The index pages at the lowest level have pointers and the addresses of the data pages.

3.  What is the advantage of indexes?

• Improve query performance by quick retrieval of information from tables.
• Enforce the uniqueness of values in a column that uses the primary key.
• Improve the performance of the join queries that retrieve data from multiple tables. Indexes make joining faster.

4.  What are the differences between clustered and non-clustered indexes?

 

Clustered index	Non-clustered index
The order of the key values stored is equivalent to the order of key values on the disk	The order of the key values does not match the physical order of the rows in the database
It can be only one clustered index per table because the Real row data is a part of this index	It is possible to create more than one non-clustered index on a table
Provide faster access and are useful in querying data that involves ranges in the key column, therefore should be built on the primary key column	The search starts from the root index page

 

5.  Which table is accessed to get the address of the root index page?

      The sysindexes table enables the database engine to access information about all the triggers stored. That is why the sysindexes table, which is also a system and user-defined indexes is accessed to get address of the root index page.

6.  On which column would you create the index for the Employee table, and which type of index would you create?

      It has to be created a clustered index on the vEmployeeID column because it is the primary key. The code for creating a clustered index is:

         CREATE CLUSTERED INDEX employ_clustr_indx

         ON Employee  (vEmployeeID)

7.  How are views stored in a database?

      A view is a virtual table consisting of a number of columns from different tables. It has no data of its own; but it manipulates the data in the underlying base table, which is the table from which the view is derived. Stored as SELECT statements, views are used to derive data from database.

8.  How does SQL process your request when you run a query against a view?

• Retrieves the definition of the view to be created from the data dictionary. The definition indicates the columns that need to be included in the view from a specified table
• Checks the user’s privileges on the view
• Converts the view query into a equivalent operation on the underlying base tables
• Fetches the data from the base table or an update in the base table. The base table is the table from which the view is derived.

9.  How can you implement security when using views?

      You can use the WITH ENCRYPTION option.

10. How WITH CHECK OPTION implements referential integrity checks on modified data?

      The WITH CHECK OPTION in the CREATE VIEW statement enables you to perform referential integrity constraints and validation checks on the data being modified using views.

11. What is the use of the WITH ENCRYPTION option for creating views?

        WITH ENCRYPTION encrypts the text for the view in the system-defined table.                             

12. What are the characteristics of complex views?

       Complex views are derived from one or more tables. They contain functions or groups of data.

13. Which three guidelines should you follow when creating indexes?              

·        The table on which the index is to be created

·        The key column(s) of the table on which the index is to be created

·        The type of index to be created

14. What are the two indexing techniques that you can use to optimize query processing in SQL Server 2000?

          SQL Server 2000