Top 50 SQL Server Interview Questions You Must Prepare In 2024

In the era where humongous amounts of data get generated on a day to day basis. Data plays a crucial role in decision making for business operations. So, to handle data we need databases and this gives us the need to understand database management systems. With various database management systems, MS SQL Server is one of the most popular relational database management systems. This type of DBMS uses a structure that allows users to identify and access data in relation to another piece of data in the database. So, knowing MS SQL Server opens the doors for you to become a Database Administrator. I believe that you are already aware of these facts and this has made you land on this MS SQL Server Interview Questions article.

In this article on Microsoft SQL Server Interview Questions, I will be discussing the top questions related to MS SQL Server asked in your interviews. These questions are collected after consulting with people having excellent skills in this field. 

Let’s get started!

Q1. Mention the differences between SQL Server and MySQL.

Q2. What do you understand by SQL Server Agent?

SQL Server Agent is a Windows service which is used to schedule and execute jobs. Here, each job contains one or more step, and each step contains a task. So, the Server Agent uses the SQL Server to store job information and run a job on a schedule.

The main components of the SQL Server Agent are Jobs, Schedules, Operators, and Alerts.

Example: 

If an enterprise wishes to take a backup of the company servers at 9:00 pm on every Friday, then you can very well automate this task to let the schedule happen on its own. In a scenario, the backup encounters an error, the SQL Server Agent records the event and notifies the corresponding team.

Q3. Mention the different authentication modes in SQL Server.

Before I tell you the different authentication modes in SQL Server, let me tell you that the authentication mode is used for authenticating a user in the SQL Server. The authentication mode is selected while setting up the database engine. So, if you want to know how to set up Microsoft SQL Server, you can refer to my article.

The different authentication modes offered by SQL SERVER are as follows:

• Windows Authentication Mode: This mode is used to connect the server through a Windows account. Here, the server takes computers’ username and password for authentication purposes. Also, the SQL server authentication mode is disabled in this mode.
• Mixed mode: The Mixed mode is used to connect with an instance of SQL Server using either the SQL Server authentication or Windows authentication. In this mode, a username and password is set by the user for the database.

Q4. Mention the differences between local and global temporary tables.

Q5. How can you check the version of SQL Server?

To check the version of SQL Server, you can use the following command:

SELECT @@version

The @@VERSION gives output as one nvarchar string. 

Q6. What is single-user mode and what are the steps you should follow to start SQL Server in single-user mode?

It may often happen that you would want to start an instance of SQL Server in the single-user mode. You could do this, either when you want to recover data from other database systems or may want to change server configurations.

When you start the SQL Server in the single-user mode then, any member of the computer’s local Administrators group gets connected to the instance of SQL Server as sysadmin.

The following events occur on starting the database in single-user mode:

• A single user connects to the server.
• The CHECKPOINT process is not executed since it is executed by default at the startup.

Also, note that you have to stop the SQL Server Agent service before connecting to an instance of SQL Server in the single-user mode.

• To start  SQL Server in single-user mode use the command: sqlcmd –m 
• To connect through the Query Editor in Management Studio use:  -m"Microsoft SQL Server Management Studio - Query".

Q7. What is SQL Server Profiler?

The Microsoft SQL Server Profiler is an interface used to create and manage traces. It also analyzes and replays the trace results. Here, events are saved in a trace file which are later analyzed or used to replay a specific series of steps while debugging an issue.

You can use SQL Server Profiler for activities such as:

1. Finding the root cause of the problem
2. Monitoring the performance of SQL Server to handle the workloads.
3. Diagnosing the slow queries
4. Capturing a series of SQL statements causing problem, to further replicate the problem on the test server, while debugging the issue.
5. It also helps in correlating performance counters to debug issues easily.

Q8. What is the TCP/IP port on which SQL Server runs?

The TCP/IP port on which SQL Server runs is 1433.

Q9. What are subqueries in SQL server? Explain its properties.

A subquery is a query inside another query where a query is defined to retrieve data or information back from the database. In a subquery, the outer query is called as the main query whereas the inner query is called subquery. Subqueries are always executed first and the result of the subquery is passed on to the main query. It can be nested inside a SELECT, UPDATE or any other query. A subquery can also use any comparison operators such as >,< or =.

