Set - 6

Question 36 :

What is an execution plan? When would you use it? How would you view the execution plan?

Answer :

The Query Analyzer has a feature called Show Execution Plan. This option allows you to view the execution plan used by SQL Server's Query Optimizer to actually execute the query. This option is available from the Query menu on the main menu of Query Analyzer, and must be turned on before the query is executed. Once the query is executed, the results of the execution plan are displayed in graphical format in a separate window, available from a tab that appears below the query results window on the screen.

Question 37 :

What is the STUFF function and how does it differ from the REPLACE function? Answer1:

Answer :

stuff-> inserts into it without removing any thing. Replace->replace the given text with the new one.

STUFF - it deletes a specified length of characters and inserts another set of characters at a specified starting point. REPLACE -Replaces all occurrences of a specified string value with another string value.

Question 38 :

What does it mean to have quoted_identifier on? What are the implications of having it off?

Answer :

SET QUOTED_IDENTIFIER ON- Causes SQL Server to follow the SQL-92 rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not usually allowed by the Transact-SQL syntax rules for identifiers.

Question 39 :

What is the difference between a Local temporary table and a Global temporary table? How is each one used?

Answer :

Local templrary table will have a single # (#tablename) appended with the table name.Global templrary table will have Double # (##tablename) appended with the table name.
Ex:create table #table1
local temp. table will be available until the session who created it logs out, but global temp. table is available till the last session gets close in SQLServer.

Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

Question 40 :

What are cursors? Name four type of cursors and when each one would be applied?

Answer :

Opening a cursor on a result set allows processing the result set one row at a time.
The four API server cursor types supported by SQL Server are:
a) Static cursors
b) Dynamic cursors
c) Forward-only cursors
d) Keyset-driven cursors