Technical interview questions and answers are crucial for MSSQL Server Interviews because companies want to assess your knowledge of SQL queries, stored procedures, triggers, indexing, performance tuning, and database administration concepts. MSSQL Server is widely used in enterprise applications, making it a common topic in backend and database interviews. Companies like TCS, Wipro, Infosys, Cognizant, and Capgemini frequently ask MSSQL-related questions during their technical rounds. This guide includes the most commonly asked MSSQL Server interview questions with clear explanations to help freshers and job seekers understand core database concepts. Preparing these questions will help you perform well in SQL-based coding tests, DBA interviews, and backend development roles.
Showing 10 of 30 questions
11. What does the Queue Reader Agent do in SQL Server 2005 replication?
This agent reads the subscriber logs and moves changes back to the publisher.
This agent is used when the queued update model is chosen with transactional replication. It moves changes from the subscribers back to the publishers.
12. What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?
Add, string concatenation, unary plus
The three functions are Add, String Concatenation, and Unary Plus.
13. The Sort component in SQL Server 2005 Integration Services is what type of component?
Blocking Transformation
The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function
14. If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?
The latency between transaction commits at the publisher and subscriber exceeds the warning level.
This status can actually mean two different things. Either the latency between the commit of a transaction at the publisher and the same commit at the subscriber is exceeding some level in a transactional level or not enough rows are being processed in a merge replication scenario.
15. Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?
DDL triggers
DDL triggers cannot be signed, but all the other objects can.
16. Where does Profiler store its temporary data in SQL Server 2005?
In the directory stored in the system variable TEMP.
Profiler uses the location specified for the TEMP system variable.
17. What is the Service Broker Identifier ?
A GUID that identifies the database on which Service Broker is running.
Each database has a Service Broker identifier. This is a GUID in the service_broker_GUID column that identifies the databases on which Service Broker is running. It ensure that messages are delivered to the right database.
18. You are looking to import a large amount of data from a remote OLEDB data source that is not a text file. Which of the following techniques can you use?
Use the select * from OPENROWSET(BULK...) command.
SQL Server 2005 includes a new option with the OPENROWSET command for getting large amounts of data from an OLEDB data source. It is the BULK option and works similar to the BULK INSERT command
19. How are modified extents tracked in SQL Server 2005 (which internal structures)?
.
Differential Change Map and Bulk Change Map
There are two internal structures that track extents modified by bulk copy operations or that have changed since the last full backup. They are the Differential Changed Map (DCM) and the Bulk Changed Map (BCM).
20. What does this return?
select (1324 & 1024)
1024
This performs a bitwise AND operation between the two integers and sets the result to this. Since 1024 is a single set bit in it's value, if the corresponding bit is set to 1, then in the result the bit is set to 1. In this case, since no other bits would generate two 1s, the result is equivalevt to the mask, or 1024.