MSSQL Interview Questions

  Top MSSQL Interview Questions




1) What is a cursor and index in SQL?
In SQL, a cursor can be defined as a tool used widely to define a particular set of results. This result can be a set of data rows. A cursor is basically used to solve complex logic and works in a row by row manner.
Index, on the other hand, has the main function of retrieving data from tables much quicker. Indexes are created by users on columns that may be accessed frequently. This enables the user to get information quickly from the table and can be created on a single column or even a group.
  
2) What is MSSQL?
MSSQL stands for Microsoft Server SQL that is Microsoft’s relational database management system. It is a featured database that is designed to compete against Oracle Database and MySQL. MSSQL is also referred to as SQL Server.

3) What is SQL server agent and what are the two modes of authentication in 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 on the security page, the modes can be changed.

4) What is SQL Profiler?
It is a tool that allows the 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.

5) What is COALESCE and CHECK constraint in SQL server?
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 integrity. It is applied to a column in a table to limit the values that have to be placed in a column.

6) Explain Subquery and state its properties?
Subquery – It is a query that 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.

7) What are the 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.

8) Explain Trigger and its types?
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

9) What do you mean by Collation recursive stored procedure?
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.

10) Explain Magic tables in SQL Server?
When the triggers are fired for any DML command, insert and delete tables are created, these tables are called Magic tables in the SQL server. These tables are used inside the triggers for transactions.

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

12) Explain CDC and SQL injection?
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 vulnerabilities as they execute all syntactically valid queries received. The parameters can also be changed by experienced and skilled attackers.

13) What methods do you follow to protect from SQL injection attacks?
Following methods are used to protect from SQL injection attacks:
Filtering input parameters
Use parameter collection with Dynamic SQL
Use Parameters for Stored procedures
In like clause, use escape characters

14) What is filter index?
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.

15)   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 is revealed to the state when the ROLLBACK is executed.

16) 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 sets 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.

17) Explain the commands in 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

18) State the difference between UNION and UNION ALL?
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.

19) Explain TABLESAMPLE?
TABLESAMPLE is used to extract the sample of rows that are necessary for the application.

20) What are the purposes of FLOOR and SIGN functions?
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.

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

22) How to delete duplicate rows?
For deleting the duplicate rows we can do the following:
• Find duplicate rows using GROUP BY clause or ROW_NUMBER() function.
Use DELETE statement to remove the duplicate rows.