SQL وقواعد البيانات

#Keys و constraints وأنواع البياناتKeys, constraints and data types

validation في API لوحدها مش بتحمي الداتا من script أو request متزامنة. الـ database constraints هي آخر خط دفاع للعلاقات والقيم الفريدة.

-- SQL Server / T-SQL unless a block explicitly says PostgreSQL.
CREATE TABLE Customers (
    Id int IDENTITY PRIMARY KEY,                         -- (1)
    Name nvarchar(100) NOT NULL,
    Email nvarchar(256) NOT NULL UNIQUE
);
CREATE TABLE Orders (
    Id int IDENTITY PRIMARY KEY,
    CustomerId int NOT NULL REFERENCES Customers(Id),    -- (2)
    CreatedAt datetime2 NOT NULL DEFAULT SYSUTCDATETIME(),
    Status varchar(20) NOT NULL,
    Total decimal(18,2) NOT NULL CHECK (Total >= 0)
);
CREATE TABLE OrderLines (
    OrderId int NOT NULL REFERENCES Orders(Id),
    LineNo int NOT NULL,
    ProductId int NOT NULL,
    Quantity int NOT NULL CHECK (Quantity > 0),
    PRIMARY KEY (OrderId, LineNo)                         -- (3)
);

SQL Server unique single-column constraint يسمح عادةً بـ NULL واحدة؛ PostgreSQL افتراضياً يسمح بأكتر من NULL، وفيه NULLS NOT DISTINCT لتغيير ده. CHAR ثابت الطول، و VARCHAR متغير وبيستخدم encoding حسب collation؛ UTF-8 collations تقدر تخزن Unicode. NVARCHAR بيستخدم UTF-16؛ مش كل character بيستهلك بالضرورة 2 bytes. للفلوس استخدم decimal وحدد precision و rounding policy.

#كل الـ joins على dataset واحدةEvery join on one sample dataset

join بيركب صفوف حسب علاقة، وممكن يكرر صف الشمال لما يلاقي أكتر من match. الداتا دي staging تعليمية من غير FK: order 13 فيها customer غير موجود؛ الـ FK في التصميم اللي فوق كانت هتمنع ده.

CREATE TABLE #Customers (Id int, Name varchar(20));
CREATE TABLE #Orders (Id int, CustomerId int, Total decimal(10,2));
INSERT INTO #Customers VALUES (1,'Ali'),(2,'Mona'),(3,'Omar');
INSERT INTO #Orders VALUES (10,1,500),(11,1,200),(12,2,300),(13,99,50);

-- (1) Matched rows only.
SELECT c.Name, o.Id FROM #Customers c
INNER JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12

-- (2) Preserve the left side.
SELECT c.Name, o.Id FROM #Customers c
LEFT JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; Omar NULL

-- (3) Preserve the right side.
SELECT c.Name, o.Id FROM #Customers c
RIGHT JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; NULL 13

-- (4) Preserve both sides.
SELECT c.Name, o.Id FROM #Customers c
FULL OUTER JOIN #Orders o ON o.CustomerId = c.Id;
-- Ali 10; Ali 11; Mona 12; Omar NULL; NULL 13

-- (5) Every combination: 3 customers x 4 orders = 12 rows.
SELECT c.Id AS CustomerId, o.Id AS OrderId
FROM #Customers c CROSS JOIN #Orders o;

-- (6) Self join: unordered pairs of orders belonging to the same customer.
SELECT a.Id, b.Id FROM #Orders a JOIN #Orders b
ON a.CustomerId = b.CustomerId AND a.Id < b.Id;
-- 10 11

-- (7) Anti-join: customers without any order.
SELECT c.Name FROM #Customers c LEFT JOIN #Orders o
ON o.CustomerId = c.Id WHERE o.Id IS NULL;
-- Omar

#Logical query order و WHERE و HAVINGLogical query order, WHERE and HAVING

