SQL Server و T-SQL (به‌همراه مدیریت پایگاه‌داده)SQL Server & T-SQL (with Database Administration)

Plenty of backend job ads carry one short line next to Java and Spring: "advanced SQL Server". Behind it: writing T-SQL, reading execution plans, explaining why a 20 ms query now takes 20 seconds, and — with no dedicated DBA — owning backup/restore and knowing how many minutes of data a dead disk costs you.

sql-mastery, rdbms-tuning and oracle-postgres-dialects built the SQL foundation, the tuning mindset and two dialects (PostgreSQL and Oracle). This chapter adds the third dialect as a first-class citizen and then goes where those chapters do not: database administration — the work nobody sees while the system is up, and everybody needs the moment it goes down.

1. The big picture

-- PostgreSQL has no equivalent knob; optimizer behaviour ships with the engine version
SHOW server_version;

SELECT name, compatibility_level FROM sys.databases;
ALTER DATABASE app SET COMPATIBILITY_LEVEL = 160;

Every step of the execution path has its own diagnostic tool: the plan cache through DMVs, the buffer pool through wait stats, the log through sys.dm_db_log_space_usage. Every database has at least one data file (.mdf, then .ndf) inside a filegroup plus one log file (.ldf). The transaction log is the accountant's journal: every change is written there before the data page changes (write-ahead logging), so after a power cut the engine can undo half-finished work.

2. The T-SQL dialect: the differences that burn you

Ninety percent of your standard SQL just works. The other ten percent either fails to compile, or — worse — compiles and quietly returns the wrong answer.

2.1 Paging

SELECT id, total FROM orders ORDER BY total DESC LIMIT 10;
SELECT id, total FROM orders ORDER BY id OFFSET 40 LIMIT 20;

SELECT TOP (10) id, total FROM orders ORDER BY total DESC;      -- T-SQL specific
SELECT id, total FROM orders ORDER BY id
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;                          -- ANSI, since 2012

TOP has two extras LIMIT lacks: TOP (10) PERCENT and TOP (10) WITH TIES. Oracle's equivalent is FETCH FIRST 10 ROWS ONLY.

2.2 Auto-numbering: IDENTITY vs SEQUENCE

CREATE TABLE customer (id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, full_name text);
CREATE SEQUENCE order_no_seq START 1000 INCREMENT 1 CACHE 50;
SELECT nextval('order_no_seq');

CREATE TABLE customer (id bigint IDENTITY(1,1) PRIMARY KEY, full_name nvarchar(200));
CREATE SEQUENCE order_no_seq AS bigint START WITH 1000 INCREMENT BY 1 CACHE 50;
SELECT NEXT VALUE FOR order_no_seq;

IDENTITY is welded to the column and you cannot supply values by hand without SET IDENTITY_INSERT customer ON;. A SEQUENCE is a standalone object, can be shared across tables, and its value can be fetched before the INSERT. (Oracle has had both since 12c.)

Three IDENTITY traps interviewers ask about.

1. @@IDENTITY returns the last identity generated in the session — even one produced by a trigger on a different table, i.e. the wrong key. Always use SCOPE_IDENTITY(), or better, OUTPUT.
2. Identity is not transactional; a rollback does not give the number back. If invoice numbers must be gap-free, identity is the wrong tool.
3. After an abrupt shutdown the identity cache can jump by a thousand. If that matters, create a SEQUENCE with NO CACHE.

2.3 NULL, strings and dates

SELECT COALESCE(nickname, full_name, 'unknown') FROM customer;

SELECT ISNULL(nickname, N'unknown') FROM customer;              -- exactly two arguments, non-ANSI
SELECT COALESCE(nickname, full_name, N'unknown') FROM customer; -- ANSI

DECLARE @v varchar(5) = NULL;
SELECT ISNULL(@v, 'default value');    -- 'defau'  <- silently truncated
SELECT COALESCE(@v, 'default value');  -- 'default value'

Two date and formatting traps.

FORMAT(t, 'yyyy-MM-dd') goes through the CLR, runs row by row and can be an order of magnitude slower than CONVERT with a style number. Display formatting belongs in the application layer.

