SQL CLUSTERED INDEX 與 PRIMARY KEY

--CLUSTERED INDEX
CREATE UNIQUE CLUSTERED INDEX CI_Table1
ON Table1 (ANO)

--PRIMARY KEY
ALTER TABLE Table1
ADD CONSTRAINT PK_Table1 PRIMARY KEY (ID);

SQL持續時間

SET @StartTime=getdate()

WAITFOR DELAY '00:00:03'

SET @Duration=DATEDIFF(ss,@StartTime,getdate())

SELECT @hh=FLOOR(@Duration/60.0/60.0) % 60
SELECT @mm=FLOOR(@Duration/60.0) % 60
SELECT @ss=@Duration % 60

SQL新增欄位 NOT NULL+Default()

--新增欄位 可 NULL+Default()
CREATE TABLE Table1
(
ID INT DEFAULT(0) NOT NULL
);

ALTER TABLE Table1
ADD Field1 VarChar(50) NULL DEFAULT('')

--新增欄位 不可NULL+Default() 流程
ALTER TABLE Table1
ADD Field1 VarChar(50) NULL DEFAULT('')

UPDATE Table1
SET Field1='';

ALTER TABLE Table1
ALTER COLUMN Field1 VarChar(50) NOT NULL

SQL傳回插入識別欄位的值

--傳回插入識別欄位的值

INSERT Table1 VALUES ('XXX'); SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];

SQL千分位數字

SELECT RIGHT(SPACE(20)+Replace(Convert(Varchar(20),CONVERT(money,
ROUND(1234567890.123456789,0)
 ),1),'.00',''),20) AS mMoney

SQL如何取得 EXEC 回傳的結果

DECLARE @DBInfo TABLE
(
Fileid VarChar(max),
FileGroup VarChar(max),
TotalExtents VarChar(max),
UsedExtents VarChar(max),
Name VarChar(max),
Filename VarChar(max)
);

INSERT INTO @DBInfo
EXEC('DBCC SHOWFILESTATS');

SELECT * FROM @DBInfo;

SQL MD5 雜湊函數

