Incorrect deployment plan order generated when a table valued function with schema binding is dependent on Temporal table
- Dominant language
- C#
- Stars
- 460
- Forks
- 29
- Avg merge
- 4d 9h
- Merged PRs (30d)
- 7
Description
- DacFx Version: 162.3.564
- Tested in SSMS latest version
-
You can reproduce the issue in the one of the 2 ways:
1. **Steps to Reproduce using WideWorldImporters Database:**
i. Install SQL Server 2022 & import [WideWorldImporters](https://github.com/microsoft/sql-server-samples/releases/download/wide-world-importers-v1.0/WideWorldImporters-Full.bak) database.
ii. Right click on 'Application.Cities' -> Encrypt columns -> Select City Name -> New Encryption key -> Go to Summary & finish.
Encryption fails with the below error.
2. **Steps to Reproduce by creating tables**
i. Install SQL Server 2022 & connect to it using SSMS.
ii. Create a new database & create a temporal table as below:
```
CREATE TABLE Department
(
DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
DeptName VARCHAR(50) NOT NULL,
ManagerID INT NULL,
ParentDeptID INT NULL,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DepartmentHistory));
```
iii. Create a table-valued function with schema binding as below:
```
CREATE FUNCTION Test(@ManagerID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT ParentDeptID
FROM dbo.Department
WHERE ManagerID > @ManagerID
```
iv. Right click on the table -> Encrypt columns -> Select DeptName -> New Encryption key -> Go to Summary & finish.
Encryption fails with the same error as above.
DetermineCustomerAccess is a table value function with schema binding which depends on the temporal table Application.Cities. During the deployment, drop system-versioning step is being executed before schema unbinding step due to which the exception is thrown.
Contributor guide
Research direction
Reproduce the failure on SQL Server 2022 using the temporal Department table and schema-bound table-valued function, or the WideWorldImporters encryption flow. Inspect the DacFx deployment plan around system-versioning removal and schema unbinding; done means the unbinding step precedes dropping system-versioning and encryption completes without the exception.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- csharp, sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100