Tag Archives: State 1

Recursive CTE – Sql Server

This is the second article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Multiple CTEs in a Single Query
Nested Common Table Expressions

Recursive Common Table Expression

CTEs provide a mechanism to write easy to understand, more readable and maintainable recursive queries. Prior to CTEs only mechanism to write recursive query is by means of recursive function or stored procedure. And these recursive functions or stored procedures support only up-to 32 levels of recursion. By default CTEs support a maximum recursion level of 100. CTEs also provide an option to set a MAXRECURSION level value between 0 to 32,767. Specifying it’s value as 0 means no limit to the recursion level, you agreed for a risk in case of a poorly written query resulting in infinite recursion level.

Recursive CTE Example:

Below is a very basic example of a recursive CTE generating numbers between 1 to 10:

RecursiveCTE Basic Example

WITH NumbersCTE AS
(
	SELECT  1 AS Number
	UNION ALL
	SELECT Number + 1 FROM NumbersCTE
	WHERE Number < 10
 )
SELECT * FROM NumbersCTE

RESULT:
Recursive CTE Basic Example result

Example of Recursive CTE to get Managers and employees reporting to them

This example uses the Employees table created in the previous article Introduction to Common Table Expression (a.k.a CTE). You can visit the link to create the table, if you have not created it already.

WITH EmpRecursiveCTE(ManagerId, EmployeeId, Name, Level) AS 
(
    SELECT ManagerId, Id, Name, 0 AS Level
    FROM dbo.Employees 
    WHERE ManagerId IS NULL
		UNION ALL
    SELECT E.ManagerId, E.Id, E.Name, Level + 1
    FROM dbo.Employees E
        INNER JOIN EmpRecursiveCTE EmpCTE
        ON E.ManagerID = EmpCTE.EmployeeId 
)
SELECT EmployeeId, Name, ManagerID, Level 
FROM EmpRecursiveCTE
ORDER BY ManagerID

RESULT:
Recursive CTE Basic Example result Employee Hierarchy

MAXRECURSION hint in Recursive CTE

By default maximum recursion level supported by CTE is 100. But CTE provides an option to change it by means of the MAXRECURSION hint. MAXRECURSION hint value can be between 0 to 32,767. Specifying it’s value as 0 means no limit.

Let us understand MAXRECURSION hint in a Recursive CTE by the below examples:

CTEs default maximum recursion level

Try to generate numbers between 1 to 200 by using a recursive CTE by the following script:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE

RESULT:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

From the above result it is clear that, the CTEs default maximum recursion level is 100.

How to change CTEs default maximum recursion level?

We can change the CTEs default maximum recursion by specifying the MAXRECURSION query hint. Change the previous recursive CTE to generate numbers between 1 to 200 by specifying the MAXRECURSION hint value as 210 as below and verify the result:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE
    WHERE Number < 200
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Recursive CTE with MAXRECURSION hint

From the above result it is clear that we can change the CTEs default maximum recursion by means of MAXRECURSION query hint.

MAXRECURSION query hint is helpful to terminate the poorly written recursive CTE

MAXRECURSION hint is very helpful in a scenario where poorly written CTE is resulting in an infinite recursion level, in such cases MAXRECURSION terminates the CTE once the defined recursion crosses. For example below is an infinite recursive CTE, but because of the MAXRECURSION hint value 210 it terminates the CTE execution once it reaches the recursion level of 210.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE   
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 210)

RESULT:
Infinite Recursive CTE with MAXRECURSION hint
Maximum recursion level that we can specify with MAXRECURSION query hint is 32,767

Try to generate the numbers between 1 to 40000 by the below script, by specifying the MAXRECURSION query hint value as 40000.

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 40000)

RESULT:

Msg 310, Level 15, State 1, Line 9
The value 40000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

From the above result it is clear that the maximum recursion level we can specify with the MAXRECURSION query hint is 32,767.

How to support the recursion level greater than the maximum recursion level 32,767?

If we come across a scenario, where we need to have recursion level greater than 32,767, in such scenarios we can achieve this by specifying MAXRECURSION value as 0. MAXRECURSION query hint value 0 means no limit to the recusion level, if we are specifying this we should make sure that our query is not resulting in an infinite recursion level. So, we can re-write the previous recursive CTE as below:

WITH NumbersCTE AS
(
    SELECT  1 AS Number
    UNION ALL
    SELECT Number + 1 FROM NumbersCTE  
	WHERE  Number < 40000
 )
SELECT * FROM NumbersCTE
OPTION (MAXRECURSION 0)

RESULT:
Recursive CTE MAXIMUM Recursion Level 0

How to rename column name in Sql Server

Many times we come across a scenario where we need to rename / change the existing table column name. We can use the SP_RENAME system stored to change/rename the table column name. In this article we will cover the following:

  1. Rename table column name using SP_Rename system stored procedure
  2. Why SP_RENAME returns the warning: “Caution: Changing any part of an object name could break scripts and stored procedures.” when renaming the column name?
  3. How to rename table column which is enclosed by “[” and “]” (i.e. Square brackets) symbol
  4. How to rename table column which has . (i.e. dot) symbol in the column name
  5. How to rename temporary table column name
  6. How to rename temporary table variable column name

To understand table column rename using SP_RENAME with extensive list of examples, let us first create a Customer table with sample records as depicted in the below image by using the following script:

