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
1. What is SQL Server ?
SQL Server is a DBMS system provided by Microsoft. SQL Server is sometimes mistakenly referred to as SQL.
2. Error severity 13 indicates what?
Transactional deadlock errors.
This level of error severity indicates a transaction deadlock error.
3. In which order do you perform an upgrade to SQL Server 2005 for replicated databases?
Distributor, Publisher, then Subscriber.
You always perform an upgrade in this order: distributor, publisher, subscriber.
4. How many Service Packs will be released for SQL Server 2005 in 2007?
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
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?
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.
6. What is the recommended way to send mail from SQLAgent in SQL Server 2005?
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.
7. When you create a new Message Type in the SQL Server 2005 Service Broker, what does the Authorization parameter signify?
The owner of the message type.
This parameter determines the owner of the message type. This defaults to the current user.
8. What the heck does ATN2 do?
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.
9. How does a differential backup know which extents have changed so that it can be very quickly run?
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.
10. If you run this, what does it return?
select applock_mode('public', 'SalesApp', 'Transaction')
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 = ' '