Top 150+ SQL Server DBA, Developer, and Designer Interview Questions

 

Table of Contents

1. Beginner-Level Questions

   • SQL Server DBA

   • SQL Server Developer

   • SQL Server Designer

2. Intermediate-Level Questions

   • SQL Server DBA

   • SQL Server Developer

   • SQL Server Designer

3. Expert-Level Questions

   • SQL Server DBA

   • SQL Server Developer

   • SQL Server Designer

4. Conclusion

---

Beginner-Level Questions

These questions focus on foundational concepts, basic T-SQL, and core SQL Server principles for entry-level candidates.

SQL Server DBA (Beginner)

1. What is SQL Server, and what are its key components?

   • Type: Basic Understanding

   • Expected Answer: SQL Server is a Microsoft RDBMS managing data with components like database engine, SSMS, and storage files (MDF, LDF).

   • Example: MDF stores data, LDF logs transactions.

2. What is the difference between a SQL Server instance and a database?

   • Type: Conceptual

   • Expected Answer: An instance is a running SQL Server service, while a database is a collection of objects (tables, views) within it.

3. What are the roles of MDF and LDF files?

   • Type: Conceptual

   • Expected Answer: MDF stores data and schema, while LDF records transaction logs for recovery.

4. What is SQL Server Management Studio (SSMS)?

   • Type: Basic Understanding

   • Expected Answer: SSMS is a GUI tool for managing SQL Server databases, queries, and configurations.

5. What is the purpose of a transaction log?

   • Type: Conceptual

   • Expected Answer: Transaction logs record operations for recovery and consistency.

   • Example: Used in point-in-time recovery.

6. What are the different recovery models in SQL Server?

   • Type: Conceptual

   • Expected Answer: Simple, Full, and Bulk-Logged models control transaction log behavior.

7. What is a backup, and why is it important?

   • Type: Basic Understanding

   • Expected Answer: A backup is a copy of a database for recovery in case of data loss or corruption.

8. What is the difference between a user and a login in SQL Server?

   • Type: Conceptual

   • Expected Answer: A login authenticates at the server level, while a user maps to a database with specific permissions.

9. What is the role of the tempdb database?

   • Type: Conceptual

   • Expected Answer: Tempdb stores temporary objects, like temp tables, and supports query execution.

10. What is a checkpoint in SQL Server?

    • Type: Conceptual

    • Expected Answer: A checkpoint writes dirty pages from memory to disk, ensuring data consistency.

SQL Server Developer (Beginner)

