MsSQL Server Interview Online Test
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.
1. What is SQL Server ?
Answer: SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.
Show Answer
Hide Answer
2. Error severity 13 indicates what?
Answer: Transactional deadlock errors.
This level of error severity indicates a transaction deadlock error.
Show Answer
Hide Answer
3. In which order do you perform an upgrade to SQL Server 2005 for replicated databases?
Answer: Distributor, Publisher, then Subscriber.
You always perform an upgrade in this order: distributor, publisher, subscriber.
Show Answer
Hide Answer
4. How many Service Packs will be released for SQL Server 2005 in 2007?
Answer: Explanation: The answer is up in the air and this is more of a poll than a real QOD. Based on the ways things are going, the staff here sees just 1, though our hope would be that 3 or 4 would be released
Show Answer
Hide Answer
5. You setup a linked server from a SQL Server 2000 server to your new SQL Server 2005 server (with defaults), however you cannot execute procedures on the 2005 server. Why not?
Answer: You need to enable RPC.
By default, RPC is disabled in SQL Server 2005. You need to set the "remote access option" in your server configuration to 1 to allow the execution of stored procedures from a remote server.
Show Answer
Hide Answer
6. What is the recommended way to send mail from SQLAgent in SQL Server 2005?
Answer: Database Mail
You can use either Database Mail or SQLMail with SQL Agent in SQL Server 2005. However since SQLMail will be removed, it is recommended that you use Database Mail.
Show Answer
Hide Answer
7. When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?
Answer: The owner of the message type.
This parameter determines the owner of the message type. This defaults to the current user.
Show Answer
Hide Answer
8. What the heck does ATN2 do?
Answer: The angle between the x-axis and a ray.
This is a mathematical function that returns the angle between the positive x-axis and the ray that passes through the two coordinates passed in. The angle is in radians.
Show Answer
Hide Answer
9. How does a differential backup know which extents have changed so that it can be very quickly run?
Answer: The DCM tracks changes. The differential backup reads the extents from this structure.
A differential backup uses the Differential Change Map to determine which extents have changed and need to be include in the backup. This greatly speeds the differential backup process.
Show Answer
Hide Answer
10. If you run this, what does it return?
select applock_mode('public', 'SalesApp', 'Transaction')
Answer: The type of lock being held by an application that requested it.
This command returns the lock mode held by an application that was requested with the sp_getapplock procedure.
insert mytable select ''
insert mytable select ' '
select * from mytable where mychar = ''
select * from mytable where mychar = ' '
Show Answer
Hide Answer
11. What does the Queue Reader Agent do in SQL Server 2005 replication?
Answer: 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.
Show Answer
Hide Answer
12. What are the three possible functions of the plus (+) operator in SQL Server 2005, the base installed T-SQL?
Answer: Add, string concatenation, unary plus
The three functions are Add, String Concatenation, and Unary Plus.
Show Answer
Hide Answer
13. The Sort component in SQL Server 2005 Integration Services is what type of component?
Answer: Blocking Transformation
The Sort component is a blocking transformation meaning that it needs all rows in order to perform its function
Show Answer
Hide Answer
14. If you received a "Performance Critical" status in the SQL Server 2005 replication monitor, what does this mean?
Answer: 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.
Show Answer
Hide Answer
15. Which of the following modules within SQL Server 2005 cannot be signed with a digital signature?
Answer: DDL triggers
DDL triggers cannot be signed, but all the other objects can.
Show Answer
Hide Answer
16. Where does Profiler store its temporary data in SQL Server 2005?
Answer: In the directory stored in the system variable TEMP.
Profiler uses the location specified for the TEMP system variable.
Show Answer
Hide Answer
17. What is the Service Broker Identifier ?
Answer: 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.
Show Answer
Hide Answer
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?
Answer: 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
Show Answer
Hide Answer
19. How are modified extents tracked in SQL Server 2005 (which internal structures)?
Answer: .
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).
Show Answer
Hide Answer
20. What does this return?
Answer: 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.
Show Answer
Hide Answer
21. What does the Log Reader agent in SQL Server 2005 replication do?
Answer: This agent reads the publisher log for transactions to send to the distributor.
This agent is tasked with reading the transaction log in transactional replication and moving those transactions that need to be replicated to the distributor.
Show Answer
Hide Answer
22. What does a @@fetch_status of -2 mean in SQL Server 2005?
Answer:
The row being fetched is missing.
This means that the row that was being fetched from the cursor is missing.
Show Answer
Hide Answer
23. You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
Answer: You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
Show Answer
Hide Answer
24. You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
Answer: Use Database Mirroring to fail over between the old reporting database and the new one.
The update process for a Scalable Shared database with minimal downtime involves putting out a new copy of the database, detaching the old database from each server, and then attaching the new database to each server.
Show Answer
Hide Answer
25. What is the cost threshhold for parallelism in SQL Server 2005?
Answer: This is the number of seconds that a serialplan cannot exceed if it is to be used. A parallel plan is used if the estimate exceeds this value.
This is the threshold at which SQL Server determines whether a serial or parallel plan is to be used. When SQL Server calculates that a serial plan exceeds the threshold, it will elect to use a parallel plan instead.
Show Answer
Hide Answer
26. You have a Scalable Shared Database setup for reporting purposes on SQL2. You want to be able to keep a point in time view of the reporting database each month. What can you do?
Answer: Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers.
A Scalable Shared Database does not support database snapshots, so you would have to manually create a new database each month with the data view you need and add this as a new Scalable Shared Database to the SAN and each reporting server.
Show Answer
Hide Answer
27. You have an old database that needs to run in compatibility mode 65 on your SQL Server 2005 server. Which framework would you use to manage this database programmatically?
Answer: SQL-DMO
SMO does not support compatibility modes 60 or 65, so you would need to use DMO instead.
Show Answer
Hide Answer
28. You have two Service Broker instances running. One is on SQL1 with the default collation and the other is on SQL2 setup for French collation. Which collation is used for Service Broker messages sent between the instances?
Answer: Service Broker does not consider the collation, using byte-by-byte matching for names.
Neither collation is used. Service Broker operates in a collation independent method that removes collation information from the messages.
Show Answer
Hide Answer
29. What does the max full-text crawl range option do?
Answer: Determines the number of partitions used in an index crawl.
This option helps optimize the full-text indexing process by specifying the number of partitions the SQL Server uses during index crawls.
Show Answer
Hide Answer
30. Make a new copy of the production database each month and then copy that to the SAN. Attach it as a new Scalable Shared Database each month to the reporting servers.
A Scalable Shared Database does not support database snapshots, so you would have to manually create a new database each month with the data view you need and add this as a new Scalable Shared Database to the SAN and each reporting server.
Answer: TEXT_IN_ROW_DATA
The three types of allocation units are: IN_ROW_DATA, LOB_DATA, and ROW_OVERFLOW_DATA. Each heap or index has IN_ROW_DATA which holds part of the data. LOB_DATA is used for large object data types and ROW_OVERFLOW_DATA is used for varible length data that causes a row to exceed the 8060 byte limit.
Show Answer
Hide Answer