3. Temp tables, table variables, CTEs and TVPs

A temp table (#t) is a real scratch pad: created in tempdb, it has statistics and you can index it afterwards. A table variable (@t) is a small whiteboard: lighter, but the optimizer knows almost nothing about its contents. A CTE stores nothing at all; it is just a name for a subquery.

4. APPLY: what PostgreSQL calls LATERAL

JOIN says "match up two lists". APPLY says "for each left row, run this subquery again with that row's values" — like walking to each customer's own shelf instead of fetching the whole warehouse.

SELECT c.id, o.id AS order_id FROM customer c
CROSS JOIN LATERAL (SELECT id FROM orders o WHERE o.customer_id = c.id
                    ORDER BY o.created_at DESC LIMIT 3) o;

SELECT c.id, o.id AS order_id FROM customer c
CROSS APPLY (SELECT TOP (3) id FROM orders o WHERE o.customer_id = c.id
             ORDER BY o.created_at DESC) o;

CROSS APPLY behaves like INNER JOIN, OUTER APPLY like LEFT JOIN. Three places it genuinely wins: top-N per group (above — with an index on (customer_id, created_at DESC) only the first three rows of each group are read), calling a table-valued function per row, and removing repeated expressions via CROSS APPLY (SELECT o.total - o.discount AS net) calc(net).

A caution: APPLY almost always compiles to a nested loop. If the left side has ten million rows and the subquery has no suitable index, you scan ten million times.

5. Window functions, PIVOT and UNPIVOT

Window function syntax is identical across all three dialects and is covered in depth in sql-mastery; here is the SQL Server-specific trap.

PIVOT is T-SQL only; PostgreSQL has no built-in equivalent and uses FILTER or crosstab from the tablefunc extension:

SELECT customer_id,
       count(*) FILTER (WHERE status = 'NEW')  AS "NEW",
       count(*) FILTER (WHERE status = 'PAID') AS "PAID"
FROM orders GROUP BY customer_id;

-- T-SQL PIVOT: SELECT ... FROM src PIVOT (COUNT(status) FOR status IN ([NEW],[PAID])) p;
-- the more portable and often faster equivalent, conditional aggregation:
SELECT customer_id,
       COUNT(CASE WHEN status = 'NEW'  THEN 1 END) AS [NEW],
       COUNT(CASE WHEN status = 'PAID' THEN 1 END) AS [PAID]
FROM orders GROUP BY customer_id;

PIVOT columns must be fixed at compile time; a dynamic list means dynamic SQL and injection risk. Senior judgment: pivot in the reporting layer.

6. MERGE and OUTPUT

INSERT INTO product AS p (sku, name, price)
SELECT sku, name, price FROM staging_product
ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price;

MERGE INTO dbo.product WITH (HOLDLOCK) AS tgt
USING (SELECT sku, name, price FROM dbo.staging_product) AS src
   ON tgt.sku = src.sku
WHEN MATCHED AND (tgt.name <> src.name OR tgt.price <> src.price)
   THEN UPDATE SET tgt.name = src.name, tgt.price = src.price
WHEN NOT MATCHED BY TARGET
   THEN INSERT (sku, name, price) VALUES (src.sku, src.name, src.price)
WHEN NOT MATCHED BY SOURCE AND tgt.is_active = 1
   THEN UPDATE SET tgt.is_active = 0;

OUTPUT is the equivalent of RETURNING, but stronger: it exposes both inserted and deleted and can write straight into a table.

WITH upd AS (
  UPDATE orders SET status = 'PAID' WHERE id = $1 AND status = 'NEW'
  RETURNING id, status)
INSERT INTO order_audit (order_id, new_status) SELECT id, status FROM upd;

UPDATE dbo.orders SET status = N'PAID', paid_at = SYSUTCDATETIME()
OUTPUT inserted.id, deleted.status, inserted.status, SYSUTCDATETIME()
  INTO dbo.order_audit (order_id, old_status, new_status, changed_at)
WHERE id = @id AND status = N'NEW';

7. JSON

CREATE TABLE event (id bigserial PRIMARY KEY, payload jsonb NOT NULL);
CREATE INDEX ix_event_payload ON event USING GIN (payload jsonb_path_ops);
SELECT payload ->> 'orderId' FROM event WHERE payload @> '{"type":"ORDER_PAID"}';

CREATE TABLE event (
  id bigint IDENTITY PRIMARY KEY,
  payload nvarchar(max) NOT NULL CONSTRAINT ck_event_json CHECK (ISJSON(payload) = 1));
-- before 2025 the only way to seek: a PERSISTED computed column plus a normal index
ALTER TABLE event ADD event_type AS JSON_VALUE(payload, '$.type') PERSISTED;
CREATE INDEX ix_event_type ON event(event_type);

SELECT JSON_VALUE(payload, '$.orderId') AS order_id      -- scalar; JSON_QUERY returns objects/arrays
FROM event WHERE JSON_VALUE(payload, '$.type') = N'ORDER_PAID';

SQL Server 2025 introduces a native json type stored as optimized binary rather than text, with a dedicated JSON index, plus JSON_CONTAINS, JSON_OBJECTAGG and JSON_ARRAYAGG (JSON_OBJECT/JSON_ARRAY arrived in 2022). On older versions the "PERSISTED computed column + index" pattern is the standard interview answer.

8. Stored procedures and procedural T-SQL

-- in PostgreSQL the same job is done by a PROCEDURE with an OUT parameter
CREATE OR REPLACE PROCEDURE app.place_order(IN p_total numeric, OUT p_order_id bigint)
LANGUAGE plpgsql AS $$ BEGIN
  INSERT INTO orders (total, status) VALUES (p_total, 'NEW') RETURNING id INTO p_order_id;
END $$;

CREATE OR ALTER PROCEDURE app.place_order
  @customer_id bigint, @total decimal(12,2), @order_id bigint OUTPUT
AS
BEGIN
  SET NOCOUNT ON;        -- suppresses the "n rows affected" messages
  SET XACT_ABORT ON;     -- any runtime error rolls the whole transaction back
  INSERT INTO dbo.orders (customer_id, total, status)
  VALUES (@customer_id, @total, N'NEW');
  SET @order_id = SCOPE_IDENTITY();
  RETURN 0;              -- the return code is an int and is only for status
END;
GO
DECLARE @id bigint;
EXEC app.place_order @customer_id = 42, @total = 199.90, @order_id = @id OUTPUT;

8.1 Errors and transactions

A transaction is like a construction contract: either the whole building is delivered or nothing is. But there is one dangerous state: a transaction can become doomed — still open, still inside CATCH, yet nothing but ROLLBACK is possible.

-- in PL/pgSQL an EXCEPTION block plays the TRY/CATCH role
DO $$ BEGIN
  INSERT INTO orders (total, status) VALUES (100, 'NEW');
EXCEPTION WHEN unique_violation THEN RAISE EXCEPTION 'duplicate order';
END $$;

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
        INSERT INTO dbo.orders (customer_id, total, status) VALUES (42, 100, N'NEW');
        INSERT INTO dbo.order_line (order_id, sku, qty) VALUES (SCOPE_IDENTITY(), N'A', 1);
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;   -- -1 means doomed, 1 means committable
    INSERT INTO dbo.error_log (error_number, message, proc_name, occurred_at)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), SYSUTCDATETIME());
    THROW;   -- rethrows the original error with its original number and severity