--MD5 雜湊
DECLARE @HashThis nvarchar(max);
SELECT @HashThis = CONVERT(nvarchar,'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('MD5', @HashThis);

SQL清除Cache

DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS ;

SQL 欄位更名

EXEC sp_rename 'Sales.SalesTerritory', 'SalesTerr';

SQL Plans Cache Enable

--optimize for ad hoc workloads disable
select * from master.sys.configurations
where name='optimize for ad hoc workloads'

--optimize for ad hoc workloads enable
sp_CONFIGURE 'show advanced options',1
reconfigure
go
sp_CONFIGURE 'optimize for ad hoc workloads',1
reconfigure
go

--check execution plan

SELECT cap.usecounts as '使用次數',cap.cacheobjtype as '快取類型', 

objtype as '物件類型',st.text, cap.size_in_bytes as '物件所耗用的位元組'    

FROM sys.dm_exec_cached_plans cap

CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

WHERE st.text not like '%sys%'

and st.text like '%SELECT * FROM%'

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

T-SQL查詢指定索引

SELECT *
FROM Table1
WITH (INDEX=PK_Table1)
WHERE KeyFld=1

SELECT *
FROM Table1
WITH (INDEX=IX_Table1)
WHERE KeyFld=1

SQL查詢所有Table筆數

CREATE TABLE #TableInfo
(
TableName VarChar(max),
Cnt INT
);

EXEC sp_MSforeachtable 'INSERT #TableInfo select ''?'' as TableName, Count(*) as Cnt from ?';

UPDATE #TableInfo
SET TableName=REPLACE(REPLACE(REPLACE(TableName,'[dbo].',''),'[',''),']','')

SELECT * FROM #TableInfo Order By Cnt DESC

DROP TABLE #TableInfo

在 SQL View 建立 Index

--CREATE VIEW
CREATE VIEW V_Table1
WITH SCHEMABINDING
AS
SELECT ID, FLD1
FROM dbo.Table1

--CREATE PK Index
CREATE UNIQUE CLUSTERED INDEX PK_Table1
ON V_Table1 (ID)

--CREATE Combine/Lookup INDEX
CREATE NONCLUSTERED INDEX NCI_Table1
ON V_Table1 (FLD1)
INCLUDE (ID)

CTE迴圈年曆

DECLARE @S_DATE AS DATE
DECLARE @E_DATE AS DATE
SET @S_DATE='2014/01/01'
SET @E_DATE='2014/12/31'
;
WITH CTE_DATE AS
(
 SELECT @S_DATE AS S_DATE
 UNION ALL
 SELECT DATEADD(d,1, S_DATE) AS S_DATE
 FROM CTE_DATE
 WHERE S_DATE<@E_DATE
)
SELECT S_DATE
FROM CTE_DATE
OPTION (MAXRECURSION 0)

CTE 遞迴

WITH CTE AS ( SELECT PostTitle , Keyword , 1 AS StartPos , -- 錨點起點　　　　　　　　 CHARINDEX(',',Keyword) AS EndPos -- 錨點終點 FROM @Temp UNION ALL SELECT PostTitle , Keyword , EndPos + 1 , -- 遞迴起點 CHARINDEX(',',Keyword,EndPos + 1) -- 遞迴終點 FROM CTE WHERE EndPos > 0 -- CHARINDEX() 大於 0 表示還有逗號存在，遞迴繼續跑，等於 0 則遞迴停止 ) SELECT PostTitle , SUBSTRING ( Keyword , StartPos , ISNULL -- 第一個參數假如為 NULL 就抓取第二參數來表示終點 ( NULLIF(EndPos,0) , -- 第一參數；利用 NULLIF() 把終點為 0 的值轉換成 NULL LEN(Keyword) + 1 -- 第二參數 ) - StartPos ) AS String FROM CTE ORDER BY PostTitle , StartPos -- 對起點進行排序，能確保關鍵字呈現順序

SQL避免在Where條件使用日期函數

--索引掃描Scan: Bad
SELECT COUNT(*) From Orders WHERE YEAR(OrderDate)=1997

--索引搜尋Seek: Good
SELECT COUNT(*) FROM Orders
WHERE OrderDate >= '1997/01/01' AND OrderDate < '1998/01/01';

SQL維護計畫手動刪除錯誤

--找出 維護計畫的ID
use msdb;
DECLARE @ID VarChar(max)
SET @ID=(select id from sysmaintplan_plans WHERE name='MaintenancePlan');

--刪除維護計畫相關Table
delete from sysmaintplan_log where plan_id = @ID
delete from sysmaintplan_subplans where plan_id = @ID
delete from sysmaintplan_plans where id = @ID

SQL多來源Table+輸出必須排序

SELECT *
FROM
(
SELECT TOP (99.999999999) PERCENT
FLD1
FROM Table1
ORDER BY FLD1
) TT

SQL大量更新資料前停用索引

--停用索引
ALTER INDEX ALL ON Table1 Disable
ALTER INDEX PK_Tabl1 ON Table1 REBUILD

--大量更新
UPDATE Table1 SET FLD1=1

--啟用索引
ALTER INDEX ALL ON Table1 REBUILD

SQL隨機撈取十筆+產生未來一年內任一天

--隨機撈取十筆
SELECT TOP 10 FLD1
 FROM Table1
 ORDER BY NEWID()

--隨機產生未來一年內某一日
SELECT CONVERT(VarChar(10), DATEADD(d,CAST(RAND(DATEPART(ms, GETDATE()))*36500 AS INT)%365, GETDATE()),111)

--隨機產生未來半年內某一日
SELECT CONVERT(VarChar(10), DATEADD(d,CAST(RAND(DATEPART(ms, GETDATE()))*18000 AS INT)%180, GETDATE()),111)

SQL撈取Table欄位名稱

CREATE FUNCTION ufn_GetTableField(@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 name+','
from syscolumns
where id=object_id(@TABLE)
--ORDER BY name
FOR XML PATH('')
) AS NAMES
) T
)
RETURN(@RESULT);
END

SQL 撈取PK欄位名稱

