25 May 2024

#SQL_Server

#SQL_Server

Key Concepts


Topic SubTopics Basic Intermediate Advanced Expert
Introduction & Basics Overview, Editions, Versions, Features ✅
Installation & Configuration System Requirements, Installation, Configuration, Services ✅ ✅
Databases Creating Databases, Database Properties, Collation, Filegroups ✅ ✅
Tables & Schemas Creating Tables, Data Types, Constraints, Schemas ✅ ✅
Data Types Numeric, String, DateTime, Binary, XML, JSON ✅ ✅
Primary & Foreign Keys PK, FK, Relationships, Referential Integrity ✅ ✅
Indexes Clustered, Non-Clustered, Columnstore, Filtered Indexes ✅ ✅
Views Simple Views, Indexed Views, Partitioned Views, Security ✅ ✅ ✅
Stored Procedures Creating, Executing, Parameters, Error Handling ✅ ✅ ✅
Functions Scalar, Table-Valued, Inline, User-Defined ✅ ✅ ✅
Triggers DML Triggers, DDL Triggers, INSTEAD OF, AFTER ✅ ✅
Transactions ACID, BEGIN/COMMIT/ROLLBACK, Isolation Levels ✅ ✅ ✅
Locks & Deadlocks Lock Types, Blocking, Deadlock Detection ✅ ✅
Constraints CHECK, DEFAULT, UNIQUE, NOT NULL ✅ ✅
Normalization & Design 1NF, 2NF, 3NF, Denormalization, ERD ✅ ✅
Querying SELECT, JOINs, WHERE, GROUP BY, HAVING, ORDER BY ✅ ✅ ✅
Advanced Queries CTE, Window Functions, Ranking, Pivot/Unpivot ✅ ✅
Performance Tuning Index Tuning, Execution Plans, Query Optimization ✅ ✅
Transactions & Concurrency Isolation Levels, Locks, Deadlocks, Snapshot Isolation ✅ ✅
Backup & Restore Full, Differential, Log Backup, Restore Scenarios ✅ ✅ ✅
Security Authentication, Authorization, Roles, Permissions, Encryption ✅ ✅ ✅ ✅
Replication & Mirroring Snapshot, Transactional, Merge, Database Mirroring ✅ ✅ ✅
High Availability Always On, Failover Cluster Instances, Log Shipping ✅ ✅ ✅
Monitoring & Maintenance Performance Monitoring, SQL Agent Jobs, Maintenance Plans ✅ ✅ ✅
Advanced Topics Partitioning, Service Broker, Query Store, PolyBase ✅ ✅
Cloud & Hybrid Azure SQL, Managed Instance, Synapse Analytics, Migrations ✅ ✅

Interview question

Basic Level

  1. What is SQL Server and how does it differ from other RDBMS?
  2. What are the main editions of SQL Server?
  3. How do you install SQL Server?
  4. What is SQL Server Management Studio (SSMS)?
  5. What are databases, tables, and schemas in SQL Server?
  6. How do you create a database in SQL Server?
  7. How do you create a table in SQL Server?
  8. What are the different data types in SQL Server?
  9. What is a primary key? How do you define it in SQL Server?
  10. What is a foreign key? How do you define it in SQL Server?
  11. What is the difference between CHAR, VARCHAR, and NVARCHAR?
  12. How do you insert data into a table?
  13. How do you update and delete records in SQL Server?
  14. What is the difference between DELETE, TRUNCATE, and DROP?
  15. What are constraints in SQL Server? Name a few types.
  16. How do you create indexes in SQL Server?
  17. What is a clustered index? How does it differ from a non-clustered index?
  18. How do you check the structure of a table in SQL Server?
  19. How do you retrieve all databases on a SQL Server instance?
  20. What is the difference between UNION and UNION ALL?
  21. How do you filter data using the WHERE clause?
  22. Explain the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
  23. What is the purpose of the TOP keyword in SQL Server?
  24. What is IDENTITY in SQL Server? How is it used?
  25. How do you back up and restore a SQL Server database?

