讀取認可快照(減少Lock)

USE master
GO
--需優化 Tempdb /cpu
ALTER DATABASE DB1
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE
GO

ufn_Split2Table 逗號 1toN Table

CREATE FUNCTION dbo.SplitInts_CTE_2(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)RETURNS @Items TABLE (Item VARCHAR(MAX))AS
BEGIN
   DECLARE @Len INT = LEN(@List) + 1;

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len)-1)))
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len),
           [value] = LTRIM(RTRIM(SUBSTRING(@List, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len)-[end]-1)))
       FROM a
       WHERE [end] < @len
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   RETURN;END

顯示統計資訊

SET STATISTICS IO ON
SET STATISTICS TIME ON

TableLock

--大量INSERT + TableLock

INSERT INTO dbo.DstTable WITH (TABLOCK)
(ID, FLD1)
SELECT * FROM dbo.SrcTable;