SQL SERVER Interview Questions-part4
- What
are cursors?
Cursors allow row-by-row prcessing of
the resultsets.
- Explain
different types of cursors.
Types of cursors:
Static, Dynamic, Forward-only, Keyset-driven.
- What
are the disadvantages of cursors?
Disadvantages of cursors:
Each time you fetch a row from the cursor, it results in a network
roundtrip, where as a normal SELECT query makes only one rowundtrip,
however large the resultset is. Cursors are also costly because they
require more resources and temporary storage (results in more IO
operations). Furthere, there are restrictions on the SELECT statements
that can be used with some types of cursors.
- How
can you avoid cursors?
Most of the times, set based
operations can be used instead of cursors. Another situation in which
developers tend to use cursors: You need to call a stored procedure when a
column in a particular row meets certain condition. You don't have to use
cursors for this. This can be achieved using WHILE loop, as long as there
is a unique key to identify each row.
- What
is a join?
Joins are used in queries to explain how
different tables are related. Joins also let you select data from a table
depending upon data from another table.
- Explain
different types of joins.
Types of joins:
INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified
as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.
- Can
you have a nested transaction?
Yes, very much.
- What
is an extended stored procedure?
An extended stored procedure is a
function within a DLL (written in a programming language like C, C++ using
Open Data Services (ODS) API) that can be called from T-SQL, just the way
we call normal stored procedures using the EXEC statement.
- Can
you instantiate a COM object by using T-SQL?
Yes, you can instantiate a COM
(written in languages like VB, VC++) object from T-SQL by using
sp_OACreate stored procedure.
- What
is the system function to get the current user's user id?
USER_ID(). Some more system functions:
USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(),
HOST_NAME().
