📌 Ne Zaman Kullanılır?

• ✅ Kurumsal uygulamalar, .NET ekosistemi, enterprise, BI/raporlama
• ⚠️ Linux'ta sınırlı (Docker ile çalışır), lisans maliyeti yüksek
• ❌ Küçük proje (MySQL/PostgreSQL yeterli), startup MVP

Önerilen Kullanım: Enterprise + ASP.NET Core + kurumsal raporlama Alternatifler: PostgreSQL (ücretsiz, güçlü), MySQL (web app)

Microsoft SQL Server (MSSQL)

Kurulum (Docker ile Linux)

docker run -d \
  --name mssql-dev \
  -e 'ACCEPT_EULA=Y' \
  -e 'MSSQL_SA_PASSWORD=Strong!Pass123' \
  -e 'MSSQL_PID=Developer' \
  -p 1433:1433 \
  mcr.microsoft.com/mssql/server:2022-latest

# sqlcmd ile baglan / Connect with sqlcmd
docker exec -it mssql-dev /opt/mssql-tools18/bin/sqlcmd \
  -S localhost -U sa -P 'Strong!Pass123' -C

sqlcmd CLI

# Temel kullanim / Basic usage
sqlcmd -S localhost -U sa -P 'Strong!Pass123'

# Dosyadan sorgu calistir / Execute from file
sqlcmd -S localhost -U sa -P 'Strong!Pass123' -i script.sql -o output.txt

-- sqlcmd icinde / Inside sqlcmd
SELECT name FROM sys.databases;
GO

USE myapp;
GO

SELECT * FROM users;
GO

SSMS (SQL Server Management Studio)

SSMS, SQL Server ile calismak için en yaygin kullanilan grafiksel aractir. Sorgu yazma, veritabani yönetimi, performans izleme ve hata ayiklama islemleri tek bir arayuzden yapılabilir.

Object Explorer

Object Explorer, sunucudaki tüm nesneleri agac yapisinda gosterir:

