DML — שליפה, הוספה ועדכון נתונים
SELECT
SELECT FirstName, LastName FROM Employees
הפקודה המרכזית ביותר בשפת SQL לשליפת נתונים. ציון עמודות ספציפיות חוסך משאבי שרת ומחזיר רק המידע המדויק שצריך.
FROM
SELECT City FROM Customers
מנחה את מנוע ה-SQL אל מקור הנתונים — באיזו טבלה, View או אוסף טבלאות לחפש. בלעדיה השאילתה חסרת כיוון.
INSERT INTO
INSERT INTO Customers (Name, City) VALUES ('Dan', 'Haifa')להזנת שורות חדשות לטבלה. חשוב: התאמה מוחלטת בין סדר העמודות לסדר הערכים ב-VALUES.
UPDATE
UPDATE Products SET Price = 150 WHERE ID = 1
לעריכת נתונים קיימים. קריטי: תמיד עם WHERE — בלעדיו תדרוס את כל השורות בטבלה!
DELETE
DELETE FROM Orders WHERE Status = 'Canceled'
מסירה שורות לצמיתות. סכנת מחיקה המונית קיימת אם שוכחים WHERE.
DISTINCT
SELECT DISTINCT City FROM Customers
מנקה כפילויות ומחזירה רשימה של ערכים ייחודיים בלבד.
SELECT INTO
SELECT * INTO BackupTable FROM OriginalTable
יוצרת טבלה חדשה ומעתיקה אליה נתונים בו-זמנית. לגיבויים מהירים ועבודה זמנית.
INSERT INTO SELECT
INSERT INTO Archive SELECT * FROM Orders WHERE Year = 2020
שולפת מטבלה אחת ודוחפת לטבלה קיימת. לתהליכי ארכיון והעברת נתונים.
MERGE
MERGE Target t USING Source s ON t.ID = s.ID WHEN MATCHED THEN UPDATE...
"Upsert" — מוצאת התאמה: מעדכנת. חסרה: מוסיפה. חוסך לוגיקה מורכבת.
AS (Alias)
SELECT FirstName AS Name FROM Employees e
שם זמני לעמודה או טבלה. לכותרות ברורות יותר ולקיצור שמות ארוכים ב-JOIN.
סינון ואופרטורים לוגיים
WHERE
SELECT * FROM Products WHERE Price > 100
מנוע הסינון הראשי. בודק כל שורה ומעביר הלאה רק רשומות שהתנאי אמיתי עבורן.
AND
WHERE Category = 'Toys' AND Price < 50
דורש התקיימות של כל התנאים במקביל. רק שורה שתענה על כולם תוצג.
OR
WHERE City = 'Haifa' OR City = 'Eilat'
מספיק שאחד מהתנאים יתקיים. חשוב להשתמש בסוגריים כשמשלבים עם AND.
NOT
WHERE NOT Category = 'Food'
שלילה על תנאי. שימושי כשקל יותר להגדיר מה לא רוצים.
IN
WHERE Department IN ('HR', 'Sales', 'IT')תחליף נקי לשרשרת OR. מקבל רשימת ערכים ובודק אם הערך תואם לאחד מהם.
NOT IN
WHERE Country NOT IN ('USA', 'UK')מוודא שהערך אינו ברשימה. לסינון והחרגת קבוצת מזהים.
BETWEEN
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
טווח סינון רציף כולל גבולות. נפוץ לטווחי תאריכים ומחירים.
NOT BETWEEN
WHERE Age NOT BETWEEN 20 AND 30
שולף נתונים מחוץ לטווח. לאיתור חריגים סטטיסטיים.
LIKE
WHERE Email LIKE '%@gmail.com'
חיפוש גמיש במחרוזות. % = כל רצף תווים. _ = תו בודד.
NOT LIKE
WHERE Address NOT LIKE '%PO Box%'
מסנן החוצה רשומות המכילות תבנית. לניקוי נתונים.
IS NULL
WHERE DeliveryDate IS NULL
NULL = "המידע לא קיים". לא ניתן להשוות ב-=. IS NULL מאתר תאים ריקים.
IS NOT NULL
WHERE PhoneNumber IS NOT NULL
מאתר שורות שיש בהן תוכן כלשהו. לניקוי דוחות ולניתוח על נתונים שלמים.
= שווה
WHERE Status = 'Active'
אופרטור ההשוואה הבסיסי. דורש התאמה מדויקת.
<> / != שונה
WHERE Role <> 'Admin'
שני התחבירים זהים. מציג כל מה שאינו הערך שהוגדר.
> גדול מ
WHERE Amount > 1000
ערכים גבוהים מנקודת הייחוס. אינו כולל את הערך עצמו.
< קטן מ
WHERE InventoryLevel < 10
ערכים נמוכים מנקודת הייחוס. להתראות על מלאי שעומד להסתיים.
>= גדול או שווה
WHERE Age >= 18
כולל את נקודת הסף. הדרך הנכונה להגדיר רף כניסה.
<= קטן או שווה
WHERE Discount <= 0.5
מציב תקרה. מוצרים עם הנחה של עד 50% כולל.
EXISTS
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.ID)
בודק "האם קיים?". מהיר מאוד — עוצר בהתאמה ראשונה. עדיף על JOIN בהרבה מקרים.
NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.ID)
ישויות שאין להן זכר בטבלה אחרת. לרוב עדיף על NOT IN מבחינת ביצועים.
ANY
WHERE Price > ANY (SELECT Price FROM Competitors)
השוואה מול אוסף תוצאות. מספיק שהתנאי יתקיים לפחות מול ערך אחד.
ALL
WHERE Price > ALL (SELECT Price FROM Competitors)
מחמיר — התנאי חייב להתקיים מול כל הערכים ברשימה ללא יוצא מן הכלל.
מיון, קיבוץ ועיצוב תצוגה
ORDER BY
ORDER BY LastName, FirstName
מארגן תוצאות לפי סדר הגיוני. ללא ORDER BY — SQL מחזיר שורות בסדר אקראי.
ASC
ORDER BY Price ASC
מיון עולה — מהקטן לגדול, מ-A ל-Z. ברירת המחדל, ניתן להשמיט.
DESC
ORDER BY OrderDate DESC
מיון יורד. לתאריך הכי עדכני, הכי רווחי, הכי גבוה — ראשון.
TOP
SELECT TOP 10 * FROM Customers ORDER BY Score DESC
X שורות ראשונות בלבד (SQL Server). עם ORDER BY DESC — "10 הטובים ביותר".
LIMIT
SELECT * FROM Customers ORDER BY Score DESC LIMIT 10
המקבילה של TOP ב-MySQL/PostgreSQL. תמיד בסוף השאילתה.
OFFSET
ORDER BY ID OFFSET 20 ROWS
דולג על X שורות. חלק ממנגנון Pagination — דפדוף בין עמודי תוצאות.
FETCH NEXT
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
משלים את OFFSET. מגדיר כמה שורות לשלוף אחרי הדילוג.
GROUP BY
SELECT Category, COUNT(*) FROM Products GROUP BY Category
יוצרת קבוצות מרשומות בעלות ערך זהה ומאפשרת חישובים כוללים לכל קבוצה.
HAVING
GROUP BY Category HAVING COUNT(*) > 50
"WHERE של הקבוצות". מסנן לאחר שהחישוב הסתיים. חייבים אותו עם פונקציות אגרגציה.
ROLLUP
GROUP BY ROLLUP (Department)
מוסיף שורת "סך הכל כולל" אוטומטית בתחתית. חוסך חישוב נפרד.
CUBE
GROUP BY CUBE (Department, JobTitle)
יוצרת שורות סיכום לכל קומבינציה אפשרית. לדוחות מטריצה אנליטיים.
GROUPING SETS
GROUP BY GROUPING SETS ((Dept), (Title), ())
שליטה מדויקת — מציין בדיוק אילו רמות סיכום לייצר. יעיל מ-CUBE.
פונקציות אגרגציה
COUNT(col)
SELECT COUNT(Email) FROM Customers
סופרת נתונים בעמודה. מתעלמת משורות NULL. 8 אימיילים מ-10 שורות → מחזירה 8.
COUNT(*)
SELECT COUNT(*) FROM Orders
סופרת כמות שורות פיזית כולל NULL. הדרך המהירה לדעת "כמה רשומות יש?".
SUM()
SELECT SUM(Amount) FROM Sales WHERE Year = 2023
מחברת ערכים מספריים לסכום כולל. הלב הפועם של דוחות כלכליים.
AVG()
SELECT AVG(Salary) FROM Employees
ממוצע אריתמטי תוך התעלמות מ-NULL. לשכר ממוצע, זמני המתנה, גיל ועוד.
MIN()
SELECT MIN(Price) FROM Products
הערך הקטן ביותר. גם לתאריך הקדום ביותר או לשם הראשון אלפביתית.
MAX()
SELECT MAX(OrderDate) FROM Orders
הערך הגבוה ביותר. לעסקה הרווחית, לחותמת הזמן העדכנית.
חיבורים (JOINs) ופעולות איחוד
INNER JOIN
SELECT c.Name, o.Amount FROM Customers c INNER JOIN Orders o ON c.ID = o.CustID
רק שורות עם התאמה מלאה משני הצדדים. הבטוח ביותר לדוחות עסקאות פעילות.
LEFT JOIN
SELECT c.Name, o.Amount FROM Customers c LEFT JOIN Orders o ON c.ID = o.CustID
כל שורות הטבלה השמאלית גם ללא התאמה (NULL לצד ימין). לאיתור לקוחות ללא הזמנות.
RIGHT JOIN
SELECT c.Name, o.Amount FROM Customers c RIGHT JOIN Orders o ON c.ID = o.CustID
כל שורות הטבלה הימנית. בדרך כלל מעדיפים לשנות סדר ולהשתמש ב-LEFT.
FULL OUTER JOIN
SELECT c.Name, o.Amount FROM Customers c FULL OUTER JOIN Orders o ON c.ID = o.CustID
הכל מכל מקום — התאמות + חוסרים משני הצדדים. לבקרה ואיתור אי-התאמות.
CROSS JOIN
SELECT Colors.Name, Sizes.Name FROM Colors CROSS JOIN Sizes
מכפלה קרטזית — כל שורה × כל שורה. 3 צבעים × 3 מידות = 9 שורות.
SELF JOIN
SELECT e1.Name AS Emp, e2.Name AS Mgr FROM Employees e1 JOIN Employees e2 ON e1.MgrID = e2.ID
חיבור טבלה לעצמה. לטיפול בהיררכיות מנהל-עובד באותה טבלה.
ON
JOIN Orders o ON c.ID = o.CustomerID
מגדיר תנאי החיבור — אילו עמודות מייצגות אותו מידע בשתי הטבלאות.
USING
JOIN Orders USING (CustomerID)
קיצור של ON כשהעמודה המקשרת נושאת שם זהה בשתי הטבלאות.
UNION
SELECT City FROM Customers UNION SELECT City FROM Suppliers
מחבר שאילתות לאורך ומנקה כפילויות. חייבים אותו מבנה עמודות.
UNION ALL
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers
איחוד ללא ניקוי כפילויות. מהיר יותר — עדיף כשאין חפיפה.
INTERSECT
SELECT ProductID FROM Sales2023 INTERSECT SELECT ProductID FROM Sales2024
חיתוך — רק שורות שמופיעות בשתי השאילתות. ללקוחות נאמנים שקנו בשתי שנים.
EXCEPT / MINUS
SELECT ProductID FROM Sales2023 EXCEPT SELECT ProductID FROM Sales2024
חיסור — שורות בשאילתה הראשונה שאינן בשנייה. מוצרים שנמכרו ב-2023 בלבד.
DDL — הגדרת מבנה ותשתיות
CREATE DATABASE
CREATE DATABASE CompanyCRM
יוצרת מסד נתונים חדש. מקצה שטח, קבצים ולוגים בשרת.
DROP DATABASE
DROP DATABASE CompanyCRM
מוחקת DB במלואו — כולל כל הטבלאות והמידע. בלתי הפיך ללא גיבוי!
CREATE TABLE
CREATE TABLE Users (ID INT, Name VARCHAR(50))
יוצרת טבלה חדשה ומגדירה מבנה עמודות וסוגי נתונים.
ALTER TABLE
ALTER TABLE Users ADD Email VARCHAR(100)
שינויים מבניים בטבלה קיימת — הוספת עמודות, מחיקה, שינוי סוג נתונים.
DROP TABLE
DROP TABLE Users
מחיקה טוטאלית — הנתונים והמבנה נמחקים. נצטרך CREATE מאפס.
TRUNCATE TABLE
TRUNCATE TABLE Logs
מרוקנת תוכן במהירות. המבנה נשאר. מהיר מ-DELETE — לאיפוס מערכת.
CREATE INDEX
CREATE INDEX idx_ln ON Employees (LastName)
"תוכן עניינים" לעמודה. מאפשר קפיצה ישירה לרשומה במקום לסרוק הכל.
DROP INDEX
DROP INDEX idx_ln ON Employees
מוחקת אינדקס. אינדקסים מאיטים כתיבה — מחיקת לא-בשימוש משפרת ביצועים.
CREATE VIEW
CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Status = 'Active'
"טבלה וירטואלית" — שומרת שאילתה מורכבת כשם. מסתירה לוגיקה ממשתמשים.
DROP VIEW
DROP VIEW ActiveUsers
מוחק View. הנתונים המקוריים נשארים ללא פגע.
CREATE PROCEDURE
CREATE PROCEDURE GetUser (@ID INT) AS BEGIN SELECT * FROM Users WHERE ID=@ID END
תוכנית קטנה מאוחסנת ב-DB. משפר אבטחה ומונע SQL Injection.
DROP PROCEDURE
DROP PROCEDURE GetUser
מסירה פרוצדורה מאוחסנת. לשדרוגי גרסאות וסילוק קוד ישן.
CREATE FUNCTION
CREATE FUNCTION CalcTax (@V DECIMAL) RETURNS DECIMAL AS BEGIN RETURN @V * 1.17 END
פונקציה מותאמת לחישוב חוזר. מחזירה ערך נקי ומשמשת בשליפות.
PRIMARY KEY
CREATE TABLE Users (ID INT PRIMARY KEY, Name VARCHAR(50))
"תעודת זהות" ייחודית לכל שורה. מונע כפילויות ומחייב ערך קיים.
FOREIGN KEY
CONSTRAINT FK_User FOREIGN KEY (UserID) REFERENCES Users(ID)
יוצר קשר קשיח בין טבלאות. מונע הזנת מזהים פיקטיביים שלא קיימים.
UNIQUE
CREATE TABLE Users (Email VARCHAR(100) UNIQUE)
כל ערכי העמודה חייבים להיות שונים. לאימיילים ומספרי טלפון ייחודיים.
NOT NULL
CREATE TABLE Users (Name VARCHAR(50) NOT NULL)
מחייב ערך בעמודה. מונע יצירת לקוחות ללא שם או מוצרים ללא מחיר.
CHECK
CREATE TABLE Products (Price DECIMAL CHECK (Price > 0))
בודק אם הערך עומד בחוקיות עסקית. לא מאפשר מחיר שלילי.
DEFAULT
CREATE TABLE Users (Status VARCHAR(20) DEFAULT 'Pending')
ממלא ערך אוטומטי אם המשתמש לא הזין. במקום שהתא יישאר NULL.
IDENTITY / AUTO_INCREMENT
CREATE TABLE Users (ID INT IDENTITY(1,1) PRIMARY KEY)
מונה רץ אוטומטי (1,2,3...). כל שורה חדשה מקבלת מזהה ייחודי.
טרנזקציות והרשאות אבטחה
GRANT
GRANT SELECT, INSERT ON Users TO AnalystRole
מעניקה היתרי גישה. ניתן לתת "קריאה בלבד" ולמנוע שינויים.
REVOKE
REVOKE INSERT ON Users FROM AnalystRole
מבטלת זכויות שניתנו. לשינוי תפקידים והגנה על מידע רגיש.
BEGIN TRAN
BEGIN TRAN; UPDATE Accounts SET Balance = Balance - 100...
פותחת בלוק עבודה — כל הפעולות מנוהלות כיחידה. עד לאישור, הן על תנאי.
COMMIT
COMMIT
חותמת את הטרנזקציה ומקבעת את כל הפעולות בצורה סופית ובלתי הפיכה.
ROLLBACK
ROLLBACK
גלגל ההצלה. מבטל את כל השינויים מתחילת הטרנזקציה ומחזיר למצב הקודם.
SAVEPOINT
SAVEPOINT Stage1
נקודת שמירה זמנית בטרנזקציה ענקית. כשלון מאוחר חוזר לנקודה זו בלבד.
פונקציות מניפולציות על טקסט
CONCAT()
SELECT CONCAT(FirstName, ' ', LastName) FROM Employees
משרשרת שדות וטקסטים. מתמודדת עם NULL בחן — מתעלמת מהם.
CONCAT_WS()
SELECT CONCAT_WS(', ', City, State, Country) FROM Addresses"Concatenate With Separator". מגדיר מפריד אחד ומחבר את כולם אוטומטית.
SUBSTRING()
SELECT SUBSTRING(IDNumber, 1, 4) FROM Customers
חיתוך טקסט לפי מיקום: שם עמודה, תו התחלה, כמה תווים.
LEN() / LENGTH()
WHERE LEN(Password) < 8
סופרת כמה תווים בטקסט. לבקרת סיסמאות קצרות ואימות נתונים.
UPPER()
SELECT UPPER(CountryCode) FROM Countries
ממירה לאותיות גדולות. לנרמול השוואות — שני צדדים ל-UPPER = חיפוש אחיד.
LOWER()
SELECT LOWER(Email) FROM Users
ממירה לאותיות קטנות. שימוש מוסכם לאימיילים.
TRIM()
SELECT TRIM(Username) FROM Accounts
מקצצת רווחים מתחילה ומסוף. ל"כלוך" מהעתק-הדבק.
LTRIM()
SELECT LTRIM(Comment) FROM Feedback
גוזמת רווחים מצד שמאל בלבד.
RTRIM()
SELECT RTRIM(Comment) FROM Feedback
גוזמת רווחים מסוף המחרוזת בלבד.
REPLACE()
SELECT REPLACE(Phone, '-', '') FROM Customers
מחליפה כל מופע של תבנית. להסרת מקפים ממספרי טלפון.
REVERSE()
SELECT REVERSE(StringCol) FROM Strings
הופכת את סדר התווים. טריק למציאת מיקום התו האחרון.
LEFT()
SELECT LEFT(OrderNum, 2) FROM Orders
X תווים מהצד השמאלי. קצר מ-SUBSTRING לחילוץ מהתחלה.
RIGHT()
SELECT RIGHT(CreditCard, 4) FROM Payments
X תווים מהצד הימני. ל-4 ספרות אחרונות של כרטיס אשראי.
CHARINDEX()
SELECT CHARINDEX('@', Email) FROM Usersמחפש תו/מילה ומחזיר מיקום מספרי. לחיתוך אימיילים באורכים משתנים.
PATINDEX()
SELECT PATINDEX('%[0-9]%', Address) FROM Customersמאתרת תבניות חכמות (SQL Server). לזיהוי מיקום ספרה ראשונה בכתובת.
FORMAT()
SELECT FORMAT(Price, 'C', 'en-US') FROM Products
מעצבת ערכים לטקסט יפה — פסיקים במיליונים, סמלי מטבע, פורמטי תאריך.
פונקציות עיבוד תאריכים וזמנים
GETDATE() / NOW()
SELECT GETDATE()
תאריך ושעה נוכחיים עד אלפית שנייה. חותמת זמן אוטומטית לרשומות חדשות.
CURRENT_DATE
SELECT CURRENT_DATE
תאריך היום בלבד ללא שעות. לתנאים שאכפת מהם מהתאריך בלבד.
CURRENT_TIME
SELECT CURRENT_TIME
השעה הנוכחית ללא תאריך. לדוחות יעילות ועומסים יומיים.
DATEADD()
SELECT DATEADD(day, 30, OrderDate) AS DueDate FROM Orders
מוסיפה (או מחסירה עם מינוס) זמן לתאריך. לחישוב תאריכי פירעון.
DATEDIFF()
SELECT DATEDIFF(day, OrderDate, ShipDate) FROM Orders
פער בין שני תאריכים. לזמני תגובה, גיל לקוח, ימים עד משלוח.
DATEPART()
SELECT DATEPART(quarter, OrderDate) FROM Sales
שולפת אלמנט ספציפי כמספר (רבעון=1-4). הכרחי ל-GROUP BY לפי רבעון.
YEAR()
SELECT YEAR(OrderDate) FROM Sales
מבודדת שנה. לדוחות הכנסות שנתיות (2021, 2022, 2023...).
MONTH()
SELECT MONTH(OrderDate) FROM Sales
מספר החודש (1-12). לניתוחי עונתיות לאורך שנים.
DAY()
SELECT DAY(BirthDate) FROM Customers
יום בחודש (1-31). לימי הולדת ותשלומי אשראי חודשיים.
EOMONTH()
SELECT EOMONTH(OrderDate) AS EndOfMonth FROM Sales
קופצת ליום האחרון של החודש. פותרת "כמה ימים יש בחודש". לסגירות פיננסיות.
ISDATE()
SELECT * FROM Import WHERE ISDATE(RawText) = 1
בודקת אם טקסט הוא תאריך חוקי. מגנה מפני נתונים שגויים מטפסים.
פונקציות מתמטיות
ROUND()
SELECT ROUND(Price, 2) FROM Products
מעגלת לX נקודות עשרוניות. לחשבוניות מדויקות.
CEILING()
SELECT CEILING(4.1) -- Returns 5
מעגלת כל שבר לשלם גבוה. 4.1 אוטובוסים → חייבים לשכור 5.
FLOOR()
SELECT FLOOR(4.9) -- Returns 4
מקצצת לשלם תחתון. 4.9 מארזים → אפשר לארוז רק 4.
ABS()
SELECT ABS(Income - Expense) FROM Books
ערך מוחלט — שלילי הופך לחיובי. לחישוב גודל פערים.
POWER()
SELECT POWER(Radius, 2) FROM Shapes
חזקה. לריבית דריבית, חישובי שטח.
SQRT()
SELECT SQRT(Area) FROM Squares
שורש ריבועי. לחישובי מרחקים וסטיות תקן.
RAND()
SELECT RAND()
מספר אקראי בין 0 ל-1. להגרלות ודגימות אקראיות.
SIGN()
SELECT SIGN(NetProfit) FROM Reports
1 (חיובי), -1 (שלילי), 0. לחיווי מהיר רווח/הפסד.
PI()
SELECT PI() * POWER(Radius, 2) AS Area
קבוע π מובנה. לחישובי גיאומטריה מדויקים.
MOD() / %
WHERE EmployeeID % 2 = 0
שארית חלוקה. לאיתור שורות זוגיות ולחלוקת רשימות לנתיבים שווים.
התניות חכמות וניהול NULL
CASE
SELECT CASE WHEN Score > 90 THEN 'A' ELSE 'F' END FROM Tests
לוגיקה ענפה בתוך השאילתה. בונה עמודה מחושבת שמקטלגת נתונים בזמן אמת.
WHEN / THEN
CASE WHEN condition THEN result END
ציר CASE. WHEN = תנאי לבדיקה, THEN = פלט אם התנאי אמיתי.
COALESCE()
SELECT COALESCE(Mobile, HomePhone, 'No Phone') FROM Contacts
מחזירה את הערך הראשון שאינו NULL. "מפל עדיפויות" לנתונים חסרים.
ISNULL() / IFNULL()
SELECT ISNULL(Discount, 0) FROM Sales
אם NULL מחזיר ערך גיבוי. הכרחי בחישובים — NULL שובר פעולות מתמטיות.
NULLIF()
SELECT Amount / NULLIF(Quantity, 0) FROM Inventory
אם שני ערכים זהים → מחזיר NULL. מונע קריסת חלוקה באפס.
המרות ושדרוגים של טיפוסי נתונים
IIF()
SELECT IIF(Stock > 0, 'In Stock', 'Out of Stock') FROM Products
CASE מקוצר לשני מצבים. SQL Server. שלוש כניסות: תנאי, אמת, שקר.
CAST()
SELECT CAST(Price AS INT) FROM Products
כלי תקני להמרת סוג נתון. מספר לטקסט לשרשור, טקסט למספר לחיבור.
CONVERT()
SELECT CONVERT(VARCHAR(10), OrderDate, 103) FROM Orders
אחות CAST עם יכולת עיצוב. מסוגלת לעצב תאריכים תוך כדי המרה.
TRY_CAST()
SELECT TRY_CAST(StringVal AS INT) FROM DirtyData
המרה בטוחה. ג'יבריש → NULL במקום קריסה. השאילתה ממשיכה לרוץ.
TRY_CONVERT()
SELECT TRY_CONVERT(DATE, StringDate) FROM DirtyData
CONVERT בטוח. לקליטת נתונים ממקורות חיצוניים לא אמינים.
Window Functions — פונקציות חלון
OVER()
SELECT Name, SUM(Salary) OVER() AS Total FROM Employees
שער לפונקציות חלון. חישובי אגרגציה על כל רשומה ללא GROUP BY. שכר + סך שכר חברה יחד.
PARTITION BY
SUM(Salary) OVER(PARTITION BY Department)
מפצל החלון לתת-קבוצות. סך שכר מחלקה במקום חברה שלמה. מתאפס בכל קבוצה.
ROW_NUMBER()
ROW_NUMBER() OVER(ORDER BY Salary DESC)
מספור סידורי ייחודי (1,2,3...). מתעלם מתיקו. לכפיית סדר ולהסרת כפילויות.
RANK()
RANK() OVER(ORDER BY Salary DESC)
דירוג תחרותי. תיקו → שניהם מקום 2. הבא → קופץ ל-4 (מדלג על 3).
DENSE_RANK()
DENSE_RANK() OVER(ORDER BY Salary DESC)
דירוג צפוף. אחרי תיקו של מקום 2, הבא מקבל 3 (לא 4). לאשראי ומנויים.
NTILE()
NTILE(4) OVER(ORDER BY Salary)
מחלק לX קבוצות שוות. NTILE(4) = ארבעה רבעונים. לסגמנטציה שיווקית.
LEAD()
LEAD(Revenue) OVER(ORDER BY Month)
מביאה נתון מהשורה הבאה. הכנסות חודש הבא לצד הנוכחי — ללא JOIN.
LAG()
LAG(Revenue) OVER(ORDER BY Month)
מביאה נתון מהשורה הקודמת. אתמול לצד היום — לחישוב % צמיחה בשאילתה אחת.
FIRST_VALUE()
FIRST_VALUE(Name) OVER(ORDER BY HireDate)
הרשומה הראשונה בחלון לכל שורה. עובד ותיק ביותר לצד כל עובד.
LAST_VALUE()
LAST_VALUE(Name) OVER(ORDER BY HireDate)
הרשומה האחרונה בחלון. השלב האחרון של הזמנה לצד כל שלבי הביניים.
מבנים מתקדמים וארכיטקטורה
WITH (CTE)
WITH HighVal AS (SELECT * FROM Orders WHERE Amount > 1000) SELECT * FROM HighVal
טבלת עבודה זמנית שמרחפת לאורך השאילתה. הופך קוד מסובך לשלבים קריאים.
PIVOT
PIVOT(SUM(Amount) FOR Year IN ([2022], [2023])) AS pvt
ממיר שורות לעמודות. לדוחות כמו Excel — חודשי שנה כעמודות.
UNPIVOT
UNPIVOT (Amount FOR Year IN ([2022], [2023])) AS unpvt
ממיר עמודות חזרה לשורות. לנרמול מטריצות ממקורות חיצוניים.
EXEC / EXECUTE
EXEC GetUser @UserID = 5
מפעיל פרוצדורות מאוחסנות. גם לביצוע קוד דינמי שנבנה כטקסט בזמן ריצה.
DECLARE
DECLARE @Year INT = 2023; SELECT * FROM Sales WHERE Year = @Year
יוצרת משתנים בסקריפטים. שינוי ערך אחד בפסגה משפיע על כל הקוד מטה.
JSON ו-XML — נתונים חצי-מובנים
FOR JSON
SELECT Name, Age FROM Users FOR JSON AUTO
הופכת תוצאות SQL לפורמט JSON. מאפשרת למפתחי ווב להשתמש בנתונים ישירות ב-JavaScript ללא המרות נוספות.
OPENJSON()
SELECT * FROM OPENJSON(@JsonString)
ממירה מחרוזת JSON לטבלה רגילה עם שורות ועמודות. לטיפול בנתונים מ-APIs חיצוניים.
JSON_VALUE()
SELECT JSON_VALUE(JsonCol, '$.Address.City') FROM Customers
חולצת ערך בודד מתוך JSON לפי נתיב. לשליפת שדה ספציפי מעמודת JSON גדולה.
JSON_QUERY()
SELECT JSON_QUERY(JsonCol, '$.Hobbies') FROM Customers
חולצת אובייקט מורכב או מערך מתוך JSON. להבדיל מ-JSON_VALUE שמחזירה ערך פשוט.
JSON_MODIFY()
UPDATE Users SET JsonCol = JSON_MODIFY(JsonCol, '$.Status', 'VIP')
עורכת ערך ספציפי בתוך JSON ללא כתיבה מחדש של כל המסמך.
ISJSON()
SELECT * FROM Logs WHERE ISJSON(RawData) = 1
בודקת אם מחרוזת היא JSON תקני. מחזירה 1 אם תקין, 0 אם פגום. לסינון נתונים שגויים.
FOR XML
SELECT Name FROM Users FOR XML PATH('User')ממירה תוצאות טבלה לפורמט XML. עדיין נפוץ בבנקים, ממשלות ומערכות ישנות.
OPENXML()
SELECT * FROM OPENXML(@doc, '/Users/User')
ממירה מחרוזת XML לטבלה רלציונית. לטיפול בנתונים מממשקים XML ישנים.
פונקציות מחרוזת מתקדמות
STRING_AGG()
SELECT Dept, STRING_AGG(Name, ', ') FROM Employees GROUP BY Dept
מאחדת מספר שורות למחרוזת אחת עם מפריד. "5 עובדים" → "דן, יעל, רון, מיכל, נועה".
STRING_SPLIT()
SELECT value FROM STRING_SPLIT('Apple,Banana,Orange', ',')מפרקת מחרוזת לשורות נפרדות לפי מפריד. הפוך מ-STRING_AGG.
REPLICATE()
SELECT REPLICATE('0', 5) + OrderNumber FROM Ordersמשכפלת תווים X פעמים. לריפוד מספרים באפסים מובילים (000045).
SPACE()
SELECT FirstName + SPACE(5) + LastName FROM Users
מייצרת X רווחים ריקים. קריא יותר מהקלדת רווחים ידניים.
SOUNDEX()
WHERE SOUNDEX(Name) = SOUNDEX('Smith')ממירה מילה לקוד פונטי (צליל). למציאת שמות עם שגיאות כתיב — "Smythe" = "Smith".
DIFFERENCE()
SELECT DIFFERENCE(Name, 'Michael') FROM Users
ציון 0-4 לדמיון פונטי בין שתי מילים. 4 = זהות קוליות. למנועי "האם התכוונת ל...?".
QUOTENAME()
SELECT QUOTENAME(TableName) FROM Sys.Tables
עוטפת שם אובייקט בסוגריים מרובעים. מונעת שגיאות לשמות עם רווחים ו-SQL Injection.
TRANSLATE()
SELECT TRANSLATE(Phone, '()-', ' ') FROM Contacts
מחליפה תווים מרובים בבת אחת. לניקוי סימני פיסוק ממספרי טלפון בשאילתה אחת.
ASCII()
SELECT ASCII(LEFT(Password, 1)) FROM Logins
מחזירה קוד ASCII של התו הראשון. לבדיקת אם סיסמה מתחילה באות גדולה.
CHAR()
SELECT 'Hello' + CHAR(13) + 'World'
ממירה קוד מספרי לתו. CHAR(13) = ירידת שורה, CHAR(9) = Tab. לתווים שלא ניתן להקליד.
פונקציות מערכת ומזהים
DB_NAME()
SELECT DB_NAME()
מחזירה שם מסד הנתונים הנוכחי. לזיהוי היכן רצה קוד בשרתים עם מסדים מרובים.
OBJECT_ID()
IF OBJECT_ID('TempTable') IS NOT NULL DROP TABLE TempTableבודקת אם טבלה/אובייקט קיים. מונעת שגיאות "טבלה לא קיימת" לפני DROP.
OBJECT_NAME()
SELECT OBJECT_NAME(1234567)
ממירה מזהה פנימי לשם קריא. לניתוח טבלאות מערכת ומעקב ביצועים.
@@VERSION
SELECT @@VERSION
מחזירה גרסת SQL Server ומערכת ההפעלה. לבדיקת תאימות לפני הרצת קוד.
@@ROWCOUNT
UPDATE Users SET Status=1; SELECT @@ROWCOUNT
כמות שורות שהושפעו מהפקודה האחרונה. לחיווי הצלחה/כישלון של UPDATE/DELETE.
@@ERROR
IF @@ERROR <> 0 PRINT 'Error occurred'
קוד שגיאה של הפקודה האחרונה. 0 = הצלחה. הוחלף ברובו ע"י TRY...CATCH.
@@IDENTITY
INSERT INTO Orders(Amt) VALUES(50); SELECT @@IDENTITY
המזהה האוטומטי שנוצר לאחרון. לקבלת מספר הזמנה לאחר INSERT.
SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
כמו @@IDENTITY אבל בטוח יותר — מחזיר מזהה רק מהסקופ הנוכחי, לא מטריגרים.
IDENT_CURRENT()
SELECT IDENT_CURRENT('Orders')המזהה האחרון בטבלה ספציפית — מכל חיבור. לניטור קצב גדילת טבלה.
HOST_NAME()
SELECT HOST_NAME()
שם המחשב שממנו בוצע החיבור. למערכות Audit לאיתור מי ניגש מאיפה.
SUSER_SNAME()
SELECT SUSER_SNAME()
שם המשתמש הנוכחי. לבנייה של Row Level Security — כל עובד רואה רק את הנתונים שלו.
@@SERVERNAME
SELECT @@SERVERNAME
שם שרת ה-SQL. לאפליקציות עם שרתים מרובים שצריכות לדעת אל מי הן מחוברות.
טיפול בשגיאות ובקרת זרימה
TRY...CATCH
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH
מנגנון טיפול בשגיאות. קוד ב-TRY שנכשל עובר ל-CATCH במקום לקרוס. הכלי הכי חשוב לפרוצדורות.
THROW
BEGIN CATCH THROW; END CATCH
זורקת את השגיאה מחדש לאפליקציה הקוראת. לרישום השגיאה ב-DB ועדיין להציג שגיאה למשתמש.
RAISERROR
RAISERROR('מלאי אזל', 16, 1)יוצרת שגיאה יזומה מותאמת. לעצירת תהליך כשיש בעיה עסקית (מלאי 0) גם ללא שגיאת SQL.
ERROR_MESSAGE()
INSERT INTO Logs VALUES (ERROR_MESSAGE())
הטקסט המלא של השגיאה בתוך CATCH. לשמירת פרטי השגיאה בטבלת לוג.
ERROR_NUMBER()
IF ERROR_NUMBER() = 2627 PRINT 'כפילות'
קוד מספרי של השגיאה. לטיפול שונה לפי סוג השגיאה — כפילות vs. הרשאות vs. חיבור.
ERROR_LINE()
SELECT ERROR_LINE() AS FailedAtRow
שורת הקוד שנכשלה. חוסכת שעות ניפוי באגים בפרוצדורות ארוכות.
WHILE
WHILE @Counter < 10 BEGIN ... SET @Counter = @Counter + 1 END
לולאה ב-SQL. לעיבוד נתונים צעד-צעד במקרים שאי אפשר בפעולת SET-based.
BREAK
IF @Value = 0 BREAK
עוצר לולאת WHILE מיד. ליציאת חירום מלולאה כשנמצאת שגיאה.
CONTINUE
IF @Skip = 1 CONTINUE
דולג לסיבוב הבא בלולאה. לדילוג על רשומות לא רלוונטיות מבלי לעצור הכל.
RETURN
IF @User IS NULL RETURN
עוצר פרוצדורה שלמה. "שומר סף" — אם הקלט לא תקין, צא מיד.
WAITFOR DELAY
WAITFOR DELAY '00:00:10'
השהיה של X זמן. לעיבוד נתונים במנות עם הפסקות כדי לא לחנוק את השרת.
WAITFOR TIME
WAITFOR TIME '02:00:00'
ממתינה עד שעה ספציפית. להרצת משימות תחזוקה ליליות.
הרשאות אבטחה מתקדמות
CREATE LOGIN
CREATE LOGIN JohnDoe WITH PASSWORD = 'Pass1'
יוצרת גישה לשרת ה-SQL. הצעד הראשון — "כניסה לבניין". עדיין לא מאפשרת גישה לנתונים.
CREATE USER
CREATE USER JohnDoeUser FOR LOGIN JohnDoe
מחברת לוגין למסד נתונים ספציפי. "תג עובד לחדר מסוים" אחרי הכניסה לבניין.
ALTER LOGIN / USER
ALTER LOGIN JohnDoe DISABLE
עריכה או הקפאת משתמש. לביטול גישה מיידי כשעובד עוזב, ללא מחיקת ההיסטוריה.
CREATE ROLE
CREATE ROLE DataAnalysts
יוצרת קבוצת הרשאות. מנהל אחת ולא כל משתמש בנפרד — סקיילבילי ונקי.
DENY
DENY DELETE ON Orders TO DataAnalysts
חסימה אגרסיבית. דורסת כל GRANT אחר — גם אם חבר בקבוצת מנהלים.
EXECUTE AS
EXECUTE AS USER = 'AdminUser'
התחזות למשתמש אחר לבדיקת הרשאות. ללא צורך בסיסמה של אותו משתמש.
REVERT
REVERT
מבטלת EXECUTE AS וחוזרת לזהות המקורית. ללא התנתקות והתחברות מחדש.
תחזוקה ואופטימיזציה
WITH (NOLOCK)
SELECT * FROM MassiveTable WITH (NOLOCK)
קריאה ללא המתנה לנעילות. מהיר יותר אבל עלול להחזיר נתונים שבאמצע עדכון (Dirty Read).
OPTION (RECOMPILE)
SELECT * FROM Users WHERE ID = @Var OPTION (RECOMPILE)
מכריח חישוב מחדש של תוכנית הריצה. לשאילתות איטיות שהשרת "זכר" תוכנית גרועה.
OPTION (MAXDOP)
SELECT * FROM HugeTable OPTION (MAXDOP 1)
מגביל כמה ליבות מעבד תשתמש השאילתה. מונע שאילתה אחת מלשתק את כל השרת.
SET NOCOUNT ON
SET NOCOUNT ON; UPDATE Users SET Name='X'
משתיק הודעות "שורות שהושפעו". מפחית עומס ברוחב פס בפרוצדורות עם אלפי פעולות.
DBCC CHECKDB
DBCC CHECKDB('MyDatabase')סורק את שלמות כל הנתונים בדיסק. מוצא נזקי קבצים (Corruption). להריץ כל שבוע.
UPDATE STATISTICS
UPDATE STATISTICS Customers
מעדכן את המפה הסטטיסטית של הנתונים. מחיש שאילתות שהתחילו לרוץ לאט.
ALTER INDEX REBUILD
ALTER INDEX ALL ON Customers REBUILD
בונה אינדקס מאפס. מסיר פרגמנטציה ומחזיר מהירות "יום ראשון". ננעל הטבלה.
ALTER INDEX REORGANIZE
ALTER INDEX idx_name ON Customers REORGANIZE
סידור מחדש ללא נעילה. עדין מ-REBUILD. לתחזוקה יומיומית שקטה.
DBCC FREEPROCCACHE
DBCC FREEPROCCACHE
מוחק כל זיכרון תוכניות ריצה. לאיפוס שרת שמתנהג לאט בגלל Plan Sniffing.
DBCC SHRINKDATABASE
DBCC SHRINKDATABASE('MyDB')מכווץ את DB ומחזיר שטח דיסק למערכת ההפעלה. אחרי מחיקת כמויות גדולות.
תאריכים מתקדמים ואזורי זמן
DATEFROMPARTS()
SELECT DATEFROMPARTS(2023, 10, 31)
בונה תאריך מ-3 מספרים נפרדים (שנה, חודש, יום). למיזוג שדות נפרדים מאפליקציה.
TIMEFROMPARTS()
SELECT TIMEFROMPARTS(14, 30, 0, 0, 0)
בונה שעה מ-5 מספרים. למערכות שעון נוכחות עם שדות שעות/דקות נפרדים.
DATETIME2FROMPARTS()
SELECT DATETIME2FROMPARTS(2023,1,1,12,0,0,0,0)
בונה DATETIME2 מלא (שנה+חודש+יום+שעה+דקה+שנייה) בדיוק גבוה.
SYSUTCDATETIME()
SELECT SYSUTCDATETIME()
שעה נוכחית בשעון UTC אוניברסלי. חובה לאפליקציות גלובליות עם משתמשים בכל העולם.
AT TIME ZONE
SELECT OrderDate AT TIME ZONE 'Israel Standard Time'
ממירה תאריך לאזור זמן ספציפי כולל שעון קיץ/חורף. ללא חישובים ידניים.
SWITCHOFFSET()
SELECT SWITCHOFFSET(OrderDate, '+03:00')
שינוי קיזוז אזור זמן ידני. לתצוגת תאריך בשעון מקומי של המשתמש.
TODATETIMEOFFSET()
SELECT TODATETIMEOFFSET(GETDATE(), '+02:00')
הופכת תאריך פשוט ל-DateTimeOffset עם זהות אזור זמן. לחישובים גלובליים.
DATENAME()
SELECT DATENAME(month, GETDATE())
מחזירה שם טקסטואלי (January, Monday). הבדל מ-DATEPART שמחזיר מספר.
טבלאות זמניות, GUIDs ורצפים
CREATE TABLE #Temp
CREATE TABLE #UserStats (ID INT, Score INT)
טבלה זמנית מקומית — חיה רק בחיבור הנוכחי. לחישובי ביניים ללא זיהום DB.
CREATE TABLE ##Temp
CREATE TABLE ##GlobalCache (Data VARCHAR(100))
טבלה זמנית גלובלית — נגישה לכל החיבורים. לשיתוף נתוני Cache בין משתמשים.
DECLARE @Table TABLE
DECLARE @MyList TABLE (EmpID INT)
טבלה בזיכרון RAM בלבד. מהיר מאוד לכמויות קטנות — ללא כתיבה לדיסק.
NEWID()
INSERT INTO Users (ID) VALUES (NEWID())
מייצרת GUID ייחודי בעולם. לזהויות בטוחות שלא ניתן לנחש ולמיזוג נתונים בין שרתים.
NEWSEQUENTIALID()
DEFAULT NEWSEQUENTIALID()
GUID רץ בסדר עולה. פותר בעיית ביצועים של NEWID — שומר על האינדקס מסודר.
CREATE SEQUENCE
CREATE SEQUENCE InvoiceSeq AS INT START WITH 1
מונה עצמאי שלא קשור לטבלה. מספר רץ שמספר טבלאות יכולות לשתף.
NEXT VALUE FOR
INSERT INTO Invoices (ID) VALUES (NEXT VALUE FOR InvoiceSeq)
שולפת את המספר הבא מ-SEQUENCE. לחשבוניות מספרות בלי כפילויות.
FORMATMESSAGE()
FORMATMESSAGE('ברוך הבא %s, קוד: %i', 'דן', 123)הזרקת משתנים לתוך שבלונת טקסט. לבניית הודעות שגיאה קריאות ומרכזיות.
פונקציות חלון מתקדמות
PERCENT_RANK()
PERCENT_RANK() OVER(ORDER BY Score)
דירוג יחסי באחוזים (0-1). "מעל 85% מהכיתה" — ללא צורך לדעת גודל הכיתה.
CUME_DIST()
CUME_DIST() OVER(ORDER BY Score)
התפלגות מצטברת — חלק מהנתונים שנמוך/שווה לערך הנוכחי. לעקומות Bell.
PERCENTILE_CONT()
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
חציון עם אינטרפולציה. מחזיר ממוצע בין שני ערכים אם אין ערך מדויק באמצע.
PERCENTILE_DISC()
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
חציון מדויק — מחזיר רק ערך שבאמת קיים במסד. ללא אינטרפולציה.
GROUPING()
SELECT Dept, GROUPING(Dept), SUM(Salary) GROUP BY ROLLUP(Dept)
מחזיר 1 לשורות סיכום של ROLLUP. להבדיל בין NULL אמיתי לשורת "סך הכל".
GROUPING_ID()
GROUPING_ID(Dep, Job) GROUP BY CUBE(Dep, Job)
קוד בינארי לזיהוי סוג הקיבוץ ב-CUBE. לסידור שורות מטריצה מרובת ממדים.
CHOOSE()
SELECT CHOOSE(MonthNum, 'ינו', 'פבר', 'מרץ'...) FROM Sales
בוחר ערך לפי אינדקס מרשימה. CHOOSE(2,...) = פריט שני. תחליף קצר ל-CASE.
ANY_VALUE()
SELECT Category, ANY_VALUE(ManagerName) FROM Items GROUP BY Category
מחזיר ערך כלשהו מהקבוצה. כשכולם זהים — מהיר יותר מ-MAX(Name) כטריק עקיף.
NULLS FIRST / LAST
ORDER BY DeliveryDate ASC NULLS LAST
שולט היכן NULL מופיע במיון. מונע קפיצת ריקים לראש הדוח. תמיכה: Postgres/MySQL.
Dynamic PIVOT
EXEC(@DynamicPivotQuery)
PIVOT שבונה את עצמו דינמית לפי הנתונים. לטבלאות ציר שהעמודות לא ידועות מראש.
שכפול נתונים ועבודה עם טבלאות (Advanced DML)
SELECT TOP WITH TIES
SELECT TOP 3 WITH TIES * FROM Orders ORDER BY Amount DESC
מחזיר את ה-TOP N כולל קשרות (Ties). אם 4 שורות שוות במקום ה-3 — כולן יוחזרו.
INSERT OR IGNORE
INSERT OR IGNORE INTO Users VALUES(1, 'Dan')
מוסיפה שורה, ואם כבר קיימת — מתעלמת בשקט ללא שגיאה. SQLite/MySQL.
INSERT OR REPLACE
INSERT OR REPLACE INTO Users VALUES(1, 'Dan')
מוסיפה שורה, ואם קיימת — מחליפה אותה. שורטקט ל-Upsert ב-SQLite.
UPDATE FROM (JOIN)
UPDATE t SET t.Price = s.NewPrice FROM Products t JOIN Source s ON t.ID = s.ID
עדכון טבלה לפי נתונים מטבלה אחרת עם JOIN. לסנכרון מחירים ממקור חיצוני.
DELETE WITH JOIN
DELETE t FROM Orders t JOIN Cancelled c ON t.ID = c.ID
מחיקת שורות לפי תנאי מטבלה מקושרת. מחיקה מדויקת יותר מ-NOT IN.
OUTPUT (SQL Server)
DELETE FROM Orders OUTPUT DELETED.* WHERE Status='Old'
מחזיר את השורות שנמחקו/עודכנו בזמן הפעולה. לתיעוד ולוג שינויים.
RETURNING (PostgreSQL)
INSERT INTO Users(Name) VALUES('Dan') RETURNING IDמחזיר ערכים מהשורה שנוצרה. מקבילת OUTPUT ב-PostgreSQL — לקבלת ID חדש.
EXCEPT ALL
SELECT ID FROM A EXCEPT ALL SELECT ID FROM B
חיסור עם כפילויות — לא מסיר כפילויות מהתוצאה. מהיר יותר מ-EXCEPT רגיל.
INTERSECT ALL
SELECT ID FROM A INTERSECT ALL SELECT ID FROM B
חיתוך עם כפילויות — שומר על כל המופעים. PostgreSQL/מנועים מתקדמים.
TABLESAMPLE
SELECT * FROM Customers TABLESAMPLE (10 PERCENT)
מחזיר דגימה אקראית של X אחוז מהטבלה. לבדיקות מהירות על טבלאות ענק.
ניתוח נתונים מתקדם (Analytics)
RATIO_TO_REPORT()
RATIO_TO_REPORT(Amount) OVER()
אחוז מסך הכל. כל שורה מחולקת בסכום הכולל — לאחוז מכירות מסך החברה.
SUM OVER (Running Total)
SUM(Amount) OVER(ORDER BY Date ROWS UNBOUNDED PRECEDING)
סכום מצטבר (Running Total) — כל שורה מסכמת את כל מה שלפניה.
AVG OVER (Moving Avg)
AVG(Amount) OVER(ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
ממוצע נע של 7 ימים. לחלקת עונתיות ומגמות בנתוני מכירות.
MIN/MAX OVER (Running)
MAX(Price) OVER(ORDER BY Date ROWS UNBOUNDED PRECEDING)
מקסימום/מינימום רץ — "כמה היה השיא עד עכשיו?" בכל נקודת זמן.
COUNT DISTINCT (Approx)
APPROX_COUNT_DISTINCT(CustomerID)
ספירת ייחודיים מקורבת (שגיאה ~2%). מהיר פי 10 מ-COUNT DISTINCT על Big Data.
LISTAGG() / GROUP_CONCAT
LISTAGG(Name, ', ') WITHIN GROUP (ORDER BY Name)
איחוד שורות לרשימה מסודרת. Oracle/PostgreSQL — STRING_AGG עם מיון פנימי.
PIVOT עם SUM/COUNT/AVG
SUM(Amount) FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])
Pivot עם פונקציות שונות — לא רק SUM. לדוחות רב-ממדיים גמישים.
CROSS APPLY
SELECT * FROM Customers c CROSS APPLY (SELECT TOP 3 * FROM Orders o WHERE o.CustID=c.ID) t
מריץ תת-שאילתה לכל שורה. כמו JOIN אבל עם קורלציה — ל-TOP N לכל לקוח.
OUTER APPLY
SELECT * FROM Customers c OUTER APPLY (...) t
כמו CROSS APPLY אבל מחזיר גם שורות ללא התאמה (כמו LEFT JOIN).
GENERATE_SERIES()
SELECT * FROM GENERATE_SERIES(1, 12)
מייצרת רצף מספרים/תאריכים. לקלנדרים, לדוחות ללא "חורים" בתאריכים חסרים.
Full-Text Search וחיפוש טקסט
CONTAINS()
SELECT * FROM Articles WHERE CONTAINS(Body, 'SQL AND performance')
חיפוש Full-Text מתקדם. מהיר מ-LIKE כי משתמש באינדקס טקסט. תומך AND/OR/NOT.
FREETEXT()
SELECT * FROM Docs WHERE FREETEXT(Content, 'database optimization')
חיפוש חופשי — מוצא גם הטיות ומילים קשורות. פחות מדויק מ-CONTAINS אבל גמיש יותר.
CONTAINSTABLE()
SELECT k.RANK, t.* FROM Docs t JOIN CONTAINSTABLE(Docs,*,'SQL') k ON t.ID=k.[KEY]
כמו CONTAINS אבל מחזיר דירוג רלוונטיות (Rank). למנועי חיפוש עם ציון התאמה.
FREETEXTTABLE()
JOIN FREETEXTTABLE(Docs,*,'query') k ON t.ID=k.[KEY]
FREETEXT עם דירוג. לחיפוש גוגל-סטייל — מחזיר תוצאות ממוינות לפי רלוונטיות.
MATCH...AGAINST (MySQL)
WHERE MATCH(title, body) AGAINST('SQL performance')Full-Text Search ב-MySQL. מצריך אינדקס FULLTEXT על העמודות. מהיר מ-LIKE מאוד.
LIKE עם Escape
WHERE Name LIKE '100\%' ESCAPE '\'
חיפוש תו % כמחרוזת ממש (לא wildcard). לחיפוש "100%" כטקסט מילולי.
REGEXP / RLIKE (MySQL)
WHERE Email REGEXP '^[a-zA-Z]+@'
חיפוש עם Regular Expression. לתיקוף פורמטים מורכבים — אימיילים, טלפונים, קודי מוצר.
SIMILAR TO (PostgreSQL)
WHERE Code SIMILAR TO '[A-Z]{3}-[0-9]+'Regular Expression בסגנון SQL Standard. ב-PostgreSQL — בין LIKE ל-REGEXP.
אינדקסים מתקדמים ואופטימיזציה
CREATE FILTERED INDEX
CREATE INDEX idx_active ON Users(Name) WHERE Status='Active'
אינדקס על תת-קבוצה בלבד. קטן יותר ומהיר יותר — לשאילתות שמסננות תמיד לפי תנאי קבוע.
CREATE COLUMNSTORE INDEX
CREATE COLUMNSTORE INDEX idx_col ON Sales(Date, Amount)
אינדקס עמודתי לאנליטיקה. מהיר פי 10-100 על GROUP BY/SUM ב-Data Warehouse.
CREATE COVERING INDEX
CREATE INDEX idx_cov ON Orders(CustID) INCLUDE(Amount, Date)
אינדקס עם עמודות נוספות ב-INCLUDE. מונע קריאה לטבלה עצמה — שאילתה מהירה יותר.
EXPLAIN / EXECUTION PLAN
EXPLAIN SELECT * FROM Users WHERE ID=1
מראה את תוכנית הריצה של השאילתה. לזיהוי Table Scan מיותר ואינדקסים חסרים.
SET STATISTICS IO ON
SET STATISTICS IO ON; SELECT ...
מציג כמה דפי דיסק נקראו. לאיתור שאילתות עם I/O גבוה שגורמות לאיטיות.
SET STATISTICS TIME ON
SET STATISTICS TIME ON; SELECT ...
מציג זמן CPU ופרסור. לניתוח ביצועים מדויק של שאילתות איטיות.
WITH (FORCESCAN)
SELECT * FROM T WITH (FORCESCAN)
מכריח Table Scan גם כשיש אינדקס. למקרים שסריקה מלאה מהירה יותר (טבלות קטנות).
WITH (FORCESEEK)
SELECT * FROM T WITH (FORCESEEK)
מכריח שימוש באינדקס. כשהמנוע טועה ובוחר Scan — FORCESEEK מתקן זאת.
sp_updatestats
EXEC sp_updatestats
מעדכן סטטיסטיקות של כל הטבלאות ב-DB. לאחר טעינת נתונים גדולה.
Stored Procedures ו-Functions מתקדמות
CREATE OR ALTER PROCEDURE
CREATE OR ALTER PROCEDURE GetOrders AS ...
יוצרת פרוצדורה או מחליפה קיימת בפקודה אחת. ללא DROP-CREATE נפרד.
פרוצדורה עם OUTPUT
CREATE PROCEDURE Calc (@In INT, @Out INT OUTPUT) AS SET @Out = @In * 2
פרוצדורה שמחזירה ערך דרך פרמטר OUTPUT. לחישובים שצריכים להחזיר יותר מערך אחד.
פרוצדורה עם DEFAULT
CREATE PROCEDURE GetData (@Days INT = 30) AS ...
פרמטר עם ברירת מחדל. אפשר לקרוא ללא פרמטר — ייקח אוטומטית 30 יום.
Inline Table-Valued Function
CREATE FUNCTION GetOrders(@ID INT) RETURNS TABLE AS RETURN (SELECT * FROM Orders WHERE CustID=@ID)
פונקציה שמחזירה טבלה. ניתן לעשות JOIN עליה כמו טבלה רגילה. מהיר מאוד.
Multi-Statement TVF
CREATE FUNCTION GetData() RETURNS @t TABLE(...) AS BEGIN INSERT INTO @t ... RETURN END
פונקציה עם לוגיקה מורכבת שמחזירה טבלה. גמישה יותר מ-Inline אבל איטית יותר.
EXEC עם משתנים
EXEC (@SQL)
הרצת SQL דינמי מתוך מחרוזת. לבניית שאילתות גמישות בזמן ריצה.
sp_executesql
EXEC sp_executesql @SQL, '@ID INT', @ID=5
SQL דינמי עם פרמטרים. בטוח יותר מ-EXEC רגיל — מונע SQL Injection.
sys.procedures
SELECT * FROM sys.procedures
רשימת כל הפרוצדורות ב-DB. לתיעוד ובדיקת קוד קיים.
Triggers — טריגרים
CREATE TRIGGER (AFTER INSERT)
CREATE TRIGGER trg_ins ON Orders AFTER INSERT AS ...
טריגר שרץ אוטומטית אחרי INSERT. לאירועים אוטומטיים — שליחת מייל, עדכון לוג.
CREATE TRIGGER (INSTEAD OF)
CREATE TRIGGER trg_v ON MyView INSTEAD OF INSERT AS ...
מחליף פעולה אחרת. לאפשר INSERT על View שלא תומך בו ישירות.
INSERTED / DELETED טבלות
SELECT * FROM INSERTED; SELECT * FROM DELETED
טבלות וירטואליות בתוך טריגר עם הנתונים שהוכנסו/נמחקו. לשמירת ערכים ישנים.
DISABLE TRIGGER
DISABLE TRIGGER trg_ins ON Orders
משביתה טריגר זמנית. לייבוא מסיבי של נתונים ללא הפעלת לוגיקה אוטומטית.
DDL TRIGGER
CREATE TRIGGER trg_ddl ON DATABASE FOR DROP_TABLE AS ...
טריגר על פעולות DDL כמו DROP TABLE. למניעת מחיקות שגויות בסביבת ייצור.
Partitioning — חלוקת טבלאות
CREATE PARTITION FUNCTION
CREATE PARTITION FUNCTION pf_year(INT) AS RANGE RIGHT FOR VALUES(2021,2022,2023)
מגדירה טווחים לחלוקה. לפיצול טבלה ענקית לחלקים לפי שנה/חודש.
CREATE PARTITION SCHEME
CREATE PARTITION SCHEME ps_year AS PARTITION pf_year ALL TO ([PRIMARY])
מקצה Filegroups לכל partition. השלב השני ביצירת טבלה מחולקת.
CREATE TABLE ON Partition
CREATE TABLE Sales(Date INT) ON ps_year(Date)
יצירת טבלה מחולקת. SQL מנהל אוטומטית היכן שורה מאוחסנת לפי הערך.
Partition Elimination
SELECT * FROM Sales WHERE Year=2023
SQL קורא רק את ה-partition הרלוונטי ומדלג על שאר. מהיר פי 10+ על טבלאות ענק.
ALTER TABLE SWITCH PARTITION
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesArchive
העברת partition שלם בין טבלאות ב-O(1). לארכוב מיידי של נתונים ישנים.
Temporal Tables — טבלאות עם היסטוריה
CREATE SYSTEM-VERSIONED TABLE
CREATE TABLE Products(ID INT, Price DECIMAL, ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(ValidFrom,ValidTo)) WITH (SYSTEM_VERSIONING=ON)
טבלה שמנהלת היסטוריה אוטומטית. SQL שומר אוטומטית כל גרסה ישנה.
FOR SYSTEM_TIME AS OF
SELECT * FROM Products FOR SYSTEM_TIME AS OF '2023-01-01'
מחזיר מה היו הנתונים בתאריך מסוים. "Time Travel" — לראות DB כפי שהיה בעבר.
FOR SYSTEM_TIME BETWEEN
FOR SYSTEM_TIME BETWEEN '2022-01-01' AND '2023-01-01'
כל השינויים בטווח תאריכים. לביקורת ולמעקב אחר שינויי נתונים לאורך זמן.
FOR SYSTEM_TIME ALL
SELECT * FROM Products FOR SYSTEM_TIME ALL
כל הגרסאות של כל השורות אי פעם. להצגת ציר זמן מלא של שינויים.
ALTER TABLE OFF System Versioning
ALTER TABLE Products SET (SYSTEM_VERSIONING=OFF)
מבטל ניהול היסטוריה. לניקוי נתונים היסטוריים ולשינויי מבנה.
Graph Database ו-Hierarchies
CREATE NODE TABLE
CREATE TABLE Person(ID INT) AS NODE
טבלת קשרים (SQL Graph). לייצוג רשתות חברתיות, גרפים ותלויות.
CREATE EDGE TABLE
CREATE TABLE Knows AS EDGE
טבלת קשת בגרף. מחברת בין שני Nodes — Person KNOWS Person.
MATCH (Graph Query)
SELECT p1.Name, p2.Name FROM Person p1, Knows, Person p2 WHERE MATCH(p1-(Knows)->p2)
שאילתת גרף — מי מכיר את מי. לניתוח רשתות חברתיות וגרפי תלויות.
Recursive CTE - Tree
WITH RECURSIVE tree AS(SELECT ID, ParentID, 0 lvl FROM Cats WHERE ParentID IS NULL UNION ALL SELECT c.ID, c.ParentID, t.lvl+1 FROM Cats c JOIN tree t ON c.ParentID=t.ID)
עץ היררכי רקורסיבי. לקטגוריות מקוננות, ארגונים, BOM עצי מוצרים.
PATH עם CTE
'' AS path ... t.path+'/'+c.Name
בניית נתיב מלא בעץ (Root/Branch/Leaf). להצגת Breadcrumb ולניתוח עומק עץ.
גיאוגרפיה ומיקום (Spatial)
geometry/geography טיפוסים
Location geography
טיפוסי נתונים מיוחדים לנקודות GPS, קווים ופולגונים. למפות ומרחקים.
STDistance()
Loc1.STDistance(Loc2)
מרחק בין שתי נקודות גיאוגרפיות במטרים. לשליחת המשלוח הקרוב ביותר.
STContains()
Zone.STContains(Point)
בדיקה אם נקודה נמצאת בתוך אזור. לשאלה "האם הלקוח בתוך אזור המשלוח?".
STIntersects()
Area1.STIntersects(Area2)
האם שני אזורים נחתכים. לזיהוי חפיפה בין אזורי שירות.
geography::Point()
geography::Point(31.77, 35.21, 4326)
יוצרת נקודת GPS (קו רוחב, קו אורך). לאחסון מיקומי לקוחות וסניפים.
Row-Level Security ו-Dynamic Masking
CREATE SECURITY POLICY
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_filter(SalesRep) ON Orders
Row Level Security — כל עובד רואה רק את השורות שלו. אוטומטי, שקוף לאפליקציה.
CREATE FUNCTION (RLS Predicate)
CREATE FUNCTION fn_filter(@User nvarchar(50)) RETURNS TABLE AS RETURN SELECT 1 r WHERE @User=USER_NAME()
פונקציית סינון ל-RLS. מחזירה 1 (מותר) או 0 (חסום) לכל שורה.
Dynamic Data Masking
Email VARCHAR(100) MASKED WITH (FUNCTION='email()')
מסתיר נתונים רגישים אוטומטית: a***@***.com. אנליסטים רואים מסוכה, DBA רואה הכל.
ADD MASK
ALTER TABLE Customers ALTER COLUMN Phone ADD MASKED WITH (FUNCTION='partial(0,"XXX-XXX-",4)')
הוספת מסוכה לעמודה קיימת. XXX-XXX-1234 — רואים רק 4 ספרות אחרונות.
GRANT UNMASK
GRANT UNMASK TO AdminUser
מאפשרת למשתמש מסוים לראות נתונים ממשיים. לנוהל "צורך לדעת".
Always Encrypted ו-Transparent Data Encryption
Always Encrypted
SSN ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=CEK, ENCRYPTION_TYPE=Deterministic)
הצפנה ברמת עמודה. אפילו DBA לא יכול לראות את הנתונים — רק האפליקציה.
Transparent Data Encryption
ALTER DATABASE MyDB SET ENCRYPTION ON
הצפנת כל קבצי ה-DB בדיסק. מגן מפני גניבת קבצים פיזיים מהשרת.
BACKUP ENCRYPTION
BACKUP DATABASE MyDB TO DISK='backup.bak' WITH ENCRYPTION
הצפנת קבצי גיבוי. ללא המפתח — הגיבוי חסר ערך לתוקף.
OPEN SYMMETRIC KEY
OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD='pass'
פותחת מפתח הצפנה לשימוש. לפני הצפנה/פענוח ידני של נתונים.
ENCRYPTBYKEY / DECRYPTBYKEY
ENCRYPTBYKEY(KEY_GUID('MyKey'), CreditCard)הצפנה ופענוח ידני של שדה. לאחסון מוצפן עם שחזור לפי צורך.
High Availability ו-Replication
ALTER DATABASE SET RECOVERY FULL
ALTER DATABASE MyDB SET RECOVERY FULL
מצב שחזור מלא — שומר את כל לוג הפעולות. חובה לסביבת ייצור עם SLA גבוה.
BACKUP LOG
BACKUP LOG MyDB TO DISK='log.bak'
גיבוי לוג הטרנזקציות. מאפשר שחזור לנקודת זמן מדויקת — Point in Time Recovery.
RESTORE DATABASE WITH STANDBY
RESTORE DATABASE MyDB WITH STANDBY='undo.bak'
שחזור במצב Read-Only עם אפשרות להמשיך. לשרתי גיבוי שניתן לקרוא מהם.
RESTORE WITH NORECOVERY
RESTORE DATABASE MyDB WITH NORECOVERY
שחזור שמאפשר המשך גיבויים נוספים. לשרשרת שחזור מגיבויים מרובים.
sys.dm_exec_requests
SELECT * FROM sys.dm_exec_requests
כל השאילתות הרצות כרגע בשרת. לאיתור שאילתות תקועות ו-blocking.
DMVs — Dynamic Management Views
sys.dm_exec_query_stats
SELECT TOP 10 * FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC
השאילתות הכי איטיות מאז הפעלת השרת. לאיתור בעיות ביצועים.
sys.dm_index_usage_stats
SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks=0
אינדקסים שלא בשימוש. מועמדים למחיקה — חוסכים מקום ומאיצים כתיבה.
sys.dm_missing_index_details
SELECT * FROM sys.dm_db_missing_index_details
אינדקסים חסרים שהשרת ממליץ עליהם. לשיפור ביצועים מהיר.
sys.dm_exec_connections
SELECT * FROM sys.dm_exec_connections
כל החיבורים הפעילים לשרת. לניטור עומס ואיתור חיבורים תקועים.
sys.dm_os_wait_stats
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
סוגי המתנה הכי ארוכים בשרת. לאיתור צווארי בקבוק (CPU, I/O, locks).
Cloud SQL ו-Modern Features
EXTERNAL DATA SOURCE
CREATE EXTERNAL DATA SOURCE BlobStorage WITH (TYPE=BLOB_STORAGE, LOCATION='https://...')
מגדיר מקור נתונים חיצוני (Azure Blob, S3). לקריאת קבצים מהענן ישירות ב-SQL.
OPENROWSET (Blob)
SELECT * FROM OPENROWSET(BULK 'data.csv', DATA_SOURCE='BlobStorage', FORMAT='CSV') t
קריאת CSV/Parquet ישירות מהענן. ב-Azure Synapse ו-SQL Server 2019+.
PolyBase
SELECT * FROM ExternalTable
שאילתות על Hadoop, Azure, S3 כאילו הם טבלאות SQL רגילות. לBig Data Analytics.
OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=OtherServer;uid=sa;pwd=p').DB.dbo.Tableגישה חד-פעמית לשרת SQL אחר ללא Linked Server. לשאילתות נדירות בין שרתים.
LINKED SERVER
EXEC sp_addlinkedserver @server='OtherServer'
חיבור קבוע לשרת חיצוני. לשאילתות סדירות בין שרתים ובין מסדי נתונים שונים.
סכימות, Meta-data וניהול אובייקטים
CREATE SCHEMA
CREATE SCHEMA Sales
יוצרת namespace לאובייקטים. Sales.Orders vs HR.Employees — ארגון וניהול הרשאות.
INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.TABLES
מידע על כל הטבלאות, עמודות וקשרים. תקני ANSI — עובד בכל מנועי SQL.
sys.columns
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('Orders')כל העמודות של טבלה עם טיפוסי נתונים. לתיעוד ולבניית SQL דינמי.
sys.foreign_keys
SELECT * FROM sys.foreign_keys
כל המפתחות הזרים ב-DB. לתיעוד הקשרים ולגרף ERD אוטומטי.
sp_help
EXEC sp_help 'Orders'
מידע מלא על טבלה — עמודות, אינדקסים, מפתחות. "תעודת הזהות" של הטבלה.