فهم ترتيب SQL المنطقي يفسّر ليه alias مش متاحة في WHERE. ده ترتيب المعنى، مش ترتيب physical execution؛ optimizer يقدر يعيد ترتيب الشغل مع الحفاظ على النتيجة.

FROM / JOIN / ON -> WHERE -> GROUP BY -> HAVING
-> SELECT -> DISTINCT -> ORDER BY -> paging / limiting

SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Total) AS Revenue
FROM Orders
WHERE Status = 'Paid' AND CreatedAt >= '20260101'         -- (1)
  AND CreatedAt < '20270101'
GROUP BY CustomerId
HAVING SUM(Total) > 1000                                -- (2)
ORDER BY Revenue DESC, CustomerId;                      -- (3)

COUNT(*) يعد الصفوف، COUNT(column) يعد غير NULL، و COUNT(DISTINCT column) يعد القيم المختلفة غير NULL. SUM بتتجاهل NULL وبترجع NULL لو مفيش values؛ COALESCE لما business semantics عايزة صفر.

#Subqueries و EXISTS و recursive CTEsSubqueries, EXISTS and recursive CTEs

لما سؤال محتاج نتيجة سؤال تاني، subquery بتعبّر عن الاعتماد. CTE بتسمي خطوة وتوضح القراءة؛ مش جدول temporary متخزّن تلقائي.

SELECT Id, Total FROM Orders
WHERE Total > (SELECT AVG(Total) FROM Orders);           -- (1)

SELECT c.Id FROM Customers c
WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);                                                     -- (2)

-- Categories(Id, ParentId, Name); roots have ParentId NULL.
WITH Tree AS (
    SELECT Id, ParentId, Name, 0 AS Depth
    FROM Categories WHERE ParentId IS NULL              -- (3)
    UNION ALL
    SELECT c.Id, c.ParentId, c.Name, t.Depth + 1
    FROM Categories c JOIN Tree t ON c.ParentId = t.Id   -- (4)
)
SELECT Id, Name, Depth FROM Tree OPTION (MAXRECURSION 100);

#Window functions من غير فقد الصفوفWindow functions without collapsing rows

عايز أحدث order لكل عميل، أو running total، مع الاحتفاظ بكل صف. GROUP BY بتختزل الصفوف؛ window functions بتحسب حوالين الصف الحالي.

SELECT Id, CustomerId, Total,
    ROW_NUMBER() OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS rn, -- (1)
    RANK() OVER (ORDER BY Total DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY Total DESC) AS dense_rnk,  -- (2)
    SUM(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id
        ROWS UNBOUNDED PRECEDING) AS RunningTotal,        -- (3)
    LAG(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS PreviousTotal,
    LEAD(Total) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id) AS NextTotal,
    100.0 * Total / NULLIF(SUM(Total) OVER (), 0) AS PercentOfTotal
FROM Orders;

فلتر rn في outer query أو CTE؛ مش WHERE في نفس المستوى. الـ sort والـ memory grant ممكن يكونوا غاليين؛ index مناسب يقلل الكلفة.

#UNION و DDL و database programmabilityUNION, DDL and database programmability

اختيار command غلط ممكن يمسح schema بدل rows، أو يضيف deduplication مش مطلوب.

CREATE OR ALTER VIEW dbo.PaidOrderTotals AS             -- (1)
SELECT CustomerId, SUM(Total) AS Revenue
FROM dbo.Orders WHERE Status = 'Paid'
GROUP BY CustomerId;
-- Run as a separate batch:
SELECT CustomerId, Revenue FROM dbo.PaidOrderTotals;

BEGIN TRANSACTION;
TRUNCATE TABLE dbo.ImportStaging;                       -- (2)
ROLLBACK TRANSACTION;

stored procedures تقلل round trips وتتيح permissions محددة، بس محتاجة versioning و tests. cached plans مش حكر عليها؛ parameterized SQL كمان. scalar UDF لكل row ممكن تكلف، لكن inlining المدعوم يغير الأداء؛ قيس. triggers مفيدة للـ audit بس تزود hidden behavior و lock duration.

