[EF] WinForm DataBinding - Edm

該篇 [EF] WinForm DataBinding 的延伸，利用 Edm 來實作看看，Code 都是一致，比較特別的是下述這段 ObservableListSource 取代 ICollection 和 HashSet 的 MSDN 說明

找到 edm 內的該 tt 檔案，並執行文字說明的取代步驟

• 尋找和取代出現兩次的 「ICollection"with"ObservableListSource"。 這些是位在大約行 296 和 484。

• 尋找和取代第一個出現的 「HashSet"with"ObservableListSource"。 這項問題位於大約第 50 行。 不這麼做取代 HashSet 稍後在程式碼中找到的第二個執行個體。

取代結束後儲存，Categories 內的  ICollection 和 HashSet 就會被 ObservableListSource 取代

突發奇想測試一下，用 ICollection 透過 BindingSource 來對 DataGridView 作 DataBinding 會發生甚麼事情，結果如下圖

[EF] WinForm DataBinding - 手動設定 BindingSource

該篇 - [EF] WinForm DataBinding 的延伸，主要是把 BindingSource 設定，用 Code 來完成，不要用精靈來拖拉，在 bsProducts 卡關，忘記是要透過 Categories.Products 導覽屬性來找出資料，直接把 Products 塞進 bsProducts 內，造成 bsCategories 移動資料時，bsProduct 內不會作動

using System.Data.Entity;
using WinFormswithEFSample.Models;

namespace WinFormswithEFSample
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private DataBindingContext _context;

        private void Form2_Load(object sender, EventArgs e)
        {
            _context = new DataBindingContext();
            _context.Categories.Load();
            bsCategories.DataSource = _context.Categories.Local.ToBindingList();

            // bsProduct 的資料來源是 bsCategories 且 DataMember 要設定 Products Property
            bsProducts.DataSource = bsCategories;
            bsProducts.DataMember = "Products";

            dgvCategories.AutoGenerateColumns = false;
            dgvProducts.AutoGenerateColumns = false;

            bindingNavigator1.BindingSource = bsCategories;
            dgvCategories.DataSource = bsCategories;
            dgvProducts.DataSource = bsProducts;
        }
    }
}

[VFP] Automation 應用 - 改變 Sheet 標籤背景顏色

透過錄製巨集發現原來設定 Excel Sheet 標籤和 Excel Cell 背景顏色在語法上有差異

Excel Sheet 標籤背景顏色主要有標準色彩和佈景主題色彩兩種設定方式

// ColorIndex 設定
loExcel.ActiveSheet.Tab.ColorIndex = lnColorIndex

// 標準色彩
loExcel.ActiveSheet.Tab.Color = lnColor
loExcel.ActiveSheet.Tab.TintAndShade = 0

// 佈景主題色彩
loExcel.ActiveSheet.Tab.ThemeColor = lnThemeColor
loExcel.ActiveSheet.Tab.TintAndShade = lnValue

• ColorIndex 顏色可以參考該篇筆記 - [VFP] Automation 應用 - 改變 Excel 儲存格顏色
• Color 除了透過錄製巨集來觀察參數外，可以直接輸入 RGB 來設定
• ThemeColor 設定通常搭配 TintAndShade，會先透過錄製巨集來觀察參數，再參考 MSDN 文章 - xlThemeColor 來找出參數對應值

設定結果

延伸閱讀
• [VFP] Automation 在 Excel 上的應用
• [VFP] Automation 應用 - 改變 Excel 儲存格顏色

參考資料
• Tab.TintAndShade property

[VFP] 自訂報表

在弄連續報表紙支票套表列印時發現，預覽時紙張設定竟然都會跑掉，以往都是把報表檔案的 Expr 欄位清空就沒問題，自訂報表會依據印表機內自訂紙張來跑，沒想到踢到鐵板，Orz

測了一段時間後，突然有個印象中，VFP 大神有提過除了 Expr 外還會清空報表檔案內的 Tag、Tag2 這兩個欄位資訊，就在要進行測試時，同事也跑來提醒 Tag、Tag2 有沒有手動去清除，果然清除後，報表就正常啦

在 Win10 64bit + VFP 9.0 SP2 的環境下，打開報表檔案的 Tag、Tag2 欄位，都會當掉，是在 [命令] 內直接下 replace 語法來清空

報表檔案內的 Expr、Tag、Tag2 三個欄位都要清空

延伸閱讀
• [VFP] 自訂標籤

參考資料
• 可以依每台電腦的印表機環境來設定報表印成半張紙格式
• 報表格式設定(中一刀設定)

[VFP] C0000005 錯誤訊息

遇上 VFP 最讓人討厭的 C0000005 錯誤，好家在有迅速破案，不過在測試階段正常，上線後一段時間才跳出來，也是頗讓人討厭，Orz

原 VFP 寫法

SELECT 
    S.NOs , 
    D.* ;
FROM curSequence AS S 
    LEFT JOIN BonusAccDetail AS D With (Buffering = .T.) ON ALLTRIM(S.BonusTitle) == ALLTRIM(D.BonusTitle) AND 
                                                            Alltrim(D.CompNO) == lcCompNO AND 
                                                            Alltrim(D.EmpNO) == lcEmpNO AND 
                                                            ALLTRIM(AccWay) == lcAccWay ;
    INTO Cursor curEmployData

