Security Best Practices

  • Prefer Windows Authentication over SQL Authentication.
  • Avoid using sa for daily operations.
  • Regularly review and remove unused or orphaned users.
  • Enforce strong passwords and role-based access.
  • Enable auditing, encryption, and backups.
  • Document user and login changes for compliance.

Encryption: SQL Server supports multiple types of encryption: Transparent Data Encryption (TDE) for encrypting the whole database at rest, column-level encryption for sensitive columns, Always Encrypted(SQL Server 2016+) for client-side protection so the database never sees plaintext, protecting sensitive data from DBAs, with keys stored locally or in Azure Key Vault, and backup encryption. Proper key management and regular backups of encryption keys are critical for maintaining access and security.

Mastering SQL (Structured Query Language) and Database Administration is essential for any backend developer or data professional. This guide provides a structured overview of core database concepts, performance tuning, and administration best practices.

1. SQL Basics

SQL (Structured Query Language) is used to store, retrieve, manipulate, and manage data in relational databases.

Common Database Ports

  • SQL Server: TCP/IP port 1433 (configurable)
  • MySQL: Default port 3306
  • Oracle: Default port 1521 (Oracle Net Listener)

SQL Server Version Info

1
2
3

SELECT SERVERPROPERTY('productversion'),
       SERVERPROPERTY('productlevel'),
       SERVERPROPERTY('edition');

2. Indexes

Indexes improve SELECT performance but slow down INSERT, UPDATE, and DELETE operations.

Clustered Index

  • Only one per table
  • Determines physical order of data
  • Leaf nodes contain actual data pages
  • Usually created on Primary Key
  • Faster reads

Non‑Clustered Index

  • Up to 249 per table
  • Logical order differs from physical storage
  • Leaf nodes store index rows, not data
  • Faster inserts/updates than clustered index

Key Notes

  • SQL Server usually uses an index only if selectivity > 95%

  • Best clustered index columns: Identity, Timestamp

  • A table can be:

    • Heap (no index)
    • Clustered index only
    • Clustered + multiple non‑clustered indexes
    • Multiple non‑clustered indexes

3. Collation Sensitivity

  • Case sensitivity: A ≠ a
  • Accent sensitivity: a ≠ á
  • Kana sensitivity: Hiragana ≠ Katakana
  • Width sensitivity: half‑width ≠ full‑width characters

4. OLTP Systems

Online Transaction Processing (OLTP) systems:

  • Use normalized relational design
  • Follow Codd’s normalization rules
  • Ensure data integrity and consistency

5. Keys & Constraints

Primary Key

  • Uniquely identifies each row
  • One per table
  • Cannot contain NULL
  • Creates clustered index by default
  • Ensures entity integrity

Unique Key

  • Enforces uniqueness
  • Allows one NULL
  • Multiple unique keys allowed
  • Creates non‑clustered index by default

Foreign Key

  • References primary key in another table
  • Enforces referential integrity
  • Prevents orphan records

Other Constraints

  • CHECK: Limits allowed values
  • NOT NULL: Prevents NULL values

6. SQL Command Categories

DDL (Data Definition Language)

  • CREATE, ALTER, DROP, TRUNCATE, RENAME

DML (Data Manipulation Language)

  • SELECT, INSERT, UPDATE, DELETE, MERGE

DCL (Data Control Language)

  • GRANT, REVOKE

TCL (Transaction Control Language)

  • COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION

7. DELETE vs TRUNCATE vs DROP

FeatureDELETETRUNCATEDROP
TypeDMLDDLDDL
WHERE clauseYesNoNo
RollbackYesNoNo
TriggersYesNoNo
Identity resetNoYesN/A
Removes tableNoNoYes

11. Subqueries

Rules

  • Enclosed in parentheses
  • Cannot contain ORDER BY

Types

  • Single‑row
  • Multi‑row
  • Multi‑column

14. Views

View

  • Virtual table
  • Stores query only
  • Improves security & abstraction

Materialized View

  • Physically stored
  • Periodically refreshed
  • Can be indexed

15. Stored Procedures

  • Precompiled SQL logic
  • Improves performance
  • Reusable and secure
  • Supports recursion (up to 32 levels)

16. Transactions

A transaction is an atomic unit of work:

  • BEGIN TRANSACTION
  • COMMIT
  • ROLLBACK

Ensures ACID properties.

17. Temporary Storage

  • Table Variable: @table
  • Local Temp Table: #table
  • Global Temp Table: ##table
  • Heap Table: No clustered index

18. Execution Plan

Execution plans show how SQL Server executes a query. Used for:

  • Performance tuning
  • Index optimization

19. SQL Server Tools

SQL Profiler

  • Monitors SQL Server events
  • Helps identify slow queries

