SQL篩選索引

--篩選索引 for IS NOT NULL WHERE 條件
CREATE NONCLUSTERED INDEX NCI_TABLE1
ON TABLE1(FLD3, FLD4)
INCLUDE (FLD1, FLD2)
WHERE FLD5 IS NOT NULL
GO

SQL Database Mail

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name='XXX',
@recipients='YYY@ZZZ.com.tw;',
        @subject = @Mail_subject,
        @body = @Mail_body,
        @body_format = 'HTML'

SQL查詢LOG檔案大小

DBCC SQLPERF (LOGSPACE)

SQL紀錄下資料更新者UserID

CREATE TRIGGER trTable1_UPD
ON Table1
AFTER UPDATE, DELETE
AS
BEGIN
    --被刪除或是被更新的資料
    SELECT *, SUSER_SNAME() AS loginUser, HOST_NAME() AS hostName FROM deleted;
    --更新後的資料
    SELECT *, SUSER_SNAME() AS loginUser, HOST_NAME() AS hostName FROM inserted;
END
GO

SQL Stored Procedure 加密

CREATE PROC usp_TEST_ENCRYPTION
WITH ENCRYPTION
AS
BEGIN
 set nocount on
SELECT @@VERSION
END
/*
EXEC usp_TEST_ENCRYPTION
*/

SQL將外顯值插入資料表的識別欄位中

SET IDENTITY_INSERT Table1 ON;

INSERT INTO Table1 (KeyFLD, FLD1)
SELECT KeyFLD, FLD1
FROM [Srv_Remote].[DB1].[dbo].[Table1];

SET IDENTITY_INSERT Table1 OFF;

SQL撈取PK欄位

CREATE FUNCTION ufn_GetPKCols
(
@TABLE VarChar(max)
)
RETURNS NVarChar(max)
AS
BEGIN
 DECLARE @RESULT NVarChar(max)
 SET @RESULT=
 (
  SELECT LEFT(NAMES,LEN(NAMES)-1) AS FLD_LIST
  FROM
  (
   SELECT
   (
SELECT  COL_NAME(ic.object_id,ic.column_id)+','
FROM    sys.indexes AS i INNER JOIN
sys.index_columns AS ic ON  i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1
AND OBJECT_NAME(ic.object_id)=@TABLE
ORDER BY ic.key_ordinal
FOR XML PATH('')
   ) AS NAMES
  ) T
 )
 RETURN(@RESULT);
END