• Databases -- Veritabanlari, tablolar, view'lar, stored procedure'ler
• Security -- Login'ler, roller, kimlik bilgileri
• Server Objects -- Linked server, backup device
• SQL Server Agent -- Zamanlanmis gorevler (job'lar)

Bir tabloya sag tiklayip Select Top 1000 Rows seçeneğini kullanarak hizlica veri goruntuleyebilirsiniz. Script Table As ile CREATE, INSERT, UPDATE, DELETE script'leri otomatik olusturulabilir.

New Query (Sorgu Penceresi)

Ctrl + N          -- Yeni sorgu penceresi ac
F5                -- Sorguyu calistir
Ctrl + L          -- Tahmini execution plan goster
Ctrl + M          -- Gercek execution plan dahil et
Ctrl + Shift + E  -- Sonuclari dosyaya kaydet

Sorgu penceresinde birden fazla sorgu yazabilir, istediginiz bolumu secip F5 ile sadece secili kismi calistirabilirsiniz.

Activity Monitor

Ctrl + Alt + A ile Activity Monitor acilir. Canli olarak sunlari gosterir:

• Processes -- Aktif bağlantı ve oturumlari listeler
• Resource Waits -- Hangi kaynaklarda bekleme oldugunu gosterir
• Data File I/O -- Disk okuma/yazma istatistikleri
• Recent Expensive Queries -- En cok kaynak tuketen son sorgular

Execution Plan

Execution plan, SQL Server'in bir sorguyu nasil calistirdigini gorsel olarak gosterir:

• Estimated Execution Plan (Ctrl + L) -- Sorguyu calistirmadan tahmini plani gosterir
• Actual Execution Plan (Ctrl + M) -- Sorguyu calistirir ve gercek istatistiklerle birlikte plani gosterir

Plan uzerinde kalin oklar yüksek veri akisini, ince oklar düşük veri akisini gosterir. Uyari isareti olan operatorleri (missing index, implicit conversion gibi) oncelikli olarak inceleyin.

T-SQL Farkliliklari (T-SQL Differences from MySQL)

Feature	MySQL	MSSQL (T-SQL)
Auto-increment	AUTO_INCREMENT	IDENTITY(1,1)
Limit rows	LIMIT 10	TOP 10 or OFFSET...FETCH
Current date	NOW()	GETDATE() / SYSDATETIME()
If null	IFNULL(a, b)	ISNULL(a, b)
String concat	CONCAT(a, b)	CONCAT(a, b) or a + b
Boolean	BOOLEAN	BIT
Substring	SUBSTRING(s,1,3)	SUBSTRING(s,1,3)
Auto UUID	N/A	NEWID()
Temp table	CREATE TEMPORARY TABLE	CREATE TABLE #temp
IF EXISTS	DROP TABLE IF EXISTS	IF OBJECT_ID('t') IS NOT NULL DROP TABLE t

CRUD

-- CREATE DATABASE & TABLE
CREATE DATABASE myapp;
GO
USE myapp;
GO

CREATE TABLE users (
    id BIGINT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL,
    email NVARCHAR(150) NOT NULL UNIQUE,
    age INT DEFAULT 0,
    is_active BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

-- INSERT
INSERT INTO users (name, email, age) VALUES (N'Ali Yilmaz', N'ali@test.com', 28);

-- SELECT (TOP ile / with TOP)
SELECT TOP 10 * FROM users ORDER BY created_at DESC;

-- OFFSET-FETCH (pagination)
SELECT * FROM users
ORDER BY id
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- UPDATE
UPDATE users SET age = 29 WHERE email = 'ali@test.com';

-- DELETE
DELETE FROM users WHERE id = 3;

-- MERGE (UPSERT)
MERGE INTO users AS target
USING (VALUES (N'Ali', N'ali@test.com', 30)) AS source (name, email, age)
ON target.email = source.email
WHEN MATCHED THEN UPDATE SET name = source.name, age = source.age
WHEN NOT MATCHED THEN INSERT (name, email, age) VALUES (source.name, source.email, source.age);

Stored Procedures

CREATE PROCEDURE GetUsersByAge
    @MinAge INT,
    @MaxAge INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT id, name, email, age
    FROM users
    WHERE age BETWEEN @MinAge AND @MaxAge
    ORDER BY age;
END;
GO

EXEC GetUsersByAge @MinAge = 20, @MaxAge = 30;

Advanced T-SQL

CTE (Common Table Expression)

CTE, karmasik sorguları okunabilir alt parcalara boler. WITH anahtar kelimesiyle tanimlanir.

-- Basit CTE
WITH ActiveUsers AS (
    SELECT id, name, email, age
    FROM users
    WHERE is_active = 1
)
SELECT * FROM ActiveUsers WHERE age > 25;

-- Recursive CTE -- hiyerarsi (ornegin kategori agaci)
CREATE TABLE categories (
    id INT PRIMARY KEY,
    name NVARCHAR(100),
    parent_id INT NULL REFERENCES categories(id)
);

WITH CategoryTree AS (
    -- Anchor: kok kategoriler
    SELECT id, name, parent_id, 0 AS depth, CAST(name AS NVARCHAR(500)) AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: alt kategoriler
    SELECT c.id, c.name, c.parent_id, ct.depth + 1,
           CAST(ct.path + N' > ' + c.name AS NVARCHAR(500))
    FROM categories c
    INNER JOIN CategoryTree ct ON c.parent_id = ct.id
)
SELECT id, name, depth, path
FROM CategoryTree
ORDER BY path;

Window Functions

Window function'lar, satirlar uzerinde gruplama yapmadan hesaplama yapar.

-- ROW_NUMBER, RANK, DENSE_RANK farki
SELECT
    name, age,
    ROW_NUMBER() OVER (ORDER BY age DESC) AS row_num,    -- benzersiz siralama
    RANK()       OVER (ORDER BY age DESC) AS rank_num,    -- esitlerde bosluk birakir
    DENSE_RANK() OVER (ORDER BY age DESC) AS dense_num    -- esitlerde bosluk birakmaz
FROM users;

-- PARTITION BY ile departman bazinda siralama
SELECT
    department, name, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

-- LAG / LEAD -- onceki ve sonraki satir degerleri
SELECT
    name, created_at,
    LAG(created_at)  OVER (ORDER BY created_at) AS prev_signup,
    LEAD(created_at) OVER (ORDER BY created_at) AS next_signup,
    DATEDIFF(DAY,
        LAG(created_at) OVER (ORDER BY created_at),
        created_at
    ) AS days_since_prev
FROM users;

-- SUM, AVG ile cumulative/running toplamlar
SELECT
    name, age,
    SUM(age) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS running_total,
    AVG(age) OVER () AS overall_avg
FROM users;

PIVOT / UNPIVOT

Satirlari sutunlara (veya tersine) donusturur. Raporlama sorgularinda cok kullanislidir.

-- Ornek tablo: aylik satislar
CREATE TABLE sales (
    employee NVARCHAR(50),
    month NVARCHAR(20),
    amount DECIMAL(10,2)
);

-- PIVOT: satirdan sutuna
SELECT employee, [Ocak], [Subat], [Mart]
FROM (
    SELECT employee, month, amount FROM sales
) AS src
PIVOT (
    SUM(amount) FOR month IN ([Ocak], [Subat], [Mart])
) AS pvt;

-- UNPIVOT: sutundan satira
SELECT employee, month, amount
FROM (
    SELECT employee, [Ocak], [Subat], [Mart] FROM pivoted_sales
) AS src
UNPIVOT (
    amount FOR month IN ([Ocak], [Subat], [Mart])
) AS unpvt;

MERGE (Detayli)

MERGE, tek bir ifadede INSERT, UPDATE ve DELETE islemlerini bir arada yapar (upsert).

MERGE INTO products AS target
USING staging_products AS source
ON target.sku = source.sku

WHEN MATCHED AND source.is_deleted = 1 THEN
    DELETE

WHEN MATCHED THEN
    UPDATE SET
        target.name = source.name,
        target.price = source.price,
        target.updated_at = SYSDATETIME()

WHEN NOT MATCHED BY TARGET THEN
    INSERT (sku, name, price, created_at)
    VALUES (source.sku, source.name, source.price, SYSDATETIME())

WHEN NOT MATCHED BY SOURCE THEN
    DELETE

OUTPUT $action, INSERTED.*, DELETED.*;

TRY...CATCH Error Handling

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO orders (user_id, total) VALUES (1, 250.00);
    INSERT INTO order_items (order_id, product_id, qty) VALUES (SCOPE_IDENTITY(), 5, 2);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    -- Hata bilgilerini yakala
    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_SEVERITY()  AS ErrorSeverity,
        ERROR_STATE()     AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE()      AS ErrorLine,
        ERROR_MESSAGE()   AS ErrorMessage;

    -- Hatayi tekrar firlat (istege bagli)
    THROW;
END CATCH;

CROSS APPLY / OUTER APPLY

APPLY, her satir için bir tablo degerli fonksiyon veya alt sorgu calistirir. JOIN'e benzer ama her satir için bagimsiz deger uretilebilir.

-- CROSS APPLY: her kullanici icin son 3 siparisi getir
SELECT u.name, o.order_date, o.total
FROM users u
CROSS APPLY (
    SELECT TOP 3 order_date, total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY order_date DESC
) o;

-- OUTER APPLY: siparisi olmayan kullanicilari da gosterir (LEFT JOIN gibi)
SELECT u.name, o.order_date, o.total
FROM users u
OUTER APPLY (
    SELECT TOP 3 order_date, total
    FROM orders
    WHERE orders.user_id = u.id
    ORDER BY order_date DESC
) o;

Index Turleri

Clustered vs Non-Clustered

Özellik	Clustered Index	Non-Clustered Index
Fiziksel siralama	Veriyi fiziksel olarak siralar	Ayri bir yapida saklar
Tablo basina	En fazla 1 adet	En fazla 999 adet
Yaprak dugum	Verinin kendisi	Veri satirina pointer
Varsayilan	PRIMARY KEY ile otomatik olusur	Manuel olusturulur
Boyut	Tablo boyutu kadar	Daha küçük
Ideal kullanım	Range sorguları, ORDER BY	WHERE, JOIN kosullari

-- Clustered index (genellikle PK ile otomatik olusur)
CREATE CLUSTERED INDEX IX_Users_Id ON users(id);

-- Non-clustered index
CREATE NONCLUSTERED INDEX IX_Users_Email ON users(email);

-- Composite index (birden fazla kolon)
CREATE NONCLUSTERED INDEX IX_Users_Name_Age ON users(name, age);

Filtered Index

Sadece belirli kosulu saglayan satirlari indexler. Daha küçük boyut, daha hızlı erisim.

-- Sadece aktif kullanicilari indexle
CREATE NONCLUSTERED INDEX IX_Users_Active
ON users(email, name)
WHERE is_active = 1;

Columnstore Index

Analitik (OLAP) sorgular için optimize edilmistir. Verileri sutun bazinda saklar, yüksek sikisstirma orani sağlar.

-- Non-clustered columnstore
CREATE NONCLUSTERED COLUMNSTORE INDEX IX_Sales_Columnstore
ON sales(employee, month, amount);

-- Clustered columnstore (tum tablo icin, veri ambari senaryolari)
CREATE CLUSTERED COLUMNSTORE INDEX IX_FactSales
ON fact_sales;

Covering Index (INCLUDE)

Sorgunun ihtiyac duydugu tüm kolonlari index icinde tasir. Key Lookup ihtiyacini ortadan kaldirir.

-- email ile arama yap, name ve age degerlerini de index'ten oku
CREATE NONCLUSTERED INDEX IX_Users_Email_Cover
ON users(email)
INCLUDE (name, age);

Index Kullanım Analizi

-- Hangi index'ler kullaniliyor, hangileri kullanilmiyor?
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    i.name AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
ORDER BY s.user_seeks + s.user_scans DESC;

-- SQL Server'in onerdigi eksik index'ler
SELECT
    d.statement AS table_name,
    d.equality_columns,
    d.inequality_columns,
    d.included_columns,
    s.user_seeks,
    s.avg_user_impact
FROM sys.dm_db_missing_index_details d
INNER JOIN sys.dm_db_missing_index_groups g ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats s ON g.index_group_handle = s.group_handle
ORDER BY s.user_seeks * s.avg_user_impact DESC;

Transactions ve Isolation Levels

BEGIN TRAN / COMMIT / ROLLBACK

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- Her sey basariliysa
COMMIT TRANSACTION;

-- Sorun varsa (genellikle TRY...CATCH icinde)
-- ROLLBACK TRANSACTION;

Isolation Levels

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performans
READ UNCOMMITTED	Olabilir	Olabilir	Olabilir	En hızlı
READ COMMITTED (varsayılan)	Engellenir	Olabilir	Olabilir	Iyi
REPEATABLE READ	Engellenir	Engellenir	Olabilir	Orta
SERIALIZABLE	Engellenir	Engellenir	Engellenir	En yavas
SNAPSHOT	Engellenir	Engellenir	Engellenir	Iyi (tempdb kullanir)

-- Isolation level degistirme
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Snapshot isolation icin veritabani ayari
ALTER DATABASE myapp SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

NOLOCK Hint

NOLOCK (veya READ UNCOMMITTED), kilitlenmemis veriyi okur. Dirty read riski tasir.

-- NOLOCK kullanimi
SELECT * FROM users WITH (NOLOCK) WHERE age > 25;

-- Ne zaman kullanilir:
--   Raporlama sorgularinda, %100 dogruluk gerekmeyen durumlarda
-- Ne zaman kullanilmaz:
--   Finansal islemler, siparis olusturma, stok kontrolu

Deadlock Tespiti ve Cozumu

-- Aktif kilitleri gorme
SELECT
    request_session_id AS session_id,
    resource_type,
    resource_description,
    request_mode,
    request_status
FROM sys.dm_tran_locks
WHERE request_status = 'WAIT';

-- Deadlock graph yakalama (Extended Events ile)
CREATE EVENT SESSION DeadlockCapture ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = N'Deadlocks.xel')
WITH (STARTUP_STATE = ON);
ALTER EVENT SESSION DeadlockCapture ON SERVER STATE = START;

Deadlock'u azaltmak için:

• Tablolara erisim sirasini tüm sorgularda ayni tutun
• Transaction'lari kisa tutun
• Uygun index'ler kullanarak lock surelerini azaltin
• READ COMMITTED SNAPSHOT isolation kullanmayi degerlendirin

Views, Triggers ve Functions

Views

-- Basit view
CREATE VIEW vw_ActiveUsers AS
SELECT id, name, email, age
FROM users
WHERE is_active = 1;

-- View'i sorgulama
SELECT * FROM vw_ActiveUsers WHERE age > 25;

-- Indexed (Materialized) View -- buyuk tablolarda performans kazanci
CREATE VIEW vw_OrderSummary
WITH SCHEMABINDING  -- zorunlu
AS
SELECT
    o.user_id,
    COUNT_BIG(*) AS order_count,  -- COUNT_BIG zorunlu
    SUM(o.total) AS total_amount
FROM dbo.orders o
GROUP BY o.user_id;
GO

-- Indexed view uzerinde unique clustered index olustur
CREATE UNIQUE CLUSTERED INDEX IX_vw_OrderSummary
ON vw_OrderSummary(user_id);

Triggers

-- AFTER trigger: islem sonrasi calisir
CREATE TRIGGER trg_Users_Audit
ON users
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO audit_log (table_name, action, record_id, changed_at)
    SELECT 'users', 'UPDATE', i.id, SYSDATETIME()
    FROM INSERTED i;
END;
GO

-- INSTEAD OF trigger: islemi yakalar, yerine kendi kodunu calistirir
CREATE TRIGGER trg_Users_SoftDelete
ON users
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE users SET is_active = 0
    WHERE id IN (SELECT id FROM DELETED);
END;
GO

-- DDL trigger: yapi degisikliklerini izler (CREATE, ALTER, DROP)
CREATE TRIGGER trg_PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT 'Tablo silme islemi engellendi. DBA ile iletisime gecin.';
    ROLLBACK;
END;
GO

Functions

-- Scalar function: tek bir deger dondurur
CREATE FUNCTION fn_GetFullName(@FirstName NVARCHAR(50), @LastName NVARCHAR(50))
RETURNS NVARCHAR(101)
AS
BEGIN
    RETURN @FirstName + N' ' + @LastName;
END;
GO

SELECT dbo.fn_GetFullName(N'Ali', N'Yilmaz') AS full_name;

-- Inline table-valued function: tek SELECT ile tablo dondurur (en performansli)
CREATE FUNCTION fn_GetUsersByCity(@City NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT id, name, email
    FROM users
    WHERE city = @City AND is_active = 1
);
GO

SELECT * FROM fn_GetUsersByCity(N'Istanbul');

-- Multi-statement table-valued function: birden fazla islem ile tablo olusturur
CREATE FUNCTION fn_GetUserStats()
RETURNS @Stats TABLE (
    metric NVARCHAR(50),
    value INT
)
AS
BEGIN
    INSERT INTO @Stats VALUES (N'Total Users', (SELECT COUNT(*) FROM users));
    INSERT INTO @Stats VALUES (N'Active Users', (SELECT COUNT(*) FROM users WHERE is_active = 1));
    INSERT INTO @Stats VALUES (N'Avg Age', (SELECT AVG(age) FROM users));
    RETURN;
END;
GO

SELECT * FROM fn_GetUserStats();

Performance

Execution Plan Okuma

-- Tahmini plan (sorguyu calistirmaz)
SET SHOWPLAN_XML ON;
GO
SELECT * FROM users WHERE age > 25;
GO
SET SHOWPLAN_XML OFF;
GO

-- Gercek plan ile birlikte calistirma
SET STATISTICS XML ON;
SELECT * FROM users WHERE age > 25;
SET STATISTICS XML OFF;

Dikkat edilecek operatorler:

• Table Scan -- Index yok, tüm tablo okunuyor (kotu)
• Index Scan -- Index var ama tamami taraniyor (genellikle kotu)
• Index Seek -- Index uzerinden dogrudan erisim (iyi)
• Key Lookup -- Non-clustered index'te bulunamayan kolon için clustered index'e gidis (covering index ile cozulur)
• Sort -- Siralama operasyonu, bellek tuketir (index ile engellenebilir)

SET STATISTICS IO/TIME

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM users WHERE email = 'ali@test.com';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Cikti ornegi:
-- Table 'users'. Scan count 1, logical reads 3, physical reads 0
-- SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms
-- logical reads sayisini dusurmeye calisin

Query Store

Query Store, sorgu planlarini ve performans istatistiklerini veritabani icinde saklar. Performans gerileme analizi için cok degerlidir.

-- Query Store'u etkinlestir
ALTER DATABASE myapp SET QUERY_STORE = ON;
ALTER DATABASE myapp SET QUERY_STORE (
    OPERATION_MODE = READ_WRITE,
    DATA_FLUSH_INTERVAL_SECONDS = 900,
    INTERVAL_LENGTH_MINUTES = 60,
    MAX_STORAGE_SIZE_MB = 1000
);

-- En cok kaynak tuketen sorgular
SELECT TOP 10
    qt.query_sql_text,
    rs.avg_duration,
    rs.avg_cpu_time,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan p ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

Index Seek vs Scan

• Seek: Index agacinda dogrudan hedefe gider. WHERE kosulu index'e uygunsa olusur.
• Scan: Index'in tamamini bastan sona okur. Büyük tablolarda cok yavas olabilir.

-- Index seek olusturur (email uzerinde index varsa)
SELECT * FROM users WHERE email = 'ali@test.com';

-- Index scan olusturur (fonksiyon index kullanimini engeller)
SELECT * FROM users WHERE UPPER(email) = 'ALI@TEST.COM';

-- Cozum: computed column + index
ALTER TABLE users ADD email_upper AS UPPER(email);
CREATE INDEX IX_Users_EmailUpper ON users(email_upper);

Key Lookup Problemi

Non-clustered index sorguyu karsilamak için yeterli kolon icermiyorsa, SQL Server clustered index'e geri doner (Key Lookup). Bu ekstra I/O demektir.

-- Key Lookup olusturur: index(email) var ama name, age index'te yok
SELECT name, age FROM users WHERE email = 'ali@test.com';

-- Cozum: covering index ile Key Lookup'i ortadan kaldir
CREATE INDEX IX_Users_Email_Cover ON users(email) INCLUDE (name, age);

En Yavas Sorgular (DMV)

SELECT TOP 20
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    qs.execution_count,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY avg_elapsed_time DESC;

Güvenlik

Login vs User

• Login: Server seviyesinde kimlik dogrulamasi (CREATE LOGIN)
• User: Database seviyesinde yetkilendirme (CREATE USER)

-- SQL Login olusturma
CREATE LOGIN app_login WITH PASSWORD = 'SecureP@ss123!';

-- Database user olusturma
USE myapp;
CREATE USER app_user FOR LOGIN app_login;

-- Yetki verme
GRANT SELECT, INSERT, UPDATE ON dbo.users TO app_user;
DENY DELETE ON dbo.users TO app_user;

Server Roles

Role	Açıklama
sysadmin	Tam yetki, her seyi yapabilir
serveradmin	Sunucu yapilandirmasini degistirebilir
securityadmin	Login'leri ve yetkileri yonetir
dbcreator	Veritabani olusturabilir
bulkadmin	BULK INSERT calistirabilir

Database Roles

Role	Açıklama
db_owner	Veritabani uzerinde tam yetki
db_datareader	Tüm tablolardan SELECT yapabilir
db_datawriter	Tüm tablolara INSERT, UPDATE, DELETE yapabilir
db_ddladmin	DDL islemleri yapabilir (CREATE, ALTER, DROP)
db_securityadmin	Yetkileri yonetebilir

-- Kullaniciyi role ekle
ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;

Row Level Security (RLS)

Satirlara kullanici bazinda erisim kontrolu sağlar.

-- Filter fonksiyonu olustur
CREATE FUNCTION fn_UserFilter(@UserId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (
    SELECT 1 AS result
    WHERE @UserId = CAST(SESSION_CONTEXT(N'UserId') AS INT)
       OR IS_MEMBER('db_owner') = 1
);
GO

-- Security policy olustur
CREATE SECURITY POLICY UserPolicy
ADD FILTER PREDICATE dbo.fn_UserFilter(user_id) ON dbo.orders
WITH (STATE = ON);

-- Oturum icin kullanici belirle
EXEC sp_set_session_context @key = N'UserId', @value = 42;
SELECT * FROM orders;  -- sadece user_id = 42 olan satirlar gorulur

Dynamic Data Masking

Hassas verileri yetkisiz kullanicilardan gizler. Veri veritabaninda acik saklanir, sadece sorgulama aninda maskelenir.

ALTER TABLE users ALTER COLUMN email
    ADD MASKED WITH (FUNCTION = 'email()');

ALTER TABLE users ALTER COLUMN phone
    ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)');

ALTER TABLE users ALTER COLUMN salary
    ADD MASKED WITH (FUNCTION = 'default()');

-- Maskelemeyi gorebilme yetkisi verme
GRANT UNMASK TO report_user;

TDE (Transparent Data Encryption)

Veritabani dosyalarini (.mdf, .ldf) disk uzerinde sifreler. Uygulama kodu degismez.

-- Master key olustur
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterK3y!Str0ng';

-- Sertifika olustur
CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Certificate';

-- Database encryption key olustur
USE myapp;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert;

-- Sifrelemeyi etkinlestir
ALTER DATABASE myapp SET ENCRYPTION ON;

-- Sertifikayi yedekleyin! Aksi halde yedekten donus yapilamaz.
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Backup\TDECert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backup\TDECert_Key.pvk',
    ENCRYPTION BY PASSWORD = 'CertB@ckupP@ss!'
);