The properties of subqueries are as follows: 

• Must be enclosed in parentheses as it must be executed first before the main query
• More than one query can be included.
• A sub query should not have ORDER BY clause but can include WHERE, GROUP BY, and HAVING CLAUSE 
• The subquery must be on the right hand side of the comparison operator of the main query
• A subquery must include the SELECT clause and a FROM clause.

Q10. How do you start single user mode in clustered installations?

In clustered installation, the SQL Server uses DLL available connection, and thus blocks any other connections to the server.

In this state, if you try to bring SQL Server Agent resources online, then it may fail over SQL resources to a different node as it could be configured to a group. To start a single user-mode in clustered installation, you can follow the steps below:

1. Go to advanced properties and remove -m startup parameter.
2. Now, put the SQL Server resource offline.
3. Issue the following command from the command prompt, and make sure you are at the current owner node of the group:
   net start MSSQLSERVER /m.
4. Next, you have to verify from the cluster administrator or fail over cluster management console that SQL Server resource is still offline or not.
5. Then, connect to the SQL Server by using the following command and perform the required operation: SQLCMD -E -S<servername>.
6. Once an operation is complete, you have to close the command prompt and then bring back the SQL and other resources online through cluster administrator.

Q11. What do you understand by replication in SQL Server? Mention the different types of replication in SQL Server.

Replication in Microsoft SQL Server is a process to synchronize the data across multiple servers. This is generally done by a replica set, and these sets provide multiple copies of data with redundancy and high availability on different servers.

Not only this, but replication provides a mechanism to recover from failures. It also removes dependencies from single server to protect the loss of data from a single server.

Following are three types of replications in SQL Server:

1. Merge Replication: This replication groups the data from various sources to a single centralized database and is used in the server to the client environment.
2. Transactional Replication: This replication is a process of distributing data from publisher to subscriber and is used in server to server environment.
3. Snapshot Replication: This replication distributes data exactly as it appears at a specific moment and is used for replicating data, which changes infrequently. 

Q12. What are the differences between MS SQL Server & Oracle?

Q13. What do you understand by INTENT locks?

Microsoft SQL Server uses a lock hierarchy whenever the data is  read or something is changed in the data. Whenever a row is read, SQL Server acquires a shared lock. Similarly, as soon as we change a row, SQL Server acquires an Exclusive lock. These locks are incompatible with each other. So, the INTENT locks are used to indicate at a higher level which locks are applied within a lock hierarchy. There are mainly three kinds of INTENT locks:

1. Intent Shared Lock(IS): This lock is used when you have a shared lock at the row level.
2. Intent Update Lock(IU): The Intent update lock is used when you have an update lock at the row level.
3. Intext Exclusive Lock(IX): This lock is used when you have an exclusive lock at the row level.

Q14. What are the steps you must follow to hide SQL Server instances?

The steps you must follow to hide SQL Server instances are as follows:

• Open the SQL Server Configuration Manager and expand the SQL Server Network Configuration.
• Then go to Protocols and choose the instance of SQL Server.
• Later on, right-click on the instance and select the Properties

• Next, in the Hide Instance box, go to the on the Flags tab, and select Yes. 
• Finally, click on OK, and close the dialog box.

Q15.  What do you understand by the data quality services in SQL Server?

The data quality services in SQL Server is a knowledge-driven data quality product. SQL Server Data Quality Services (DQS)  enable the user to build a knowledge base and thereafter use it to perform tasks such as correction, deduplication, enrichment, standardization of data.

Apart from this, DQS also provides profiling and enables you to perform data cleaning with the help of cloud-based data services.

DQS consists of two components:

• Data Quality Server: It is an SQL Server instance feature consisting of three SQL Server catalogs with data-quality functionality and storage
• Data Quality Client: It is an SQL Server feature which users can use to perform computer-assisted data quality analysis and manage their data quality interactively.

Q16. Explain Magic Tables in SQL server

Magic Tables are tables automatically created tables in SQL Server used to internally store the inserted, updated values for DML operations such as (SELECT, DELETE, INSERT, UPDATE, etc).

Q17. What do you understand by Change Data Capture?