SQL Server Agent

  • Schedules jobs
  • Automates maintenance tasks

BCP / BULK INSERT

  • Fast bulk data loading

Log Shipping

  • Disaster recovery solution
  • Automates log backup & restore

20. JMeter (Performance Testing)

  • Open‑source load testing tool
  • Tests Web, API, DB performance
  • Uses Thread Groups to simulate users
  • Key metrics: Throughput, Response Time, Deviation

Common Controllers

  • Loop
  • Once Only
  • If / While
  • Transaction
  • Throughput
  • Random

21. Normalization

Normalization reduces data redundancy by:

  • Splitting data into logical tables
  • Defining relationships

22. Miscellaneous

  • DISTINCT returns unique values
  • IDENTITY auto‑generates numbers
  • STUFF overwrites characters
  • REPLACE replaces all occurrences
  • Linked servers use OLE‑DB providers

Data buffering

  • SQL Server data buffering = keeping frequently used data in memory to speed up database operations
  • In SQL Server, data buffering refers to how the database engine temporarily stores data pages in memory (RAM) to reduce disk I/O and improve query performance.
  • More formally, SQL Server uses a memory area called the Buffer Pool (Buffer Cache) to hold:
    • Data pages (table rows)
    • Index pages
    • Work pages for query processing

How Data Buffering Works:

  1. A query requests data (CPU starts processing the request)
  2. SQL Server first checks the buffer cache in memory (RAM) (No disk access yet)
  3. If found → returned immediately (logical read) (no disk I/O)
  4. If not found (in RAM)→ fetched from disk I/O into memory (RAM) (physical read) ((disk I/O)physical read from disk, then loaded into RAM)
  5. The page stays in memory (RAM) for future use. (Frequently used data stays “hot” in RAM, allowing SQL Server to serve repeat queries faster without hitting disk again).

Result/Why Buffering Is Important

Faster query execution. More logical (in RAM) reads, fewer physical (from disk I/O) reads, less disk I/O, and Better overall performance. Frequently used data stays “hot” in RAM

📊 Key Terms You’ll See in SQL Server

TermMeaning
Buffer PoolMain memory cach (a portion of memory (RAM) that a database uses to cache data pages)[Think of it as a workspace in RAM for your database]
Logical ReadRead from memory (RAM)
Physical ReadRead from disk (Disk I/O)
Dirty PageModified in memory but not yet written to disk (Page = a fixed-size block of data (like 8 KB in SQL Server or InnoDB).(Dirty = the data in memory is newer than what’s on disk.))
CheckpointWrites dirty pages to disk

Notes:

  • Dirty page = memory version ≠ disk version
  • Clean page = memory version = disk version
  • The buffer pool may contain both dirty and clean pages
  • Too many dirty pages → risk of data loss if a crash occurs before flushing

📌 Simple Example

If you run:

1

SELECT * FROM Customers WHERE CustomerID = 10;
  • First time → SQL Server loads page from disk into buffer
  • Next time → SQL Server reads from memory (much faster)

⚠️ What Affects Buffering Performance

  • Amount of RAM
  • Query design
  • Index usage
  • Server workload
  • Memory configuration

Ways to log in:

Login TypeWhere StoredAuthentication MethodNotes
Windows AuthenticationActive DirectoryUses Windows credentialsStrong, centralized, recommended
SQL Server AuthenticationSQL ServerUsername + passwordFlexible, but DBA-managed passwords
Azure / Token AuthCloudToken / AAD loginModern, for cloud-based DBs

1️⃣ What is the difference between a login and a user in SQL Server?

A login is a server-level security principal used to connect to the SQL Server instance, while a user is a database-level principal used to grant permissions inside a specific database. A user is mapped to a login using a SID. One login can have users in multiple databases.

2️⃣ What is an orphaned user and how do you fix it?

Answer: An orphaned user is a database user that does not have a corresponding login at the server level, usually caused by database restore or migration. It can be fixed by mapping the user to an existing login using ALTER USER … WITH LOGIN or by recreating the login with the same SID.

3️⃣ How do you check who has excessive permissions in a database?

Answer: I review database role memberships such as db_owner, db_securityadmin, and db_datawriter, and check explicit permissions using system views like sys.database_role_members and sys.database_permissions. I follow the principle of least privilege and remove unnecessary permissions.

4️⃣ What security best practices do you follow as a DBA?

Answer: I avoid using sa and excessive db_owner access, use Windows authentication where possible, regularly review logins and users, remove unused or orphaned users, enforce strong password policies, and ensure encryption, backups, and auditing are properly configured.

5️⃣ Bonus tip (interview gold ✨)

If they ask “What happens if we delete a login?” Say this:

“Deleting a login does not delete the database user, which can result in orphaned users that must be remapped or cleaned up.”