修正為，先把 BonusAccDetail 所需資料抓進暫存 Cursor，之後再去跟 curSequence cursor 合併

SELECT * FROM BonusAccDetail With (Buffering = .T.) ;
    WHERE Alltrim(CompNO) == lcCompNO AND 
          Alltrim(EmpNO) == lcEmpNO And 
          Alltrim(AccWay) == lcAccWay ;
    INTO Cursor curBonusAccDetailTemp

Select S.NOs , D.* ;
    FROM curSequence As S Left Join curBonusAccDetailTemp As D On Alltrim(S.BonusTitle) == Alltrim(D.BonusTitle) ;
    INTO Cursor curEmployData

Use In Select("curBonusAccDetailTemp")

透過上述修改就避開 C0000005 的錯誤出現

參考資料
• 嚴重錯誤:例外程式碼=C000005經驗分享
• 古金楠【小聊VFP9與致命錯誤C0000005】
• 解决C0000005错误的几则方法
• VFP 論壇討論

[SQL] 存入看不見的控制字元

使用者跑來告知，為什麼已輸入的資料，竟然系統搜尋功能竟然都找不到

把資料拿來測試也覺得很神奇，同樣的條件，竟然一個找的到，一個找不到

把兩個搜尋條件轉成 varbinary 來觀察，就發現問題點

0x41 是 ASCII A 沒有問題，查 0x0341 是一個 unicdoe 符號，詳見該資料，但問題是該欄位資料型態是 char，又不是 nchar 或 nvarchar，對於編碼議題不熟，感覺這又不是 root casue，只能請使用者刪除後重新 key 資料，結案。

原想把上述測試結果記錄在 OneNote 時，TSQL 語法複製進去，兇手就現形啦，A 前面有一個隱藏字元

把該隱藏字元單獨複製進 SSMS 內轉 varbinary 來觀察，是 End of Text 符號，Orz

參考資料
• [SQL]奇怪的 Unicode
• 控制字元

[EF] WinForm DataBinding

參考這篇 MSDN 文章 - 資料繫結與 WinForm 的練習筆記

IListSource 實作集合

• 這個類別會啟用雙向資料繫結，以及排序。
• 類別衍生自 ObservableCollection<T>並新增明確的 IListSource 實作。
• IListSource getlist() 方法實作傳回 IBindingList 實作，它與 ObservableCollection 保持同步。
• 產生 ToBindingList IBindingList 實作支援排序。
• EntityFramework 組件中定義 ToBindingList 擴充方法。

// ObservableCollection<T> 所在 namespace
using System.Collections.ObjectModel;
// IListSource 所在 namespace
using System.ComponentModel;
// IList 所在 namespace
using System.Collections;
// EntityFramework 組件中定義 ToBindingList 擴充方法
using System.Data.Entity;

namespace WinFormswithEFSample
{
    public class ObservableListSource<T> : ObservableCollection<T>, IListSource
        where T : class
    {
        private IBindingList _bindingList;

        public bool ContainsListCollection
        {
            get { return false; }
        }

        public IList GetList()
        {
            return _bindingList ?? (_bindingList = this.ToBindingList());
        }
    }
}

[LINQ] GroupJoin

根據 MSDN 文章 - 執行左方外部聯結 的 GroupJoin 練習

namespace GroupJoinSample
{
    class Program
    {
        static void Main(string[] args)
        {
            var Data = DemoHelper.GetData();
            var People = Data.People;
            var Pets = Data.Pets;
            
            var LINQResult = People
                .GroupJoin(
                    Pets,
                    pe => pe,
                    pt => pt.Owner,
                    (personArg, petArg) => new { PersonProp = personArg, PetsProp = petArg });

            Console.WriteLine("----- LINQ GroupJoin 結果 -----");
            foreach (var person in LINQResult)
            {
                Console.WriteLine($"飼主：{person.PersonProp.FirstName} 有 {person.PetsProp.Count()} 隻寵物");

                foreach (var pet in person.PetsProp)
                {
                    Console.WriteLine($"--- 寵物名稱：{pet.Name}");
                }

                Console.WriteLine("");
            }

            Console.WriteLine("----- TSQL Left Join 結果 -----");

            var TSQLResult = People
                .GroupJoin(
                    Pets,
                    pe => pe,
                    pt => pt.Owner,
                    (personArg, petArg) => new { PersonProp = personArg, PetsProp = petArg })
                .SelectMany(
                    p => p.PetsProp.DefaultIfEmpty(), 
                    (pe, pt) => new {
                                        OwnerName = $"{pe.PersonProp.FirstName}",
                                        // 傳統寫法
                                        // PetName = pt == null ? "無寵物" : pt.Name
                                        // 語法糖寫法
                                        PetName = pt?.Name ?? "無寵物"
                                    });

            Console.WriteLine("飼主_寵物名字");
            foreach (var Record in TSQLResult)
            {
                Console.WriteLine(Record.OwnerName + "_" + Record.PetName);
            }

        }
    }

    public class DemoHelper
    {
        public static (List<Person> People, List<Pet> Pets) GetData()
        {
            Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
            Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

            Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
            Pet barley = new Pet { Name = "Barley", Owner = terry };
            Pet boots = new Pet { Name = "Boots", Owner = terry };
            Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };

            Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
            Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };

