[SQL] 案例觀點

在網路上發現該篇文章 - Find 40 Problems in this SQL Server Stored Procedure，這標題還頗有吸引力，要在一隻 Store Procedure 內找出 40 個問題，該篇是個人看完該 Store Procedure 後的心得筆記

文章範例是以 AdventrueWorks 當成範例，之前完全沒用過 Production.ProductSubCategory Table，把相關 Table ER 圖拉出來了解各 Table 關係

範例 Store Procedure 商業邏輯是要找出滿足下列條件的客戶 Email，分別為

• 一年前有採購過，但後來都沒有採購紀錄的客戶
• 近一年採購次數超過 (含) 三次以上的客戶
• 過去兩周內有採購指定類別的客戶

該 Store Procedure 請至文章內查詢，以下是個人想法

Point1：distinct 

select customerid, firstname, emailaddress 
from
(
	-- last placed an order more than a year ago
	select distinct customerid, firstname, emailaddress 
	from 
		.....
	union 
	-- placed at least 3 orders in the past year
	select distinct customerid, firstname, emailaddress 
	from 
		.....
	union 
	-- ordered within specified category in past two weeks
	select distinct customerid, firstname, emailaddress 
	from 
		.....
) 
x 

三段商業邏輯是透過 union 來串接結果，union 本身就會移除重覆資料，所以每段商業邏輯的 distinct 就顯得是多餘，延伸閱讀 - UNION

Point2：Table Join 方式

select distinct customerid, firstname, emailaddress
from sales.customer a (nolock), 
	person.person b(nolock), 
	person.emailaddress c(nolock) 
where personid = b.businessentityid 
	and b.businessentityid = c.businessentityid 
	.....

SQL Server 2005 以 ANSI SQL 標準為主，Table Join 會寫成 Join On 形式，SQL Server 2000 因為支援 SQL-92，所以 Table Join 會寫成上述語法，要實踐 Left Join 和 Right Join 可以寫成 *= 和 =* 

-- Left Join
SELECT * 
FROM TableA A , TableB B 
WHERE A.ColName *= B.ColName

-- Right Join
SELECT * 
FROM TableA A , TableB B 
WHERE A.ColName =* B.ColName

但 SQL Server 2005 開始就不支援 SQL-92 寫法，*= 和 =* 就無法使用，雖然該例子寫法還可以用，但也不建議使用了，參考資料 - Using Outer Joins

 

Point3：TOP (2147483647) 搭配 Order By

where customerid in 
(
	select top (2147483647) 
		customerid 
	from
		....		
	order by customerid
)

想在子查詢或 View 內指定資料排序，就會看見 TOP 搭配 Order By 語法，以前都是看見 TOP 100 Percent 搭配 Order By，這比較特別是指定數字，實務上真的要確保輸出資料排序，要在最外層下 Order By 來指定排序，延伸閱讀 - [SQL] Order By 排序

Point4：避免對欄位進行函數轉換

and datediff(day, oh.orderdate, convert(DATE,getdate())) <= 14

對欄位進行欄位轉換會導致 OrderDate 欄位索引失效，延伸閱讀 - [SQL] WHERE 中的資料篩選

Point5：指定欄位順序來進行排序

select customerid, firstname, emailaddress 
from
(
	.....
) x 
order by 1

排序除了指定 select 欄位名稱外，還可以透過指定欄位順序來指定，以該範例來說就是以 CustomerID 來排序，但這種寫法最大缺點就是改變 select 內欄位順序的話，沒有意識到排序也得改的話，排序結果就會不如預期，之前大多是在查詢系統相關或 DMV 語法內會看見，實務上還真的沒有遇上有人使用，另一方面知道該用法的人也少就是

 

Point6：篩選條件位置

where customerid in 
(
	select
    	.....
	from sales.salesorderheader oh (nolock)
		join sales.salesorderdetail od (nolock) on oh.salesorderid = od.salesorderid
													and datediff(day, oh.orderdate, convert(DATE,getdate())) <= 14
		join production.product pr (nolock) on od.productid = pr.productid
		inner join production.productsubcategory AS sc (nolock) on pr.productsubcategoryid = sc.productsubcategoryid
																	and sc.productcategoryid = @paramIntCategoryId 
		.....
)

Join On 條件內，個人習慣會是 Table 關聯條件，以該例子來說，日期和類別這兩個篩選會放在 where 條件內，通常是 Left Join 情況下，會影響資料篩選時間點才會把篩選條件放在 On 上，延伸閱讀 - [SQL] T-SQL 語法邏輯順序

Point 7：該語法改善想法

select customerid, firstname, emailaddress 
from
(
	-- last placed an order more than a year ago
	select
		.....
	from sales.customer a (nolock), 
		person.person b(nolock), 
		person.emailaddress c(nolock) 
	where personid = b.businessentityid 
		and b.businessentityid = c.businessentityid 
		.....
	union
	-- placed at least 3 orders in the past year
	select
		.....
	from person.person p (nolock) 
		join person.emailaddress e (nolock) on p.businessentityid = e.businessentityid
		join sales.customer c (nolock) on personid = p.businessentityid
		.....
	union
	-- ordered within specified category in past two weeks
	select
		.....	
	from person.emailaddress em (nolock) 
		join person.person pp with (nolock) on em.businessentityid = pp.businessentityid
		join sales.customer cu (nolock) on personid = pp.businessentityid
		.....
) 

Sales.Customer、Person.Person、Person.EmailAddress 這三個 Table 在三段邏輯內重覆出現，假如可以把篩選邏輯拆分出，這三個 Table 就只需要各讀取一次就行，但只有想法並沒有實際去改就是

以上七點是個人想法，作者的 40 個觀點請至文章內閱讀，文章內還有改善後的 TSQL 語法可以參考喔