|
Q38. What is an extended stored procedure? Can
you instantiate a COM object by using T-SQL?
Ans. 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. See books online to learn how to
create extended stored procedures and how to add them to SQL Server.
Yes, you can instantiate a COM (written in languages like VB, VC++)
object from T-SQL by using sp_OACreate stored procedure. Also see books
online for sp_OAMethod, sp_OAGetProperty, sp_OASetProperty, sp_OADestroy.
For an example of creating a COM object in VB and calling it from T-SQL,
see 'My code library' section of this site.
Q39. What is the system function to get the
current user's user id?
Ans. USER_ID(). Also check out other system functions like USER_NAME(),
SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().
Q40. What are triggers? How many triggers you
can have on a table? How to invoke a trigger on demand?
Ans. Triggers are special kind of stored procedures that get executed
automatically when an INSERT, UPDATE or DELETE operation takes place on
a table.
In SQL Server 6.5 you could define only 3 triggers per table, one for
INSERT, one for UPDATE and one for DELETE. From SQL Server 7.0 onwards,
this restriction is gone, and you could create multiple triggers per
each action. But in 7.0 there's no way to control the order in which the
triggers fire. In SQL Server 2000 you could 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 on the table on which
they are defined.
Triggers are generally used to implement business rules, auditing.
Triggers can also be used to extend the referential integrity checks,
but wherever possible, use constraints for this purpose, instead of
triggers, as constraints are much faster.
Till SQL Server 7.0, triggers fire only after the data modification
operation happens. So in a way, they are called post triggers. But in
SQL Server 2000 you could create pre triggers also. Search SQL Server
2000 books online for INSTEAD OF triggers.
Also check out books online for 'inserted table', 'deleted table' and
COLUMNS_UPDATED()
There is a trigger defined for INSERT operations on a table, in an OLTP
system. The trigger is written to instantiate a COM object and pass the
newly insterted rows to it for some custom processing. What do you think
of this implementation? Can this be implemented better?
Instantiating COM objects is a time consuming process and since you are
doing it from within a trigger, it slows down the data insertion
process. Same is the case with sending emails from triggers. This
scenario can be better implemented by logging all the necessary data
into a separate table, and have a job which periodically checks this
table and does the needful.
Q41. What is a self join? Explain it with an
example.
Ans. Self join is just like any other join, except that two instances of
the same table will be joined in the query. Here is an example:
Employees table which contains rows for normal employees as well as
managers. So, to find out the managers of all the employees, you need a
self join.
CREATE TABLE emp ( empid int, mgrid int, empname char(10) )
INSERT emp SELECT 1,2,'Vyas' INSERT emp SELECT 2,3,'Mohan' INSERT emp
SELECT 3,NULL,'Shobha' INSERT emp SELECT 4,2,'Shridhar' INSERT emp
SELECT 5,2,'Sourabh'
SELECT t1.empname [Employee], t2.empname [Manager] FROM emp t1, emp t2
WHERE t1.mgrid = t2.empid
Here's an advanced query using a LEFT OUTER JOIN that even returns the
employees without managers (super bosses)
SELECT t1.empname [Employee], COALESCE(t2.empname, 'No manager')
[Manager] FROM emp t1 LEFT OUTER JOIN emp t2 ON t1.mgrid = t2.empid
|