            // 該員沒有飼養寵物
            Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

            List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
            List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

            return (people, pets);
        }
    }

    public class Person
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public class Pet
    {
        public string Name { get; set; }
        public Person Owner { get; set; }
    }
}

參考資料
• Enumerable.GroupJoin Method
• 執行群組聯結 - 文章內有直接產生 XML 的 LINQ 作法
• 執行左方外部聯結
• [C#.NET][LINQ] Inner Join & Left Outer Join

[LINQ] SelectMany 應用 - Cross Join

利用 SelectMany 來達成 TSQL Cross Join 效果

namespace CrossJoinSample
{
    class Program
    {
        static void Main(string[] args)
        {
            string[] Departments = { "研發" , "會計" , "人事" , "資訊" };
            string[] Employees = { "張三" , "李四" , "王五" };

            var result = Departments.SelectMany(
                e => Employees, 
                (d, e) => new {
                    Department = d ,
                    Employees = e
                });

            foreach (var item in result)
            {
                Console.WriteLine($"{item.Department} - {item.Employees}");
            }
        }
    }
}

延伸閱讀
• [LINQ] SelectMany

參考資料
• SelectMany (cross join)

[SQL] 分析函數應用 - 統計群組執行時間

在該 FB 社群內的問題，拿來練習視窗函數，問題為

在限定時間內，要統計每個群組起訖時間差，每一筆的下一筆為該資料的結束時間

原 PO 提供的測試資料

typeA 2018/09/01 12:00
typeB 2018/09/01 12:03
typeC 2018/09/01 12:08
typeB 2018/09/01 12:15
typeC 2018/09/01 12:22
typeA 2018/09/01 12:28

原 PO 提供的資料分析

要TypeA,TypeB,TypeC的累計時間噢~
TypeA:(5分鐘)
12:00~12:03=>3分鐘
12:28~12:30=>2分鐘
TypeB:(12分鐘)
12:03~12:08=>5分鐘
12:15~12:22=>7分鐘
TypeC:(13分鐘)
12:08~12:15=>7分鐘
12:22~12:28=>6分鐘

TSQL

-- 製作資料
DECLARE @Temp Table (TypeName char(5) , DataTime datetime)
INSERT INTO @Temp 
SELECT 'typeA' , '2018/09/01 12:00' UNION ALL
SELECT 'typeB' , '2018/09/01 12:03' UNION ALL
SELECT 'typeC' , '2018/09/01 12:08' UNION ALL
SELECT 'typeB' , '2018/09/01 12:15' UNION ALL
SELECT 'typeC' , '2018/09/01 12:22' UNION ALL
SELECT 'typeA' , '2018/09/01 12:28'

-- 實際執行
SELECT
  T.TypeName , 
  SUM(DATEDIFF(mi , StartTime , EndTime))
FROM
  (
    SELECT 
      TypeName ,
      DataTime AS StartTime ,
      -- 利用視窗函數 LEAD 來取得下一筆資料時間，為該筆資料結束時間
      -- 沒有下一筆資料的話，預設以 2018/09/01 12:30 為結束時間
      LEAD(DataTime, 1, '2018/09/01 12:30') OVER (ORDER BY DataTime) AS EndTime
    FROM @Temp
    WHERE DataTime BETWEEN '2018/09/01 12:00' AND '2018/09/01 12:30'
  ) AS T
GROUP BY T.TypeName

每筆資料起訖時間結果

最終群組統計結果

參考資料
• 如何計算各分類佔用分鐘數

[LINQ] SelectMany

筆記利用 SelectMany() 來產生類似 TSQL JOIN 效果

namespace SelectManySample
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("===== MSDN 寫法 =====");

            var msdn = DemoHelper.GetDate()
                .SelectMany(
                    OwnerPet => OwnerPet.Pets,
                    (petOwner, petName) => new { petOwner, petName })
                .Where(w => w.petName.StartsWith("S"))
                .Select(ownerAndPet => new Result
                {
                    PetOwner = ownerAndPet.petOwner.Name,
                    PetName = ownerAndPet.petName
                });

            DemoHelper.ShowData(msdn);

            Console.WriteLine("===== 巢狀寫法 =====");

            var Nested = DemoHelper.GetDate()
                .SelectMany(p => p.Pets.Select(Pet => new Result { PetOwner = p.Name, PetName = Pet }))
                .Where(w => w.PetName.StartsWith("S"));

            DemoHelper.ShowData(Nested);
        }
    }

    public class PetOwner
    {
        public string Name { get; set; }
        public List<string> Pets { get; set; }
    }

    public class Result
    {
        public string PetOwner { get; set; }
        public string PetName { get; set; }

        public override string ToString()
        {
            return $"PetOwner：{PetOwner} - PetName：{PetName}";
        }
    }

    public static class DemoHelper
    {
        public static List<PetOwner> GetDate()
        {
            return new List<PetOwner>() {
                new PetOwner { Name="Higa",Pets = new List<string>{ "Scruffy", "Sam" } },
                new PetOwner { Name="Ashkenazi",Pets = new List<string>{ "Walker", "Sugar" } },
                new PetOwner { Name="Price",Pets = new List<string>{ "Scratches", "Diesel" } },
                new PetOwner { Name="Hines",Pets = new List<string>{ "Dusty" } } };
        }

        public static void ShowData(IEnumerable<Result> Data)
        {
            foreach (var item in Data)
            {
                Console.WriteLine(item);
            }
        }
    }
}