#Normalization من 1NF لـ BCNFNormalization from 1NF to BCNF

تكرار current customer address في كل order بيعمل update anomalies. normalization بتنظم facts حسب dependencies، مش بس عدد الجداول.

Raw: Orders(OrderId, CustomerId, CustomerCity, Products="Mug,Pen")
1NF: Orders + OrderLines(OrderId, ProductId, ProductName, Qty)
2NF: Products(ProductId, ProductName) + OrderLines without ProductName
3NF: Customers(CustomerId, CurrentCity) + Orders without CurrentCity
Snapshot: OrderLines(UnitPriceAtSale) and Orders(ShippingAddressAtPurchase)

1. 1NF: قيم مفردة مناسبة للـ domain ومفيش repeating groups أو list منتجات في field واحدة.
2. 2NF: 1NF ومفيش non-prime attribute بيعتمد على جزء صحيح من candidate key. مثال أوضح: key للسطر (OrderId, ProductId) لو المنتج بيتكرر مرة واحدة؛ ProductName يعتمد على ProductId بس.
3. 3NF: لكل nontrivial dependency X → A، إما X superkey أو A prime attribute. في المثال CustomerId → CurrentCity تنقل لجدول Customers.
4. BCNF: لكل nontrivial dependency، determinant لازم superkey. أقوى من 3NF اللي بتسمح باستثناء prime attributes.

مثال BCNF: ProductSupplier(ProductId, SupplierId, AgentId)، مع (ProductId, SupplierId) → AgentId و AgentId → SupplierId. keys هي (ProductId, SupplierId) و (ProductId, AgentId). الـ AgentId مش superkey، مع إن SupplierId prime؛ العلاقة ممكن تحقق 3NF وتخالف BCNF. فصل AgentSupplier(AgentId, SupplierId) و ProductAgent(ProductId, AgentId) lossless، لكن حفظ كل dependencies ممكن يحتاج check إضافي.

denormalization زي summary table تحسن reads مقابل تكلفة sync. سعر البيع وعنوان الشحن وقت الشراء facts تاريخية مختلفة عن القيم الحالية؛ مش duplicate لازم يتحدّث مع كل تغيير. ماتعملش joins أكتر لمجرد الوصول لأكبر normal form من غير فهم constraints والـ workload.

#ACID و isolation و transactions في .NETACID, isolation and transactions in .NET

order و inventory لازم يتغيروا مع بعض، ومنافسين مايكسرشوا stock invariant. Atomicity كل التغيير أو مفيش؛ Consistency احترام constraints والـ business invariant؛ Isolation يحدد التداخل؛ Durability بقاء commit حسب ضمانات storage والـ configuration.

using Microsoft.Data.SqlClient;
using System.Data;

await using var conn = new SqlConnection(connectionString);
await conn.OpenAsync(ct);
await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(ct); // (1)
await using var cmd = conn.CreateCommand();
cmd.Transaction = tx;
cmd.CommandText = "UPDATE Products SET Available = Available - @qty " +
    "WHERE Id = @id AND Available >= @qty";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = productId;
cmd.Parameters.Add("@qty", SqlDbType.Int).Value = quantity;  // (2)
if (quantity <= 0) throw new ArgumentOutOfRangeException(nameof(quantity));
if (await cmd.ExecuteNonQueryAsync(ct) != 1)
    throw new InvalidOperationException("Reservation failed");
// Insert the order using this same connection and transaction.
await tx.CommitAsync(ct);                                 // (3)

deadlock دائرة انتظار؛ SQL Server يختار victim بـ error 1205. خلي ترتيب الموارد ثابت والـ transactions قصيرة وافحص deadlock graph، واعمل bounded retry للوحدة الآمنة. row versioning يقلل reader/writer blocking، لكنه مش بيلغي writer/writer locks أو كل deadlocks. PostgreSQL isolation implementations تختلف؛ التفاصيل الأعمق في topic 12.