SELECT  i.name AS IndexName,
        OBJECT_NAME(ic.object_id) AS TableName,
        COL_NAME(ic.object_id,ic.column_id) AS ColumnName
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;

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);

SQL Server 最佳記憶體設定

1、鎖定記憶體分頁 (Lock Pages in Memory)
A.執行 gpedit.msc
[本機電腦原則]/[電腦設定]/[Windows 設定]/[安全性設定]/[本機原則]/[使用者權限指派]
[鎖定記憶體中的分頁] 新增 [服務]中 SQLServer的啟動帳號
B.執行 gpupdate /force 即時更新原則設定

2、SQL Server可用記憶體限制設定
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'3200'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

實體記憶體	Max Server Memory
2GB	1500
4GB	3200
6GB	4800
8GB	6400
12GB	10000
16GB	13500
24GB	21500
32GB	29000
48GB	44000
64GB	60000
72GB	68000
96GB	92000
128GB	124000

SQL TempTable差異及使用場合

#TempTable
在需要新增多筆暫存資料的場合，盡量使用 temp table。

DECLARE @Table Table(ID INT);
只有單筆記錄的場合才使用 table variable

SQL將所有資料表放入MemoryCache

DECLARE @TableName NVarChar(max)
DECLARE @TSQL NVarChar(max)

DECLARE curSelect CURSOR FAST_FORWARD FOR
SELECT name FROM sys.tables WHERE type='U' ORDER BY name;

OPEN curSelect

FETCH NEXT FROM curSelect
INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @TSQL=N'
SELECT * FROM '+@TableName
EXEC(@TSQL);

WAITFOR DELAY '00:00:01'

    FETCH NEXT FROM curSelect
    INTO @TableName
END
CLOSE curSelect;
DEALLOCATE curSelect;

移除SQL執行計畫快取

由於執行計畫可能被快取，所以有時候後來執行的TSQL會使用到前面的執行計畫，若要確保使用新的執行計畫，可以透過以下語法，從計畫快取移除所有元素

DBCC FREEPROCCACHE;

SQL使用CURSOR語法

DECLARE vendor_cursor CURSOR FAST_FORWARD FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
--WHILE do something
WAITFOR DELAY '00:00:01'

    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

SQL執行動態TSQL回傳值

declare @rsql Varchar(250)

declare @csql Varchar(300)

declare @rc Nvarchar(500)

declare @cstucount int

declare @ccount int

set @rsql='(select Classroom_id from EA_RoomTime where zc='+@zc+' and xq='+@xq+' and T'+@time+'=''否'') and ClassroomType=''1'''

--exec(@rsql)

set @csql='select @a=sum(teststucount),@b=sum(classcount) from EA_ClassRoom where classroom_id in '

set @rc=@csql+@rsql

exec sp_executesql @rc,N'@a int output,@b int output',@cstucount output,@ccount output--將exec的結果放入變數中的做法

SQL動態CREATE VIEW指定DB

DECLARE @sql nvarchar(4000);
DECLARE @sql_package nvarchar(4000);
DECLARE @dbName varchar(100);

set @dbName = 'Northwind';
set @sql = 'create view dbo.test_view '
set @sql = @sql + ' as'
set @sql = @sql + ' select * from customers;'