MSDN SelectMany 說明

SelectMany<TSource,TCollection,TResult>(IEnumerable<TSource>, Func<TSource,IEnumerable<TCollection>>, Func<TSource,TCollection,TResult>)

簡單的用兩張圖片來輔助說明，方便了解

參考資料
• Enumerable.SelectMany

[SQL] 分頁 - TOP

紀錄在 SQL2000 上要做到分頁效果

使用 TOP 語法來達到

USE [AdventureWorks2016]
GO

CREATE PROCEDURE uspPagingByTop (@PageIndex int , @PageRow int )
AS
  BEGIN
    DECLARE @Query nvarchar(800) 
    SET @Query=
    '
    SELECT TOP ' + CONVERT(NVARCHAR(3),@PageRow)+' *
    FROM Sales.SalesOrderHeader
    WHERE SalesOrderID NOT IN
    (
      SELECT TOP ' + CONVERT(NVARCHAR(3), @PageRow * (@PageIndex-1) ) + ' SalesOrderID
      FROM Sales.SalesOrderHeader
      ORDER BY SalesOrderID
    )
    ORDER BY SalesOrderID'

    EXEC (@Query)
  END

執行 Store Procedure 要取出 第 2 頁，每頁 10 筆資料的分頁資料

EXEC uspPagingByTop 2 , 10

左結果為分頁結果、右結果為直接 Select 全部資料，搭配 SSMS 的資料編號來閱讀

參考資料
• 在 SQL Server 實做分頁的 T-SQL 語法
• 真正高效的SQLSERVER分页查询

[SQL] Check 條件約束

最近因為無法確認 AP 端問題在哪，導致必須在 SQL Server 上建立條件約束來避免異常資料存進來，就順道整理條件約束相關內容

• 建立 Check 並測試

USE tempdb
GO

-- 判斷資料表是否存在
DROP TABLE IF Exists Temp

-- 建立 Temp Table
CREATE TABLE Temp (StartDate Date , EndDate Date)

-- 假設該資料表和欄位已存在，要加上 Check 條件約束
ALTER TABLE Temp
ADD CONSTRAINT CK_DateCompare CHECK (StartDate < EndDate ); 

-- 建立錯誤資料
INSERT INTO Temp (StartDate , EndDate) VALUES('20180919','20180901')

INSERT 陳述式與 CHECK 條件約束 "CK_DateCompare" 衝突。衝突發生在資料庫 "tempdb"，資料表 "dbo.Temp"。

• 修改 Check

無法直接利用 TSQL 語法修改 Check 條件約束，必須先刪除再新增

-- 刪除 Check 條件
ALTER TABLE Temp
DROP CONSTRAINT CK_DateCompare 

-- 再建立新的條件約束
ALTER TABLE Temp
ADD CONSTRAINT CK_DateCompare CHECK (StartDate <= EndDate); 

• 停用、啟用 Check

-- 使用 INSERT 與 UPDATE 陳述式停用檢查條件約束
ALTER TABLE Temp NOCHECK CONSTRAINT CK_DateCompare

--

-- 啟用:
-- 於 INSERT 或 UPDATE 時強制套用：是
-- 檢查建立或重新啟用時的現有資料：否
ALTER TABLE Temp CHECK CONSTRAINT CK_DateCompare

-- 啟用:
-- 於 INSERT 或 UPDATE 時強制套用：是
-- 檢查建立或重新啟用時的現有資料：是
ALTER TABLE Temp WITH CHECK CHECK CONSTRAINT CK_DateCompare

• 利用 TSQL 查詢條件約束

SELECT 
  s.[name] AS ScheamName ,
  o.[name] AS TableName ,
  i.[name] AS CheckName ,
  i.definition ,
  i.type_desc ,
  i.create_date ,
  i.modify_date ,
  i.is_disabled ,
  i.is_not_for_replication ,
  i.is_not_trusted
FROM sys.check_constraints i
  INNER JOIN sys.objects o ON i.parent_object_id = o.[object_id]
  INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
GO

參考資料
• BS 討論
• 建立檢查條件約束
• 修改檢查條件約束
• 刪除檢查條件約束
• 使用 INSERT 與 UPDATE 陳述式停用檢查條件約束
• sys.check_constraints
  
• Foreign Keys or Check Constraints Not Trusted
  

[LINQ] ToDictionary()

對 Dictionary 的認知就是 key、value 組合，所以使用 ToDictionary() 時，很直覺會是需要輸入 key、value，看 MSDN 時發現原來有只輸入 key 的 overload 可以使用，value 會直接是該物件型別