#Clustered و covering و filtered indexesClustered, covering and filtered indexes

index زي فهرس كتاب: يختصر البحث، لكن كل إضافة بتحتاج صيانة الفهرس. في SQL Server rowstore، clustered index leaf level هي data rows؛ ترتيبها المنطقي بالمفتاح مش وعد إن disk sectors متجاورة أو SELECT هيرجع مرتب.

-- Assumes Orders has a clustered Id key and a StoreId column.
CREATE INDEX IX_Orders_Store_Created
ON Orders (StoreId, CreatedAt DESC, Id DESC)
INCLUDE (Total, Status);                                 -- (1)

SELECT Id, CreatedAt, Total, Status FROM Orders
WHERE StoreId = @store AND CreatedAt >= @from
ORDER BY CreatedAt DESC, Id DESC;

CREATE INDEX IX_Orders_Pending
ON Orders (CreatedAt) WHERE Status = 'Pending';          -- (2)

فيه clustered واحد أو heap من غيره؛ nonclustered كتير، و locator يبقى clustered key أو RID للـ heap. PK constraint مش لازم clustered. covering وصف علاقة index بـ query، مش نوع مستقل مضمون لكل queries. (StoreId, CreatedAt) مفيد للـ leading prefix؛ تاريخ لوحده غالباً مش efficient direct seek عليه. راجع write amplification والـ storage والـ statistics قبل الإضافة.

#Sargability و execution plans و parameter sniffingSargability, plans and parameter sensitivity

index موجودة مش معناها إنها هتستخدم كويس. predicate وطريقة توزيع البيانات والـ estimates بيحددوا الخطة.

SELECT Id FROM Orders WHERE YEAR(CreatedAt) = 2026;

SELECT Id FROM Orders
WHERE CreatedAt >= '20260101' AND CreatedAt < '20270101';

Sargable يعني predicate مناسبة للـ index access؛ مش guarantee إن optimizer يختار seek. انتبه لـ implicit conversions و leading wildcard زي LIKE '%pen'، والـ collation. computed أو expression indexes ممكن تدعم expressions معينة، فمش كل function تمنع كل index.

1. حدد query من Query Store أو traces، وسجّل duration والـ logical reads والـ rows والـ waits.
2. اقرأ actual plan: فرق estimates عن actuals، و spills، و memory grants، و lookups كتير، و blocking.
3. قلّل columns و rows؛ صلّح N+1؛ عدّل predicates و indexes؛ راجع statistics.
4. جرّب parameters متنوعة وحجم production. scan ممكن يبقى أحسن لو محتاج نسبة كبيرة من الجدول؛ seek ممكن يقرأ كتير برضه.
5. قارن قبل وبعد، وراجع تكلفة writes والـ plan stability؛ cache أو summaries لما القياس يبرر.

Parameter sniffing: compile باستخدام قيم parameters حالية بيساعد التقدير، لكن plan مناسبة لمتجر صغير ممكن تسوء مع متجر ضخم. افحص compiled و runtime values. OPTION(RECOMPILE) يزود compile CPU؛ OPTIMIZE FOR UNKNOWN ممكن يدي compromise سيئة؛ Query Store forcing و query variants حلول محتاجة قياس. SQL Server الحديث فيه Parameter Sensitive Plan optimization للـ eligible queries حسب version و compatibility level؛ مش علاج لكل حالة.

#Queries عملية: السعر والترتيب والتقاريرPractice queries: prices, rankings and reports

التمارين دي بتستخدم schema تدريب موسّعة: Products(Id, CategoryId, ParentProductId, Price)، و Customers(Id, Email)، و Orders(Id, CustomerId, StoreId, CreatedAt, Status, Total)، و OrderLines(OrderId, ProductId, Quantity)، و AccountEntries(Id, CustomerId, CreatedAt, Amount). اعتبر الأعمدة دي موجودة للتمرين؛ duplicate emails في import staging قبل unique constraint. أنماط الرواتب اتنقلت لأسعار products علشان نفضل في domain واحد.

