1. A WITH clause containing a SELECT that generates a valid table. 
2. An outer SELECT that references the table expression.

A recursive CTE consists of

1. an __(a)__, which is the source of the __(b)__, along with a __(c)__ statement and a __(d)__, which recurses across the anchor query
2. An outer query which references the routine and specifies the __(e)__

      a. anchor query

      b. recursion

      c. UNION ALL

      d. second query

      e. number of recursion levels

Use a recursive CTE that will return

'Components Road Frames' and all its children

 Reference ProductCategory, ProductSubCategory and Product from AdventureWorks2008R2.

declare @searchname varchar(50)

set @searchname ='Components Road Frames'

;with

hierarchycte (id, name, parentid) as

(

select ProductCategoryID ID, Name, null ParentID

from Production.ProductCategory

union all

select ProductSubcategoryID ID, c.Name+' '+s.Name Name, s.ProductCategoryID ParentID

from Production.ProductCategory c

join Production.ProductSubcategory s on c.ProductCategoryID = s.ProductCategoryID

union all

select p.ProductID ID, c.Name+' '+s.Name+' '+p.Name Name, p.ProductSubcategoryID ParentID

from Production.ProductCategory c

join Production.ProductSubcategory s on c.ProductCategoryID = s.ProductCategoryID

join Production.Product p on s.ProductSubcategoryID = p.ProductSubcategoryID

),

productcte(ID, Name, ParentID,Level) AS

(

SELECT ID, Name, ParentID , 2 as Level-- level, provide starting point

FROM hierarchycte

WHERE Name = @searchname

union all

select r.id, r.name, r.parentid,Level+ 1

from hierarchycte r

join productcte on productcte.ID = r.parentid

)

select * from productcte order by ID

What is the syntax of the RANK function?

RANK() OVER(PARTITION BY <column_name>

                     ORDER BY <column_Name>) AS <alias>

What is the difference between RANK()

and DENSE_RANK()?

What is the difference between ROW_NUMBER()

and RANK()?

ROW_NUMBER() assigns incrementing sequential numbers  based on logical order specified in the ORDER BY subclause. RANK() assigns ranking numbers based on the value of the column specified in the ORDER BY subclause.  

Which two of the following are allowed in a stored procedure?

1. CREATE VIEW
2. CREATE TABLE
3. ALTER VIEW
4. ALTER INDEX
5. CREATE TRIGGER

use master
go
create procedure sp_one
as
   select 'this'
go
use test2
go
create procedure sp_one
as
   select 'that'
go

exec sp_one

What will be the output?

When do you absolutely need to use the BEGIN and END with an IF or a WHILE? (Select one answer)

a. Never

b. Always

c. When executing 2 or more lines of code

d. When  executing 3 or more lines of code

Differentiate between @@IDENTITY

and SCOPE_IDENTITY().

@@IDENTITY is a global variable and SCOPE_IDENTITY() is a function. @@IDENTITY contains the last identity value inserted for the connection. SCOPE_DENTITY() returns the last identity value inserted in the scope of the current statement.

An output parameter has the keyword OUT/OUTPUT in the CREATE PROCEDURE <stored procedure> and

the EXEC <stored procedure>.

(e.g. @parm1 INT OUTPUT)

1. Fatal.
2. Cause conection to be terminated.
3. Open transactions are rolled back.

True or False, I can return the corresponding error

message from error 2627 from within a TRY...CATCH block using the RAISERROR command.

False, any number less than 50000 is considered a system error and cannot be returned even with the RAISERROR command. You will need to dynamically build a custom message and use error number 50000 to return the message with RAISERROR command.

In a TRY block, a RAISERROR immediately passes control to the CATCH block without returning an error message to the application. In a CATCH block, a RAISERROR closes the transaction and returns control to the application with the specified message.

XACT_STATE with the values (1) - open transaction to be committed or rolled back, (0) - no open transaction, (-1) open transaction in a doomed state and can only be rolled back.

With a TRY block, the transaction is not terminated and control is transferred to the CATCH block. If XACT_ABORT is set on, any error is fatal. A transaction inside a CATCH block cannot be committed if XACT_ABORT is turned on. 

1. DECLARE is used to define the SELECT statement that is the basis for the rows in the cursor.
2. OPEN executes the SELECT and loads the row into a memory structure.
3. FETCH retrieves one row at a time from the cursor.
4. CLOSE closes the processing on the cursor.
5. DEALLOCATE removes the cursor and releases the memory structures containing the cursor result sets.

1. FAST_FORWARD - fastest performing, moves forward one row at a time, same as FORWARD_ONLY and READ_ONLY cursors, default option.
2. STATIC - result is retrieved and stored in a temporary table in tempdb, supports scrolling.
3. KEYSET - set of keys that uniquely identify each result set row is stored in a temporary table in tempdb.
4. DYNAMIC - most expensive, cursor reflects all changes made to result set.

With a scalar function you RETURN a ____(a)____

____(b)____.

With an inline table-valued function you RETURN

a ____(c)____ ____(d)____.

a. single

b. value

c. SELECT

d. statement

With the exception of a/an ____(a)____ function,

all the code within a function is required to be enclosed

in a ____(b)____. 

a. inline table-valued

b. BEGIN...END block

1. ENCRYPTION
2. SCHEMABINDING
3. RETURNS NULL ON NULL INPUT/CALLED NULL ON NULL INPUT
4. EXECUTE AS

a. inline table-valued

b. view

1. The data modification must reference exactly one table.
2. Columns in the view must reference columns in a table directly.
3. THe column cannot be derived from an aggregate.
4. The column cannot be computed as a result of a UNION/UNION ALL, CROSS JOIN, EXCEPT, INTERSECT.
5. The column being modified cannot be affected by the GROUP BY, DISTINCT or HAVING clause.
6. The TOP operator is not used.