TSQL邏輯太複雜，請使用SP
如果查詢陳述句太過複雜，請使用SP:
如果你的商業邏輯複雜導致查詢陳述句過長又龐大，
建議可以使用SP來撰寫，因為用戶端(client)只會傳SP Name給SQL Server(而不是一長串的TSQL)，
所以網路的傳輸量也會降低不少。
請使用兩節式命名
請使用兩節式命名:
相關的物件請都使用兩節式(schema name+object name)命名，
因為這可以直接且明確找到該物件和編譯過的執行計畫，
而省下搜尋其他schema底下可能的object所浪費的資源和時間。
1
2
3
4
5
6
7
8
9
--兩節式命名
select * from dbo.test
exec dbo.myproc
--避免
select * from test
exec myproc
命名勿使用 sp_ 開頭(使用usp開頭)
stored procedure命名勿使用 sp_ 開頭:
如果stored procedure使用sp_開頭，那SQL Server 會先搜尋master database完後，
在搜尋現階段連線的database，
這不僅讓費時間和資源，也增加出錯的機率(如果master database有相同的stroed procedure名稱)
1
2
3
4
5
--usp開頭
create proc dbo.usp_xxx
--避免
create proc dbo.sp_xxx
加上 set nocount on
不要忘記 set nocount on:
SQL Server會針對每個Select 和 DML 回傳訊息給用戶端，
當有設定 nocount on時就可以關閉SQL Server 回傳訊息的行為，
這樣對效能會有不錯的改善，因為網路的傳輸量會降低不少。
因為減少了網路往返流量，就可以提昇其執行效能。
在執行每句
T-SQL陳述式時，依據預設值，系統都會自動傳送此陳述式所影響到的資料列之筆數。
但若是要執行包含了多個陳述式的預存程序、觸發程序等，或是包含了迴圈等運算邏輯的陳述式，反而會產生大量的網路傳輸量。
因此，建議在預存程序、觸發程序、使用者自訂函數等內或是執行T-SQL陳述式之前，加入設定SET NOCOUNT為ON，調整為不要回傳所影響的資料列之筆數訊息。
1
2
3
4
5
6
7
8
9
10
11
12
13
USE AdventureWorks2022;
GO
CREATE PROCEDURE HumanResources.uspGetEmployeesTest2
@LastName nvarchar(50),
@FirstName nvarchar(50)
AS
SET NOCOUNT ON; --設定不要傳回所影響的資料列筆數之訊息
SELECT FirstName, LastName, Department
FROM HumanResources.vEmployeeDepartmentHistory
WHERE FirstName = @FirstName AND LastName = @LastName
AND EndDate IS NULL;
GO
Begin End
BEGIN/END 這二個關鍵字用來包住程式碼的區塊，可用可不用，只是讓程式碼看起來更明確。
1
2
3
4
5
6
7
8
9
10
11
12
--建立sp
create procedure usp_Test
@id int
as
begin
select sum(數量) as "販賣數量"
from 販賣資料
where 商品ID = @id
end
--執行sp
exec dbo.usp_Test 1
檢測預存程序是否存在
1
2
3
IF OBJECT_ID('dbo.uspAddEmp', 'P') IS NOT NULL
--DROP PROC dbo.uspAddEmp;
PRINT 'EXIST'
MSDN - SET NOCOUNT (Transact-SQL)
MSDN - 建立預存程序
德瑞克：SQL Server 學習筆記 - 減少網路傳輸量，進而提升效能，以設定 SET NOCOUNT ON 不要回傳所影響的資料列筆數之訊息為例
vito-note - Stored Procedures
[SQL SERVER][Memo]撰寫Stored Procedure小細節