【SQL Server】使用SqlPackage產生SQL語法

2022-09-07

342
0
SQLServer

最近為解決工作上的問題，測試了一下SqlPackeage產出的SQL指令，但過程沒有很順利，碰到了各種的阻礙，而且阿猩發現有一些非常重要的概念，故再多寫一篇網誌，特別紀錄這些有用的訊息。

SqlPackage

使用Visual Studio進行.dacpac檔案的比較後，雖然可以產生SQL語法，但只提供Create Table的語法(參考1)。對於資料庫中已存在的Table，阿猩還是花了很多時間，人工比對並逐一撰寫Alter的語法。因此，阿猩這週一直思考，是否有更快速的方法可以解決目前問題。

趁著假日再研究一下SqlPackage，Action的參數除了Extract之外，還有Publish、Export、Script等Action可用。看了微軟的說明，還是沒甚麼頭緒，但後來找到另一篇文章(參考2)，似乎看到了一線曙光，使用Script Action居然可以直接產生Alter的指令。

使用SqlPackage 之Script Action
語法如下

sqlpackage
/action:Script
/SourceFile:”C:\temp\SnapshotV1.dacpac”
/TargetFile:”C:\temp\SnapshotV2.dacpac”
/TargetDatabaseName:SQLShackDemoDB
/OutputPath:”C:\temp\SnapshotDiff.sql”

不同版本SQL Server 造成的錯誤
執行語法後得到的的回應是
*** 部署計畫產生期間發生錯誤。部署無法繼續。
無法將指定 SQL Server 2019 做為目標平台的專案發行到 SQL Server 2017。
阿猩使用兩個自行建立的資料庫匯出的.dacpac檔案，並沒有遇到這個問題，就猜想可能是客戶的SQL Server版本，與阿猩本機的版本不相容，阿猩猜想既然.dacpac檔案內會紀錄SQL Server的版本，那在阿猩本機，使用部署資料層應用程式匯入，再重新匯出，應該有機會成功？嘗試的結果是…可行！

SQL進階知識補充

Transaction
在討論SqlPackage 產出的SQL語法之前，先補充Transaction的概念。在實務中，經常會碰到複雜的資料庫異動，例如客戶下訂單，需要更新訂單、庫存、紅利累積紀錄等Table。如果個別進行更新，很有可能會因為突然出現的問題，而造成表單之間的資料不一致。

Transaction解決了這個問題，在開啟交易功能(BEGIN TRANSACTION)後，所執行的異動，會暫時存起來，直到提交交易(COMMIT TRANSACTION)後才會真的將此筆交易寫入硬碟，過程中如有異動失敗，則使用ROLL BACK機制，讓資料還原交易原先的狀態(參考3)。

Transaction Isolation Levels
假設在一時間內，有多人對同一Table，進行讀取及異動，就會面臨誰先誰後的問題，Isolation Level可設定表單操作遇到競爭時的行為表現。SQL Server提供了5種Isolation Level(參考4)

READ UNCOMMITTED :可讀取其他交易未Commit的資料。
READ COMMITTED：只能讀取其他交易已Commit的資料，但會受到SNAPSHOT設定而影響行為。
REPEATABLE READ：A交易不能讀取其他交易未Commit的資料，且在A交易完成之前，其他交易不能修改A交易已讀取的資料，但可插入新資料。
SNAPSHOT：A交易在讀取前就會先產生快照，其他交易的讀取或變動都不影響，但如果A交易進行Rollback，過程中需要讀取別的表(且正在RollBack)，A交易會被暫停。
SERIALIZABLE：A交易不能讀取其他交易未Commit的資料，且在A交易完成之前，其他交易不能修改A交易已讀取的資料，也不可插入新資料。

在Transaction中可設定Isolation Level，使用SQL語法時，也可以使用同樣的概念來滿足需求，例如查詢下

SELECT * FROM Table1 with(noLock)

阿猩另外做了一個確認測試，如果Table沒有索引，Transaction未Commit時會鎖表，如果有索引的情況下，Transaction未Commit時只會鎖該行資料。阿猩爬文的過程中，找到一篇非常厲害的文章，有興趣深入研究可參考 (參考5)。

SqlPackage Script產出之SQL語法解析

Create Table
如果只是單純Create Table，SqlPackeage產出語法會像是

CREATE TABLE [dbo].[Test1] (
   [Last Name]     VARCHAR (5)  NOT NULL,
   [First Name]       VARCHAR (50) NOT NULL, [Old] VARCHAR (10) NULL,
   [Address] VARCHAR (100) NULL,
   CONSTRAINT [PK_Test1_1] PRIMARY KEY CLUSTERED ([Last Name] ASC, [First Name] ASC)
);
GO

除了欄位之外，有索引鍵也會一併產出語法，讚。

Alter Table
Alter Table就是原資料庫已經存在A資料表，而要更新A資料表的結構，一般常見的Alter Table的動作，例如ADD CONSTRAINT 、ADD、ALTER COLUMN。

ALTER TABLE [dbo].[Login] ALTER COLUMN [IP] VARCHAR (50) NOT NULL;

但阿猩在產出的SQL中，發現在某些情況下，例如原索引鍵修改，或其他未知(?)情況，ALTER 。

產生一個暫存(tmp)的Table
將原Table的資料，寫入tmp Table
將原Table刪除
將tmp Table，改為原Table Name

SQL會像是

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

CREATE TABLE [dbo].[tmp_ms_xx_Test] (
   [Id]         VARCHAR (5)    NOT NULL,
   [Name]       VARCHAR (50)   NOT NULL,
   [Color]      VARCHAR (50)   NOT NULL
   CONSTRAINT [tmp_ms_xx_constraint_PK_Test] PRIMARY KEY CLUSTERED ([Id] ASC, [Name] ASC)
);
IF EXISTS (SELECT TOP 1 1 
          FROM   [dbo].[Test])
   BEGIN
       INSERT INTO [dbo].[tmp_ms_xx_Test] ([Id], [Name], [Color])
       SELECT   [Id],
                [Name],
                [Color]
       FROM     [dbo].[Test]
       ORDER BY [Id] ASC, [Name] ASC;
   END
DROP TABLE [dbo].[Test];
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_Test]', N'Test';
EXECUTE sp_rename N'[dbo].[tmp_ms_xx_constraint_PK_Test]', N'PK_Test', N'OBJECT';
COMMIT TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

流程是會碰到資料轉移及刪除原表，雖然產出的SQL有使用Transaction，但如果人為操作失誤，例如複製後忘了改Table名稱，或是Rollback失敗(?)，感覺會發生慘案，要不要使用就自行斟酌囉。

其他
像是View、StoredProcedure都大致跟上述語法差不多，如果團隊好棒棒，有整理Table欄位描述(圖1 )，則會出現內建的StoredProcedure-sp_addextendedproperty，更改欄位描述則會是sp_updateextendedproperty

參考資料

回首頁

阿猩的程式蛋糕店

熱衷於自己喜歡的事情，時間久了，就能讓生硬的內容變成一塊美味的蛋糕。

【SQL Server】使用SqlPackage產生SQL語法

標籤雲

系列文章

最新留言