sql-faq-13

99. What are triggers? How many triggers you can have on a table? How to invoke a trigger on
demand?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_settriggerorderTriggers 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()

100. 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.

101. What is a self join? Explain it with an example.
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 t2WHERE 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



102. What do you use triggers for?
A trigger is a special type of stored procedure that is executed by the SQL Server when an Insert, Modify or Delete operation is performed Against a given table. Triggers are run AFTER the operation take effect to ensure data integrity






103. What are stored procedures? Why use them? Advantages?
Stored procedures are the way to create routines and procedures that are run on the server.

Why use them?
The execution time is much less than at the workstation

Advantages:
1. Stored procedures are compiled the first time they’re run and are stored in a system table of the current database
2. We can execute a stored procedure on either local or remote SQL Server
3. An application can also execute stored procedure

104. What is the difference between WHERE and HAVING clauses?
Where clauses form a row selection expression that specifies the rows Should be included in the query.

Having clauses is used to determine the groups to be displayed in the output of the SELECT statement

105. Describe the function of the "select into" statement?
Retrieve rows and columns from one source table to a target table

106. There are 100 rows in a table. How to fetch the first 10 rows from a table?
SELECT TOP 10 * FROM [dbo].[Premium]
where premium is the name of the table

107. What is the ultimate difference between SQL SERVER 7.0 and SQL SERVER 2000?
Speed, size, and complexity handling has improved. ODBC, connections. Basically not
much different but plenty of minor differences.
108. What is the difference between Batches and Stored Procedures?
Both are unrelated. Stored procedure is code preset to make alterations to the table, updating, deleting, changing items etc. the batch, is a set of records that are opened and altered in one go or by one person, the lock is placed on this batch. It is set of records and is not separate from the table, whereas stored procedure is a separate identity created for specific purpose.

109. What is the GROUP BY clause. Explain with an example?
Groupby combines records from a column(as against sum,max,min etc).
110. How to know description of a table in SQL server 2000?
sp_help

111. Define Heap and Extent?
Heap: Heaps are collection of Datapages for a table. Each Datapage contain 8KB of
information.
Extent: A group eight (8) adjacent datapages is called Extent.