According to Microsoft's Official Document, Common Table Expression is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
But as far as I observed, it acts like a Temporary View and can be used as a substitute for view in many cases. The scope of CTE is present until the execution of its same batch. You cannot access the CTE outside its batch.
Let me be more clear with an example.
;WITH
CTE
AS
(
SELECT
ID1,ID2 FROM
myTable
)
SELECT
*
FROM
CTE
For understanding purpose, I've taken a small table containing only two columns. The above CTE should be executed as a single batch. If you execute leaving the SQL statement below or if you want to try to access it like a base table by using SELECT statement, SQL Server raises an exception.
A CTE should always be started with a WITH keyword followed by a custom name, followed by AS keyword inside which you can write your own query. Please do keep in mind that a CTE should always be followed by a SQL statement like SELECT, INSERT, UPDATE or DELETE.
The semicolon (;) before WITH keyword is a delimiter. It is used to separate the CTE from other queries in the batch. If you want to execute only the CTE as a batch then no need of keeping it. If you want to execute the CTE along with other SQL statements then it should be specified, otherwise SQL Server raises an exception.
The main advantage of using CTE is, it reduces the overhead of using sub queries and joins if you want to use them for many times in a Query Window. You can write multiple queries inside CTE but between two queries operator like UNION,INTERSECT,EXCEPT or UNION ALL must be specified based upon the requirement.
One more advantage is, CTE can be made recursive. Let's have an example of recursive CTE
WITH
CTE
AS
(
SELECT
X
=
CONVERT(VARCHAR(1000),'HELLO')
UNION
ALL
SELECT
X
=
CONVERT(VARCHAR(1000),'GOODBYE')
UNION
ALL
SELECT
CONVERT(VARCHAR(1000),X
+
'A')
FROM
CTE
WHERE
LEN(X)
<
10
)
SELECT
X
FROM
CTE
ORDER
BY
LEN(X),
X
I got the above example from a blog but I wanna have a detailed explanation of the recursion which I came to know by implementing. In the above CTE, there is a recursion applied in the last statement i.e., the function CONVERT(VARCHAR(1000),X + 'A') will be implemented by taking the values obtained from its above two lines. This will be implemented until the length of X is less than 10. It means the CTE is recursively called for 10 times.
SQL Server provides a default recursion limit of 100 times. If you want to have your own recursion limit, you need to specify it manually by using an option MAXRECURSION. Let's test it. Suppose I change the above code as follows. Please observe carefully:
WITH
CTE
AS
(
SELECT
X
=
CONVERT(VARCHAR(1000),'HELLO')
UNION
ALL
SELECT
X
=
CONVERT(VARCHAR(1000),'GOODBYE')
UNION
ALL
SELECT
CONVERT(VARCHAR(1000),X
+
'A')
FROM
CTE
WHERE
LEN(X)
<
200
)
SELECT
X
FROM
CTE
ORDER
BY
LEN(X),
X
If I execute the above code, SQL Server throws the following error:
The
statement terminated. The maximum recursion 100 has been exhausted
before statement completion.
It means I crossed the limit of recursion by specifying LEN(X) < 200. By the error message, we can know that the automatic recursion limit provided by SQL Server is 100. That's fine. Now I want to execute the above CTE with LEN(X) < 200. Then I will go by specifying the recursion limit manually by adding a line to the above CTE code. It goes as follows:
WITH
CTE
AS
(
SELECT
X
=
CONVERT(VARCHAR(1000),'HELLO')
--> ANCHOR MEMBER
UNION
ALL
SELECT
X
=
CONVERT(VARCHAR(1000),'GOODBYE')
–> ANCHOR MEMBER
UNION
ALL
SELECT
CONVERT(VARCHAR(1000),X
+
'A')
FROM
CTE
WHERE
LEN(X)
<
200 --> RECURSIVE MEMBER
)
SELECT
X
FROM
CTE
ORDER
BY
LEN(X),
X
OPTION(MAXRECURSION
200)
I won't get any error now as I specified the limit. But note that you cannot specify this limit as you wish. The default limit provided is 100. The manual limit is restricted to 32767. You can specify the limit ranging from 0 to 32767. There is also one more option here. Like in other programming languages, we have an infinite looping even here. If you specify MAXRECURSION as "NOLIMIT" then it results in an infinite loop. Unlike other languages, SQL Server's infinite looping is so dangerous. If it occurs, it results in the crash of your server. So be careful about this point.
There is also a terminology used in the CTE which I specified in the above example. The members of CTE are called ANCHOR MEMBERS. If you use a recursion there then that CTE member is called RECURSIVE MEMBER.
That's all about the basics of Common Table Expression (CTE). I'll write more about it in my coming posts because I don't want to increase the length of this post so that you may feel tired in scrolling as I do.
For the basic concept of CTE, read the official documentation of Microsoft
No comments:
Post a Comment