11. What is T-SQL, and how does it differ from SQL?

    • Type: Basic Understanding

    • Expected Answer: T-SQL is Microsoft’s extension of SQL, adding procedural programming features.

    • Example:

      DECLARE @name VARCHAR(50) = 'John';
      PRINT 'Hello, ' + @name;
      ```[](https://www.geeksforgeeks.org/sql-server/sql-server-interview-questions/)

12. What is a stored procedure in SQL Server?

    • Type: Conceptual

    • Expected Answer: A stored procedure is a precompiled T-SQL script for reusable logic.

13. What is the difference between a function and a stored procedure?

    • Type: Conceptual

    • Expected Answer: Functions return a value and can be used in queries, while procedures do not.

    • Example:

      CREATE FUNCTION GetSalary(@empId INT) RETURNS INT AS
      BEGIN
        RETURN (SELECT Salary FROM Employees WHERE EmployeeID = @empId);
      END;

14. What is a trigger in SQL Server?

    • Type: Conceptual

    • Expected Answer: A trigger is a T-SQL block that executes automatically on DML/DDL events.

    • Example:

      CREATE TRIGGER LogUpdate
      ON Employees
      AFTER UPDATE
      AS
      BEGIN
        INSERT INTO AuditLog (EmployeeID, UpdateDate)
        SELECT EmployeeID, GETDATE() FROM inserted;
      END;

15. What is a cursor in T-SQL?

    • Type: Conceptual

    • Expected Answer: A cursor processes query results row by row.

    • Example:

      DECLARE @empId INT;
      DECLARE emp_cursor CURSOR FOR SELECT EmployeeID FROM Employees;
      OPEN emp_cursor;
      FETCH NEXT FROM emp_cursor INTO @empId;
      WHILE @@FETCH_STATUS = 0
      BEGIN
        PRINT CAST(@empId AS VARCHAR);
        FETCH NEXT FROM emp_cursor INTO @empId;
      END;
      CLOSE emp_cursor;
      DEALLOCATE emp_cursor;

16. What is the purpose of the SELECT ... FOR UPDATE statement?

    • Type: Practical

    • Expected Answer: It locks rows to prevent concurrent updates (not directly supported; use WITH (UPDLOCK)).

    • Example:

      SELECT * FROM Employees WITH (UPDLOCK) WHERE EmployeeID = 100;

17. What is a view in SQL Server?

    • Type: Basic Understanding

    • Expected Answer: A view is a virtual table based on a query, storing no data itself.

    • Example:

      CREATE VIEW EmpView AS
      SELECT EmployeeID, FirstName FROM Employees;

18. What is the difference between DELETE and TRUNCATE?

    • Type: Conceptual

    • Expected Answer: DELETE removes specific rows with logging, while TRUNCATE removes all rows without logging.

19. What is an index, and why is it used?

    • Type: Basic Understanding

    • Expected Answer: An index speeds up query performance by organizing data.

    • Example:

      CREATE INDEX idx_emp_id ON Employees(EmployeeID);

20. What is the purpose of the DBCC command?

    • Type: Practical

    • Expected Answer: DBCC performs maintenance tasks like checking database integrity.

    • Example:

      DBCC CHECKDB ('MyDatabase');
      ```[](https://www.sqlshack.com/sql-server-dba-interview-questions-and-answers/)

SQL Server Designer (Beginner)

21. What is database normalization, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Normalization reduces redundancy and ensures integrity using normal forms (1NF, 2NF, 3NF).

    • Example: Splitting a table into Orders and Customers to avoid duplicate data.

22. What is a primary key in SQL Server?

    • Type: Conceptual

    • Expected Answer: A primary key uniquely identifies rows and enforces integrity.

23. What is a foreign key, and how does it work?

    • Type: Conceptual

    • Expected Answer: A foreign key links tables, ensuring referential integrity.

    • Example:

      CREATE TABLE Orders (
        OrderID INT PRIMARY KEY,
        CustomerID INT,
        FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
      );

24. What is the difference between a logical and physical data model?

    • Type: Conceptual

    • Expected Answer: Logical models define data structure conceptually, while physical models specify storage details.

25. What is an ERD (Entity-Relationship Diagram)?

    • Type: Basic Understanding

    • Expected Answer: An ERD visualizes entities, attributes, and relationships.

26. What is a clustered index in SQL Server?

    • Type: Conceptual

    • Expected Answer: A clustered index determines the physical order of data in a table.

    • Example:

      CREATE CLUSTERED INDEX idx_clust ON Employees(EmployeeID);

27. What is the purpose of a schema in database design?

    • Type: Conceptual

    • Expected Answer: A schema organizes database objects for modularity and security.

28. What is the role of constraints in SQL Server?

    • Type: Basic Understanding

    • Expected Answer: Constraints enforce rules like uniqueness or nullability.

    • Example:

      ALTER TABLE Employees ADD CONSTRAINT emp_pk PRIMARY KEY (EmployeeID);

29. What is data modeling, and why is it important?

    • Type: Basic Understanding

    • Expected Answer: Data modeling designs the database structure to meet business needs and ensure efficiency.

30. What is the difference between 1NF and 2NF?

    • Type: Conceptual

    • Expected Answer: 1NF eliminates repeating groups, while 2NF ensures non-key attributes depend on the entire primary key.

---

Intermediate-Level Questions

These questions target candidates with 2–5 years of experience, focusing on practical scenarios, optimization, and advanced SQL Server features.

SQL Server DBA (Intermediate)

31. How do you monitor SQL Server performance?

    • Type: Practical

    • Expected Answer: Use DMVs, Performance Monitor, or Extended Events to track metrics like CPU and memory usage.

    • Example:

      SELECT * FROM sys.dm_os_performance_counters WHERE counter_name LIKE 'Page life expectancy%';

32. What is the purpose of the SQL Server Agent?

    • Type: Conceptual

    • Expected Answer: SQL Server Agent automates tasks like backups and maintenance plans.

33. How do you handle a database corruption issue?

    • Type: Scenario-Based

    • Expected Answer: Run DBCC CHECKDB, restore from backup, or repair with data loss.

    • Example:

      DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS;

34. What is SQL Server Always On Availability Groups?

    • Type: Conceptual

    • Expected Answer: Always On provides high availability and disaster recovery via replica databases.

35. How do you configure a full database backup?

    • Type: Practical

    • Expected Answer: Use T-SQL or SSMS to schedule full backups.

    • Example:

      BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

36. What is the difference between a full and differential backup?

    • Type: Conceptual

    • Expected Answer: Full backups copy all data, while differential backups copy changes since the last full backup.

37. How do you troubleshoot a slow-running query?

    • Type: Scenario-Based

    • Expected Answer: Analyze execution plans, check indexes, and optimize T-SQL.

    • Example:

      SET SHOWPLAN_ALL ON;
      SELECT * FROM Employees WHERE Salary > 50000;

38. What is the role of the transaction log in recovery?

    • Type: Conceptual

    • Expected Answer: Transaction logs enable point-in-time recovery in Full recovery model.

39. How do you manage database permissions?

    • Type: Practical

    • Expected Answer: Use roles, schemas, and GRANT/REVOKE statements.

    • Example:

      GRANT SELECT ON Employees TO HR_Role;

40. What is the purpose of the sys.dm_exec_requests DMV?

    • Type: Practical

    • Expected Answer: It monitors currently executing queries and their status.

    • Example:

      SELECT * FROM sys.dm_exec_requests WHERE status = 'running';

SQL Server Developer (Intermediate)

41. How do you optimize a T-SQL query for performance?

    • Type: Practical

    • Expected Answer: Use proper indexing, avoid cursors, and minimize joins.

    • Example:

      SELECT e.EmployeeID, e.FirstName
      FROM Employees e
      WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.EmployeeID = e.EmployeeID);

