Thursday, February 16, 2006

Interview Q's - 1

Why SELECT * is not preferred in embedded SQL programs?

For three reasons:

· If the table structure is changed ( a field is added ), the program will have to be modified

· Program might retrieve the columns which it might not use, leading on I/O over head.

· The chance of an index only scan is lost.

What are correlated subqueries?

A subquery in which the inner ( nested ) query refers back to the table in the outer query. Correlated subqueries must be evaluated for each qualified row of the outer query that is referred to

What are the three lock types?

Ans-3. The three types are shared, update and exclusive. Shared locks allow two or more programs to read simultaneously but not change the locked space.An exclusive lock bars all other users from accessing the space.An update lock is less restrictive; it allows other transactions to read or acquire shared locks on the space.

What is Cursor Stability(CS)?

Ans-2. A page lock is held only while the cursor is positioned on that page. When the cursor moves to another page, the lock is released. When a page is locked concurrent application programs cannot update or delete a row of the locked page. The current lock is not released until a new lock is acquired. If an application program updates or deletes data, the lock is held until the data is committed. CS applies only to data that is read. All changed data remains locked until COMMIT or ROLLBACK