B2-Query data by using table expressions

Question 1

What are Common Table Expressions?

Answer 1

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

Question 2

Where are CTE’s used?

Answer 2

A CTE can be used to:

• Create a recursive query.
• Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
• Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
• Reference the resulting table multiple times in the same statement.

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views

Question 3

What is the structure of a CTE?

Answer 3

A CTE is made up of an expression name representing the CTE, an optional column list, and a query defining the CTE. After a CTE is defined, it can be referenced like a table or view can in a SELECT, INSERT, UPDATE, or DELETE statement. A CTE can also be used in a CREATE VIEW statement as part of its defining SELECT statement.

The basic syntax structure for a CTE is:

• WITH expression_name [( column_name [,…n] ) ]
• AS
• ( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

• SELECT
• FROM expression_name;

Question 4

What are the restrictions of CTE’s?

Answer 4

A CTE is a local Transact-SQL statement and cannot be used in different places in the system

If the CTE is built from multiple tables and is used to query hierarchies those tables must be merged with UNION ALL;

• The first query called an anchor is used to find the starting point.
• The second query joins to the first query to find all parts that construct the engine;

CTE’s have the scope of a single SELECT, INSERT, DELETE or CREATE VIEW statement;

Question 5

What is syntax of a common table expression?

Answer 5

[WITH [ ,…n] ]

::=

expression_name [( column_name [ ,…n] ) ]

AS

( CTE_query_definition )

Question 6

What does the DECLARE CURSOR statement does?

Answer 6

Defines the attributes of a Transact-SQL server cursor, such as its scrolling behavior and the query used to build the result set on which the cursor operates. DECLARE CURSOR accepts both a syntax based on the ISO standard and a syntax using a set of Transact-SQL extensions.

Question 7

What are some of the limitations of the declare cursor statement?

Answer 7

You cannot use cursors or triggers on a table with a clustered columnstore index. This restriction does not apply to nonclustered columnstore indexes; you can use cursors and triggers on a table with a nonclustered columnstore index.

Question 8

What are some of the Guidelines for Creating and Using non recursive CTE’s?

Answer 8

• A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
• Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
• A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
• Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.
• The following clauses cannot be used in the CTE_query_definition:
• ORDER BY (except when a TOP clause is specified)
• INTO
• OPTION clause with query hints
• FOR BROWSE
• When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
• A query referencing a CTE can be used to define a cursor.
• Tables on remote servers can be referenced in the CTE.
• When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error.

Question 9

What are some of the Guidelines for creating recursive’s CTE’s?

Answer 9

• The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
• Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
• The number of columns in the anchor and recursive members must be the same.
• The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
• The FROM clause of a recursive member must refer only one time to the CTE expression_name.
• The following items are not allowed in the CTE_query_definition of a recursive member
  
  • SELECT DISTINCT
  • GROUP BY
  • PIVOT (When the database compatibility level is 110 or higher. See Breaking Changes to Database Engine Features in SQL Server 2016.)
  • HAVING
  • Scalar aggregation
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
  • Subqueries
  • A hint applied to a recursive reference to a CTE inside a CTE_query_definition.

Question 10

What are the guidelines to using a recursive common table expression?

Answer 10

All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.

An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).

A view that contains a recursive common table expression cannot be used to update data.

Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.

Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. This is one way to confirm proper recursion.

Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data passed to the recursive part of the CTE. For more information, see example K. Using analytical functions in a recursive CTE that follows.

Question 11

How may types of table expressions are there?

Answer 11

There are four types of table expressions:

• Derived tables:
  
  • The scope of a derived table is local to the statement and so could not be used several times in the batch;
  • Derived tables can be used as an input ito an outer query.
• Common table expressions (CTEs);
• Views;
• Inline table-valued functions.

Question 12

DECLARE @local_variable (Transact-SQL) def

Answer 12

def: Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

Question 13

DECLARE @local_variable (Transact-SQL) syntax

Answer 13

DECLARE

{

{ @local_variable [AS] data_type [= value] }
| { @cursor_variable_name CURSOR }

} [,…n]

::=

TABLE ( { | } [,…n] )

::=

column_name { scalar_data_type | AS computed_column_expression }

[COLLATE collation_name]

[[ DEFAULT constant_expression] | IDENTITY [(seed ,increment )] ]

[ROWGUIDCOL]

[]

::=

{ [NULL | NOT NULL]

| [PRIMARY KEY | UNIQUE]

| CHECK ( logical_expression )

| WITH ( ) }

::=

{ { PRIMARY KEY | UNIQUE } ( column_name [,…n] )

| CHECK ( search_condition ) }

::=

See CREATE TABLE for index option syntax.

{ @table_variable_name [AS] }

Question 14

DECLARE @local_variable remarks

Answer 14

Variables are often used in a batch or procedure as counters for WHILE, LOOP, or for an IF…ELSE block.

Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE.

The scope of a local variable is the batch in which it is declared.

A table variable is not necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb.

A cursor variable that currently has a cursor assigned to it can be referenced as a source in a:

• CLOSE statement.
• DEALLOCATE statement.
• FETCH statement.
• OPEN statement.

Positioned DELETE or UPDATE statement.

SET CURSOR variable statement (on the right side).

In all of these statements, SQL Server raises an error if a referenced cursor variable exists but does not have a cursor currently allocated to it. If a referenced cursor variable does not exist, SQL Server raises the same error raised for an undeclared variable of another type.

A cursor variable:

• Can be the target of either a cursor type or another cursor variable. For more information, see SET @local_variable (Transact-SQL).
• Can be referenced as the target of an output cursor parameter in an EXECUTE statement if the cursor variable does not have a cursor currently assigned to it.
• Should be regarded as a pointer to the cursor.

Question 15

What is a batch of SQL statements?

Answer 15

A batch of SQL statements is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements. In some implementations, the entire batch statement is executed before any results are available. This is often more efficient than submitting statements separately, because network traffic can often be reduced and the data source can sometimes optimize execution of a batch of SQL statements.

Types of batches:

• Explicit Batches An explicit batch is two or more SQL statements separated by semicolons (;)
• Procedures If a procedure contains more than one SQL statement, it is considered to be a batch of SQL statements. For example, the following SQL Server-specific statement creates a procedure that returns a result set containing information about a customer and a result set listing all the open sales orders for that customer;
• Arrays of Parameters-Arrays of parameters can be used with a parameterized SQL statement as an effective way to perform bulk operations.

​

Question 16

what are the rules for using temporary tables?

Answer 16

You create a temp table, populate it, and reference it in future queries. This can lead to improved estimates and reliability, because temporary tables can have column and index related statistics. Those statistics help the optimizer estimate how many rows will be returned at different points along the way.

Temp tables are a great tool to use, but remember a few key rules:

• 1. Column level statistics will be created for you when you run queries using the temp table.
• 2. If you modify the temp table in multiple steps, those statistics may get out of date. Out of date statistics on temp tables can hurt performance.