Intermediate Level

  1. What are SQL Server system databases?
  2. Explain the purpose of master, msdb, model, and tempdb.
  3. What is tempdb used for?
  4. Explain the difference between a view and a materialized view.
  5. What are stored procedures in SQL Server?
  6. How do you create and execute a stored procedure?
  7. What are triggers in SQL Server?
  8. Explain the difference between AFTER and INSTEAD OF triggers.
  9. What are functions in SQL Server? Differentiate between scalar and table-valued functions.
  10. What is a common table expression (CTE)?
  11. How do you implement recursive queries in SQL Server?
  12. What are transactions in SQL Server?
  13. Explain ACID properties in SQL Server.
  14. What is the difference between COMMIT and ROLLBACK?
  15. How does SQL Server handle concurrency?
  16. What are isolation levels in SQL Server? List them.
  17. What is the difference between optimistic and pessimistic concurrency control?
  18. How do you use the EXCEPT and INTERSECT operators?
  19. What are SQL Server Operators like LIKE, BETWEEN, IN, and EXISTS?
  20. What is the CASE statement in SQL Server?
  21. How do you handle NULL values in SQL Server?
  22. What is the difference between ISNULL() and COALESCE()?
  23. How do you grant and revoke permissions in SQL Server?
  24. What are SQL Server logins and users? How do they differ?
  25. What is the SQL Server Agent? What is it used for?

Advanced Level

  1. What is indexing strategy in SQL Server?
  2. What are included columns in a non-clustered index?
  3. What is a filtered index in SQL Server?
  4. What are indexed views in SQL Server?
  5. What are statistics in SQL Server? Why are they important?
  6. How does SQL Server query optimizer work?
  7. What is the execution plan in SQL Server? How do you read it?
  8. What is parameter sniffing in SQL Server?
  9. How do you avoid parameter sniffing issues?
  10. What is a deadlock in SQL Server? How do you resolve it?
  11. How do you detect blocking in SQL Server?
  12. Explain difference between NOLOCK and other table hints.
  13. What is sp_who2 used for?
  14. How do you find the longest-running queries in SQL Server?
  15. What are Dynamic Management Views (DMVs)?
  16. How do you monitor performance using DMVs?
  17. What is partitioning in SQL Server?
  18. What are partitioned tables and partitioned indexes?
  19. What is replication in SQL Server? Types?
  20. What is the difference between snapshot, transactional, and merge replication?
  21. What is log shipping in SQL Server?
  22. What is database mirroring in SQL Server?
  23. What is AlwaysOn Availability Groups in SQL Server?
  24. How do you configure failover clustering in SQL Server?
  25. What is database sharding? Is it supported in SQL Server?

Expert Level

  1. Explain SQL Server architecture (SQL OS, storage engine, relational engine).
  2. What is the difference between SQL Server on-premises and Azure SQL Database?
  3. What are extended events in SQL Server?
  4. What is SQL Profiler? Why is it deprecated?
  5. How do you capture deadlock graphs in SQL Server?
  6. Explain tempdb contention and how to resolve it.
  7. How do you tune queries in SQL Server?
  8. What is Query Store in SQL Server?
  9. How do you use Query Store for performance troubleshooting?
  10. What are columnstore indexes? When should you use them?
  11. What is in-memory OLTP in SQL Server?
  12. How does SQL Server handle encryption (TDE, Always Encrypted)?
  13. What is row-level security (RLS) in SQL Server?
  14. What are dynamic data masking techniques in SQL Server?
  15. Explain PolyBase in SQL Server.
  16. How do you connect SQL Server to external data sources (linked servers)?
  17. What is Service Broker in SQL Server?
  18. What is Change Data Capture (CDC)?
  19. What is Change Tracking in SQL Server?
  20. What is the difference between CDC and Change Tracking?
  21. How do you implement auditing in SQL Server?
  22. What are best practices for SQL Server index maintenance?
  23. How do you configure high availability and disaster recovery (HADR)?
  24. What is the difference between synchronous and asynchronous replication in SQL Server?
  25. Compare SQL Server with PostgreSQL and Oracle in terms of scalability, features, and use cases.

Related Topics


   Complex_Query   
-
Labels: , ,