Rename column name in Sql Server 1
Script:

USE TEMPDB
GO
--Create Customer Table for the Demo
CREATE TABLE dbo.Customer(Id INT, CustName NVARCHAR(50), 
[[PhoneNo]]] VARCHAR(20), [Zip Code] VARCHAR(10), 
[City.Name] VARCHAR(50))
GO
--Add sample records to the Customer table
INSERT INTO dbo.Customer(id, CustName, [[PhoneNo]]],
	[Zip Code], [City.Name])
VALUES(1,'Basavaraj Biradar', '123-123', '560001', 'Bangalore'),
      (2,'Shreeganesh Biradar', '321-321', '560025', 'Mysore')
GO 
--Create Procedure to get all the records from customer table
CREATE PROCEDURE dbo.GetCustomers
AS 
	SELECT id, CustName, [[PhoneNo]]],
		[Zip Code], [City.Name]
	FROM dbo.Customer
GO
--Get customer table data
EXEC dbo.GetCustomers
GO

1. Rename table column name using SP_Rename system stored procedure

Below is the SYNTAX of the SP_RENAME system stored procedure for renaming the column name:

SYNTAX:

SP_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Example 1: Rename Customer table column CustName to FullName using SP_RENAME

SP_RENAME 'Customer.CustName' , 'FullName', 'COLUMN'

Result:
Rename column name in Sql Server 2
Below is the pictorial representation of the table column rename using SP_RENAME
Rename column name in Sql Server 3

2) Why SP_RENAME returns the warning: “Caution: Changing any part of an object name could break scripts and stored procedures.” when renaming the column name?

Basically, sql server returns this warning to alert us to update all the places wherever we are referring this column. For example take a scenario where if the old column name is still referred in a stored procedure even after the column rename using sp_rename. In this scenario, the sp will start throwing error post the column rename. To avoid such issues, Sql server alerts us by returning the warning when we use SP_RENAME.

Example:The script at the beginning of this article is creating the stored procedure: GetCustomers and it was working. Now let us verify whether it is still working after renaming the column CustName to FullName by the previous example:

--Get customer table data
EXEC dbo.GetCustomers
GO

RESULT:

Msg 207, Level 16, State 1, Procedure GetCustomers, Line 4
Invalid column name ‘CustName’.

From the above result it is clear that column rename using SP_RENAME will just change the column name in the table. It will to change in all the places, it is developer’s responsibility to update at all the place. Let us modify the GetCustomers stored procedure to reflect the new column name using the below script and then verify the stored procedure execution:

–Modify the stored procedure to refer the new column

ALTER PROCEDURE dbo.GetCustomers
AS 
	SELECT id, FullName, [[PhoneNo]]],
		[Zip Code], [City.Name]
	FROM dbo.Customer
GO
--Now the storde procedure works
EXEC dbo.GetCustomers
GO

RESULT:
Rename column name in Sql Server 5

3. How to rename table column which is enclosed by “[” and “]” (i.e. Square brackets) symbol

Example: The Customer table created at the beginning of this article has a column [PhoneNo]. As Square bracket has special meaning it doesn’t work straight forward. Let us try renaming by the below script:

SP_RENAME 'Customer.[PhoneNo]' , 'PhoneNo', 'COLUMN'
GO

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

The reason for the above error is, sql server treats Customer.[PhoneNo] as Customer.PhoneNo. To solve this issue we have to add two open square brackets at the beginning of PhoneNo column and three closing square brackets at the end of the PhoneNo column as shown below:

SP_RENAME 'Customer.[[PhoneNo]]]' , 'PhoneNo', 'COLUMN'
GO
SELECT * FROM dbo.Customer

RESULT:
Rename column name in Sql Server 6

4. How to rename table column which has . (i.e. dot) symbol in the column name

Example: The Customer table created at the beginning of this article has a column City.Name. This column has . (i.e. dot symbol) in it’s name, let us try renaming by the below script:

SP_RENAME 'Customer.City.Name' , 'City', 'COLUMN'
GO

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

The reason for the above error is, sql server treats Customer.City.Name as three part naming convention and treating City as table name and Name as column name and Customer as Schema. Because of this it is not finding the column name in the table definition and failing to update it. To solve this issue we have to enclose column name within square brackets as shown in the below script:

SP_RENAME 'Customer.[City.Name]' , 'City', 'COLUMN'
GO
SELECT * FROM dbo.Customer

RESULT:
Rename column name in Sql Server 8

5. How to rename temporary table column name

Renaming a temporary table column name is same as that of the regular table column name.

Example: Let us create a temporary table #Customer and insert a record by using the below script

CREATE TABLE #Customer(Id INT)
GO
INSERT INTO #Customer (Id) Values ('100')
GO

Now we can rename the temporary table #Customer column Id name to SlNo using the below script:

SP_RENAME '#Customer.ID' , 'SlNo', 'COLUMN'
GO

Rename column name in Sql Server 9

6. How to rename temporary table variable column name

Sql Server doesn’t support the renaming of the Table Variable column name

Example: Let us verify this behaviour with an example

DECLARE  @Customer TABLE(id INT)
EXEC SP_RENAME '@i.ID' , 'SlNo', 'COLUMN'

RESULT:

Msg 15248, Level 11, State 1, Procedure sp_rename, Line 238
Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong.

Rename column name in Sql Server 10