Multiple Choice Questions on SQL Server - Common Table Expressions
1. SQL Server supports two types of CTEs:
A) indexed and nonindexed
B) recursive and nonrecursive
C) with view and without views
D) None of above
2. You cannot reference .............. clause in a select statement for a CTE unless the statement also includes a TOP clause.
A) Update
B) Where
C) an ORDER BY
D) DISTINCT
3. If you need to reference/join the same data set multiple times, then CTE is not the choice.
A) True
B) False
4. You can use a CTE within a CREATE VIEW statement.
A) True
B) False
5. SELECT statements within recursive CTEs cannot contain which of the following:
A) The DISTINCT Keyword
B) The TOP keyword
C) GROUP BY or Having clauses
D) All of above
6. CTEs are unindexable but on the other hand CTEs can use existing indexes on referenced objects.
A) True
B) False
7. CTE is different from view in a way that CTE is not created as .............. in the database and therefore is only available for this single statement.
A) Table
B) Link
C) Procedure
D) an object
8. CTEs rely on stats of the ................... as CTEs do not have dedicated stats of their own.
A) system Tables
B) static views
C) underlying objects
D) None of above
9. Tables which are on the remote servers cannot be referenced in the CTE.
A) True
B) False
10. A CTE can reference itself and previously defined CTEs in the same WITH clause.
A) True
B) False
Answers
1. SQL Server supports two types of CTEs:
A) indexed and nonindexed
B) recursive and nonrecursive
C) with view and without views
D) None of above
2. You cannot reference .............. clause in a select statement for a CTE unless the statement also includes a TOP clause.
A) Update
B) Where
C) an ORDER BY
D) DISTINCT
3. If you need to reference/join the same data set multiple times, then CTE is not the choice.
A) True
B) False
4. You can use a CTE within a CREATE VIEW statement.
A) True
B) False
5. SELECT statements within recursive CTEs cannot contain which of the following:
A) The DISTINCT Keyword
B) The TOP keyword
C) GROUP BY or Having clauses
D) All of above
6. CTEs are unindexable but on the other hand CTEs can use existing indexes on referenced objects.
A) True
B) False
7. CTE is different from view in a way that CTE is not created as .............. in the database and therefore is only available for this single statement.
A) Table
B) Link
C) Procedure
D) an object
8. CTEs rely on stats of the ................... as CTEs do not have dedicated stats of their own.
A) system Tables
B) static views
C) underlying objects
D) None of above
9. Tables which are on the remote servers cannot be referenced in the CTE.
A) True
B) False
10. A CTE can reference itself and previously defined CTEs in the same WITH clause.
A) True
B) False
Answers