Interview Question and Answers for MCSA SQL Server Professionals?

SQL is Microsoft’s answer to  Oracle’s 11g  and IBM’s DB2. The software serves as a database for storing, sharing and managing data over a single or multiple computers connected to the same network. There is a stiff competition between the two data platforms to grab the maximum share in the database management system and this has opened a tremendous amount of opportunities for IT professionals to secure the most high profile job of their career.

MCSA SQL Server certification  requires an individual to secure passing marks in total three examinations. The latest development in the certification (2016) requires you to pass 2 exams and you can seek expertise in any one of the three credentials

  • MCSA: SQL 2016 Database Development

  • MCSA: SQL 2016 Database Administration

  • MCSA: SQL 2016 Business Intelligence Development

The credential imparts enough skills in implementing, designing, configuring, managing, installing databases across organisations at the highest level. This can provide you with good opportunity as database administrator or a database developer (based on the choice of credential) for a reputed organisation.

There is no easy shortcut for finding success and you’ll need a well planned strategy and long hours of hard work to gain the acclaimed credential of Microsoft. And even after the certification, a viable presentation of your skills in Microsoft DBMS in an interview will decide the fate of your professional career in DBMS industry.

The Interview Questions and Answers

1.What is the maximum size of a row?

Ans: 8060 bytes.

2.What is the difference between delete and truncate statement?

Ans: The truncate statement deletes all the data in one go and you cannot opt for specific rows to be deleted. It works faster and cannot be rolled back and the data gets deleted permanently unless you have a backup for recovery. The delete statement cleans data one by one and you can opt of specific rows to be cleared out. It takes the time to perform and the data can be recovered via transactions logs.

3.What is SQL Server?

Ans: It is a vastly enriched database management application founded by Microsoft which promotes storing and sharing data for different software applications and solutions between different computers connected by a single network over the internet. There are a large number of SQL servers which have different audiences, share different kinds of workloads (small scale to large scale); all focused on database management role.

4.What is a cursor in SQL Server?

Ans: Cursors are database objects used to manipulate data in a set on a row-by-row basis(iterative).We can also fetch cursor rows and perform operations on them in a loop just like using any looping mechanism found in any other programming language.

5.What is the difference between a primary key and a unique key?

Ans: The primary key allows the creation of a clustered index on the column while unique key allows the creation of a non-clustered index on the column by default. Another difference between the two is that primary key does not allow nulls.

6.Name the various triggers?

Ans: There are 4 kinds of triggers:

  • Insert

  • Delete

  • Update

  • Instead of

7.What is a trigger?

Ans: A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event.

DML events are INSERT, UPDATE, or DELETE statements on a table or view

8.What are the types of sub query?

Ans:Their are two major categories

  1. Co-rellated sub-queries

  2. Self contained sub-queries

Both are sub categorized by-:

  • Single valued sub-query

  • Multi-valued sub-query