Change Data Capture or most commonly known as CDC is used to record INSERT, UPDATE, DELETE activities applied on the tables. So, as the name suggests, Change Data Capture is used to capture the data that has been changed recently. The column information and metadata required to apply the changes to the target environment are captured for the modified rows and are eventually stored in the change tables. These change tables are the mirror image of the original column structure.  

Q18. What do you understand by triggers and mention the different types of it?

Triggers are used to execute batches of SQL code whenever INSERT, DELETE, or UPDATE commands are executed against a table. So, basically triggers are automatically executed whenever the data is modified based on the data manipulation operations.

The different types of triggers are as follows:

1. INSERT
2. UPDATE 
3. DELETE
4. INSTEAD OF

Q19. What do you understand by recursive stored procedure?

A recursive stored procedure is a problem-solving method through which you can arrive at the solution again and again. 

Q20. Explain Log Shipping and mention its advantages.

The process of automation of backup to restore databases from one standalone server to another standalone standby server is known as Log Shipping. You can also understand Log shipping as one of the disaster recovery solutions, as it makes sure that even if one server fails, the standby server will have the same data as that of the server itself.

The advantages of Log Shipping are as follows:

• Needs low maintenance and is easy to set up
• The secondary database created is used for read-only purpose.
• You can create multiple secondary standby servers 
• Allows a delay specified by the user for the time between the primary server backs up the log of the primary database when the secondary servers restore (apply) the log backup.

Q21. What are trace flags and mention a few common trace flags used with SQL Server?

These flags are used to alter server behavior or set server characteristics. Few common trace flags used with SQL Server are as follows

• 1204, 1205, 1222 – These flags are used for deadlock Information.
• 174 –  This trace flag increases the SQL Server Database Engine plan cache bucket count from 40,009 to 160,001 on 64-bit systems.
• 1118 – Forces uniform extent allocations instead of mixed page allocations – (SQL 2005 and 2008) To reduces TempDB contention.
• 652  – This trace flag disables page prefetching scans. 
• 2566 – Used to run the DBCC CHECKDB command without data purity check, unless the DATA_PURITY option is specified.

Q22. Mention the differences between SUBSTR and CHARINDEX in SQL Server.

Q23. What do you understand by the Analysis Services in SQL Server?

Analysis Services in Microsoft SQL Server is an analytical data engine used in business analytics and decision support. This service provides enterprise-grade semantic models for client applications and reports such as Power BI, Microsoft Excel, and other visualization tools. 

The Analysis Services is available in platforms such as :

1. Azure Analysis Services
2. Power BI Premium
3. SQL Server Analysis Services

Q24. What do you understand by Mirroring and mention the advantages of the Mirroring?

Mirroring in SQL Server is designed to maintain a hot standby server, that is consistent with the primary server in terms of a transaction. Also, the transaction log records are sent from the principal server to the secondary server. 

Following are the advantages of Mirroring:

1. Consists of an automatic fail over mechanism.
2. More efficient than Log Shipping and is more robust.
3. The  primary server  is synced with the secondary server in real-time

Q25. When do you think a developer should use SQL Server-based cursors?

SQL Server-based cursors are used when you wish to work on a record at any instance of time, rather than taking all the data from a table as a bulk. However, cursors are not preferred to be used when large volumes of data is present as it affects performance. In a scenario, where it is not possible to avoid cursors, then try to reduce the number of records to process by using a temporary table and then eventually build the cursor from this.

Q26. What part does database design plays a role in performance of an SQL Server-based application?

The physical and logical design plays an important part in the performance of SQL Server-based applications. We need to ensure that correct data is captured in proper tables, the data items have proper relationships between them and data redundancy is reduced. I would also suggest that while you are designing a database, make sure that it is an iterative process to achieve all the required system goals and is under constant observation. Once the database design is set, it is very tough to change the design according to requirement. You can only add new relationships and data items.

Q27. What do you understand by User-Defined function in the SQL Server and explain the steps to create and execute a user-defined function in the SQL Server?

A user-defined function is a function written as per the needs of the user by implementing logic. In these kinds of functions the user is not limited to pre-defined functions and simplify the complex code of predefined function by writing simple code. This function returns a scalar value or a table.

To create a user-defined function, refer to the following example:

CREATE FUNCTION samplefunc(@num INT)
RETURNS TABLE
AS
RETURN SELECT * FROM customers WHERE CustId=@num

To execute the above-created function, refer to the following command:

SELECT * FROM samplefunc(10)

Q28. How can you ensure that the database and SQL Server based application perform well?

A developer must check type of information stored, volume of data and the data that will be accessed. 

In a scenario, where you are upgrading an existing system, you should analyze the present data, existing data volumes occur, and check the method through which data is accessed, to help you understand the problem areas for design.

In a scenario, where you are using a new system, you have to keep the information about what data will be captured, and what are the constituents of data, and the relationship between the data items.

Q29. What are relationships and mention different types of relationships in the DBMS

A relationship in DBMS is the scenario where two entities are related to each other. In such a scenario, the table consisting of foreign key references to that of a primary key of the other table.

The different types of relationships in DBMS are as follows:

• One-to-One Relationship –  Used when a single row in Table A is related to a single row in Table B.
• One-to-Many Relationship – Used when a single row in Table A is related to many rows in table B.
• Many-to-Many Relationship – Used when many rows in table A can be related to many rows in table B.
• Self -Referencing Relationship – Used when a record in table A is related to record in same table.

Q30. What are joins in SQL and what are the different types of joins?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. It is used to merge two tables or retrieve data from there. There are 4 joins in SQL namely:

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

Q31. What is the command DBCC CHECKDB used for?

The command DBCC CHECKDB is used to check the physical and logical integrity of all the objects in the mentioned database. To do that, it performs the following operations:

• Runs DBCC CHECKALLOC on the mentioned database.
• On every table and view in the database, the DBCC CHECKTABLE  command is executed.
• Runs DBCC CHECKCATALOG on the database.
• Then it validated the contents of every indexed view in the mentioned database.
• It also validates link-level consistency between file system directories and table metadata while storing varbinary(max) data in the file system using FILESTREAM.
• Finally, it validates the Service Broker data in the database.

So, you just have to execute the DBCC CHECKDB command, and automatically, DBCC CHECKALLOC, DBCC CHECKTABLE, or DBCC CHECKCATALOG commands get executed.

Also, note that DBCC is supported on databases that contain memory-optimized tables but does not provide repair options. This implies that you must regularly back up databases and test those backups.

Q32. What do you understand by CHECK constraint in SQL Server?

The CHECK constraint in SQL Server is used to limit the values or type of data stored in a column. Once you apply the CHECK constraint on a single column, you can go forward and apply specific values for that particular column.

Example: 

CREATE TABLE Customer (  
    Cust_ID int NOT NULL,  
    FirstName varchar(255),  
    Age int,  
    City varchar(255),  
    CONSTRAINT CHK_Customer CHECK (Age>20 AND City= 'Hyderabad')  
);  

Q33.  What do you understand by COALESCE in SQL Server?

This function is used to return the first non-null expression within arguments. The COALESCE command is used to return a non-null value from more than a single column in arguments.

Example:

SELECT COALESCE(CustID, CustName, Amount) from Customers;

Q34. Explain the usage of FLOOR function in SQL Server.

The FLOOR function is used to round up a non-integer value to the previous least integer value. This function returns a unique value after rounding the digits.

Syntax:

FLOOR(expression)

Example:

FLOOR(7.3)

Q35. What is the command used to check locks in Microsoft SQL Server?

To check locks in the database, you can use the in-built stored procedure sp_lock.

Syntax

sp_lock [ [ @spid1 = ] 'session ID1' ] [ , [@spid2 = ] 'session ID2' ]
[ ; ]

Example:

To list all the locks currently held in an instance of the Database Engine, use the following command:

USE SampleDB;  
GO  
EXEC sp_lock;  
GO  

Q36. Mention the 3 ways to get a count of the number of records in a table.

The following are three ways to count the number of records in the table:

SELECT * FROM TableName;
SELECT COUNT(*) FROM TableName;
SELECT rows FROM indexes WHERE id = OBJECT_ID(TableName) AND indexid< 2;

Q37. What is the usage of the SIGN function?

This function is used to determine whether the mentioned number is zero, positive, and negative. So, it will either return 0, +1, -1.

Syntax:

SIGN(number)

Example:

SIGN (0)  returns 0
SIGN (21)  returns 1
SIGN (-21)  returns -1

Q38. Write an SQL Query to find first weekday of the month?

To find the first week day of the month, you can write a query as follows:

SELECT DATENAME(dw, DATEADD(dd, – DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay;

Q39. Mention the command used to rename the database.

To rename a database, you have to use the sp_renamedb command in the following manner:

sp_renamedb 'OldDatabaseName', 'NewDatabaseName';

Q40. Write a query to find 5th highest amount paid from the Customer table.

To find the 5th highest amount paid from the customers’ table, you can write a query as below:

SELECT TOP 1 amount FROM (SELECT DISTINCT TOP 5 amount FROM customers ORDER BY amount DESC) ORDER BY amount;

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

To delete a table in SQL Server, use the Delete command. 

Syntax:

DELETE TableName

Example:

DELETE Customers;

Q42. What is the purpose of UPDATE STATISTICS and SCOPE_IDENTITY() function?

• UPDATE _STATISTICS is used to update the information used by indexes such as the distribution of key values for one or more statistic groups in the mentioned indexed view or table. 
• SCOPE_IDENTITY is used to create identity value for tables in the current execution scope.

Q43. What do you understand by PHYSICAL_ONLY option in DBCC CHECKDB?

• The PHYSICAL_ONLY option is used to limit check to the integrity of the physical structure of the record headers, page, and also the allocation consistency of the database.
• The PHYSICAL_ONLY check is used to provide a small overhead check of the physical consistency of the database.
• Also, the PHYSICAL_ONLY option causes a shorter run-time for DBCC CHECKDB on large databases. So, it is generally recommended for frequent use on production systems.

Q44. Can you explain how long are locks retained within the REPEATABLE_READ and SERIALIZABLE isolation levels, during a read operation with row-level locking?

With REPEATABLE_READ and SERIALIZABLE isolation levels, locks are held during the transaction.  But, if you consider READ_COMMITTED, then locks are held for isolation level.

Q45. Mention the differences between HAVING and WHERE clause.

Note: Whenever GROUP BY is not used, HAVING behaves like a WHERE clause.

Q46. What do you understand by integration services in SQL Server?

Integration services is a platform offered by Microsoft to build enterprise-level data transformation solutions and integration. These services solve complex business problems by loading data warehouses, perform data wrangling, copy or download files, and manage SQL Server objects.

Also, integration services can extract and transform data from a wide variety of sources such as relational data sources, XML data files, load the data into more than a single database. So, basically, you can use the integration services to create solutions without coding, code complex tasks, program the extensive integration object model to create packages.  

The integration services include good set of built-in tasks and transformations, graphical tools used for building packages and also contain the Catalog database to store, run and manage packages.

Q47. What do you understand by Hotfixes and Patches in SQL Server?

Hotfixes are single, cumulative software packages applied to live systems. This includes one or more files used to address a problem in a software product. Patches are a programs installed on the machines to rectify the problem occurred in the system and ensured the security of the system. So, basically hotfixes are a kind of patch offered by Microsoft SQL Server  to address specific issues.

Q48. Can you name a few encryption mechanisms in SQL server? 

These are few encryption mechanisms in SQL Server to encrypt data in the database:

1. Transparent Data Encryption
2. Symmetric Keys
3. Asymmetric Keys
4. Transact SQL functions
5. Certificates

Q49.  What are the options which must be set to allow the usage of optimistic models?

The READ_COMMITED_SNAPSHOT option and the ALLOW_SNAPSHOT_ISOLATION option must be set to allow the usage of optimistic models.

• The READ_COMMITTED_SNAPSHOT option is used for the read committed optimistic model.
• ALLOW_SNAPSHOT_ISOLATION option is used for the snapshot isolation level.

Q50. What are the common performance issues in SQL Server?

The common performance issues in SQL Server are as follows:

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

If you wish to learn Microsoft SQL Server and build a career in the relational databases, functions, queries, variables, etc domain, then check out our interactive, live-online Microsoft SQL Server certification CourseC here, which comes with 24*7 support to guide you throughout your learning period.

So this brings us to the end of the SQL Server Interview Questions article. I hope this set of SQL Server Interview Questions will help you ace your job interview. All the best for your interview!

Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions, etc. End of the training you will be able to create and administer your own MySQL Database and manage data.

Got a question for us? Please mention it in the comments section of this “SQL Server Interview Questions” article and we will get back to you as soon as possible.