42. What is a Common Table Expression (CTE)?

    • Type: Conceptual

    • Expected Answer: A CTE is a temporary result set for query readability.

    • Example:

      WITH EmpCTE AS (
        SELECT EmployeeID, FirstName FROM Employees WHERE Salary > 50000
      )
      SELECT * FROM EmpCTE;

43. How do you handle errors in T-SQL?

    • Type: Practical

    • Expected Answer: Use TRY-CATCH blocks for error handling.

    • Example:

      BEGIN TRY
        SELECT 1/0;
      END TRY
      BEGIN CATCH
        SELECT ERROR_MESSAGE();
      END CATCH;

44. What is the difference between UNION and UNION ALL?

    • Type: Conceptual

    • Expected Answer: UNION removes duplicates, while UNION ALL does not, improving performance.

45. How do you implement pagination in a query?

    • Type: Coding Challenge

    • Expected Answer:

      SELECT * FROM Employees
      ORDER BY EmployeeID
      OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

46. What is a table-valued function, and when is it used?

    • Type: Conceptual

    • Expected Answer: Table-valued functions return result sets, used in complex queries.

    • Example:

      CREATE FUNCTION GetEmployeesByDept(@deptId INT)
      RETURNS TABLE AS
      RETURN (SELECT * FROM Employees WHERE DepartmentID = @deptId);

47. How do you use dynamic SQL in T-SQL?

    • Type: Practical

    • Expected Answer: Use EXEC or sp_executesql for dynamic queries.

    • Example:

      DECLARE @sql NVARCHAR(100) = 'SELECT * FROM Employees WHERE Salary > @sal';
      EXEC sp_executesql @sql, N'@sal INT', 50000;

48. What is the purpose of the MERGE statement?

    • Type: Conceptual

    • Expected Answer: MERGE performs insert, update, or delete based on conditions.

    • Example:

      MERGE INTO Employees e
      USING NewEmployees ne
      ON e.EmployeeID = ne.EmployeeID
      WHEN MATCHED THEN UPDATE SET e.Salary = ne.Salary
      WHEN NOT MATCHED THEN INSERT VALUES (ne.EmployeeID, ne.Salary);

49. How do you create a sequence in SQL Server?

    • Type: Practical

    • Expected Answer:

      CREATE SEQUENCE EmpSeq START WITH 1 INCREMENT BY 1;

