SQL Server Interview Questions and Answers : Series 4

Question: What is common table expression (CTE)?

Answer: CTE is a new feature introduced in SQL Server 2005. CTE is a virtual view which exists at runtime. It can be used for the logic which need to be used other than the current scenario. CTE is major is used for handling the hierarchical and recursive data.

Question: What is difference between CTE and View?

Answer: CTE is a not a physical object and View is a physical object.

Question: What is the table variable?

Answer: Table variable is the variable used to store data in table form. It like temporary table but it scopes remains till the execution of the query only. It a variable so no index can be created on it.
Table variable declaration is just like other variables
Declare @table table (column data type, Column2 data type)

Temporary table is the just like the physical table but it exist in temp Db. and its scope remains till the transaction is open.  The temp table starts with # symbol.
Temporary table syntax is just like physical table.
Create #table table (column1 data type, column2 data type)

Question: Difference between temporary table and table variable? What is the temporary table?

Answer: It’s like physical table creates with the create table syntax while table variable is a variable creates with declare variable syntax.

Question: What is the global temporary table?

Answer: Global temporary tables are the table which is created one transaction and can be accessed in other transaction till the earlier transaction is open.The ## is used or global temporary tale alias.

Question: What are the updated views?

Answer: Views through which data can be updated. For doing multiple operation while updating

Question: What is the union all clause?

Answer: Union all clause is used to merger  two queries which have same no of columns in there select  statement.

Select  A1,A2 from table1

Union all

Select  t1,t2 from table2

Result set will contain entire data from both the tables.

Question: What is union Clause?

Answer: Union clause work same as union all clause it returns different rows in result set

Question: Difference between union all and union clause?

Answer: Union all can return duplicate values while union clause filters duplicate rows from both the clauses.

Question: What is stored procedure in sql server?

Answer: Stored procedure is a batch of SQL statement which is returned in order to perform a certain task.

Question: Is nesting possible in stored procedure? If yes how many number of times?

Answer: Yes and nesting up to 32 level.


GO BACK TO MAIN QUESTIONS LIST