Databases Ch. 8

What is a cursor?

A cursor is a database object that can be used to pick up certain rows or columns for an application program. With a cursor, you can keep track of records used by a database application. By using a cursor, you can browse a result set. In a cursor, data are arranged in the row-by-column format so that a user can browse through the result set more easily. Once a specified row is located, you can perform database operations, such as modifying data in a result set and searching for and retrieving specified rows.

What is a composite index?

An index that is created by including multiple columns. For example, an index is created on a primary key that consists of multiple columns.

Describe the dynamic cursor.

Can move back and forth and see all data changes made to the records in a result set.

When can a view be updated?

For a single-table-based view to be updatable All not-null columns in the table should be present in the view No calculated columns No aggregate function columns No keywords GROUP BY, UNION, DISTINCT Must specify a value for not-null columns Must follow the data and integrity constraints in the table Use INSTEAD OF triggers to update views based on any number of tables.

What is a clustered index? What is the advantage of a clustered index?

One of two types of indexes in SQL Server stored in a B-tree. A clustered index places each row in a table to the leaf node of the B-tree. You can access rows directly in a cluster and query performance of clustered index is better. In SQL Server, each table can have one clustered index; it can be used by a nonclustered index to improve performance.

What is a nonclustered index?

One of two types of indexes in SQL Server stored in a B-tree. A nonclustered index places the reference to each row in a table to the leaf node of the B-tree. Nonclustered indexes should be created after a clustered index is created.

What are the advantages of using views in database applications?

Pg. 222 Views can be used to limit the actions such as inserting, deleting, and updating tables directly by front-end users. They are more secure. A view can contain data from multiple tables and can be used as a single table. Views reduce the complexity of data manipulation tasks for users. Views can be used to display results calculated by using columns in tables. Views can have triggers different from the triggers originally defined on the tables.

What are the restrictions when creating a view?

The name of a view must be unique within the database. The query used to create a view cannot contain the ORDER BY, COMPUTE, and INTO keywords. You must specify the column name in a view if the column is derived from an arithmetic expression, a built-in function, a constant, or columns from different tables that have the same column name.

What is a result set?

The result from a SQL statement. In SQL Server, data stored in a result set does not have the row-by-column format. This has caused some difficulties when a database application needs to process certain rows or columns. A SELECT statement returns a complete set of rows that meet the search criteria, even though sometimes interactive applications need only a small portion of the entire returned result or only one row of the entire output. To efficiently use the results from SELECT statements, cursors have been introduced to manage result sets.

Why do we need a cursor?

To efficiently use the results from SELECT statements, cursors have been introduced to manage result sets.

In what situations will indexes not improve performance?

Too many indexes on frequently updated tables. Every time the data in an indexed column are changed or the structure of the column is changed, the index is automatically updated. The updating of indexes slows down query performance.

Describe the keyset cursor.

Use a set of keys to identify the records in a result set for better performance, can move back and forth, but cannot see the newly inserted records.

Describe the forward only cursor.

With the forward only cursor, you can only browse forward through a result set from the first to the last record. If changes are made to the result set, the forward only cursor can let you see only the changes ahead of it. The forward only cursor is faster than the other types of cursors.

Describe the static cursor.

With the static cursor, you can make a snapshot of the records from a result set returned by a SQL statement or a database application. As the name indicates, the records returned by the static cursor will not show changes made by other transactions. That is, the static cursor cannot let you see other users' modifications. It allows back and forth movements when you search for a record.

How can indexes be used to improve query performance?

pg.223 Performance is significantly improved if an index is created on a column used in A WHERE clause with exact match. A WHERE clause with a search condition containing a wildcard. A table join condition in a FROM clause.