50. What is the difference between INNER JOIN and LEFT JOIN?

    • Type: Conceptual

    • Expected Answer: INNER JOIN returns matching rows, while LEFT JOIN includes all rows from the left table.

SQL Server Designer (Intermediate)

51. How do you design a database schema for a multi-tenant application?

    • Type: Scenario-Based

    • Expected Answer: Use separate databases, schemas, or tenant IDs in tables.

    • Example:

      CREATE TABLE Tenants (
        TenantID INT PRIMARY KEY,
        TenantName NVARCHAR(100)
      );

52. What is the role of indexing in database design?

    • Type: Conceptual

    • Expected Answer: Indexes improve query performance but increase storage and maintenance.

53. How do you handle denormalization in database design?

    • Type: Scenario-Based

    • Expected Answer: Denormalize for read-heavy systems, balancing redundancy and performance.

54. What is a composite key, and when is it used?

    • Type: Conceptual

    • Expected Answer: A composite key uses multiple columns for uniqueness when no single column suffices.

55. How do you model a many-to-many relationship?

    • Type: Practical

    • Expected Answer: Use a junction table with foreign keys.

    • Example:

      CREATE TABLE StudentCourses (
        StudentID INT,
        CourseID INT,
        PRIMARY KEY (StudentID, CourseID)
      );

56. What is the role of partitioning in SQL Server?

    • Type: Conceptual

    • Expected Answer: Partitioning splits large tables for performance and manageability.

    • Example:

      CREATE PARTITION FUNCTION pf_Sales (DATE)
      AS RANGE RIGHT FOR VALUES ('2025-01-01');

57. How do you ensure data integrity in a database design?

    • Type: Practical

    • Expected Answer: Use constraints, triggers, and foreign keys.

58. What is the difference between BCNF and 3NF?

    • Type: Conceptual

    • Expected Answer: BCNF is stricter, ensuring no non-trivial functional dependencies.

59. How do you design a database for scalability?

    • Type: Scenario-Based

    • Expected Answer: Use partitioning, indexing, and sharding.

60. What is the purpose of the sys schema in SQL Server?

    • Type: Conceptual

    • Expected Answer: The sys schema stores system metadata for database objects.

---

Expert-Level Questions

These questions challenge senior professionals with complex scenarios, advanced SQL Server features, and critical system design tasks.

SQL Server DBA (Expert)

61. How do you implement Always On Availability Groups for high availability?

    • Type: Scenario-Based

    • Expected Answer: Configure replicas, listeners, and failover modes.

    • Example:

      CREATE AVAILABILITY GROUP AG1
      FOR DATABASE MyDatabase
      REPLICA ON 'Server1' WITH (ENDPOINT_URL = 'TCP://Server1:5022', FAILOVER_MODE = AUTOMATIC);

62. What is SQL Server’s Buffer Pool, and how do you optimize it?

    • Type: Conceptual

    • Expected Answer: The Buffer Pool caches data pages in memory; optimize by adjusting memory settings.

63. How do you recover a database from a corrupted transaction log?

    • Type: Scenario-Based

    • Expected Answer: Restore from a full backup and apply transaction log backups.

    • Example:

      RESTORE DATABASE MyDatabase FROM DISK = 'C:\Backups\MyDatabase.bak';

64. What is the role of SQL Server Profiler?

    • Type: Conceptual

    • Expected Answer: Profiler captures and analyzes database events for troubleshooting.

65. How do you optimize SQL Server for high-concurrency workloads?

    • Type: Scenario-Based

    • Expected Answer: Use connection pooling, optimize locking, and tune indexes.

    • Example:

      SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

66. What is the difference between physical and logical backups?

    • Type: Conceptual

    • Expected Answer: Physical backups copy files, while logical backups export data (e.g., SSIS).

67. How do you implement SQL Server log shipping?

    • Type: Practical

    • Expected Answer: Configure primary and secondary servers for log backups and restores.

    • Example:

      EXEC sp_add_log_shipping_primary_database @database = 'MyDatabase';

68. What is the role of Dynamic Management Views (DMVs)?

    • Type: Practical

    • Expected Answer: DMVs provide real-time system performance data.

    • Example:

      SELECT * FROM sys.dm_exec_query_stats;