SET @sql_package = 'USE ' + @dbName + '; EXEC sp_executesql N''' + @sql + '''';
EXEC (@sql_package)

SQL更改ServerName

--更名後 ServerName
SELECT CONVERT(varchar(255), SERVERPROPERTY('servername'));
GO
--更名前 ServerName
select @@servername;
GO

--Drop錯的 ServerName
sp_dropserver 'old_name';
GO
--新增正確 ServerName
sp_addserver 'new_name', local;
GO

SQL更改資料庫名稱

USE master;
GO

-- 切換至單人模式
ALTER DATABASE DB1
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO

-- 更名資料庫
EXEC sp_renamedb '<欲更名的資料庫名稱>', '<新的資料庫名稱>';
GO

-- 回復多人模式
ALTER DATABASE DB1
SET MULTI_USER
GO

--更改群組檔案、資料庫檔案、資料庫日誌檔的名稱
ALTER DATABASE <舊資料庫名稱>
Modify NAME = <新資料庫名稱>;

SQL恢復[正在還原]資料庫

RESTORE DATABASE DB1
WITH RECOVERY

SQL搬移Table到新filegroup語法

--適用 PK Index 同時存在 PK Constraint
1.搬移 Data & 刪除 PK Index:
ALTER TABLE Table1 DROP CONSTRAINT PK_Table1 WITH (ONLINE=ON, MOVE TO [FG_NEW]);

2.新增 PK Index:
ALTER TABLE Table1  ADD CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED ([KeyCol]) ON [FG_NEW];

3.搬移 NonClustered index:
CREATE NONCLUSTERED INDEX IX_Table1
ON Table (FLD1)
WITH (DROP_EXISTING = ON, ONLINE = ON, FILLFACTOR =90)
 ON [FG_NEW];

SQL 錯誤訊息抓取

DECLARE @ERR_NO INT
DECLARE @ERR_MSG NVarChar(max)

SELECT 1/0;
SET @ERR_NO=@@ERROR

SELECT @ERR_MSG='ERR('+CAST(@ERR_NO AS NVarChar(max))+'): '+text
FROM sys.messages
WHERE language_id=1028
AND message_id = @ERR_NO;

PRINT @ERR_MSG;

--===============================

BEGIN TRY
SELECT 1/0;
END TRY
BEGIN CATCH
PRINT 'ERR('+CAST(ERROR_NUMBER() AS NVarChar(max))+'): '+ERROR_MESSAGE()
END CATCH
PRINT '仍繼續往下'

T-SQL 執行計畫中排序為最高成本的解決方法

SELECT FLD1, FLD2
FROM TABLE1
ORDER BY FLD3, FLD4

1.建立 Index
CREATE NONCLUSTERED INDEX NCI_TABLE1
ON TABLE1(FLD3, FLD4)
INCLUDE (FLD1, FLD2)

2.更新統計資訊
UPDATE STATISTICS TABLE1;

SQL 並用 PARTITION 與 ROW_NUMBER()範例

--總筆數TotalCounts, 群組筆數GroupCounts, 排序每筆RowID
SELECT TotalCounts=COUNT(*) OVER(),
               GroupCounts=COUNT(*) OVER(PARTITION BY TerritoryName),
               ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS RowID
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
ORDER BY TerritoryName;

SQL StoreProcedure 參數範例

IF OBJECT_ID ( 'HumanResources.uspGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName nvarchar(50),
    @FirstName nvarchar(50)
AS
 
    SET NOCOUNT ON;
    SELECT FirstName, LastName, JobTitle, Department
    FROM HumanResources.vEmployeeDepartment
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO

SQL統計資訊方針

1.取消自動更新統計資訊設定

2.新增維護計劃固定更新統計資訊

3.新增維護計劃刪除SQL自動更新統計資訊(_WA_SYS)

Store Procedure 執行計劃最佳化

--SQL Server 2008 以上版本在Store procedure 中, 加上 OPTIMIZE FOR
SELECT * FROM Person.Address
WHERE City = @city_name
OPTION (OPTIMIZE FOR (@city_name UNKNOWN));

更新SQL資料庫或資料表狀態

1.更新DB:
EXEC sp_updatestats;
GO
DBCC UPDATEUSAGE (DB1);
GO

2.更新Table
UPDATE STATISTICS Table1;
GO
DBCC UPDATEUSAGE (DB1,'Table1');
GO

3.快速更新Table(剔除Index)
UPDATE STATISTICS Table1 WITH FULLSCAN, COLUMNS;
GO

將 SQL 單一大 DB.mdf 資料平均分配到多個 DB.ndf

1.新增 DB.ndf:
ALTER DATABASE DB1
ADD FILE
(
NAME = DF1,
FILENAME = 'D:\SQL_DATA\df1.ndf',
SIZE = 10240MB,
FILEGROWTH = 100MB
)

2.將資料從大DB.mdf 搬至其他 DB.ndf:
DBCC SHRINKFILE(DB, EMPTYFILE)

3.觀察資料搬移情形(UsedExtents), 並隨時中止搬移:
DBCC SHOWFILESTATS