Interview Q/A

Q What are the magic tables in SQL Server?

A There are two tables Inserted and deleted in the SQL Server, which are popularly known as the Magic tables. These are not the physical tables but the SQL Server virtual tables usually used with the triggers to retrieve the inserted, deleted or updated rows. These tables contain the information about inserted rows, deleted rows and the updated rows. This information can be summarized as follows:

Action	Inserted	Deleted
Insert	Table contains all the inserted rows	Table contains no row
Delete	Table contains no rows	Table contains all the deleted rows
Update	Table contains rows after update	Table contains all the rows before update

-When a record is inserted in the table that record will be there on INSERTED Magic table.

-When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table.

-When a record is deleted from that table that record will be there on DELETED Magic table.

Q What are the difference between Stored Procedure and Trigger in SQL Server?

1) We can execute a stored procedure whenever we want with the help of the exec command, but a trigger can only be executed whenever an event (insert, delete and update) is fired on the table on which the trigger is defined.

2) We can call a stored procedure from inside another stored procedure but we can't directly call another trigger within a trigger. We can only achieve nesting of triggers in which action (insert, delete and update) defined within a trigger can initiate the execution of another trigger defined on the same table or different table.

3) Stored procedures can be scheduled through a job to execute on a predefined time, but we can't schedule a trigger.

4) Stored procedure can take the input parameters, but we can't pass the parameters as an input to a trigger.

5) Stored procedures can return values but a trigger cannot return a value.

6) We can use the Print commands inside the stored procedure to debug purpose but we can't use the print command inside a trigger.

7) We can use the transaction statements like begin transaction, commit transaction and rollback inside a stored procedure but we can't use the transaction statements inside a trigger.

8) We can call a stored procedure from front end (.asp files, .aspx files, .ascx files etc.) but we can't call a trigger from these files.

Q How can you achive Recurrsion in SQL Server 2008

A SQL Server 2008 has got a real nice feature called CTE Comman Table Expressions through which you can achive recurrsion

Q What is the difference between Delete command and Truncate command?

1) Delete command maintained the logs files of each deleted row but Truncate command do not maintain the logs files for each deleted row but maintains the record for deallocation of the datapages in the log files.The deallocation of the datafiles means that the data rows still exists in the data pages but the extends have marked as empty for reuse.

2) Truncate command is much faster than delete command.

3) You can use Where clause in case of Delete command to delete a particular row but in case of Truncate command you have to delete the data from all the row since Where clause is not work with Truncate command.

4) Triggers is fired in case of Delete command only and they are not fired when Truncate command is used.

5) Truncate command resets the Identity property to its initial value whereas Delete command do not resets the Identity property of the column.

6) Delete is a DML command and Truncate is a DDL command.

Q What is the difference between Unique Key and Primary key?

1)There can be only one Primary key possible in a table but there can be many unique keys possible in a table.

2)Primary key do not allow NULL values but a Unique key allow one NULL value.If we try to insert NULL value into the primary key, we get an error message.

3)When a Primary key is created, a clustered index is made by default but if an Unique key is created, a non-clustered index is created by default.

Q I have a stored procedure like below and have created a temporary table inside it and have not written any drop statement for this Temporary Table. Now when i execute this procedure for first time it will create the temporary table. Now when i run this procedure for second time, will it execute successfully or it will throw and an error stating "Table already exists in database". Test procedure. CREATE procedure [dbo].[testtemptable] as begin create table #testtemptable ( a varchar(10) null ) end

A The stored procedure will execute without any error no matter how many times you run it.

The reason being, when you create a local temporary table, it stays alive for that session and once session is over the temporary table is automatically deleted. When you run a procedure the session begins and it creates the temporary table and when the execution of Stored procedure is completed, the session ends and the table gets deleted.

8. How debug MSI Error in installation?

A:

53. Differentiate between a Local and a Global temporary table?

- A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

- Global temporary tables (created with a double “##”) are visible to all sessions.
- Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it.

71. What is difference between EXEC(@sql) and sp_executeSQL @sql ?

When we execute a T-SQL query string we use both EXEC and sp_executesql statements at a time.

But there are following most important differences between EXEC and sp_executeSQL.

1. sp_executesql allows for statements to be parametrized therefore It’s more secure than EXEC in terms of SQL injection.

2. sp_executesql can take the advantage cached query plans. The T-SQL string is compiled once and run every time so execution plan reused by the sp_executesql.

3. Temp tables created in EXEC can not use temp table caching mechanism

4. The T-SQL statements in the executed sql query variable string do not have access to any of the variables declared in the batch that contains the sp_executesql or EXECUTE statement.

The batch containing the sp_executesql or EXECUTE statement does not have access to variables or local cursors defined in the executed string.

5. If the executed string has a USE statement in the string that changes the database context, the change to the database context only lasts until sp_executesql or the EXECUTE statement completes.

75. What is row locator ?

AN index row of a non-clustered index contains a pointer to the corresponding data row of the table, This pointer is called row-locator.