Mssql Interview Questions

Mssql Interview Questions Download Mssql Interview Questions PDF

Mssql Interview Questions

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

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