SQL Trigger 範例

--新增Trigger
CREATE TRIGGER trTable1_ADD
ON Table1
AFTER INSERT
AS
IF (Select Count(*) From INSERTED) > 0 and (Select Count(*) From DELETED) = 0
BEGIN

 SET NOCOUNT ON;

INSERT INTO Table1_ADDLOG ([ID])
SELECT ID FROM INSERTED
END

--更新Trigger
CREATE TRIGGER trTable1_UPD
ON Table1
AFTER UPDATE
AS
IF (Select Count(*) From INSERTED) > 0 and (Select Count(*) From DELETED) > 0
BEGIN
 IF UPDATE(WEB_PD)
 BEGIN
 SET NOCOUNT ON;
INSERT INTO Table1_UPDLOG ([ID], [FLD1_BF], [FLD1_AF])
SELECT I.ID, D.FLD1, I.FLD1
FROM INSERTED I
INNER JOIN DELETED D ON I.ID=D.ID
 END
END

--刪除Trigger
CREATE TRIGGER trTable1_DEL
ON Table1
AFTER DELETE
AS
IF (Select Count(*) From INSERTED) = 0 and (Select Count(*) From DELETED) > 0
BEGIN
 SET NOCOUNT ON;
INSERT INTO Table1_DELLOG ([ID])
SELECT ID FROM DELETED
END

--暫停 Trigger
DISABLE TRIGGER trTable1_UPD ON Table1;

SQL tempdb 調整

1.修改 tempdb Size:
ALTER DATABASE tempdb MODIFY FILE (NAME=tempdev, SIZE=40MB, MAXSIZE=UNLIMITED, FILEGROWTH=5MB);

2.新增 tempdb .ndf file:
ALTER DATABASE tempdb ADD FILE (NAME=tempdev_1, FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdev_1.ndf', SIZE=40MB, MAXSIZE=UNLIMITED, FILEGROWTH=5MB);

SQL多筆資料合併成一筆, 以逗號隔開

--方法1
SELECT LEFT(FLD1S, LEN(FLD1S)-1) AS FLD1S_MERGE
FROM
(
SELECT
(
SELECT FLD1 + ','
FROM Table1
FOR XML PATH('')
) AS FLD1S
) TT

--方法2
 SELECT STUFF(
(
 SELECT ','+ FLD1
 FROM Table1
 FOR XML PATH('')
)
, 1,1,'') AS FLD1S

手動壓縮SQL資料庫

USE DB1
--保留10%可用空間for異動
DBCC SHRINKDATABASE ([DB1],10);

SQL設定Table壓縮模式

--1.先更新 Usage
DBCC UpdateUsage('DB1','Table1');

--2.檢視 Table 空間使用狀態
EXEC sp_spaceused Table1;

--3.預估可壓縮空間
EXEC sp_estimate_data_compression_savings 'dbo', 'Table1', NULL, NULL, 'Page' ;

--4.執行 Table 壓縮設定 Page/Row, CPU MAX=1/2
ALTER TABLE dbo.Table1 REBUILD WITH (DATA_COMPRESSION=PAGE, MAXDOP=8);