Intermediate SQL Server Interview Questions

This section delves into commonly encountered interview questions for intermediate users. Test your knowledge on topics like joins, functions, and performance optimization to impress your interviewer.

16. How to Determine If an Index is Effective?

• Execution Plans: Analyze the execution plans of queries that use the index. Look for Index Seek operations instead of Index Scan operations. Seek operations indicate that the index is effectively narrowing down the rows being retrieved.
• Query Performance: Measure the query execution time with and without the index. If the query performs significantly better with the index, it indicates that the index is effective.
• Index Usage Statistics: SQL Server maintains statistics about index usage. You can query dynamic management views like sys.dm_db_index_usage_stats to see how frequently an index is being used. If an index is rarely or never used, it might not be effective and could potentially be dropped.
• Index Design: Evaluate the design of the index itself. Consider factors such as the columns included in the index, their data types, and the order of columns.

17. Can We Write a Query to find Unused Indexes?

We can find unused indexes by querying the dynamic management views (DMVs) provided by SQL Server. Here’s a query to find unused indexes:

SELECT
    OBJECT_NAME(s.[object_id]) AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates
FROM
    sys.dm_db_index_usage_stats AS s
INNER JOIN
    sys.indexes AS i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
WHERE
    OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND (s.user_seeks + s.user_scans + s.user_lookups) = 0
ORDER BY
    TableName,
    IndexName;

This query retrieves information about indexes along with their usage statistics. Unused indexes will have NULL values for user seeks, scans, and lookups but non-NULL values for user updates.

18. What are the Benefits of using Views in SQL Server?

• Simplicity and Abstraction: Views provide a way to abstract complex SQL queries into a single, easy-to-use object.
• Data Security: Views can be used to restrict access to certain columns or rows of a table. By granting users access to views rather than the underlying tables, you can implement security policies to control what data users can see.
• Performance Optimization: Views can help optimize query performance by precomputing joins, aggregations, or complex calculations.
• Simplified Data Access: Views can simplify data access by combining data from multiple tables into a single virtual table. This can eliminate the need for complex joins in application code, making queries more straightforward and easier to manage.

19. How to Create a View with Joins and Aggregates?

Here we have created a view named loss_summary_view with columns week, from_code, to_code, count_a, sum_x, sum_y, and sum_z. The view is constructed by selecting data from tables history and calendar and performing joins and aggregate functions.

CREATE VIEW loss_summary_view (week, from_code, to_code, count_a, sum_x, sum_y, sum_z)
AS 
SELECT c.week, h.from_code, h.to_code, COUNT(h.a) AS count_a, SUM(h.x) AS sum_x, SUM(h.y) AS sum_y, SUM(h.z) AS sum_z
FROM history AS h
JOIN calendar AS c ON c.month = 100610 AND c.day = h.day 
GROUP BY c.week, h.from_code, h.to_code;

20. How to Update Data through a View?

Create a view showing customers from Paris using the WITH CHECK OPTION statement.

CREATE VIEW vwCustomersParis
AS
 SELECT CompanyName, ContactName, Phone, City
 FROM Customers
 WHERE City = 'Paris'
WITH CHECK OPTION

Update the above created view by moving everyone from Paris to Lyons.

UPDATE vwCustomersParis
SET City = 'Lyons'

The above statement update data by moving everyone from Paris to Lyons will fail because Lyons does not meet the criteria defined in the view.

21. Explain the Security Implications of using Views.

• Data Restriction: Views can restrict access to sensitive data by exposing only specific columns or rows from underlying tables. This allows administrators to control who can see what data, enhancing overall data security.
• Access Control: By granting permissions on views rather than underlying tables, administrators can implement fine-grained access control. This means users can be given access to specific views without needing direct access to the underlying tables, reducing the risk of unauthorized data access.
• Data Masking: Views can be used to apply data masking techniques, such as replacing sensitive data with masked values or aggregating data to provide a summarized view while protecting underlying details. This helps prevent unauthorized users from accessing sensitive information.

22. How to Identify Performance Bottlenecks in a Query?

• To identify performance bottlenecks, start by monitoring our queries for execution time, resource consumption, and query plan.
• Tools like SQL Server Profiler can help capture and analyze query metrics, highlighting queries that are slow, resource-intensive, or have suboptimal plans.
• Use SQL Server Management Studio (SSMS) or another database management tool to execute the query and view its execution plan. Look for areas with high estimated or actual costs, such as table scans, index scans, key lookups, or sorts.

23. What are some Techniques for Query Optimization?

• Query Execution Plans: The query execution plan in SQL Server details how a query is executed. It shows which tables are accessed, how they are accessed, how they are joined, and any other operations performed.
• Statistics: Regularly update statistics to ensure the query optimizer has accurate information for generating efficient execution plans. Use the UPDATE STATISTICS command or enable auto-update statistics.
• Query Rewriting: Rewrite queries to use more efficient syntax or to optimize joins, filters, and subqueries. Simplify complex queries to improve performance.
• Avoid Cursors: Avoid using cursors whenever possible, as they can lead to poor performance. Instead, use set-based operations like SELECT, INSERT, UPDATE, and DELETE to manipulate data.

24. Explain the Role of Statistics in Query Optimization.

• Query Plan Generation: When SQL Server receives a query, the query optimizer evaluates various execution plans to determine the most efficient way to retrieve the required data. Statistics provide the optimizer with estimates of the number of rows and data distribution, helping it choose the optimal plan.
• Index Selection: Statistics help the optimizer decide whether to use indexes or perform a table scan.
• Join Order and Join Type Selection: Statistics provide information about the size and selectivity of tables involved in join operations.

25. How can we Use Partitioning to Improve Performance?

• For very large databases and tables, such as one with 27 billion rows, table partitioning can significantly improve query performance. By partitioning a table, queries can be applied to only the relevant partitions, making them more efficient and faster.
• Partitioning simplifies data management by logically dividing large tables or indexes into smaller, more manageable partitions. This can lead to faster data loads, deletes, and index rebuilds, as operations are performed on smaller subsets of data.
• Partitioning can increase parallelism by allowing SQL Server to process multiple partitions concurrently. This can lead to faster query execution times, especially for CPU-bound queries that can benefit from parallel processing.

26. Describe Different Authentication Modes in SQL Server.

In SQL Server, there are two different kinds of authentication modes:

• Windows Authentication Mode: In Windows Authentication mode, SQL Server uses the Windows username for authentication, and the SQL Server username and password boxes are disabled. Authentication is based on Windows credentials rather than SQL Server credentials.
• Mixed Mode: SQL Server supports both Windows and SQL Server authentication modes. we can require a username and password for authentication, allowing users to connect through either Windows authentication or SQL Server authentication. Administrators can maintain user accounts in SQL Server.

27. How can we Implement Role-Based Access Control (RBAC) in SQL Server?

1. Identify Roles: Determine the roles needed for our application or system.

2. Create Database Roles: Use the CREATE ROLE statement to create database roles that correspond to the identified roles.

CREATE ROLE SalesRole;
CREATE ROLE HRRole;

3. Assign Permissions to Roles: Grant appropriate permissions to each role using the GRANT statement.

GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.SalesTable TO SalesRole;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.EmployeeTable TO HRRole;

4. Assign Users to Roles: Add users to the appropriate roles using the ALTER ROLE statement or the sp_addrolemember stored procedure.

ALTER ROLE SalesRole ADD MEMBER SalesUser;
EXEC sp_addrolemember 'HRRole', 'HRUser';

5. Use Role-Based Security: Modify our application or system to utilize role-based security. Instead of directly granting permissions to users, check their roles and grant permissions based on their roles.

IF USER_IN_ROLE('SalesRole')
BEGIN
    -- Allow access to sales-related functionality
END
ELSE IF USER_IN_ROLE('HRRole')
BEGIN
    -- Allow access to HR-related functionality
END

28. What are Stored Procedures and How can they Improve Security?

Stored procedures in SQL Server are precompiled sets of SQL statements that can be reused.

Stored procedures can improve security in several ways:

• Access Control: Granting permissions to execute stored procedures instead of directly on tables controls data access tightly.
• Parameterized Queries: Stored procedures accept parameters, preventing SQL injection attacks.
• Transaction Management: They manage transactions, ensuring data integrity by grouping SQL statements into atomic operations.

29. Explain Different Types of Replication in SQL Server.

In SQL Server, three different types of replications are available:

• Snapshot replication: Snapshot replication is ideal for replicating data that changes infrequently and is easy to maintain. For example, it can be used to distribute lists that are updated once per day from a main server to branch servers.
• Transactional replication: Transactional replication is a method of data distribution from a publisher to a subscriber. It is suitable for scenarios where data changes frequently.
• Merge replication: Merge replication consolidates data from various sources into a single centralized database. It is used when central and branch databases need to update information simultaneously.

30. When would we use Transactional Replication Versus Merge Replication?

Transactional Replication:

• For near real-time data distribution from a publisher to subscribers.
• One-way replication with minimal latency.

Merge Replication:

• For bidirectional synchronization of frequently changing data.
• Suitable for scenarios with disconnected or mobile clients.

Are you preparing for an SQL Server interview? Preparing for a SQL Server interview requires a comprehensive understanding of SQL concepts, ranging from basic to advanced levels. Whether you’re a seasoned professional or a beginner, it’s essential to review and practice common interview questions to ensure readiness.

In this interview preparation guide, we’ll cover a range of SQL Server interview questions categorized by difficulty level, from easy to advanced, including query-based questions. From explaining fundamental SQL Server concepts to tackling complex query scenarios, this comprehensive resource will help you ace your SQL Server interview.