Important Microsoft Sql SQL Server Interview Question and Answer

Q.What will be the result of the query below? Explain your answer and provide a version that behaves correctly.

select case when null = null then 'Yup' else 'Nope' end as Result;

============================================================Ans:

 This query will actually yield “Nope”, seeming to imply that null is not equal to itself! The reason for this is that the proper way to compare a value to null in SQL is with the is operator, not with =.

Accordingly, the correct version of the above query that yields the expected result (i.e., “Yup”) would be as follows:

select case when null is null then 'Yup' else 'Nope' end as Result;

----------------------------------------------------------------------------------------------------------------

Q. What is an execution plan? When would you use it? How would you view the execution plan?

==============================================================Ans:

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN, and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.

-----------------------------------------------------------------------------------------------------------------
Q.What does UNION do? What is the difference between UNION and UNION ALL?
===============================================================
Ans: UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.
It is important to note that the performance of UNION ALL will typically be better than UNION, since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.
-----------------------------------------------------------------------------------------------------------------
Q.Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.
(Note: You may assume that there are at least 10 records in the Employee table.)
==============================================================Ans:
SELECT TOP (1) Salary FROM
(    SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC
) AS Emp ORDER BY Salary
-----------------------------------------------------------------------------------------------------------------
Q.List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.
==============================================================Ans:

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

• Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
• Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
• Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.
• Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

-----------------------------------------------------------------------------------------------------------------

Q. What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

==============================================================Ans:

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100}. For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4}.

---------------------------------------------------------------------------------------------------

Q.What is the difference between the WHERE and HAVING clauses?

==============================================================Ans:

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.
However, when GROUP BY is used:
The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.

The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).
---------------------------------------------------------------------------------------------------

Q.What are various DML commands in SQL? Give brief description of their purposes.
==============================================================Ans:

Following are various DML or Data Manipulation Language commands in SQL −

• SELECT − it retrieves certain records from one or more tables.
• INSERT − it creates a record.
• UPDATE − it modifies records.
• DELETE − it deletes records.

----------------------------------------------------------------------------------------

Q. Is a NULL value same as zero or a blank space? If not then what is the difference?

==============================================================Ans:

A NULL value is not same as zero or a blank space. A NULL value is a value which is ‘unavailable, unassigned, unknown or not applicable’. Whereas, zero is a number and blank space is a character.

----------------------------------------------------------------------------------------

Q. What is the purpose of the condition operators BETWEEN and IN?

==============================================================Ans:

The BETWEEN operator displays rows based on a range of values. The IN condition operator checks for values contained in a specific set of values.
----------------------------------------------------------------------------------------