Mssql Interview Questions

There are a dozen of editions of Microsoft SQL server in the market, which targets different audiences and workloads of all interfaces ranging from the small single interface applications to large interface application with lots of concurrent users.

With lots of scope in the industry, the candidates aiming to cruise with the knowledge of MSSQL can get an extra pair of hands with these interview questions to crack the interview.

MSSQL Interview Questions and answers

#1 Write query to return all rows sql?

#2 Explain TABLESAMPLE?

TABLESAMPLE is used to extract the sample of rows that are necessary for the application.

#3 What are types of subqueries?

The types of Subqueries are:
  • Single Row – It returns only one row.
  • Multiple Row – It returns multiple rows.
  • Multiple Column – It returns multiple columns to the main query.

#4 Explain Logical operators in SQL Server?

The logical operators are basically used to test the truths of conditions.
  • ALL – It returns true if all the set of operations are true.
  • AND – It returns true if the Boolean expressions are true.
  • IN – It returns true if the operand is equal to one of the lists of expressions.
  • ANY – It returns true if any of the set of comparisons is true.
  • BETWEEN – It returns true if the operand is within a range.
  • EXISTS – It returns true if the subquery contains any rows.
  • LIKE – It returns true if the operand matches a pattern
  • NOT – It reverses the values of any Boolean operator.
  • OR – It returns true if either of the Boolean expression is true.
  • SOME – It returns true if some of the set of comparisons are true.

#5 What is a virtual table in SQL?

#6 Explain cross join or cartesian product in sql?

#7 Explain Subquery and state its properties?

Subquery – It is a query which is used when expressions are allowed and can be nested inside the main query like SELECT, UPDATE, DELETE or INSERT statements.
Properties:
  • A subquery in the main query that has to be placed on the right-hand side of the comparison operator
  • A subquery has to be placed parenthesis, in order to get executed first before the main query.
  • It cannot have any order by clause.
  • More than one subquery can be included in the main query.

#8 What is SQL Profiler?

It is a tool that allows system’s administrator to monitor the events in SQL Server.It is mainly used to capture and save the data of each event of a file or a table for analysis.

#9 What is a cursor, index in SQL ?

#10 What is MSSQL?

MSSQL stands for Microsoft Server SQL that is a Microsoft’s relational database management system. It is a featured database which is designed to compete against Oracle Database and MySQL. MSSQL is also referred as SQL Server.

#11 How are the exceptions handled in SQL Server Programming?

The exceptions are handled using TRY-CATCH blocks, wherein TRY block the scripts are written and in CATCH block the errors are handled.

#12 State the difference between Local and Global temporary tables?

Local temporary table – These tables are invisible when there is a connection and are deleted when it is closed.

Global temporary table – These tables are visible to all users and are deleted when the connection is closed.

Also Prepare: Frequently Asked MongoDB Interview Questions

#13   Distinguish between COMMIT and ROLLBACK?

COMMIT – Every statement between BEGIN and COMMIT becomes persistent to the database when the COMMIT is executed.
ROLLBACK – Every statement between BEGIN and ROLLBACK are revealed to the state when the ROLLBACK is executed.

#14 What is the difference between left and right outer join?

#15 What is difference between clustered index and non clustered index?

Ask a Question