MsSQL Server Interview Questions & Answers
Showing 10 of 30 questions | Page 3
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
21. What does the Log Reader agent in SQL Server 2005 replication do?
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.
22. What does a @@fetch_status of -2 mean in SQL Server 2005?
The row being fetched is missing.
This means that the row that was being fetched from the cursor is missing.
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?
You want to be sure that your Scalable Shared Database is as available as possible. Which of the following is not needed for this?
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?
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.
25. What is the cost threshhold for parallelism in SQL Server 2005?
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.
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?
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.
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?
SQL-DMO
SMO does not support compatibility modes 60 or 65, so you would need to use DMO instead.
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?
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.
29. What does the max full-text crawl range option do?
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.
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.
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.