Mssql Interview Questions

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

Download Mssql Interview Questions PDF

Mssql Interview Questions

When the triggers are fired for any DML command, insert and delete tables are created, these tables are called Magic tables in SQL server.These tables are used inside the triggers for transactions.
Trigger – It is used to execute a batch of SQL code when the commands like INSERT, UPDATE or DELETE are executed against a table.
They are automatically executed or triggered when the data gets modified.
Types of Triggers:
  • Insert
  • Update
  • Delete
  • Instead of
TABLESAMPLE is used to extract the sample of rows that are necessary for the application.
FLOOR – It is used to round up the non-integer value to the preceding least integer.
SIGN – It is used to determine whether the number provided is positive, Zero, negative and returns +1, 0, -1.
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.
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.
SQL Server agent
The SQL Server agent plays an important part in the day to day tasks of the SQL Server Database Administrator (DBA).
Its purpose is to implement the tasks easily with the Scheduler engine that allows our tasks to run at a scheduled time and date.
Modes of Authentication in SQL Server
The two authentication modes in SQL Server are:
  • Windows Mode
  • Mixed Mode

From the tools menu of SQL Server configuration properties in the security page, the modes can be changed.

COALESCE – It is a function that is used to return the first non-null expression from more than one column within the arguments.
CHECK constraint – It is used to enforce the integrity. It is applied to a column in a table to limit the values that have to be placed in a column.
The exceptions are handled using TRY-CATCH blocks, wherein TRY block the scripts are written and in CATCH block the errors are handled.
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.