69. How do you handle a deadlock scenario?

    • Type: Scenario-Based

    • Expected Answer: Identify deadlocks with Profiler or DMVs and optimize queries.

    • Example:

      SELECT * FROM sys.dm_tran_locks WHERE resource_type = 'OBJECT';

70. What is the role of the Query Store in SQL Server?

    • Type: Conceptual

    • Expected Answer: Query Store tracks query performance and execution plans for optimization.

SQL Server Developer (Expert)

71. How do you implement bulk insert in T-SQL?

    • Type: Coding Challenge

    • Expected Answer:

      BULK INSERT Employees
      FROM 'C:\Data\employees.csv'
      WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n');

72. What is the difference between a scalar and table-valued function?

    • Type: Conceptual

    • Expected Answer: Scalar functions return a single value, while table-valued return result sets.

73. How do you implement parallel query execution?

    • Type: Practical

    • Expected Answer: Use MAXDOP and optimize query plans.

    • Example:

      SELECT * FROM Sales OPTION (MAXDOP 4);

74. What is the role of the APPLY operator?

    • Type: Conceptual

    • Expected Answer: APPLY applies a function to each row of a table.

    • Example:

      SELECT e.EmployeeID, d.DeptName
      FROM Employees e
      CROSS APPLY (SELECT DeptName FROM Departments WHERE DeptID = e.DeptID) d;

75. How do you handle XML data in SQL Server?

    • Type: Practical

    • Expected Answer: Use XML data type and methods like query() or value().

    • Example:

      DECLARE @xml XML = '<employee><id>100</id></employee>';
      SELECT @xml.value('(/employee/id)[1]', 'INT');

76. What is the difference between ROW_NUMBER and RANK?

    • Type: Conceptual

    • Expected Answer: ROW_NUMBER assigns unique numbers, while RANK assigns same rank for ties.

77. How do you implement a recursive CTE?

    • Type: Coding Challenge

    • Expected Answer:

      WITH OrgChart AS (
        SELECT EmployeeID, ManagerID, FirstName
        FROM Employees WHERE ManagerID IS NULL
        UNION ALL
        SELECT e.EmployeeID, e.ManagerID, e.FirstName
        FROM Employees e
        INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
      )
      SELECT * FROM OrgChart;

78. What is the role of DBCC SHRINKDATABASE?

    • Type: Practical

    • Expected Answer: Shrinks database files but should be used cautiously due to fragmentation.

    • Example:

      DBCC SHRINKDATABASE ('MyDatabase');

79. How do you secure T-SQL code?

    • Type: Practical

    • Expected Answer: Use encryption or schema-based permissions.

    • Example:

      CREATE PROCEDURE MyProc WITH ENCRYPTION AS
      BEGIN
        SELECT * FROM Employees;
      END;

80. What is the difference between a correlated and non-correlated subquery?

    • Type: Conceptual

    • Expected Answer: Correlated subqueries reference outer queries, executed per row, while non-correlated are independent.

SQL Server Designer (Expert)

81. How do you design a database for high availability?

    • Type: Scenario-Based

    • Expected Answer: Use Always On, mirroring, or clustering with proper indexing.

82. What is the role of table partitioning in SQL Server?

    • Type: Conceptual

    • Expected Answer: Partitioning improves query performance and manageability for large tables.

83. How do you implement data archiving in SQL Server?

    • Type: Practical

    • Expected Answer: Use partitioning and stretch databases.

    • Example:

      ALTER TABLE Sales ENABLE STRETCH TO AZURE;

84. What is the difference between a heap and clustered table?

    • Type: Conceptual

    • Expected Answer: Heap stores data unordered, while clustered tables store data in index order.

85. How do you design a database for real-time analytics?

    • Type: Scenario-Based

    • Expected Answer: Use columnstore indexes and in-memory OLTP.

    • Example:

      CREATE TABLE Sales (
        SaleID INT,
        INDEX idx_sales NONCLUSTERED COLUMNSTORE
      );

86. What is the role of SQL Server’s In-Memory OLTP?

    • Type: Conceptual

    • Expected Answer: In-Memory OLTP stores tables in memory for faster transactions.

    • Example:

      CREATE TABLE Orders (
        OrderID INT PRIMARY KEY,
        MEMORY_OPTIMIZED = ON
      );

