在 MS SQL Server 中正確使用 varbinary 類型

我在一個新位置，我被告知要實現一個儲存過程，該過程將接受一個使用者 ID 列表並更新一個標誌。

當我建議使用表值參數（數組仿真）時，我被告知要實現如下所示，因為這是以前的做法。我不是 DBA，但我是一名全棧開發人員，這對我來說很有趣。

僅供參考 - 使用者 ID 被實現為類型int

CREATE PROCEDURE [dbo].[UpdateUsers]       
@rgIDs varbinary(max)      -- contains several ids
AS      
DECLARE @tblTmp TABLE (ID int PRIMARY KEY)      

DECLARE @ich int, @cch int, @ID int      
SET @cch = DATALENGTH (@rgIDs)      
SET @ich = 1   

WHILE (@ich < @cch)      
BEGIN      
 SET @ID = SUBSTRING (@rgIDs, @ich, 4) 

 UPDATE dbo.Users u 
 SET isUpdated = 1
 WHERE u.ID = @ID

 SET @ich = @ich + 4      
END      

表值類型在​​這裡不是更好嗎？更具可讀性，性能，更不容易出錯等……？

我相信我將使用 SQL Server 2012 或 2014。絕對 > 2008。

我被告知要按照下面的展示來實施，因為這是以前的做法。……這對我來說很有趣。

你知道有什麼比這種方法更有趣的嗎？那行“推理”。舊的“這一直是這樣做的”只是一種避免思考和討論它的手段。即使發現程式碼是最好的方法，僅此一項就應該引發危險。我以前也被告知過同樣的事情，這是出於同樣的悲哀的根本原因：採用這種方法是出於過時的原因（例如舊版本的 SQL Server 沒有特定功能，但我們使用的是具有該功能的較新版本）。非常令人沮喪。

表值類型在​​這裡不是更好嗎？更具可讀性，性能，不易出錯等……

假設您使用的是 SQL Server 2008 或更高版本，那麼答案通常是肯定的。我的意思是，目前的程式碼是一種避免字元串拆分操作的有點聰明的方法。但是，TVP 可以是強類型的，因此無需int將應用層中的值轉換為byte[]just 以便可以在WHILE此處循環解包。基於該列表中有多少個 ID（目前程式碼創建並送出），單個基於集合的UPDATE語句在性能方面將比 N 個UPDATEs 更好（目前程式碼創建並送出）每個UPDATE語句 1 個事務，因為WHILE循環未包裝在顯式事務中. 因此，在目前方法中，要更新的 20 個 ID = 20 個事務。這就是為什麼單一的、基於集合的UPDATE效率更高。

只需確保實現全流式方法，這意味著：不要將集合中的值轉儲到DataTable! 不幸的是，在大多數範例中，這樣做是對時間、記憶體和 CPU 的不必要浪費。相反，創建一個實現IEnumerable、接受 UserID 集合併返回的方法IEnumerable<SqlDataRecord>。然後，將該方法用作SqlParameterTVP 的“值”。在該方法中，循環遍歷集合，並為每個元素呼叫yield return;.

在我對以下問題的回答中，我有一些關於 StackOverflow 的範常式式碼：

將字典傳遞給儲存過程 T-SQL

請注意，雖然尚未測試，但我聽說在較新版本的 SQL Server（2014 或更高版本）上，通過對使用者定義的表類型使用記憶體中 OLTP 可能會獲得額外的性能提升。

---

此外，由於您提到這是對“更易讀的程式碼”的渴望的一部分，我建議使用有意義的參數和變數名稱:-)。是的，我確實意識到這可能不是您的程式碼，但只是想我會提到它。另外，我會擺脫它，DECLARE @tblTmp因為它沒有被使用，但我再次意識到這可能是您編輯的更大範例的一部分。

引用自：https://dba.stackexchange.com/questions/186863