namespace ConsoleApp2
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("--------- 個人原寫法 ---------");
            Dictionary<long, Package> jt = DemoHelper.GetData()
                .ToDictionary(k => k.TrackingNumber, v => v);
            DemoHelper.ShowData(jt);

            Console.WriteLine("--------- ToDictionary() 多載 ---------");
            Dictionary<long, Package> msdn = DemoHelper.GetData()
                .ToDictionary(k => k.TrackingNumber);
            DemoHelper.ShowData(msdn);

        }
    }

    class Package
    {
        public string Company { get; set; }
        public double Weight { get; set; }
        public long TrackingNumber { get; set; }

        public override string ToString()
        {
            return $"{TrackingNumber} ,{Company}, {Weight}";
        }
    }

    static class DemoHelper
    {
        public static List<Package> GetData()
        {
            return
                new List<Package>{
                    new Package { Company = "Coho Vineyard", Weight = 25.2, TrackingNumber = 89453312L },
                    new Package { Company = "Lucerne Publishing", Weight = 18.7, TrackingNumber = 89112755L },
                    new Package { Company = "Wingtip Toys", Weight = 6.0, TrackingNumber = 299456122L },
                    new Package { Company = "Adventure Works", Weight = 33.8, TrackingNumber = 4665518773L } };
        }

        public static void ShowData(Dictionary<long, Package> Data)
        {
            foreach (var item in Data)
            {
                Console.WriteLine(item.ToString());
            }
        }
    }
}

參考資料
• ToDictionary()

[SSRS] 透過 ReportViewer 變更文字字型

論壇問題，基本上一開始誤會意思，不過既然都練習了，那就筆記一下囉，:p

練習目標是透過 ReportViewer 傳入指定字型來變更報表文字字型

[SQL] 避免在 Select 中使用 Scalar Function

避免在 Select 中使用 Scalar Function，原因在於 Query Optimizer 無法正確解析，從執行計畫、Statistics IO 內無法看出相關資源的實際使用情況，必須透過 SQL Profile 來確認實際的資源使用

Microsoft SQL Server Performance Tuning 效能調校 P551 說明

使用函數另一個要注意地方是執行計畫不會累加該函數呼叫的次數，進而算出該函數的資源比重。所以透過執行計畫呈現呼叫自定函數的 T-SQL 語法，自定函數的執行計畫耗用資源相對於整句語法可能比例很小，但其實像這樣被呼叫多次，累積的結果才是最耗資源的地方

利用計算採購單總金額來驗證

建立 getTotalPrice() 使用者自訂函數，來統計採購單內容各品項總金額 (單價 X 數量)

CREATE FUNCTION getTotalPrice(@PurNO char(11)) 
RETURNS money
BEGIN
 
  DECLARE @TotalPrice as money 

  SELECT
    @TotalPrice = SUM(ROUND(PurQty * Price , 0))
  FROM PurchDetail
  WHERE PurNO = @PurNO

  RETURN @TotalPrice
END
GO

實際執行

SET STATISTICS IO , TIME ON

SELECT 
  PurNO , 
  dbo.getTotalPrice(PurNO) AS TotalPrice
FROM Purch
WHERE PurDate BETWEEN '20180101' AND '20180630'

執行計畫中，getTotalPrice() 只被執行一次，為下圖中的 [計算存量 operator]，且沒有看見 PurchDaetail Table 出現在執行計劃內

從 operator 屬性資訊來確認 Expr1002 是 getTotalPrice()

Statistics 相關資訊
• Purch Table Logical Reade = 7
• CPU Time = 93 ms

利用 SQL Profile 的 Turning 範本來觀察 getTotalPrice() 實際運作

指定資料行，需要 TextData、Duration、Reads 這三個欄位資訊

指定特定 DB，可以較明確收集到要的資訊

執行 TSQL 語法，並從 SQL Profile 側錄結果就可以發現，getTotalPrice() 被執行多次，在執行計畫中只有一次，且 Duration 和 Reads 都明顯高於 Statistics IO 的數據

使用資源比較

	Statistics	SQL Profile
Logical Read	7	7,410
CPU Time	93ms	225ms

利用 SentryOne Plan Explorer 來觀察，可以發現 operator 上有 Warnings，明確指出使用自訂函數的效能副作用

• 延伸閱讀
• [SQL] 使用者自訂函數
• 參考資料
• Microsoft SQL Server Performance Tuning 效能調校
• T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)、(part 2)
• 效能調校-案例探討
• IT 邦討論

[SQL] 利用 STRING_SPLIT() 拆解特定符號隔離字串資料

在這篇文章 [SQL] 特定符號隔離字串資料 內是利用 CTE 來拆解資料，SQL Server 2016 新函數 - STRING_SPLIT() 可以更快速、簡潔達到相同效果

MSDN 回傳值說明

傳回有片段的單一資料行資料表。 資料行的名稱是 value。 如果任何輸入引數是 nvarchar 或 nchar，則傳回 nvarchar。 否則傳回 varchar。 傳回類型的長度與字串引數的長度相同。

DECLARE @Temp TABLE (PostTitle char(100),Keyword char(100))
INSERT INTO @Temp VALUES('[SQL] 模擬死結產生','SQL,DealLock')
INSERT INTO @Temp VALUES('[Windows] Win7 和 內碼輸入法','Windows 7,內碼,輸入法')
INSERT INTO @Temp VALUES('阿朗壹古道','CYMC,Hiking')
INSERT INTO @Temp VALUES('威力導演 9 - 直式相片模糊','威力導演,模糊,Video')
INSERT INTO @Temp VALUES('馬桶漏水','居家生活,馬桶,乾式安裝,漏水,HCG,和成')