87. How do you model a temporal database?

    • Type: Practical

    • Expected Answer: Use system-versioned temporal tables.

    • Example:

      CREATE TABLE EmployeeHistory (
        EmpID INT,
        ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
        ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
      ) WITH (SYSTEM_VERSIONING = ON);

88. What is the difference between OLTP and OLAP in database design?

    • Type: Conceptual

    • Expected Answer: OLTP handles transactional data, while OLAP supports analytical queries.

89. How do you handle schema evolution in a production database?

    • Type: Scenario-Based

    • Expected Answer: Use online schema changes and backward-compatible migrations.

    • Example:

      ALTER TABLE Employees ADD NewColumn NVARCHAR(50) NULL;

90. What is the role of SQL Server’s Columnstore Indexes?

    • Type: Conceptual

    • Expected Answer: Columnstore indexes optimize analytical queries for data warehousing.

91. How do you design a database for GDPR compliance?

    • Type: Scenario-Based

    • Expected Answer: Implement data masking and encryption.

    • Example:

      ALTER COLUMN Salary ADD MASKED WITH (FUNCTION = 'default()');

92. What is the role of SQL Server’s Resource Governor?

    • Type: Conceptual

    • Expected Answer: Resource Governor manages resource allocation for workloads.

93. How do you design a database for audit logging?

    • Type: Practical

    • Expected Answer: Use temporal tables or audit features.

    • Example:

      AUDIT SERVER AUDIT MyAudit TO FILE (FILEPATH = 'C:\Audit');

94. What is the difference between a clustered and non-clustered index?

    • Type: Conceptual

    • Expected Answer: Clustered indexes define data order, while non-clustered are separate structures.

95. How do you optimize a star schema for data warehousing?

    • Type: Scenario-Based

    • Expected Answer: Use denormalized tables and columnstore indexes.

96. What is the role of SQL Server Integration Services (SSIS)?

    • Type: Conceptual

    • Expected Answer: SSIS manages ETL processes for data integration.

97. How do you design a database for global applications?

    • Type: Scenario-Based

    • Expected Answer: Use replication, sharding, and multilingual support.

98. What is the difference between a bitmap and B-tree index?

    • Type: Conceptual

    • Expected Answer: Bitmap indexes suit low-cardinality columns, while B-tree suit high-cardinality.

99. How do you implement row-level security?

    • Type: Practical

    • Expected Answer: Use security policies.

    • Example:

      CREATE SECURITY POLICY EmpPolicy
      ADD FILTER PREDICATE dbo.fn_security(@EmpID) ON Employees;

100. What is the role of SQL Server’s Stretch Database?

     • Type: Conceptual

     • Expected Answer: Stretch Database moves cold data to Azure for cost efficiency.

101. How do you design a database for high-concurrency applications?

     • Type: Scenario-Based

     • Expected Answer: Use optimistic locking and in-memory tables.

102. What is the role of SQL Server’s PolyBase?

     • Type: Conceptual

     • Expected Answer: PolyBase queries external data sources like Hadoop or Azure.

103. How do you handle data migration across SQL Server versions?

     • Type: Scenario-Based

     • Expected Answer: Use backup/restore or SSIS.

     • Example:

       BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase.bak';

104. What is the role of SQL Server Analysis Services (SSAS)?

     • Type: Conceptual

     • Expected Answer: SSAS supports OLAP and data mining for analytics.

105. How do you design a database for IoT data?

     • Type: Scenario-Based

     • Expected Answer: Use time-series tables and partitioning.

     • Example:

       CREATE TABLE SensorData (
         SensorID INT,
         Timestamp DATETIME,
         Value FLOAT
       ) PARTITION BY RANGE (Timestamp);

SQL Server Developer (Expert, Continued)

106. How do you implement a T-SQL scheduler?

     • Type: Coding Challenge

     • Expected Answer:

       EXEC msdb.dbo.sp_add_jobschedule @job_name = 'MyJob', @name = 'DailyRun', @freq_type = 4;

107. What is the role of JSON support in SQL Server?

     • Type: Conceptual

     • Expected Answer: JSON support enables parsing and querying JSON data.

     • Example:

       SELECT JSON_VALUE(data, '$.name') FROM JsonTable;

