命令執行SQL語法

--SSMS 大量Script不足記憶體, 改用 sqlcmd 方式
sqlcmd -S PcName\SQLEXPRESS -U sa -P password -d DataBaseName -i C:\Script.sql -o C:\log.txt

讀取認可快照(減少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;

SQL從其他Table Update欄位

UPDATE Table1
SET FLD1=O.FLD1
FROM Table2 O
WHERE KEY=O.KEY

SQL檢查欄位是否存在

IF EXISTS (SELECT * FROM sys.columns C WHERE name='FLD1'
AND EXISTS(SELECT * FROM sys.tables WHERE name='Table1' AND type='U' AND object_id=C.object_id))
PRINT 'EXISTS'
ELSE
PRINT 'NOT EXISTS'