Always Encrypted

Veriler client tarafinda sifrelenir, SQL Server sifresiz veriyi hicbir zaman gormez. Hassas veriler (TC kimlik, kredi karti vb.) için idealdir.

-- Column master key ve column encryption key SSMS uzerinden olusturulur.
-- Ardindan kolon sifrelenir:
ALTER TABLE users ALTER COLUMN tc_kimlik NVARCHAR(11)
ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = CEK_Auto1,
    ENCRYPTION_TYPE = DETERMINISTIC,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
);

SQL Server Audit

-- Server audit olustur
CREATE SERVER AUDIT MainAudit
TO FILE (FILEPATH = 'C:\AuditLogs\', MAXSIZE = 100 MB);
ALTER SERVER AUDIT MainAudit WITH (STATE = ON);

-- Database audit specification
USE myapp;
CREATE DATABASE AUDIT SPECIFICATION UserTableAudit
FOR SERVER AUDIT MainAudit
ADD (SELECT, INSERT, UPDATE, DELETE ON dbo.users BY PUBLIC);
ALTER DATABASE AUDIT SPECIFICATION UserTableAudit WITH (STATE = ON);

-- Audit loglarini okuma
SELECT event_time, action_id, succeeded, server_principal_name, statement
FROM fn_get_audit_file('C:\AuditLogs\MainAudit*.sqlaudit', DEFAULT, DEFAULT);

Backup ve Recovery

Backup Turleri

-- Full Backup: veritabaninin tamami
BACKUP DATABASE myapp
TO DISK = 'C:\Backup\myapp_full.bak'
WITH COMPRESSION, INIT, NAME = 'myapp Full Backup';

-- Differential Backup: son full backup'tan bu yana degisen veriler
BACKUP DATABASE myapp
TO DISK = 'C:\Backup\myapp_diff.bak'
WITH DIFFERENTIAL, COMPRESSION, INIT;

-- Transaction Log Backup: son log backup'tan bu yana olan log kayitlari
BACKUP LOG myapp
TO DISK = 'C:\Backup\myapp_log.trn'
WITH COMPRESSION, INIT;

Backup Stratejisi

Önerilen strateji: Full (gunluk) + Differential (saatlik) + Log (15 dakikada bir)

Bu strateji ile maksimum 15 dakikalik veri kaybi yasanir. Kritik sistemlerde log backup araligi 5 dakikaya dusurulmelidir.

RESTORE Komutu

-- Full backup'tan geri yukleme
RESTORE DATABASE myapp
FROM DISK = 'C:\Backup\myapp_full.bak'
WITH REPLACE, NORECOVERY;

-- Differential ekleme
RESTORE DATABASE myapp
FROM DISK = 'C:\Backup\myapp_diff.bak'
WITH NORECOVERY;

-- Son log backup'i uygulama
RESTORE LOG myapp
FROM DISK = 'C:\Backup\myapp_log.trn'
WITH RECOVERY;

Point-in-Time Recovery

Belirli bir zamana kadar olan verileri geri yukler. Yanlis bir DELETE veya UPDATE sonrasi kurtarma için kullanilir.

-- Once full backup'i NORECOVERY ile yukle
RESTORE DATABASE myapp
FROM DISK = 'C:\Backup\myapp_full.bak'
WITH NORECOVERY;

-- Log backup'i belirli bir zamana kadar uygula
RESTORE LOG myapp
FROM DISK = 'C:\Backup\myapp_log.trn'
WITH STOPAT = '2026-04-09T14:30:00', RECOVERY;

.NET / C# ile Kullanım (Entity Framework)

// Connection string (appsettings.json)
// "ConnectionStrings": {
//   "Default": "Server=localhost;Database=myapp;User Id=sa;Password=Strong!Pass123;TrustServerCertificate=True;"
// }

// Model
public class User
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Age { get; set; }
    public bool IsActive { get; set; }
    public DateTime CreatedAt { get; set; }
}