END CATCH;

Diagram: transaction lifecycle and the doomed state | دیاگرام: چرخهٔ عمر تراکنش و حالت doomed

stateDiagram-v2
  [*] --> NoTransaction
  NoTransaction --> Active: BEGIN TRANSACTION
  Active --> Committed: COMMIT
  Active --> RolledBack: ROLLBACK
  Active --> Doomed: runtime error with XACT_ABORT ON
  Doomed --> RolledBack: ROLLBACK only
  Doomed --> Doomed: any DML fails with error 3930

8.2 Dynamic SQL

EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', p_table) INTO v_count USING p_status;

DECLARE @sql nvarchar(max), @cnt int;
SET @sql = N'SELECT @cnt_out = COUNT(*) FROM ' + QUOTENAME(@table_name)
         + N' WHERE status = @status';      -- the value is a parameter; only the identifier is concatenated
EXEC sys.sp_executesql @sql,
     N'@status nvarchar(20), @cnt_out int OUTPUT',
     @status = @status, @cnt_out = @cnt OUTPUT;

Three iron rules of dynamic SQL.

1. Never concatenate a value; values are always sp_executesql parameters. EXEC('...' + @x) is an open door for injection (appsec-owasp). 2. Wrap identifiers in QUOTENAME() and pass them through an allow-list. 3. Replace EXEC(@sql) with sp_executesql: only the latter is parameterized, and because the statement text stays constant the plan is reused; concatenating values means a new plan per value and a polluted plan cache.

