SQL Server Questions and Answers

1.What is #temp table and @table variable in SQL Server?

#temp Table (Temporary Table)
temp table is a temporary table that is generally created to store session specific data. Its kind of normal table but it is created and populated on disk, in the system database tempdb — with a session-specific identifier packed onto the name, to differentiate between similarly-named #temp tables created from other sessions.
The data in this #temp table (in fact, the table itself) is visible only to the current scope. Generally, the table gets cleared up automatically when the current procedure goes out of scope, however, we should manually clean up the data when we are done with it.
Syntax:
-- create temporary table
CREATE TABLE #myTempTable (
AutoID int,
MyName char(50) )

-- populate temporary table
INSERT INTO #myTempTable (AutoID, MyName )
SELECT  AutoID, MyName
FROM  myOriginalTable
WHERE  AutoID <= 50000

-- Drop temporary table
drop table #myTempTable

@table variable
table variable is similar to temporary table except with more flexibility. It is not physically stored in the hard disk, it is stored in the memory. We should choose this when we need to store less 100 records.
Syntax:
DECLARE @myTable TABLE (
AutoID int,
myName char(50) )

INSERT INTO @myTable (AutoID, myName )
SELECT  YakID, YakName
FROM  myTable
WHERE  AutoID <= 50

We don't need to drop the @temp variable as this is created inside the memory and automatically disposed when scope finishes.

2.What is trigger?

Triggers allows us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table.
Triggers are special types of stored procedures that are defined to execute automatically in place of or after data modifications.
They can be executed automatically on the insert, delete and update operation.

3.How many types of triggers are there?

There are four types of triggers.
1. Insert
2. Delete
3. Update
4. Instead of

4. What is the diff between a HAVING CLAUSE and a WHERE CLAUSE?

You can use Having Clause with the GROUP BY function in query and WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

5.What is cursor in SQL Server?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.
Typical syntax of cursor is
DECLARE @fName varchar(50), @lName varchar(50)
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC
FOR
Select firstName, lastName FROM myTable
OPEN cursorName -- open the cursor
FETCH NEXT FROM cursorName
   INTO @fName, @lName
   PRINT @fName + ' ' + @lName -- print the name
WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM cursorName
   INTO @fName, @lName
   PRINT @fName + ' ' + @lName -- print the name
END

CLOSE cursorName -- close the cursor
DEALLOCATE cursorName -- Deallocate the cursor