SELECT 
  T.PostTitle ,
  SS.[value] AS String
FROM @Temp AS T
  CROSS APPLY STRING_SPLIT(T.Keyword, ',') AS SS
WHERE RTRIM(SS.[value]) <> ''

結果

MSDN 重點：排序

輸出資料列可能為任何順序。 子字串的順序「不」 保證與輸入字串的相同。 您可以在 SELECT 語句上使用 ORDER BY 子句來覆寫最後的排序次序，例如 ORDER BY value 或 ORDER BY ordinal 。

MSDN 重點：空字串

當輸入字串包含兩個或更多個連續出現的分隔符號字元時，會出現長度為零的空白子字串。 空白子字串視為純文字子字串來處理。 例如，您可以使用 WHERE 子句來篩選出包含空白子字串的任何資料列 WHERE value <> '' 。 如果輸入字串為 NULL ，STRING_SPLIT 資料表值函數會傳回空的資料表。

• 參考資料
• STRING_SPLIT
• CSV String (欄位值) 2 Row (Table)
  
• Should You Use SQL Server 2022’s STRING_SPLIT?

[SQL] 篩選索引 - 條件唯一

公司感應卡是離職後繳回，感應卡沒有故障的話，後續到職員工可以繼續使用，隨著時間增加，員工感應卡資料內會有一張感應卡出現在多位員工使用過的資料，但可以肯定的是，現職員工使用感應卡，一定是唯一的，要避免 AP 端沒有寫好，產生出勤資料異常，因此透過篩選索引來設定資料唯一

卡片會重覆使用，所以無法建立唯一索引

CREATE UNIQUE INDEX IX_EmployCard_Filter ON EmployCard (CardNO)

發現物件名稱 'dbo.EmployCard' 和索引名稱 'IX_EmployCard_Filter' 的重複索引鍵，CREATE UNIQUE INDEX 陳述式已結束。重複的索引鍵值為 (0005023090)。

建立 Filter Index，員工手邊正在使用的卡片必須是唯一

CREATE UNIQUE INDEX IX_EmployCard_Filter ON EmployCard (CardNO)
WHERE [Out] = 0

故意輸入違反唯一

INSERT INTO EmployCard (EmpNO , CardNO , [Out])
    VALUES('09702' , '0005117122' , 0)

無法以唯一索引 'IX_EmployCard_Filter' 在物件 'dbo.EmployCard' 中插入重複的索引鍵資料列。重複的索引鍵值是 (0005117122)。

參考資料
• Filtered Index 篩選索引

[SQL] 單一資料表使用多個索引

之前上課時，老師有提到一個 Table 只會使用到一個 Index，沒有想很多就這樣記在腦海裡，最近閱讀效能調校書籍時，提到一個 Table 會用上多個 Index，打破既有的觀念，測試結果也真的有使用多個 Index

上述範例有使用到 IX_LastName、IX_FirstName、IX_MiddleName 這三個 Index 來搜尋資料

參考資料
• Microsoft SQL Server Performance Tuning 效能調校 P450 8-2-4 單一查詢使用多個索引

[SQL] 清除指定執行計劃快取

在測試環境常常下 DBCC FREEPROCCACHE 來清除執行計畫，發現 DBCC FREEPROCCACHE 可以輸入 plan_handle 為參數來清除指定執行計畫，並不是一定都要清除全部

執行 TSQL 語法並透過 DMV 找出該執行計畫的 plan_handle

USE AdventrueWorks2016
GO

SELECT * FROM Person.Person
GO

SELECT
  cap.plan_handle ,
  cap.usecounts ,
  cap.cacheobjtype ,
  cap.objtype ,
  st.text
FROM sys.dm_exec_cached_plans AS cap
  CROSS APPLY sys.dm_exec_sql_text(cap.plan_handle) AS st
WHERE st.text NOT LIKE '%sys%'
  AND st.text LIKE N'SELECT * FROM Person.Person%'
GO

清除指定執行計畫

DBCC FREEPROCCACHE(0x06000900FB17592A9082C9015102000001000000000000000000000000000000000000000000000000000000)

參考資料
• DBCC FREEPROCCACHE
• [SQL Server]清理計畫快取中指定的執行計畫

[SQL] 避免欄位比較 - 計算欄位

在 [SQL] 避免欄位比較 - 篩選索引 這篇筆記內，是建立一個待運送 (剩餘數量) 欄位來改善，這篇筆記就改為建立 [已運輸數量欄位] 來筆記囉

建立已運輸數量欄位、更新資料並建立 IX_物流申請_已運輸數量 Index

ALTER TABLE 物流申請 ADD 已運輸數量 int NOT NULL DEFAULT(0)
GO

CREATE INDEX IX_物流申請_已運輸數量 ON 物流申請 (已運輸數量)
GO

要在已運送數量欄位形式，去找出待運送數量資訊，就只有下面兩種不符合 SARG 寫法

-- 欄位資料 運算子 欄位資料
SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

-- 對欄位進行運算
SELECT * FROM 物流申請 WHERE 申請運輸數量 - 已運輸數量 > 0