A security note: dynamic SQL inside a procedure breaks ownership chaining, so the caller needs direct permissions on the underlying tables; the correct fix is WITH EXECUTE AS OWNER or signing the procedure with a certificate.

8.3 UDFs, triggers and cursors

-- inline TVF: a single SELECT, no BEGIN/END; the optimizer expands it
CREATE OR ALTER FUNCTION app.orders_of(@customer_id bigint)
RETURNS TABLE
AS RETURN (SELECT id, total, created_at FROM dbo.orders WHERE customer_id = @customer_id);

-- by contrast a multi-statement TVF (`RETURNS @r TABLE ... BEGIN ... END`) builds and fills
-- a table and is a black box to the optimizer

SELECT is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID('app.net_total');
CREATE OR ALTER FUNCTION app.f(...) RETURNS int WITH INLINE = OFF AS ...;   -- function level
ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;     -- database level
SELECT ... OPTION (USE HINT ('DISABLE_TSQL_SCALAR_UDF_INLINING'));          -- statement level

Triggers in SQL Server are set-based, not row-based: one UPDATE touching 1,000 rows fires the trigger once with 1,000 rows in inserted/deleted.

CREATE TRIGGER orders_audit AFTER UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION trg_orders_audit();   -- row-level

CREATE OR ALTER TRIGGER dbo.trg_orders_audit ON dbo.orders AFTER UPDATE AS
BEGIN
  SET NOCOUNT ON;
  IF NOT UPDATE(status) RETURN;
  INSERT INTO dbo.order_audit (order_id, old_status, new_status, changed_at)
  SELECT i.id, d.status, i.status, SYSUTCDATETIME()
  FROM inserted i JOIN deleted d ON d.id = i.id
  WHERE i.status <> d.status;      -- set-based, not row-based
END;

9. Performance: from the plan to the index

A plan is the map the optimizer drew for your query: estimated (without running it) and actual (after execution, with real row counts; Ctrl+M in SSMS). Read it right to left and check three things in order:

1. The gap between Estimated Rows and Actual Rows. Estimated 1, actual 200,000 is the root cause: stale statistics, a table variable, a function, or parameter sniffing. Until you fix that, the rest is guesswork.
2. Expensive operators: Key Lookup, Sort, Hash Match, Table Spool, and any Scan on a large table.
3. Warnings (the yellow triangle): Implicit Conversion, No Join Predicate, Spill to tempdb (the memory grant was too small and the sort went to disk).

9.1 Indexes

Every table is either a heap or has a clustered index, meaning the table itself is stored in clustered-key order on the leaf pages of a B-tree — so a table has exactly one clustered index. Nonclustered indexes are separate structures whose leaves carry the clustering key (or a RID in a heap) so they can get back to the row.

-- in PostgreSQL every index is secondary; physical ordering is a manual CLUSTER
CREATE INDEX ix_orders_cust ON orders (customer_id, created_at DESC) INCLUDE (total);
CREATE INDEX ix_orders_open ON orders (created_at) WHERE status = 'NEW';   -- partial index

CREATE CLUSTERED INDEX cx_orders ON dbo.orders (id);
CREATE NONCLUSTERED INDEX ix_orders_cust ON dbo.orders (customer_id, created_at DESC)
  INCLUDE (total, status)
  WITH (ONLINE = ON, DATA_COMPRESSION = PAGE, FILLFACTOR = 90,
        OPTIMIZE_FOR_SEQUENTIAL_KEY = ON);                          -- the last one is 2019+