// DbContext
public class AppDbContext : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options.UseSqlServer(connectionString);
}

// CRUD
using var db = new AppDbContext();

// Create
db.Users.Add(new User { Name = "Ali", Email = "ali@test.com", Age = 28 });
db.SaveChanges();

// Read
var users = db.Users.Where(u => u.Age > 25).OrderBy(u => u.Name).ToList();
var user = db.Users.FirstOrDefault(u => u.Email == "ali@test.com");

// Update
user.Age = 29;
db.SaveChanges();

// Delete
db.Users.Remove(user);
db.SaveChanges();

Sik Kullanilan Komutlar (Quick Reference)

Command	Description
SELECT name FROM sys.databases;	Veritabanlarini listele
SELECT * FROM INFORMATION_SCHEMA.TABLES;	Tablolari listele
sp_help 'users';	Tablo detaylarini göster
sp_who2;	Aktif baglantilari göster
DBCC CHECKDB;	Veritabani butunlugunu kontrol et
BACKUP DATABASE myapp TO DISK='backup.bak';	Yedek al

Tips ve Best Practices

Index Stratejisi

• Clustered index: her zaman PRIMARY KEY uzerinde
• Non-clustered index: WHERE, JOIN ve ORDER BY'da sik kullanilan kolonlar
• Foreign key kolonlarini mutlaka indexleyin
• Cok fazla index yazma performansini dusurur, dengeyi koruyun