-- (1) Second DISTINCT highest price; NULL if none exists.
SELECT MAX(Price) AS SecondPrice FROM Products
WHERE Price < (SELECT MAX(Price) FROM Products);

-- (2) Nth distinct price, including all tied products if needed.
WITH Ranked AS (
    SELECT Id, Price, DENSE_RANK() OVER (ORDER BY Price DESC) AS rnk
    FROM Products WHERE Price IS NOT NULL
)
SELECT DISTINCT Price FROM Ranked WHERE rnk = @N;

WITH Ranked AS (
    SELECT Id, CategoryId, Price,
        RANK() OVER (PARTITION BY CategoryId ORDER BY Price DESC) AS rnk
    FROM Products WHERE Price IS NOT NULL
)
SELECT Id, CategoryId, Price FROM Ranked WHERE rnk = 1;   -- (3)

SELECT Email, COUNT(*) AS Copies FROM CustomerImport
WHERE Email IS NOT NULL GROUP BY Email HAVING COUNT(*) > 1; -- (4)

WITH Duplicates AS (
    SELECT Id, Email,
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Id) AS rn
    FROM CustomerImport WHERE Email IS NOT NULL
)
DELETE FROM Duplicates WHERE rn > 1;                    -- (5)

SELECT c.Id FROM Customers c WHERE NOT EXISTS (
    SELECT 1 FROM Orders o WHERE o.CustomerId = c.Id
);                                                     -- (6)

SELECT variant.Id, variant.Price, parent.Price AS ParentPrice
FROM Products variant JOIN Products parent
ON variant.ParentProductId = parent.Id
WHERE variant.Price > parent.Price;                     -- (7)

WITH Monthly AS (
    SELECT DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1) AS MonthStart,
        SUM(Total) AS Revenue
    FROM Orders WHERE Status = 'Paid'
    GROUP BY DATEFROMPARTS(YEAR(CreatedAt), MONTH(CreatedAt), 1)
)
SELECT MonthStart, Revenue,
    Revenue - LAG(Revenue) OVER (ORDER BY MonthStart) AS Change
FROM Monthly ORDER BY MonthStart;                       -- (8)

WITH Sales AS (
    SELECT o.StoreId, l.ProductId, SUM(l.Quantity) AS Units
    FROM Orders o JOIN OrderLines l ON l.OrderId = o.Id
    WHERE o.Status = 'Paid'
    GROUP BY o.StoreId, l.ProductId
), Ranked AS (
    SELECT StoreId, ProductId, Units,
        ROW_NUMBER() OVER (
            PARTITION BY StoreId ORDER BY Units DESC, ProductId) AS rn
    FROM Sales
)
SELECT StoreId, ProductId, Units FROM Ranked WHERE rn <= 3; -- (9)

SELECT CustomerId, Id, Amount,
    SUM(Amount) OVER (
        PARTITION BY CustomerId ORDER BY CreatedAt, Id
        ROWS UNBOUNDED PRECEDING) AS Balance
FROM AccountEntries;                                   -- (10)

SELECT COUNT(CASE WHEN Status = 'Paid' THEN 1 END) AS Paid,
    COUNT(CASE WHEN Status = 'Pending' THEN 1 END) AS Pending,
    COUNT(CASE WHEN Status = 'Cancelled' THEN 1 END) AS Cancelled
FROM Orders;                                           -- (11)

WITH Days AS (
    SELECT DISTINCT CAST(CreatedAt AS date) AS OrderDay FROM Orders
), Islands AS (
    SELECT OrderDay,
        DATEADD(day, -CAST(ROW_NUMBER() OVER (ORDER BY OrderDay) AS int),
            OrderDay) AS IslandKey
    FROM Days
)
SELECT MIN(OrderDay) AS StartDay, MAX(OrderDay) AS EndDay, COUNT(*) AS DayCount
FROM Islands GROUP BY IslandKey ORDER BY StartDay;       -- (12)