觀察執行計劃就會發現，上述兩種寫法，因為違反 SARG，所以根本就不會使用 IX_物流申請_已運輸數量，通通跑 Clustered Index Scan

強制指定 Index 來看看結果

SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WITH (INDEX(IX_物流申請_已運輸數量)) WHERE 申請運輸數量 > 已運輸數量

結果當然是更慘囉

原本是要避免 [資料欄位 運算子 資料欄位]，這種違反 SARG 的設計，沒想到因為是建立並儲存 [已運送數量] 欄位，仍然是陷入同樣問題，在木已成舟情況下，要使用符合 SARG 語法並找出 [待運送數量] 資料，還能透過 [計算欄位] 來達到

建立計算欄位並建立計算 Index

ALTER TABLE 物流申請 ADD ComputeCol AS (申請運輸數量 - 已運輸數量) PERSISTED 

CREATE INDEX IX_物流單_ComputeCol ON 物流申請 (ComputeCol)

原語法和計算欄位較

SELECT * FROM 物流申請 WHERE 申請運輸數量 > 已運輸數量

SELECT * FROM 物流申請 WHERE ComputeCol > 0

公司生管派工系統，彼此之間會已完工數量來溝通，基本上不會有人跟下製程說，還剩下多少數量沒有完成，而是告知已完成數量，也因此在 Table 設計時，各製程是儲存已完工數量資訊，在判斷製程完工時也會遇上該問題，剛好整理一下，以後這類系統發生問題時，至少有個明確方向可以來改善

• 參考資料
• [SQL]利用計算欄位搭配 Index 來提升效能

[SQL] 避免欄位比較 - 篩選索引

續 [SQL] 避免欄位比較 該篇內容，確定要更改 Table Schema 後，就直接增加一個 [待運輸數量 (剩餘數量)]  欄位來儲存資料並建立 Index 提高效能

建立待運輸數量欄位並把資料更新進去

ALTER TABLE 物流申請 ADD 待運輸數量 int NOT NULL DEFAULT(0)

因為商業邏輯上是待運送數量，運送完後數量會減少，所以預期會有一堆 0 的資料，因此建立篩選索引來過濾掉 0 的資料

利用下述語法，確認資料分布情況

SELECT
  待運輸數量 ,
  COUNT(待運輸數量) AS 待運輸總數量
FROM 物流申請
GROUP BY 待運輸數量
ORDER BY 待運輸總數量 DESC

故意建立一般非叢集索引和篩選索引來比較一下

-- 建立非叢集索引
CREATE INDEX IX_物流申請_待運輸數量 ON 物流申請 (待運輸數量)

-- 建立篩選索引
CREATE INDEX IF_物流申請_待運輸數量 ON 物流申請 (待運輸數量)
WHERE 待運輸數量 > 0

指定索引來測試看看是否有差異

SET STATISTICS IO , TIME ON

-- 強制使用非叢集索引
SELECT * FROM 物流申請 WITH (INDEX(IX_物流申請_待運輸數量)) WHERE 待運輸數量 > 0

-- 強制使用篩選索引
SELECT * FROM 物流申請 WITH (INDEX(IF_物流申請_待運輸數量)) WHERE 待運輸數量 > 0

測試相關資料

	非叢集	篩選
Logical Read	92	89
執行計劃成本	0.0855	0.0855
Index 使用方式	Index Seek	Index Scan

就效能來說，是幾乎沒有提升，只好查詢一下兩個 Index 資訊，來輔助一下使用篩選索引的決定囉，至少維護索引成本是比較低的，哈

SELECT 
  T2.index_id, 
  T2.name, 
  T2.type_desc, 
  T1.reserved_page_count, 
  T1.used_page_count,
  T1.row_count,
  T2.filter_definition 
FROM sys.dm_db_partition_stats AS T1
  INNER JOIN sys.indexes T2 ON T1.[Object_ID] = T2.[Object_ID] 
                              AND T1.index_id = T2.index_id
WHERE T1.[Object_ID] = OBJECT_ID('物流申請')
  AND T2.[Name] IN ('IX_物流申請_待運輸數量','IF_物流申請_待運輸數量')

最後一步就是 AP 端也改一改，利用一個欄位來儲存資料，就可以避免欄位比較情況

• 參考資料
• [SQL SERVER][Memo]篩選索引
• [SQL SERVER][Memo]篩選索引(2)

[SQL] 避免欄位比較

閱讀 Microsoft SQL Server Performance Tuning 效能調校的 SARG 介紹時，提到 [資料欄位 運算子 資料欄位] 是不符合 SARG 原則，以往看到的資料大多是強調 [資料欄位 符合 SARG 運算子 <常數或變數>] 符合 SARG，最近調校有剛好發現很多 [資料欄位 運算子 資料欄位] 語法，不太能從 TSQL 語法變化來提升效能，所以重新閱讀書籍內容時特別有感

SARG 意義

在查詢子句中，SARG 代表用來搜尋的常數或變數可以直接與索引鍵值做比較 

SARG 運算子列表

• 符合：=、<、>、>=、<=、BETWEEN、LIKE (看 % 位置) 

• 不符合：NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN、NOT LIKE 

SARG 格式理論

• 符合：

• 資料欄位 符合 SARG 運算子 <常數或變數>

