[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 篩選索引