SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31

Click here to get free chapters (PDF) in the mailbox

Please read the Introductory Post before continue reading interview question and answers.

List of all the Interview Questions and Answers Series blogs

How does Using a Separate Hard Drive for Several Database Objects Improves Performance Right Away?

A non-clustered index and tempdb can be created on a separate disk to improve performance.

(Read more here)

How to Find the List of Fixed Hard Drive and Free Space on Server?

We can use the following Stored Procedure to figure out the number of fixed drives (hard drive) a system has along with free space on each of those drives.

EXEC master..xp_fixeddrives

Why can there be only one Clustered Index and not more than one?

Cluster Index physically stores data, or arranges data in one order (depends on which column(s) you have defined Clustered index and in which order).

As a fact, we all know that a set of data can be only stored in only one order; that is why only one clustered index is possible.(Read more here)

What is Difference between Line Feed (\n) and Carriage Return (\r)?

Line Feed – LF – \n – 0x0a – 10 (decimal)

Carriage Return – CR – \r – 0x0D – 13 (decimal)

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )

(Read more here)

Is It Possible to have Clustered Index on Separate Drive From Original Table Location?

No! It is not possible. (Read more here)

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.(Read more here)

There are three different types of hints. Let us understand the basics of each of them separately.

Join Hint

This hint is used when more than one table is used in a query. Two or more tables can be joined using different types of joins. This hint forces the type of join algorithm that is used. Joins can be used in SELECT, UPDATE and DELETE statements.

Query Hint

This hint is used when certain kind of logic has to be applied to a whole query. Any hint used in the query is applied to the complete query as opposed to a part of it. There is no way to specify that only a certain part of a query should be used with the hint. After any query, the OPTION clause is specified to apply the logic to this query. A query always has any of the following statements: SELECT, UPDATE, DELETE, INSERT or MERGE (SQL 2K8); and this hint can be applied to all of them.

Table Hint

This hint is used when certain kind of locking mechanism of tables has to be controlled. SQL Server query optimizer always puts the appropriate kind of lock on tables, when any of the Transact SQL operations SELECT, UPDATE, DELETE, INSERT or MERGE is used. There are certain cases when the developer knows when and where to override the default behavior of the locking algorithm, and these hints are useful in those scenarios. (Read more here)

How to Delete Duplicate Rows?

We can delete duplicate rows using CTE and ROW_NUMBER () feature of SQL Server 2005 and SQL Server 2008.

e.g.

WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount >1

(Read more here)

Why the Trigger Fires Multiple Times in Single Login?

It happens because multiple SQL Server services are running and also as intellisense is turned on. (Read more here)

List of all the Interview Questions and Answers Series blogs

Reference: Pinal Dave (https://blog.sqlauthority.com)