CREATE NONCLUSTERED INDEX ix_orders_open ON dbo.orders (created_at) WHERE status = N'NEW';

9.2 Statistics and parameter sniffing

Statistics are a histogram of a column's value distribution, and they are the only thing the optimizer reasons from. Parameter sniffing means the engine sniffs the parameter on the first execution, builds the best plan for that value and caches it; with a skewed distribution (one customer with 2 orders, another with 2 million) that plan is imposed on everyone.

UPDATE STATISTICS dbo.orders WITH FULLSCAN;               -- first of all: fresh statistics
SELECT ... OPTION (RECOMPILE);                            -- a bespoke plan every time
SELECT ... OPTION (OPTIMIZE FOR (@customer_id = 42));     -- optimise for a representative value
SELECT ... OPTION (OPTIMIZE FOR UNKNOWN);                 -- use average density instead
EXEC sys.sp_query_store_set_hints @query_id = 39,
     @query_hints = N'OPTION(RECOMPILE)';                 -- no application change needed, 2022+

9.3 The troubleshooting toolbox

Turn on SET STATISTICS IO ON; and SET STATISTICS TIME ON;. Logical reads (8 KB pages touched) is the best metric for comparing two versions of a query, because unlike elapsed time it does not depend on what else the server is doing.

Query Store is the flight recorder: it stores query text, plans, runtime statistics and waits inside the database itself and survives restarts, unlike the plan cache. Since SQL Server 2022 it is on by default in READ_WRITE mode for new databases.

Enable it with ALTER DATABASE app SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO, MAX_STORAGE_SIZE_MB = 2048); and pin a good plan with sys.sp_query_store_force_plan.

10. Concurrency: locking, isolation, deadlocks

SQL Server is lock-based by default rather than version-based — the biggest behavioural difference from PostgreSQL and Oracle. Under the default READ COMMITTED, readers take shared locks and writers block readers; under MVCC in PostgreSQL and Oracle, reads never block.

SHOW default_transaction_isolation;   -- PostgreSQL is MVCC by nature

ALTER DATABASE app SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
ALTER DATABASE app SET ALLOW_SNAPSHOT_ISOLATION ON;   -- for explicit SNAPSHOT
SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = 'app';

A deadlock is two transactions each holding a lock the other wants. SQL Server detects the cycle, picks a victim (error 1205) and rolls its transaction back completely. The deadlock graph comes from the system_health extended events session, which is on by default.

Diagram: how a classic deadlock forms | دیاگرام: تشکیل یک deadlock کلاسیک

sequenceDiagram
  participant A as Session A
  participant O as orders row 1
  participant L as order_line row 9
  participant B as Session B
  A->>O: UPDATE, holds X lock
  B->>L: UPDATE, holds X lock
  A->>L: UPDATE, waits for B
  B->>O: UPDATE, waits for A
  Note over A,B: cycle detected, error 1205 kills one victim

tempdb is the shared bottleneck of the instance; the classic symptom is PAGELATCH_* waits on allocation pages (2:1:1 is PFS). The cure is multiple data files of equal size and equal autogrowth (start with one per core up to eight), plus, from SQL Server 2019, ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;.

11. Columnstore and partitioning

Columnstore stores data column-wise and compressed in row groups of up to 1,048,576 rows, and brings batch mode, where each operator call processes a batch of rows instead of one. On analytical tables it is a 10–100x difference; for single-row OLTP lookups it is the worst choice.

Partitioning is first of all a management tool, not a performance one; its real payoff is instant removal of old data with SWITCH instead of a million-row DELETE.

CREATE TABLE orders (id bigint, created_at date NOT NULL) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_01 PARTITION OF orders FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
ALTER TABLE orders DETACH PARTITION orders_2026_01;   -- instant removal

CREATE CLUSTERED COLUMNSTORE INDEX ccx_fact_sales ON dbo.fact_sales;

CREATE PARTITION FUNCTION pf_month (date) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-02-01');
CREATE PARTITION SCHEME ps_month AS PARTITION pf_month ALL TO ([PRIMARY]);
CREATE TABLE dbo.orders (id bigint, created_at date NOT NULL) ON ps_month (created_at);

