Date: Fri, 29 Mar 2024 09:01:33 +0000 (GMT) Message-ID: <1268859360.619.1711702893694@ip-172-31-38-4.eu-west-1.compute.internal> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_618_1021082893.1711702893689" ------=_Part_618_1021082893.1711702893689 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
When importing programmable objects into your SQL Change Automat= ion project for the first time, you may notice that one or more scripts are= placed into a folder called Unsupported, instead of the usual Programmable Objects folder. This can happen to a script containing a stored procedure/view/u= ser defined function object if one or more of the following applies:
Scena= rio A: The object contains the SCHEMABINDING clause and it is part of a= dependency chain with other schema-bound objects
Scena= rio B: (User Defined Functions only) There is a dependency on = the object from a table object, for example a computed column or check cons= traint with a call to the function
Scena= rio C: (User Defined Functions in SQL Server 2016+/Azure SQL Databa= se only) There is a dependency on the function from a security policy = object
Scena= rio D: The object contains the NATIVE_COMPILATION clause, preventing it= from being deployed within a user transaction
If any of the above applies, SQL Change Automation wil= l separate the affected object files into a folder called Unsupported= em>, which is not included in project build or database deployment operatio= ns. The files are moved into this folder during the import process to preve= nt the scripts from failing at deployment or verification time.
By default, SQL Change Automation will import all of y= our schema objects -- both table and programmable object types -- into nume= rically ordered migration scripts. However, you can decide to split programmable object types (stor= ed procedures, views, functions etc) into individual files to simplify sour= ce control branch and merge activities, allowing your team to make changes = to code objects in parallel.
This works well for objects with "sof= t" dependencies: for example, a view that selects data from another view, o= r a stored procedure that calls another stored procedure, since those types= of objects can be repeatedly dropped and recreated in any order without ge= nerating errors at deployment time. Things get more complicated when dealin= g with "hard" dependencies, such as when a schema-bound view depends on ano= ther schema-bound view, or when a table contains a check constraint that de= pends on a user-defined function.
To preserve the integrity of schema-b= ound objects, SQL Server enforces the dependencies between such objects at = deployment time. If you try to change any object that is chained to another= object without first unwinding the dependency tree, SQL Server will preven= t the change from occurring by raising an error, causing the deployment to = be aborted.
To demonstrate how objects in a depen= dency chain can be turned into programmable object scripts, let's take a sc= enario that involves two schema-bound views:
-- ViewA= .sql CREATE VIEW [dbo].[ViewA] WITH SCHEMABINDING AS SELECT 1 As MyCol; GO =20 -- ViewB.sql CREATE VIEW [dbo].[ViewB] WITH SCHEMABINDING AS SELECT MyCol FROM [dbo].[ViewA]; GO
Here [ViewB] has a dependency on [Vie= wA]. If we try to make a change to [ViewA], e.g.
ALTER VI= EW [dbo].[ViewA] WITH SCHEMABINDING AS SELECT 2 As MyCol; GO
The following error will be raised:= span>
Msg 3729= , Level 16, State 3, Procedure ViewA, Line 16 Cannot ALTER 'dbo.ViewA' because it is being referenced by object 'ViewB'.<= /pre>
SQL Server has indicated that the cha= nge cannot be made until all references to the object are removed. The diff= iculty with using SQL Change Automation's programmable object= s feature in this scenario is that every script file is executed independen= tly of the others, so dropping and recreating all the objects in the depend= ency tree is not possible.
In order to turn the unsupported= files into programmable object files, the objects must be combined into a = single script to allow the entire dependency tree to be deployed in one ato= mic operation.
In summary, the programmable object s= cript must perform the following operations:
The programmable object script (Progr= ammable Objects\Views\dbo.ViewA_dbo.ViewB.sql) will end up looking somethin= g like this:
IF OBJECT_ID('[dbo].[ViewB]') IS NOT NULL DROP VIEW [dbo].[ViewB]; GO =20 IF OBJECT_ID('[dbo].[ViewA]') IS NOT NULL DROP VIEW [dbo].[ViewA]; GO =20 CREATE VIEW [dbo].[ViewA] WITH SCHEMABINDING AS SELECT 1 As MyCol; GO =20 CREATE VIEW [dbo].[ViewB] WITH SCHEMABINDING AS SELECT MyCol FROM [dbo].[ViewA]; GO
In this example, both views are dropp= ed and recreated in order of dependency. The conditional logic around the D= ROP statements ensures that the script is re-runnable (idempotent), allowin= g changes to the objects to deployed incrementally to your target databases= .
To demonstrate how a table that has a dependency on a function can be tu= rned into a programmable object script, let's take the scenario of a comput= ed column that includes a call to a user defined function. For example, the= AdventureWorks2014 sample database contains the following table d= efinition:
CREATE = TABLE [Sales].[Customer] ( [CustomerID] [int] NOT NULL IDENTITY(1, 1), [PersonID] [int] NULL, [StoreID] [int] NULL, [TerritoryID] [int] NULL, [AccountNumber] AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]),'')), [rowguid] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT [DF_Customer_ro= wguid] DEFAULT (newid()), [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Customer_ModifiedDate] DE= FAULT (getdate()) ) ALTER TABLE [Sales].[Customer] ADD CONSTRAINT [PK_Customer_CustomerID] PRIM= ARY KEY CLUSTERED ([CustomerID]) GO CREATE UNIQUE NONCLUSTERED INDEX [AK_Customer_AccountNumber] ON [Sales].[Cu= stomer] ([AccountNumber]) GO
The table's [AccountNumber] column has a reference to the func= tion object, [ufnLeadingZeros]:
SET QUO= TED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE FUNCTION [dbo].[ufnLeadingZeros]( @Value int )=20 RETURNS varchar(8)=20 WITH SCHEMABINDING=20 AS=20 BEGIN DECLARE @ReturnValue varchar(8); SET @ReturnValue =3D CONVERT(varchar(8), @Value); SET @ReturnValue =3D REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @Re= turnValue; RETURN (@ReturnValue); END; GO
Note that the computed column also has an index on it. In order to make = it possible to change the function in an idempotent way, the dependency tre= e that involves the computed column and that index upon that column must be= unwound and then recreated in the appropriate order within the programmabl= e object script itself:
IF OBJECT_ID('[dbo].[ufnLeadingZeros]') IS NOT NULL BEGIN =09DROP INDEX [Sales].[Customer].[AK_Customer_AccountNumber]; =09ALTER TABLE [Sales].[Customer] DROP COLUMN [AccountNumber]; =09DROP FUNCTION [dbo].[ufnLeadingZeros]; END GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[ufnLeadingZeros]( @Value int )=20 RETURNS varchar(8)=20 WITH SCHEMABINDING=20 AS=20 BEGIN DECLARE @ReturnValue varchar(8); SET @ReturnValue =3D CONVERT(varchar(8), @Value); SET @ReturnValue =3D REPLICATE('0', 8 - DATALENGTH(@ReturnValue)) + @Re= turnValue; RETURN (@ReturnValue); END; GO ALTER TABLE [Sales].[Customer] =09ADD [AccountNumber] AS (isnull('AW'+[dbo].[ufnLeadingZeros]([CustomerID]= ),'')); GO CREATE UNIQUE NONCLUSTERED INDEX [AK_Customer_AccountNumber] =09ON [Sales].[Customer] ([AccountNumber]); GO
Changes to both the function, as well as the computed column that uses t= he function, can then be made by simply editing the programmable object fil= e and deploying the SQL Change Automation project.
To demonstrate how a security policy that has a dependency on a function= can be turned into a programmable object script, let's take the following = example from the WideWorldImporters sample database:
CREATE = SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole] ADD FILTER PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityI= D]) ON [Sales].[Customers], ADD BLOCK PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityID= ]) ON [Sales].[Customers] AFTER UPDATE WITH (STATE =3D ON) GO
The security policy references the [Application].[DetermineCustomer= Access] function:
SET QUO= TED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE FUNCTION [Application].[DetermineCustomerAccess](@CityID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT 1 AS AccessResult WHERE IS_ROLEMEMBER(N'db_owner') <> 0 OR IS_ROLEMEMBER((SELECT sp.SalesTerritory FROM [Application].Cities AS c INNER JOIN [Application].StateProvinces AS sp ON c.StateProvinceID =3D sp.StateProvinceID WHERE c.CityID =3D @CityID) + N' Sales') <>= 0 =09 OR (ORIGINAL_LOGIN() =3D N'Website' =09=09 AND EXISTS (SELECT 1 =09=09 FROM [Application].Cities AS c =09=09=09=09 INNER JOIN [Application].StateProvinces AS sp =09=09=09=09 ON c.StateProvinceID =3D sp.StateProvinceID =09=09=09=09 WHERE c.CityID =3D @CityID =09=09=09=09 AND sp.SalesTerritory =3D SESSION_CONTEXT(N'SalesTerrit= ory')))); GO
In order to make it possible to change the function within a programmabl= e object script, the security policy must first be dropped and then recreat= ed after the function is modified:
IF OBJECT_ID('[Application].[DetermineCustomerAccess]') IS NOT NULL BEGIN =09DROP SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole]= ; =09DROP FUNCTION [Application].[DetermineCustomerAccess]; END SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE FUNCTION [Application].[DetermineCustomerAccess](@CityID int) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT 1 AS AccessResult WHERE IS_ROLEMEMBER(N'db_owner') <> 0 OR IS_ROLEMEMBER((SELECT sp.SalesTerritory FROM [Application].Cities AS c INNER JOIN [Application].StateProvinces AS sp ON c.StateProvinceID =3D sp.StateProvinceID WHERE c.CityID =3D @CityID) + N' Sales') <>= 0 =09 OR (ORIGINAL_LOGIN() =3D N'Website' =09=09 AND EXISTS (SELECT 1 =09=09 FROM [Application].Cities AS c =09=09=09=09 INNER JOIN [Application].StateProvinces AS sp =09=09=09=09 ON c.StateProvinceID =3D sp.StateProvinceID =09=09=09=09 WHERE c.CityID =3D @CityID =09=09=09=09 AND sp.SalesTerritory =3D SESSION_CONTEXT(N'SalesTerrit= ory')))); GO CREATE SECURITY POLICY [Application].[FilterCustomersBySalesTerritoryRole] ADD FILTER PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityI= D]) ON [Sales].[Customers], ADD BLOCK PREDICATE [Application].[DetermineCustomerAccess]([DeliveryCityID= ]) ON [Sales].[Customers] AFTER UPDATE WITH (STATE =3D ON) GO
Changes to both the function, as well= as the security policy that uses the function, can then be made by simply = editing the programmable object file and deploying the SQL Change Automatio= n project.
Objects that contain the NATIVE_CO= MPILATION clause require special handling because it is not possible to cre= ate, drop or alter these types of objects within a user transaction. This i= s important because typically SQL Change Automation will = try to execute all of your m= igrations and programmable objects within a single transaction to ensure that the deployment is performed atomically. However, in order = to deploy natively-compiled stored procedures and functions, it is necessar= y to disable SQL Change Automation= 's automatic transaction handling. This can be done at the script level= by pasting the following metadata onto the first line of affected script(s= ):
-- <= Migration TransactionHandling=3D"Custom" /> GO
For example, this is how the [RecordCo= ldRoomTemperatures] procedure in the WideWorldImporters&= nbsp;sample database could be turned into a programmable object:
-- <Migration TransactionHandling=3D"Custom" /> GO IF OBJECT_ID('[Website].[RecordColdRoomTemperatures]') IS NOT NULL =09DROP PROCEDURE [Website].[RecordColdRoomTemperatures]; GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE [Website].[RecordColdRoomTemperatures] @SensorReadings Website.SensorDataList READONLY WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER AS BEGIN ATOMIC WITH ( =09TRANSACTION ISOLATION LEVEL =3D SNAPSHOT, =09LANGUAGE =3D N'English' ) BEGIN TRY =09=09DECLARE @NumberOfReadings int =3D (SELECT MAX(SensorDataListID) FROM = @SensorReadings); =09=09DECLARE @Counter int =3D (SELECT MIN(SensorDataListID) FROM @SensorRe= adings); =09=09DECLARE @ColdRoomSensorNumber int; =09=09DECLARE @RecordedWhen datetime2(7); =09=09DECLARE @Temperature decimal(18,2); =09=09-- note that we cannot use a merge here because multiple readings mig= ht exist for each sensor =09=09WHILE @Counter <=3D @NumberOfReadings =09=09BEGIN =09=09=09SELECT @ColdRoomSensorNumber =3D ColdRoomSensorNumber, =09=09=09 @RecordedWhen =3D RecordedWhen, =09=09=09=09 @Temperature =3D Temperature =09=09=09FROM @SensorReadings =09=09=09WHERE SensorDataListID =3D @Counter; =09=09=09UPDATE Warehouse.ColdRoomTemperatures =09=09=09=09SET RecordedWhen =3D @RecordedWhen, =09=09=09=09 Temperature =3D @Temperature =09=09=09WHERE ColdRoomSensorNumber =3D @ColdRoomSensorNumber; =09=09=09IF @@ROWCOUNT =3D 0 =09=09=09BEGIN =09=09=09=09INSERT Warehouse.ColdRoomTemperatures =09=09=09=09=09(ColdRoomSensorNumber, RecordedWhen, Temperature) =09=09=09=09VALUES (@ColdRoomSensorNumber, @RecordedWhen, @Temperature); =09=09=09END; =09=09=09SET @Counter +=3D 1; =09=09END; END TRY BEGIN CATCH THROW 51000, N'Unable to apply the sensor data', 2; RETURN 1; END CATCH; END; GO