25 May 2024

#SQL_Server

#SQL_Server

Key Concepts


Topic Sub-Topics Basic Intermediate Advanced Expert
SQL Server Basics Installation, Editions, Tools (SSMS, Azure Data Studio) ✅ ✅
Database Design Schemas, Tables, Views, Keys, Normalization ✅ ✅ ✅
SQL Queries SELECT, INSERT, UPDATE, DELETE, Joins, Subqueries ✅ ✅ ✅
Stored Procedures & Functions User-defined procedures, Scalar & Table-valued functions ✅ ✅ ✅
Indexes Clustered, Non-clustered, Unique, Filtered, Columnstore ✅ ✅ ✅
Transactions & Concurrency ACID, Isolation levels, Deadlocks, Locking ✅ ✅ ✅
Triggers & Constraints DML Triggers, DDL Triggers, Check, Default, Foreign Key ✅ ✅
Performance Tuning Execution Plans, Index Tuning, Query Optimization ✅ ✅
Replication & High Availability Log Shipping, Mirroring, AlwaysOn, Replication ✅ ✅
Security Authentication, Authorization, Roles, Encryption, Auditing ✅ ✅ ✅
Backup & Recovery Full, Differential, Log backups, Restore strategies ✅ ✅ ✅ ✅
Advanced Features Partitioning, Service Broker, Change Data Capture (CDC) ✅ ✅
Integration & BI SSIS, SSRS, SSAS, Power BI Integration ✅ ✅ ✅
Cloud & Hybrid SQL Server on Azure, Managed Instances, Elastic Pools ✅ ✅ ✅
Monitoring & Maintenance Jobs, Alerts, DMV queries, Index maintenance ✅ ✅ ✅

Interview question

1. SQL Server Basics (Q1?Q10)

  1. What is SQL Server and why is it used?
  2. Explain SQL Server editions.
  3. What are system databases in SQL Server?
  4. Difference between SQL Server and MySQL.
  5. What are SQL Server instances?
  6. What are SQL Server services?
  7. What is the default port used by SQL Server?
  8. Difference between SQL Server and Azure SQL Database.
  9. What tools can be used to manage SQL Server?
  10. Explain SQL Server architecture.

2. Database Design & Modeling (Q11?Q20)

  1. What is normalization and its types?
  2. What is denormalization?
  3. Difference between primary key and unique key.
  4. What are foreign keys in SQL Server?
  5. Explain composite keys with an example.
  6. Difference between clustered and non-clustered index.
  7. What are constraints in SQL Server?
  8. What is a surrogate key?
  9. Difference between schema and database.
  10. Explain database diagrams in SQL Server.

3. SQL Queries & Joins (Q21?Q30)

  1. Difference between INNER JOIN and OUTER JOIN.
  2. What is a CROSS JOIN?
  3. Explain SELF JOIN with an example.
  4. Difference between UNION and UNION ALL.
  5. What are subqueries in SQL Server?
  6. What are CTEs (Common Table Expressions)?
  7. Difference between correlated and non-correlated subqueries.
  8. Explain EXISTS vs IN operator.
  9. What are window functions in SQL Server?
  10. Explain ROW_NUMBER(), RANK(), and DENSE_RANK().

4. Indexing & Performance (Q31?Q40)

  1. What are indexes in SQL Server?
  2. Difference between clustered and non-clustered indexes.
  3. What is a unique index?
  4. What is a covering index?
  5. Difference between heap tables and indexed tables.
  6. What is index fragmentation?
  7. How do you rebuild or reorganize an index?
  8. What is a filtered index?
  9. Explain execution plan in SQL Server.
  10. How do you optimize slow queries?

5. Transactions & Concurrency (Q41?Q50)

  1. What is a transaction in SQL Server?
  2. Explain ACID properties.
  3. What is transaction isolation level?
  4. Difference between READ COMMITTED and READ UNCOMMITTED.
  5. Explain dirty reads, phantom reads, and non-repeatable reads.
  6. What is optimistic vs pessimistic locking?
  7. What is deadlock in SQL Server?
  8. How do you detect deadlocks in SQL Server?
  9. What is blocking in SQL Server?
  10. How do you resolve concurrency issues?

6. Stored Procedures, Functions & Triggers (Q51?Q60)

  1. What is a stored procedure in SQL Server?
  2. Difference between stored procedure and function.
  3. What is a scalar function?
  4. What are table-valued functions?
  5. What are system stored procedures?
  6. Difference between deterministic and non-deterministic functions.
  7. What are triggers in SQL Server?
  8. Difference between AFTER trigger and INSTEAD OF trigger.
  9. Can a trigger call another trigger?
  10. What are the disadvantages of triggers?

7. Security & Users (Q61?Q70)

  1. How do you create a login in SQL Server?
  2. Difference between login and user.
  3. What are SQL Server roles?
  4. Difference between fixed roles and user-defined roles.
  5. What are server-level vs database-level permissions?
  6. What is SQL Server authentication vs Windows authentication?
  7. How do you implement row-level security?
  8. What is Transparent Data Encryption (TDE)?
  9. How do you audit login activity in SQL Server?
  10. What is Always Encrypted in SQL Server?

8. Backup & Recovery (Q71?Q80)

  1. What is a full backup in SQL Server?
  2. Difference between full, differential, and transaction log backup.
  3. What is a tail-log backup?
  4. How do you restore a database from backup?
  5. What is point-in-time recovery?
  6. What is a filegroup backup?
  7. Difference between backup compression and backup encryption.
  8. What is log shipping in SQL Server?
  9. What is database mirroring?
  10. What is AlwaysOn Availability Groups?

9. SQL Server Advanced Features (Q81?Q90)

  1. What is SQL Server Integration Services (SSIS)?
  2. What is SQL Server Reporting Services (SSRS)?
  3. What is SQL Server Analysis Services (SSAS)?
  4. Difference between OLTP and OLAP.
  5. What are partitioned tables?
  6. What is Change Data Capture (CDC)?
  7. What is Change Tracking in SQL Server?
  8. Explain SQL Server Service Broker.
  9. What are memory-optimized tables?
  10. What are temporal tables in SQL Server?

10. SQL Server Administration (Q91?Q100)

  1. How do you monitor SQL Server performance?
  2. What is SQL Server Profiler?
  3. What are DMVs (Dynamic Management Views)?
  4. How do you monitor deadlocks in SQL Server?
  5. How do you schedule jobs in SQL Server Agent?
  6. What are maintenance plans in SQL Server?
  7. How do you check SQL Server version and build?
  8. What is database shrink operation?
  9. How do you handle orphaned users in SQL Server?
  10. What are best practices for SQL Server database administration?

Related Topics


-
Labels: , ,