-- Non-SARGable: Function on column
WHERE DATEPART(year, OrderDate) = 2024
WHERE UPPER(CustomerName) = 'JOHN'
WHERE OrderAmount + 100 > 500

-- SARGable: Preserve column
WHERE OrderDate >= '2024-01-01' AND OrderDate < '2025-01-01'
WHERE CustomerName = 'john' COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE OrderAmount > 400

-- Bad: Implicit conversion (varchar column compared to int)
WHERE VarcharColumn = 12345

-- Good: Match types exactly
WHERE VarcharColumn = '12345'

-- Check for implicit conversions in execution plan
-- Look for CONVERT_IMPLICIT warnings

-- Inefficient: OR on different columns
SELECT * FROM Orders
WHERE CustomerID = 1 OR ProductID = 2

-- Better: UNION for OR optimization
SELECT * FROM Orders WHERE CustomerID = 1
UNION ALL
SELECT * FROM Orders WHERE ProductID = 2 AND CustomerID <> 1

-- EXISTS: Best for semi-joins (checking existence)
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID)

-- IN: Good for small static lists
SELECT * FROM Products WHERE CategoryID IN (1, 2, 3)

-- JOIN: Best when you need data from both tables
SELECT c.*, o.OrderDate
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID

-- First execution with CustomerID=1 (10 rows) creates plan
-- Subsequent execution with CustomerID=999 (1M rows) uses same plan
CREATE PROCEDURE GetOrders @CustomerID INT AS
    SELECT * FROM Orders WHERE CustomerID = @CustomerID

CREATE PROCEDURE GetOrders @CustomerID INT AS
    SELECT * FROM Orders
    WHERE CustomerID = @CustomerID
    OPTION (RECOMPILE)
-- Best for: Infrequent queries, highly variable data distribution

-- Optimize for specific value
OPTION (OPTIMIZE FOR (@CustomerID = 1))

-- Optimize for unknown (average statistics)
OPTION (OPTIMIZE FOR UNKNOWN)

CREATE PROCEDURE GetOrders @CustomerID INT AS
BEGIN
    DECLARE @LocalID INT = @CustomerID
    SELECT * FROM Orders WHERE CustomerID = @LocalID
END
-- Hides parameter from optimizer, similar to OPTIMIZE FOR UNKNOWN

EXEC sys.sp_query_store_set_hints
    @query_id = 12345,
    @hints = N'OPTION (RECOMPILE)'
-- Apply hints without code changes

-- Enable Parameter Sensitive Plan optimization
ALTER DATABASE YourDB SET COMPATIBILITY_LEVEL = 160
-- Automatically creates multiple plans based on parameter values

-- Large difference indicates statistics problem
-- Check if stats need updating:
UPDATE STATISTICS TableName WITH FULLSCAN

-- Or enable auto-update:
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS ON

SELECT
    CONVERT(DECIMAL(18,2), migs.avg_user_impact) AS AvgImpact,
    mid.statement AS TableName,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_groups mig
JOIN sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY migs.avg_user_impact DESC

DBCC SHOW_STATISTICS('TableName', 'IndexName')

-- Update all statistics on table
UPDATE STATISTICS TableName

-- Update with full scan (most accurate)
UPDATE STATISTICS TableName WITH FULLSCAN

-- Update specific statistics
UPDATE STATISTICS TableName StatisticsName

-- Enable async auto-update (better for OLTP)
ALTER DATABASE YourDB SET AUTO_UPDATE_STATISTICS_ASYNC ON