Mssql Interview Questions

  1. Truncate vs Delete in MSSQL .
  2. List the data types available in mssql?
  3. Write query to return all rows sql?
  4. What is a virtual table in SQL?
  5. Explain cross join or cartesian product in sql?
  6. What is difference between foreign key and unique key?
  7. What is difference between clustered index and non clustered index?
  8. What is the difference between left and right outer join?
  9. Write an sql query for deleting duplicate rows ?
  10. What is a cursor, index in SQL ?
  11. Explain Subquery and state its properties?
  12. What are types of subqueries?
  13. Explain Trigger and its types?
  14. What do you mean by Collation recursive stored procedure?
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

Delete in MSSQL

  • Deleting is one the most expensive operation, it's logged. Every row deleted is logged to the transaction log.
  • Delete from ... allows to you delete specific rows.
  • Delete from ... marks the row as a ghost row.
  • Delete from ... does not release the space of the deleted rows, you will need to run DBCC SHRINKFILE to recover the space [single user mode].

Truncate in MSSQL

  • Often people want to empty a temporary table to repopulate it with current data, then it's much faster to use the truncate statement instead of the delete statement.
  • truncate table is the most efficient way of emptying a table.
  • It's not possible to use the truncate table when you only want to delete some rows based on a condition.
  • Truncate table does not delete rows, it deallocates the data pages and release the space.
  • Truncate table cannot be used when the table is referenced by a foreign key or involved in data replication.
  • Truncate table is recommended for temporary tables that need to be cleared for the next process.
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.
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.
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
Collation – It is defined to specify the sort order and there are three sort orders.
  • Binary
  • Case sensitive
  • Case insensitive.

Recursive stored procedure – It is defined as a process of resolving a problem where the solution occurs repetitively.This can be nested up to 32 levels.

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.
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.

CDC – CDC stands for Change Data Capture, which captures the data that has been modified recently.
SQL injection – SQL injection is an attack by malicious users in which the malicious code is inserted into the strings that are passed to an instance of SQL server for parsing and execution.
All the statements have to be checked for the vulnerabilities as they execute all syntactically valid queries received.The parameters can also be changed by the experienced and skilled attackers.
Following methods are used to protect from SQL injection attack:
  • Filtering input parameters
  • Use parameter collection with Dynamic SQL
  • Use Parameters for Stored procedures
  • In like clause, use escape characters
When the index is created with WHERE clause, it is called Filter index.It is used to filter some of the rows in a table in order to improve the performance, index maintenance and reduce the index storage cost.
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.
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.
DML (Data Manipulation Language)
  • Select
  • Insert
  • Update
  • Delete

DDL (Data Definition Language)

  • Create
  • Alter
  • Drop
  • Truncate

DCL (Data Control Language)

  • Grant
  • Revoke

TCL (Transactional Control Language)

  • Commit
  • RollBack
  • Save Transaction
UNION – It is used to select the related information.
It is similar to that of JOIN command.
UNION ALL – It is similar to that of UNION command, but it selects all the values.
It does not remove the values from the table but will retrieve the data.
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 exceptions are handled using TRY-CATCH blocks, wherein TRY block the scripts are written and in CATCH block the errors are handled.
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.
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.

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.
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.