ALTER TABLE dbo.orders SWITCH PARTITION 2 TO dbo.orders_archive PARTITION 2;  -- metadata only
ALTER PARTITION FUNCTION pf_month() SPLIT RANGE ('2026-04-01');
TRUNCATE TABLE dbo.orders WITH (PARTITIONS (2));      -- 2016+

12. Backup and restore

There are three backup types: full (the whole database), differential (only pages changed since the last full — not since the last differential), and log (all log records since the last log backup; only under the full or bulk-logged recovery model).

-- PostgreSQL has no in-engine BACKUP statement; base backup + continuous WAL archiving
--   pg_basebackup -D /backup/base -Ft -z -X stream -c fast
ALTER SYSTEM SET archive_mode = on;
ALTER SYSTEM SET archive_command = 'test ! -f /wal/%f && cp %p /wal/%f';

ALTER DATABASE app SET RECOVERY FULL;
BACKUP DATABASE app TO DISK = 'B:\app_full.bak' WITH INIT, CHECKSUM, COMPRESSION, STATS = 5;
BACKUP DATABASE app TO DISK = 'B:\app_diff.bak' WITH DIFFERENTIAL, CHECKSUM, COMPRESSION;
BACKUP LOG app TO DISK = 'B:\app_log_1015.trn' WITH CHECKSUM, COMPRESSION;

-- point-in-time restore: "at 10:20 somebody forgot the WHERE and updated the whole table"
BACKUP LOG app TO DISK = 'B:\app_tail.trn' WITH NORECOVERY, CHECKSUM;   -- 1) tail of the log
RESTORE DATABASE app FROM DISK = 'B:\app_full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE app FROM DISK = 'B:\app_diff.bak' WITH NORECOVERY;
RESTORE LOG app FROM DISK = 'B:\app_log_1015.trn' WITH NORECOVERY;
RESTORE LOG app FROM DISK = 'B:\app_tail.trn'
  WITH STOPAT = '2026-08-13T10:19:00', RECOVERY;                        -- 2) just before the disaster

Diagram: the point-in-time restore chain | دیاگرام: زنجیرهٔ بازیابی نقطه‌ای

flowchart LR
  F[Full backup 02:00] --> D[Differential 08:00]
  D --> L1[Log 10:00]
  L1 --> L2[Log 10:15]
  L2 --> T[Tail-log backup NORECOVERY]
  T --> R[RESTORE ... STOPAT 10:19 WITH RECOVERY]

13. The transaction log: growth, shrinking and VLFs

Internally the log file is divided into virtual log files (VLFs). Each log backup frees the inactive parts for reuse — a logical truncation, not a shrinking of the file. Inspect it with sys.databases.log_reuse_wait_desc, sys.dm_db_log_space_usage and sys.dm_db_log_info(DB_ID('app')) (VLF count, 2017+).

14. Index and statistics maintenance

REINDEX INDEX CONCURRENTLY ix_orders_cust;
ANALYZE orders;

ALTER INDEX ix_orders_cust ON dbo.orders REORGANIZE;      -- light, online, interruptible
ALTER INDEX ALL ON dbo.orders REBUILD
  WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP = 4, DATA_COMPRESSION = PAGE);
UPDATE STATISTICS dbo.orders WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;

The traditional rule — "under 5% do nothing, 5–30% reorganize, above 30% rebuild" — is simplistic today. On SSDs logical fragmentation matters little and updating statistics usually helps more than a rebuild (REBUILD refreshes statistics with a full scan, REORGANIZE does not touch them). Concentrate maintenance on the genuinely large, volatile tables and use ONLINE = ON with RESUMABLE = ON.

15. Security

A login is the badge that opens the building (the instance), a user is the key to one room (a database), a role is a bundle of keys for a job, and a schema is the shelving inside the room. Restore a database where the target login does not exist and the user is "orphaned": the key is there, the badge is not.

CREATE ROLE app_login LOGIN PASSWORD 'x';
CREATE ROLE app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
GRANT app_rw TO app_login;

