Top 50 SQL Server Interview Questions You Must Prepare In 2023

SQL Server Interview Questions

1. Mention the differences between SQL Server and MySQL.

2. 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.

3. 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.

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

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

There are several ways to check the version of SQL Server:

1. Using T-SQL: You can use the following T-SQL statement to check the version of SQL Server:

Copy codeSELECT @@VERSION

This will return the version number and other information about the instance of SQL Server you are connected to.

2. Using SQL Server Management Studio (SSMS): You can check the version of SQL Server by opening SSMS and connecting to the instance of SQL Server you want to check. The version number is displayed in the Object Explorer under the “Management” node.
3. Using the Windows Registry: You can also check the version of SQL Server by looking in the Windows Registry. To do this, follow these steps:

• Press the Windows key + R to open the Run dialog box.
• Type “regedit” and press Enter to open the Registry Editor.
• Navigate to the following key: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\
• The version number is displayed in the “CurrentVersion” value under the appropriate key for the instance of SQL Server you are interested in.

4. Using PowerShell: You can use PowerShell to check the version of SQL Server. To do this, open a PowerShell window and run the following command:

Copy codeInvoke-Sqlcmd -Query "SELECT @@VERSION"

This will return the version number and other information about the instance of SQL Server you are connected to.

The @@VERSION gives output as one nvarchar string. 

6. 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".

7. 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.

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

By default, SQL Server listens for incoming connections on TCP port 1433. However, you can configure SQL Server to use a different port by changing the value of the “TCP Dynamic Ports” and “TCP Port” settings in the SQL Server Configuration Manager.

To change the port, follow these steps:

1. Open the SQL Server Configuration Manager.
2. In the left pane, expand “SQL Server Network Configuration” and click on “Protocols for [instance name]”.
3. In the right pane, right-click on “TCP/IP” and select “Properties”.
4. In the “TCP/IP Properties” dialog, go to the “IP Addresses” tab.
5. In the “IPAll” section, scroll down to the “TCP Dynamic Ports” and “TCP Port” settings.
6. If the “TCP Dynamic Ports” setting is blank, enter the port number you want to use in the “TCP Port” setting. If the “TCP Dynamic Ports” setting is not blank, clear the value and enter the port number you want to use in the “TCP Port” setting.
7. Click “OK” to save the changes.
8. Restart the SQL Server service for the changes to take effect.

Note that you may need to update your firewall rules to allow incoming connections on the new port. You will also need to specify the port number when connecting to the SQL Server instance from a client application.

9. 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.

10. 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. So, to start a single user-mode in clustered installation, you can follow the below steps:

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:
4. net start MSSQLSERVER /m.
5. Next, you have to verify from the cluster administrator or fail over cluster management console that SQL Server resource is still offline or not.
6. Then, connect to the SQL Server by using the following command and perform the required operation: SQLCMD -E -S<servername>.
7. 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.

11. 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. 

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

13. 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.

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

There may be situations where you want to hide an instance of SQL Server from clients so that it is not visible when they browse for available servers. To hide an instance of SQL Server, you can follow these steps:

1. Open the SQL Server Configuration Manager.
2. In the left pane, expand “SQL Server Network Configuration” and click on “Protocols for [instance name]”.
3. In the right pane, right-click on “Named Pipes” and select “Properties”.
4. In the “Named Pipes Properties” dialog, go to the “General” tab.
5. Uncheck the “Enabled” checkbox.
6. Click “OK” to save the changes.
7. Repeat steps 3-6 for the “TCP/IP” protocol.
8. Restart the SQL Server service for the changes to take effect.

After completing these steps, the instance of SQL Server will no longer be visible when clients browse for available servers. Note that this will also disable the named pipes and TCP/IP protocols for the instance, which may affect the ability of clients to connect to the server.

Alternatively, you can use the “SQL Server Browser” service to hide specific instances of SQL Server. To do this, follow these steps:

1. Open the SQL Server Configuration Manager.
2. In the left pane, expand “SQL Server Services” and click on “SQL Server Browser”.
3. In the right pane, right-click on “SQL Server Browser” and select “Properties”.
4. In the “SQL Server Browser Properties” dialog, go to the “Service” tab.
5. Under “Instance Configuration”, check the “Hide Instance” checkbox for the instance you want to hide.
6. Click “OK” to save the changes.
7. Restart the “SQL Server Browser” service for the changes to take effect.

After completing these steps, the instance of SQL Server will no longer be visible when clients browse for available servers. However, the named pipes and TCP/IP protocols will still be enabled, so clients will still be able to connect to the server using the appropriate connection string.

15.  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.

16. Explain Magic Tables in SQL server

In SQL Server, a magic table is a special type of temporary table that is automatically created and populated with data when an INSTEAD OF trigger is fired. Magic tables are used to store the data that is being inserted, deleted, or updated by the trigger. They are called “magic” because they are created automatically by the database engine and are not visible in the database schema.

There are two types of magic tables in SQL Server:

1. Inserted: This magic table is created and populated with data when an INSTEAD OF INSERT trigger is fired. It contains a copy of the rows that are being inserted into the table.
2. Deleted: This magic table is created and populated with data when an INSTEAD OF DELETE trigger is fired. It contains a copy of the rows that are being deleted from the table.

You can use the data in the magic tables to modify the data being inserted or deleted, or to perform additional actions based on the data being affected by the trigger.

Here’s an example of how you might use a magic table in an INSTEAD OF INSERT trigger:

Copy codeCREATE TRIGGER tr_Employee_InsteadOfInsert
ON Employee
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO Employee (FirstName, LastName, Salary)
    SELECT i.FirstName, i.LastName, i.Salary
    FROM Inserted i
    WHERE i.Salary > 50000
END

In this example, the trigger will only insert rows into the Employee table if the salary value is greater than 50,000. The Inserted magic table contains a copy of the rows being inserted, and the trigger uses it to filter the rows and only insert the ones that meet the criteria.

17. 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.  

18. 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

19. 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. 

20. 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.

21. 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.

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

23. 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

24. 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

25. 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.

26. 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.

27. 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:

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

28. 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.

29. 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.

30. 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

31. 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.

32. 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: 

33.  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:

34. 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:

Example:

35. 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

Example:

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

36. 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:

37. 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:

Example:

38. 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:

39. Mention the command used to rename the database.

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

40. 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:

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

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

Syntax:

Example:

42. 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.

43. 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.

44. 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.

45. Mention the differences between HAVING and WHERE clause.

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

46. 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.

47. 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.

48. 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

49.  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.

50. 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