108. How do you handle large data sets in T-SQL?

     • Type: Scenario-Based

     • Expected Answer: Use bulk operations and temp tables.

109. What is the difference between a global and local temporary table?

     • Type: Conceptual

     • Expected Answer: Global temp tables (##) are shared across sessions, while local (#) are session-specific.

110. How do you implement a T-SQL REST API consumer?

     • Type: Coding Challenge

     • Expected Answer:

       DECLARE @response NVARCHAR(MAX);
       EXEC sp_invoke_external_rest_endpoint @url = 'http://api.example.com', @response = @response OUTPUT;

111. What is the role of DBCC CHECKIDENT?

     • Type: Practical

     • Expected Answer: Resets identity column values.

     • Example:

       DBCC CHECKIDENT ('Employees', RESEED, 1);

112. How do you implement error logging in T-SQL?

     • Type: Practical

     • Expected Answer: Use TRY-CATCH and log to a table.

     • Example:

       BEGIN TRY
         INSERT INTO Employees (EmployeeID) VALUES (NULL);
       END TRY
       BEGIN CATCH
         INSERT INTO ErrorLog (ErrorMsg) VALUES (ERROR_MESSAGE());
       END CATCH;

113. What is the difference between IDENTITY and SEQUENCE?

     • Type: Conceptual

     • Expected Answer: IDENTITY is table-specific, while SEQUENCE is database-wide.

114. How do you implement a T-SQL job chain?

     • Type: Practical

     • Expected Answer:

       EXEC msdb.dbo.sp_add_jobstep @job_name = 'MyJob', @step_name = 'Step1', @command = 'EXEC MyProc';

115. What is the role of OPENXML in SQL Server?

     • Type: Conceptual

     • Expected Answer: OPENXML parses XML data into relational format.

     • Example:

       EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

SQL Server Designer (Expert, Continued)

116. How do you design a database for microservices?

     • Type: Scenario-Based

     • Expected Answer: Use separate databases per microservice with API integration.

117. What is the role of SQL Server’s FileTables?

     • Type: Conceptual

     • Expected Answer: FileTables store and manage unstructured data as files.

118. How do you implement a data warehouse in SQL Server?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas and SSAS for analytics.

119. What is the difference between a snowflake and star schema?

     • Type: Conceptual

     • Expected Answer: Star schemas are denormalized, while snowflake schemas normalize dimension tables.

120. How do you design a database for time-series data?

     • Type: Practical

     • Expected Answer:

       CREATE TABLE TimeSeries (
         SensorID INT,
         Timestamp DATETIME,
         Value FLOAT
       ) PARTITION BY RANGE (Timestamp);

121. What is the role of SQL Server’s Service Broker?

     • Type: Conceptual

     • Expected Answer: Service Broker enables asynchronous messaging.

122. How do you handle cross-database queries in SQL Server?

     • Type: Practical

     • Expected Answer: Use fully qualified names or linked servers.

     • Example:

       SELECT * FROM Server2.MyDatabase.dbo.Employees;

123. What is the role of SQL Server’s Database Mail?

     • Type: Conceptual

     • Expected Answer: Database Mail sends email notifications for alerts.

124. How do you design a database for audit compliance?

     • Type: Scenario-Based

     • Expected Answer: Use audit features and temporal tables.

     • Example:

       ALTER SERVER AUDIT MyAudit ENABLE;

125. What is the difference between a global and local partition index?

     • Type: Conceptual

     • Expected Answer: Global indexes span partitions, while local indexes are partition-specific.

126. How do you implement data encryption in SQL Server?

     • Type: Practical

     • Expected Answer: Use TDE or column-level encryption.

     • Example:

       CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword';

127. What is the role of SQL Server’s Azure Arc?

     • Type: Conceptual

     • Expected Answer: Azure Arc extends SQL Server management to hybrid environments.

128. How do you design a database for high-frequency trading?

     • Type: Scenario-Based

     • Expected Answer: Use in-memory OLTP and optimized indexing.

129. What is the difference between ACID and BASE properties?

     • Type: Conceptual

     • Expected Answer: ACID ensures reliability, while BASE prioritizes availability.

130. How do you implement a data retention policy?

     • Type: Practical

     • Expected Answer: Use partitioning and automated jobs.

     • Example:

       ALTER TABLE Logs DROP PARTITION;

131. What is the role of SQL Server’s Replication?

     • Type: Conceptual

     • Expected Answer: Replication synchronizes data across servers.

132. How do you design a database for global applications?

     • Type: Scenario-Based

     • Expected Answer: Use replication and sharding.

133. What is the role of SQL Server’s Machine Learning Services?

     • Type: Conceptual

     • Expected Answer: Machine Learning Services integrate R and Python for analytics.

134. How do you handle schema versioning in SQL Server?

     • Type: Practical

     • Expected Answer: Use migrations and versioning scripts.

     • Example:

       ALTER TABLE Employees ADD Version INT DEFAULT 1;

135. What is the difference between a heap and index-organized table?

     • Type: Conceptual

     • Expected Answer: SQL Server uses heaps; index-organized tables are Oracle-specific.

136. How do you implement a change data capture (CDC) system?

     • Type: Practical

     • Expected Answer: Use SQL Server’s CDC feature.

     • Example:

       EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Employees';

137. What is the role of SQL Server’s Security Center?

     • Type: Conceptual

     • Expected Answer: Security Center provides vulnerability assessments and compliance.

138. How do you design a database for machine learning workloads?

     • Type: Scenario-Based

     • Expected Answer: Use SSAS and optimized storage.

139. What is the difference between a unique and non-unique index?

     • Type: Conceptual

     • Expected Answer: Unique indexes enforce uniqueness, while non-unique do not.

140. How do you implement a failover mechanism in SQL Server?

     • Type: Practical

     • Expected Answer: Use Always On Availability Groups.

     • Example:

       ALTER AVAILABILITY GROUP AG1 FORCE_FAILOVER;

141. What is the role of SQL Server’s Query Tuning Assistant?

     • Type: Conceptual

     • Expected Answer: Query Tuning Assistant recommends performance improvements.

142. How do you design a database for event-driven architectures?

     • Type: Scenario-Based

     • Expected Answer: Use Service Broker and triggers.

143. What is the difference between a full and incremental backup?

     • Type: Conceptual

     • Expected Answer: Full backups copy all data, while incremental copy changes.

144. How do you implement a data masking solution?

     • Type: Practical

     • Expected Answer: Use Dynamic Data Masking.

     • Example:

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

145. What is the role of SQL Server’s Temporal Tables?

     • Type: Conceptual

     • Expected Answer: Temporal tables track historical data changes.

146. How do you design a database for reporting?

     • Type: Scenario-Based

     • Expected Answer: Use star schemas and SSRS for reporting.

147. What is the role of SQL Server’s Resource Manager?

     • Type: Conceptual

     • Expected Answer: Resource Manager allocates resources to workloads.

148. How do you handle large-scale data imports?

     • Type: Practical

     • Expected Answer: Use SSIS or BULK INSERT.

     • Example:

       BULK INSERT Data FROM 'C:\data.csv';

149. What is the difference between a synonym and a view?

     • Type: Conceptual

     • Expected Answer: A synonym is an alias, while a view is a virtual table.

150. How do you implement a distributed transaction in SQL Server?

     • Type: Practical

     • Expected Answer: Use MSDTC for distributed transactions.

     • Example:

       BEGIN DISTRIBUTED TRANSACTION;

151. What is the role of SQL Server’s Extended Events?

     • Type: Conceptual

     • Expected Answer: Extended Events provide lightweight event tracking.

152. How do you design a database for high-performance analytics?

     • Type: Scenario-Based

     • Expected Answer: Use columnstore indexes and SSAS.

153. What is the difference between a physical and logical data model?

     • Type: Conceptual

     • Expected Answer: Physical models define storage, logical models focus on business rules.

154. How do you implement a backup strategy for a 24/7 database?

     • Type: Scenario-Based

     • Expected Answer: Use differential backups and Always On.

     • Example:

       BACKUP DATABASE MyDatabase TO DISK = 'C:\Backups\MyDatabase_diff.bak' WITH DIFFERENTIAL;

155. What is the role of SQL Server’s Data Quality Services (DQS)?

     • Type: Conceptual

     • Expected Answer: DQS ensures data accuracy and consistency.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone