SQL Server Interview Questions

Introduction

SQL Server is a relational database management system developed and maintained by Microsoft. Its primary functions include storing, retrieving, and manipulating data as requested by different software applications. With so many applications SQL server becomes one of the most important Relational database management systems (RDBMS), since it is an important topic many questions are asked about SQL Server in a technical interview.

In this blog, we will discuss some of the most important SQL Server interview questions and answers. This article consists of 35 SQL Server interview questions that are most likely to be asked in the Interviews. 

Most Asked SQL Server Interview Questions for 2024

1. What is SQL Server?

Ans. SQL Server is a Relational Database Management System (RDBMS) application software by Microsoft that can be used for designing, maintaining, managing, and deploying RDBMS systems. It is a widely used application because it allows numerous users to work on database systems simultaneously, with users ranging from small office workstations to massive Internet-based servers.

2. What do you understand by SQL Server Agent?

Ans. SQL Server Agent is a Windows service that allows you to schedule and run tasks. Each job has one or more steps, and each step has one or more tasks. As a result, the Server Agent uses the SQL Server to store job information and scheduled task execution.

The SQL Server Agent's key components are Jobs, Schedules, Operators, and Alerts.

Example:

If a corporation wants to back up its servers at 9:00 p.m. every Friday, it may easily automate the process and let the schedule run independently. When a backup meets a problem, the SQL Server Agent logs the event and alerts the appropriate team.

3. What is SQL Server Profiler?

Ans. The Microsoft SQL Server Profiler provides a user interface for creating and managing traces. The trace findings are also analyzed and replayed. Events are captured in a trace file, which may then be studied or utilized to repeat a specific sequence of actions when troubleshooting an issue.

SQL Server Profiler may be used for a variety of tasks, including

1. Identifying the source of the problem
2. Monitoring the performance of SQL Server in handling workloads is being monitored.
3. Troubleshooting Slow Queries
4. Capturing a series of SQL queries that create the problem to replicate the situation on the test server while troubleshooting the problem.
5. It also aids in simply connecting performance metrics to diagnose issues.

4. What are the different types of backups available in SQL Server?

Ans. Different backups which are available are:

• Differential Backup
• Full Backup
• Transactional Log Backup
• Files and Filegroup Backup
• Copy Only Backup

5. What is RDBMS?

Ans. Relational Database Management Systems, or RDBMS, are database management systems that store data in tables. We can establish connections between the tables. An RDBMS can recombine data elements from different files, providing strong data-usage capabilities.

6. What is Mirroring?

Ans. Mirroring is a high-availability solution. It is meant to keep a hot standby server transactionally consistent with the primary server. Transaction Log records are delivered directly from the primary server to a secondary server, which maintains the secondary server in sync with the primary server.

7. What is Log Shipping?

Ans. Log shipping is the automated backup and restoration of a database from one server to another independent standby server. If one server dies, identical data will be available on the backup server.

8. What are scheduled tasks in SQL Server?

Ans. Scheduled tasks or jobs are used to automate procedures that may be done regularly at a predetermined time. This job scheduling helps decrease human interference during the night, and feeding may be done at a certain time. The user can additionally specify which tasks should be produced.

9. Differentiate between SQL Server and MySQL.

10. What is JOIN in SQL, and what are the different types of joins?

Ans. A JOIN clause is used to join rows from two or more tables based on a common column. It is used to join two tables or extract data from them. SQL has four joins, which are as follows:

• Inner Join
• Right Join
• Left Join
• Full Join

Also see, Natural Join in SQL

11. What do you understand by recursive stored procedure?

Ans. A recursive stored procedure is a problem-solving strategy that allows you to return to the solution again.

12. Explain Magic tables in SQL Server.

Ans. Magic Tables are SQL Server tables that are automatically generated and are used to hold the entered, updated data for DML operations such as insert and update (SELECT, DELETE, INSERT, UPDATE, etc.).

13. On what TCP/IP port does SQL Server run?

Ans. SQL Server runs on port no 1433.

14. What is a subquery in SQL Server?

Ans. A subquery is a query defined within another query to get data or information from the database. The outer query is referred to as the main query, while the inner query is referred to as the subquery. Subqueries are always executed first, and their results are passed on to the main query. It can be nested within a SELECT, UPDATE, or other queries. A subquery can also utilize comparison operators like > and =.

15. What are relationships, and mention different types of relationships in SQL Server?

Ans. A relationship in DBMS is a situation in which two entities are connected to one another. In this case, the Table containing foreign keys refers to the other table's main key.

The following are the many types of relationships in DBMS:

• One-to-One relationship: It is used when a single row of Table X is related to a single row of Table Y.
• One-to-Many relationship: It is used when a single row of Table X is related to many rows of Table Y.
• Many-to-One relationship: Is is used when many rows of Table X are related to a single row of Table Y.
• Self-referencing relationship: It is used when a record of a Table is related to another record of the same Table.

16. What is COALESCE in SQL Server?

Ans. COALESCE returns the first non-null expression in the arguments. This method returns a non-null value from more than one column in the parameters.

Example-

Select COALESCE(empid, empname, empdep, salary) from employee;

17. How can we check the SQL Server version?

Ans. We can check the SQL Server version by running the following command:

SELECT @@Version

18. What is a CHECK constraint?

Ans. A CHECK constraint restricts the types of data or values that may be placed in a column. They are used to ensure the integrity of the domain.

For example,

CREATE TABLE Student(  
STD_ID int NOT NULL,  
Name varchar(255) NOT NULL,  
Age int,  
City varchar(255),  
CONSTRAINT CHK_Student CHECK (Age>20 AND City= 'Delhi') 
);  

19. What is the SIGN function used for?

Ans. The SIGN function is used to detect if a number is Positive, Negative, or Zero. This will return a value of +1, -1, or 0.

Example- 

SIGN(14) returns 1
SIGN(-29) returns -1

20. How can data be copied from one table to another?

Ans. 

INSERT INTO SELECT

This command inserts data into a previously constructed table.

SELECT INTO

This command creates a new table and copies the structure and data from an existing table.

21. What are SQL Server's two authentication modes?

Ans. Authentication is the process of identifying a user based on their login and password.

SQL Server has two authentication types.

Windows Authentication: It is SQL Server's default authentication mode. When trusted user and group accounts connect to the system, they are authenticated. They are not required to provide any further credentials.

Mixed Authentication: It supports both Windows and SQL Server authentication. SQL Server keeps a username and password for valid users.

You may select an authentication mode by modifying Server Authentication on the Security tab of SQL Server Management Studio's Properties.

22. Differentiate between Local Temporary Table and Global Temporary Table.

23. What are Indexes in SQL?

Ans. SQL indexes are used in relational databases to speed up data retrieval. They are comparable to indexes at the end of books, and their goal is to help you discover a topic fast. SQL Server supports Create Index, Alter Index, and Drop Index commands for creating a new index, updating an existing index, and deleting an index.

24. What are SQL Server Hotfixes and Patches?

Ans. Hotfixes are software patches that are deployed on live systems. A hotfix is a single, cumulative package containing one or more files that are used to address an issue with a software product.

As an example, consider a software bug,

A patch is a program placed on a machine to correct a flaw in the system and assure its security. A hotfix is a type of patch supplied by Microsoft.

Hotfixes are tiny changes in Microsoft SQL Server that solve specific issues, most commonly newly identified security flaws. 

25. What is CDC in SQL Server?

Ans. CDC stands for "Change Data Capture." It records the most recent INSERT, DELETE, and UPDATE operations performed on the SQL Server table. It saves modifications to the SQL server table in a suitable manner.

26. What do you mean by SQL Server's data quality services?

Ans. SQL Server's data quality services are a knowledge-driven data quality product. SQL Server Data Quality Services (DQS) allow users to create a knowledge base and then utilize it to accomplish operations such as data rectification, deduplication, enrichment, and standardization.

Apart from that, DQS offers profiling and allows you to undertake data cleaning via cloud-based data services.

DQS is composed of two components:

• Data Quality Server: It is a SQL Server instance feature that consists of three SQL Server catalogs that provide data-quality functionality and storage.
• Data Quality Client: It is a SQL Server tool that allows users to do computer-assisted data quality analysis and control the quality of their data.

27. Mention the command used to rename the database.

Ans. To rename a database, we can use the following command:

sp_renamedb 'oldDatabase', 'newDatabase';

28. How can we delete a table in SQL Server?

Ans. We can use the following delete command to delete a table:

DELETE TableName 

29. Differentiate between the WHERE clause and the HAVING clause.

30. What are some of the common performance issues faced by users?

Ans. The common performance issues in SQL Server are as follows:

• Fragmentation
• Input/Output bottlenecks
• Blocking Queues
• Deadlocks
• Missing and unused indexes

31. How can you optimize a SQL Server query using complex joins and subqueries?

We can optimize an SQL server query with complex joins and subqueries using:

1. Indexing- We can create indexes on columns that are used in JOIN conditions and also in WHERE clauses.
2. Query Refactoring- We can simplify the query by breaking it into smaller pieces.
3. Execution Plan- We can analyze the execution plan so that we can identify inefficient operations.
4. Statistics and Maintenance- We can ensure up-to-date database statistics and perform regular index maintenance.

32. What is a SQL Server "table partitioning" and how it helps to improve performance?

SQL server table partitioning divides a large table into smaller tables. It makes more manageable pieces also known as partitions, based on a specified column. It improves the performance by:

1. Improving Query Performance
2. Enhancing Manageability
3. Optimizing Indexing

33. How does SQL Server handle transactions, and what is the significance of the SERIALIZABLE isolation level?

SQL Server handles transactions to ensure data integrity and consistency. The SERIALIZABLE isolation level is the topmost level of isolation. It also ensures to complete isolation of transactions. 

34. What is the main use of the feature of Database Snapshot in SQL Server?

A Database Snapshot in SQL Server creates a read-only static view of a database at a specific point in time. It is used for:

1. Reporting: It can run reports on a static dataset without affecting the production database.
2. Recovery: It can provide a recovery point in case of accidental data loss or corruption.
3. Testing: It can perform testing and validation on a snapshot without impacting the live database.

35. What do you mean by SQL Server Always On Availability Groups and what are the benefits of it?

SQL Server Always On Availability Groups helps to provide high availability and disaster recovery for databases. It offers several benefits, such as:

1. Automatic failover to a secondary replica in case of primary replica failure. It helps in minimizing the downtime.
2. Secondary replicas can be used for read-only operations, offloading read traffic from the primary.
3. It enables automated backups. It also ensures data consistency across replicas.
4. It allows for the failover of multiple databases as a group. It also simplifies the management and reduces the complexity.