• <常數或變數> 符合 SARG 運算子 資料欄位

• 不符合：資料欄位 運算子 資料欄位

案例說明

物流申請例子來說明，商業邏輯很簡單派車單 (檔頭、檔身) 搭配物流申請，物流單申請可以存在多張多張派車單內，所以要知道待運送的物流申請資料，必須用下述語法去比對出來

SELECT
  T.*
  , D.物流單編號
  , ISNULL(D.已運輸數量, 0) AS 已運輸數量
FROM 物流申請 AS T
  LEFT JOIN 
    (
      SELECT
        物流單編號
        , SUM(已運輸數量) AS 已運輸數量
      FROM 派車檔身
      GROUP BY 物流單編號
    ) AS D ON T.物流單編號 = D.物流單編號
WHERE (D.物流單編號 IS NULL        -- 物流申請還未派車
  OR T.物流申請數量 > D.已運輸數量) -- 物流申請已經派車，但還未完全派車完

當時這樣設計看執行 Statistics 覺得很 OK、還覺得執行計劃有跑出平行處理很不錯，時過境遷後，現在看到平行處理就覺得事情不尋常，Orz

醜醜的執行計畫，物流申請和派車檔身都跑 Clustered Index Scan 來處理

有該篇 - [SQL] 利用 UNION ALL 取代 OR 條件 經驗，當然是要來嘗試一下

-- 物流申請還未派車
SELECT
  T.* ,
  0 AS 已運輸數量
FROM 物流申請 AS T
WHERE NOT EXISTS
  (
    SELECT 1 FROM 派車檔身 AS D WHERE T.物流單編號 = D.物流單編號
  )
UNION ALL
-- 物流申請已經派車，但還未完全派車完
SELECT
  T.*
  , D.已運輸數量
FROM 物流申請 AS T
  JOIN
    (
      SELECT
        物流單編號
        , SUM(已運輸數量) AS 已運輸數量
      FROM 派車檔身
      GROUP BY 物流單編號
    ) AS D ON T.物流單編號 = D.物流單編號
WHERE T.物流申請數量 > D.已運輸數量

改完之後發現更慘，Statistics 為原本兩倍左右，執行計畫也肥了不少

無法從語法獲得改善，就只能動 Table Schema 來改善囉

• 延伸閱讀
• [SQL] 避免欄位比較 - 篩選索引
• [SQL] 避免欄位比較 - 計算欄位

[SQL] 避免使用不等於

SARG 的一個原則 - 避免使用否定語法，EX：<>，調校時剛好有用上，記錄一下

[F1.工序結案數量 <> S1.派工數量] 是關鍵條件，剛好了解這條件商業邏輯，派工單各工序數量不等於派工單數量情況，換句話說就是，各工序數量小於派工數量的情況，擔心誤會還特定跟同事確認，應該不太可能會有各工序完工數量，超過派工數量的情況才對

SELECT
	..................
FROM
	..................
	JOIN [派工工序 Table] AS F1 ON D.派工單號 = F1.派工單號
			AND D.派工工序 = F1.派工工序
			AND F1.工序結案數量 <> S1.派工數量 -- 關鍵條件

條件修正

---- 原語法
F1.工序結案數量 <> S1.派工數量
----- 了解商業邏輯情況下改為
F1.工序結案數量 < S1.派工數量
----- 完全不懂商業邏輯下會改為
F1.工序結案數量 < S1.派工數量 OR F1.工序結案數量 > S1.派工數量

上述兩種改法都是可以得到相同結果，Statistics 雖然沒有甚麼變化，執行計劃成本降低不少

[SQL] 避免 CTE 遞迴產生日期總表

剛學 CTE 時，喜歡用 CTE 來跑日期總表，發現效能實在是很糟糕，後來建立一個實體 Table - DateTable，用來記錄日期資訊，這個算是漏網之魚，^^''

;
WITH CTE
AS
(
   SELECT
     CAST(@P1 AS DATETIME) AS CTEDate UNION ALL SELECT
     DATEADD(DD, 1, CTEDate)
   FROM CTE
   WHERE DATEADD(DD, 1, CTEDate) <= CAST(@P2 AS DATETIME)
)
SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM CTE AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= CTEDate
                             AND N.結案時間 < DATEADD(DD, 1, CTEDate)
          ..................
      ) AS TF
    GROUP BY ..................
  ) AS F
OPTION (MAXRECURSION 0)

Statistics 很糟糕外，執行計畫也跑出平行處理，下圖為部分截圖

修正後語法

SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM DateTable AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= T.Date
                             AND N.結案時間 < DATEADD(DD, 1, T.Date)
          ..................
        WHERE T.Date BETWEEN @P1 AND @P2
      ) AS TF
    GROUP BY ..................
  ) AS F

修正前後執行計劃成本差異

 

修正前後 Statistics 差異

	修正前	修正後
CPU Time	1,562 ms	31 ms
WorkTable Logical Read	815,419	0

[Win10] 使用小型工作列按鈕

同事來詢問，為什摩 Win10 右下角的日期時間，他的 PC 上竟然只有時間，沒有日期，如下圖

可是我的右下角有日期時間，兩個資訊都有

後來他才發現到他有調整 [使用小型工作列按鈕] 這個選項，所以才沒有顯示日期