QUESTION -
What are cursors in SQL Server?
ANSWER -
Cursors allow row-by-row prcessing of the
resultsets.
Types of cursors:
Static, Dynamic, Forward-only,
Keyset-driven.
Each time you fetch
a row from the cursor, it results in a network
roundtrip
Cursors are also
costly because they require more resources and temporary
storage
QUESTION -
What is a join and explain different types of
joins.
ANSWER -
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.
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.
QUESTION -
What is an extended stored procedure in SQL
Server?
ANSWER -
An extended stored procedure compiles as DLL and
are created to expand capabilties of user defined stored
procedure. It uses xp_ prefix as naming
convention.
QUESTION -
What are triggers in SQL Server?
ANSWER -
Triggers are special kind of event driven stored
procedures.
Executed
automatically when an INSERT, UPDATE or DELETE operation takes
place on a table,
Can specify which
trigger fires first or fires last using
sp_settriggerorder,
Triggers can't be
invoked on demand,
They get triggered
only when an associated action (INSERT, UPDATE, DELETE)
happens,
Triggers are
generally used to implement business rules,
auditing,
Triggers can also
be used to extend the referential integrity checks.
QUESTION -
What is a self join in SQL
Server?
ANSWER -
Two instances of the
same table will be joined in the query.
QUESTION - What is the difference between
UNION ALL Statement and UNION?
ANSWER - UNION
statement eliminates duplicate rows whereas UNION ALL
statement includes duplicate rows. UNION statement can be used
to combine any number of queries whereas UNION ALL statement
can be used to combine a maximum of two queries. UNION
statement cannot be used with aggregate functions whereas
UNION ALL statement can be used with aggregate
functions.
QUESTION - Write some disadvantage of
Cursor?
ANSWER - Cursor manipulates records row
by row. It requires temporary storage for row manipulation.
Thus manipulating row using cursor is costly affair since it
consumes extra resource. Fetch record row by row means server
roundtrip that consumes network recourses.
QUESTION - What is Log Shipping?
ANSWER -
This process synchronizes two SQL servers and thus
provides ready server in case one fails. It automatically
backup transaction log file throughout the day and restore
them on the standby server.