CREATE LOGIN app_login WITH PASSWORD = 'S3cure!Pass', CHECK_POLICY = ON;
CREATE USER app_user FOR LOGIN app_login WITH DEFAULT_SCHEMA = app;
CREATE ROLE app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::app TO app_rw;
ALTER ROLE app_rw ADD MEMBER app_user;
ALTER USER app_user WITH LOGIN = app_login;      -- fixes an orphaned user after a restore

Never give the application sysadmin or db_owner; create a custom role with schema-level permissions so new tables are covered automatically. The data-protection layers are TDE (transparent encryption at rest), Always Encrypted (client-side encryption), Row-Level Security (CREATE SECURITY POLICY ... ADD FILTER PREDICATE) and Dynamic Data Masking (ADD MASKED WITH (FUNCTION = 'email()')).

16. High availability and disaster recovery

ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (s1, s2)';
SELECT client_addr, state, sync_state, replay_lag FROM pg_stat_replication;

CREATE AVAILABILITY GROUP ag_app
WITH (CLUSTER_TYPE = WSFC, CONTAINED)                 -- CONTAINED is SQL Server 2022+
FOR DATABASE app
REPLICA ON
  N'NODE1' WITH (ENDPOINT_URL = N'TCP://node1.corp.local:5022',
                 AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC,
                 SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

Diagram: availability group topology | دیاگرام: توپولوژی availability group

flowchart TD
  APP[Application] -->|listener DNS + port| LSN[AG Listener]
  LSN -->|ReadWrite intent| P[Primary replica]
  LSN -->|ApplicationIntent=ReadOnly| S1[Secondary sync, readable]
  P -->|log blocks, synchronous| S1
  P -->|log blocks, asynchronous| S2[Secondary in DR site]
  WSFC[WSFC quorum + witness] --- P
  WSFC --- S1

Monitor replica health with sys.dm_hadr_database_replica_states (log_send_queue_size, redo_queue_size).

17. Baseline configuration and monitoring

The configuration checked in every health review: max server memory set to total RAM minus 4–8 GB; cost threshold for parallelism between 25 and 50 (the default of 5 is far too low); MAXDOP at most 8 and at most the cores per NUMA node; several equally sized tempdb data files; PAGE_VERIFY CHECKSUM; instant file initialization; Query Store everywhere; and a weekly DBCC CHECKDB.

EXEC sys.sp_configure 'cost threshold for parallelism', 50; RECONFIGURE;
ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;   -- 2019+
ALTER DATABASE app SET ACCELERATED_DATABASE_RECOVERY = ON;            -- instant rollback, 2019+
DBCC CHECKDB (app) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY;

Good monitoring is a handful of meaningful signals, not a hundred dashboards: the last successful backup of each database, free space on data/log/tempdb volumes, log fullness and log_reuse_wait_desc, replica lag, blocking longer than n seconds and deadlock counts, per-file I/O latency, and severity 19+ errors alongside DBCC CHECKDB results. Keep data, log, tempdb and backups on separate physical paths.

18. Connecting from Spring Boot: JDBC and JPA

spring.datasource.url=jdbc:sqlserver://sql01.corp.local:1433;databaseName=app;encrypt=true;trustServerCertificate=false;hostNameInCertificate=sql01.corp.local;sendStringParametersAsUnicode=false;applicationName=orders-api;loginTimeout=30;multiSubnetFailover=true
spring.datasource.username=app_login
spring.datasource.password=${DB_PASSWORD}
spring.datasource.hikari.maximum-pool-size=20
spring.jpa.properties.hibernate.jdbc.batch_size=50
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

The Maven dependency is com.microsoft.sqlserver:mssql-jdbc:13.4.0.jre11 (the jre11 build covers Java 11 and later).

SQL Server-specific JPA/Hibernate notes (fundamentals in jpa-hibernate): GenerationType.IDENTITY destroys insert batching, because Hibernate must send each INSERT separately to retrieve the generated key — for bulk inserts use a SEQUENCE with a sensible allocationSize. For tests, start the official mcr.microsoft.com/mssql/server:2022-latest image with Testcontainers, and in type mapping prefer datetime2 and decimal(19,4).