Naming Conventions

Nesne	Convention	Örnek
Tablo	PascalCase	dbo.UserOrders
Kolon	PascalCase veya snake_case	CreatedAt veya created_at
Index	IX_Tablo_Kolon	IX_Users_Email
Foreign Key	FK_Child_Parent	FK_Orders_Users
Stored Procedure	usp_IslemAdi	usp_GetUsersByAge
View	vw_Aciklama	vw_ActiveUsers
Trigger	trg_Tablo_Olay	trg_Users_Audit

Temp Table vs Table Variable

Özellik	#TempTable	@TableVariable
Kapsam	Oturum boyunca	Batch/SP boyunca
Index	Olusturulabilir	Yalnizca PRIMARY KEY
Istatistik	Olusturulur	Olusturulmaz
Transaction	ROLLBACK'ten etkilenir	Etkilenmez
Kullanım	Büyük veri setleri	Küçük veri setleri (<100 satir)

-- Temp table: buyuk veri setleri icin
CREATE TABLE #TempUsers (
    id INT,
    name NVARCHAR(100)
);
INSERT INTO #TempUsers SELECT id, name FROM users WHERE age > 25;

-- Table variable: kucuk veri setleri icin
DECLARE @RecentUsers TABLE (
    id INT,
    name NVARCHAR(100)
);
INSERT INTO @RecentUsers SELECT TOP 10 id, name FROM users ORDER BY created_at DESC;

Diger Oneriler

-- Her stored procedure'un basina ekleyin
SET NOCOUNT ON;  -- "xx rows affected" mesajini kapatir, performans kazandirir

-- sp_ prefix'i kullanmayin! SQL Server once master db'de arar.
-- Kotu:  CREATE PROCEDURE sp_GetUsers
-- Iyi:   CREATE PROCEDURE usp_GetUsers

-- SELECT * yerine kolon adi belirtin
-- Kotu:  SELECT * FROM users
-- Iyi:   SELECT id, name, email FROM users

-- Sorgularda NOLOCK'u dikkatli kullanin (yukarida aciklandi)
-- WHERE kosulunda fonksiyon kullanmaktan kacinin (index kullanimini engeller)

---

Ilgili Rehberler

Veritabani

• Veritabani Genel Bakış
• MySQL
• PostgreSQL
• SQLite
• MongoDB
• Redis

Diger Kategoriler

• Backend Genel Bakış
• Frontend Genel Bakış
• DevOps Genel Bakış
• Teknoloji Genel Bakış