Browse by Tags

T-SQL使用逗號分隔字串當作WHERE IN條件

寫Stored Procedure時有一個麻煩情境是由外界傳入參數當作WHERE IN條件，由於參數數量不定，難以事先寫成WHERE … IN (@val1, @val2, @val3)，開發者往往會走上用傳入參數組裝SQL指令的險路，稍有不慎就搞出SQL Injection，導致難以想像的災難。（是的，SQL Injection不只會出現在ASP/ASP.NET/PHP/Java/C++，也可能藏在Stored Procedure裡) 最常見的例子是開放使用者勾選一個到多個類別作為篩選條件，例如使用者選取了...

Posted 13 March 2016 10:16 PM 由 Jeffrey | 4 comment(s) 18,801

Filed under: SQL

將複合字串拆成多欄位－以ORACLE及SQL為例

先說說我的需求。某資料表使用複合欄位當Primary Key，例如：由OrgId、DeptId、UserId三欄組成唯一鍵值。當要查詢特定資料，理論上應寫成WHERE OrgId='…' AND DeptId = '…' AND UserId = '…'。為求簡便，在.NET程式端以及某些資料表我發明了一種複合代碼字串"OrgId-DeptId-UserId"，只用一個參數或一個欄位就搞定關聯，省時又省力。不過，每個做法總有黑暗面...

Posted 09 March 2016 06:32 AM 由 Jeffrey | 5 comment(s) 51,711

Filed under: ORACLE, SQL

GUID Primary Key資料庫避雷守則

【聲明】該不該用GUID當Primary Key是可以讓開發人員大戰三百回合的好題材，由標題可知我屬於GUID陣營，這篇文章不打算花時間論證該不該用GUID PK，假設讀者已接受使用GUID當PK，只聚焦如何避免GUID PK導致資料庫效能悲劇。 故事源起MVP James最近寫的幾篇 GUID鬼故事 （包含一起寫入資料3秒變40秒的 案例 ，也實證了GUID作為叢集索引造成 索引破碎現象 ，值得一看），讓我有所警覺，身為一個偏好GUID Primary Key的開發者，有必要正視這個問題，避免掉進資料庫效能陷阱...

Posted 29 January 2016 07:16 AM 由 Jeffrey | 19 comment(s) 69,021

Filed under: SQL

欄位合併效能比較：CTE vs FOR XML

前一篇文章 介紹用CTE實現SQL一對多關聯欄位合併的方法，找資料期間曾發現另一個替代做法，利用SQL Server的FOR XML PATH('')技巧，可將多筆資料轉成單一字串（ 參考 ），看來也相當簡潔。但當下覺得子查詢加FOR XML轉換的做法有效能疑慮，理應不如CTE（事實不然！），加上搞CTE比較有挑戰性，故選了CTE研究嚐鮮。 網友 ChoeChin 留言提到FOR XML寫法，讓我重新思考：如果要大量重度應用，效能議題不可忽視，CTE效能是否真的比FOR XML方法好...

Posted 21 January 2016 08:46 PM 由 Jeffrey | 7 comment(s) 34,280

Filed under: SQL

CTE應用－將多筆查詢結果合併成逗號分隔字串

一對多關聯是常見的資料庫應用情境，有時我會遇到將多筆關聯資料特定欄立合併成逗號分隔字串的需求。聽起來有點抽象，用個實例說明。 假設有兩個資料表，應用系統清單及負責該系統的工程師名字，用以下指令建立模擬資料： CREATE TABLE AppSystem ( Name VARCHAR (16), CodeName VARCHAR (16) ) INSERT INTO AppSystem VALUES ( 'HR' , 'Mars' ); INSERT INTO AppSystem...

Posted 19 January 2016 06:36 AM 由 Jeffrey | 5 comment(s) 23,281

Filed under: SQL

【茶包射手日記】SqlException Login failed for user '<Domain>\<Computer>$'.

在ASP.NET專案使用"Data Source=(local);Integrated Security=SSPI;Initial Catalog=MyDB"以Windows驗證連線本機SQL Server，Visual Studio測試正常，搬到IIS後出現以下錯誤： System.Data.SqlClient.SqlException: Login failed for user '<Domain>\<Computer>$'. Visual...

Posted 24 September 2015 10:13 PM 由 Jeffrey | with no comments 4,295

Filed under: ASP.NET, Trouble-Shooting, SQL

SQL筆記：再談動態WHERE條件

前一篇文章 探討了「WHERE 1＝1動態查詢條件組裝」的效能問題，並介紹如何利用C#語言特性簡單寫出沒有多餘WHERE 1=1的馬甲線SQL指令。而在前文提到的Code Review會議，還有一招不需要組裝WHERE指令的做法也被提及。 //REF: http://goo.gl/SBF1Wi by 91 /// <summary> /// 當資料物件為null時傳回DBNull.Value /// </summary> /// <param name="obj"><...

Posted 18 August 2015 08:53 AM 由 Jeffrey | 1 comment(s) 15,267

Filed under: Performance, SQL

SQL筆記：WHERE 1=1會拖累效能嗎？

稱不上DB咖的我，反常地連寫兩篇SQL筆記，其實都是研究「動態產生SQL查詢條件」議題的副產品，這篇才算步入正題，鴨架子湯先來兩碗，烤鴨才上桌，哈！但這樣安排是對的，以下探討有一部分需要先前筆記的基礎才好聊下去。 兩週前，參加一場Code Review討論，會中大家剛好聊到「動態產生SQL查詢條件」這檔事兒。它的情境是：使用者在操作介面上有多項條件選擇，例如：日期、類別、關鍵字，每個條件使用者可選擇輸入或不輸入（不輸入代表不限定）。從程式的角度，使用者依輸入條件不同，可能形成以下幾種SQL查詢條件...

Posted 17 August 2015 07:37 AM 由 Jeffrey | with no comments 24,736

Filed under: Performance, SQL

SQL筆記：Literal, Variable與Parameter

繼續研究不同SQL寫法對執行計劃的影響。 如果大家讀過 上一篇筆記 ，就會知道以下兩則查詢將使用不同的執行計劃，前者走Clustered Index Scan，後者則是Index Seek + Key Lookup。 SELECT ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE ProductID = 870 --4688筆 SELECT ProductID, OrderQty FROM Sales.SalesOrderDetail WHERE...

Posted 16 August 2015 05:13 PM 由 Jeffrey | with no comments 9,367

Filed under: Performance, SQL

SQL筆記：Index Scan vs Index Seek

寫這篇筆記是因為前陣子在實驗SQL查詢效能，發現自己對於「相似的SQL查詢有時使用Index Scan，有時又選擇Index Seek」的行為有些迷惑，決定花點時間重新認識這幾個基本資料庫概念。（對於SQL我只有玩票的水準，內容如有謬誤，敬請指正） 當我們對資料庫下達SELECT … WHERE …指令，資料庫引擎必須對指令進行分析，找出最有效率的方法儘快查到資料算出結果，而這個「找到資料算出結果的方法」就是所謂的執行計劃（Execution Plan）。要從資料表找到指定的資料，有很多做法，最笨的方法就是整個資料表每一筆都翻出來檢查...

Posted 15 August 2015 12:55 PM 由 Jeffrey | 2 comment(s) 14,501

Filed under: SQL

搜尋