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

SQL Server Interview Questions and Answers
Print Book Available (207 Pages) | Sample Chapters

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 (http://blog.SQLAuthority.com)

6 thoughts on “SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 12 of 31

  1. Hi pinal,

    Delete Duplicate Rows is most of frequently asked inteview question, thank you soo much for providing the answer for this. This will help a lot for all.

    Like

  2. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Complete Downloadable List – Day 0 of 31 Journey to SQLAuthority

  3. Hi Pinal,

    the most frequently asked question is “How do you delete duplicate rows in a table”, this page on the interview questions really helps new job seekers.. Also could you add more information and questions/answers for the SCOPE_IDENTITY keyword for stored procedure, information like why it is used and when it is used…

    Thank you..

    Like

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

    No!

    I think it should be yes. we can Move clustered index to different drive. By moving clustered index we are actually moving the entire table to new Drive/Filegroup.

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #037 | Journey to SQL Authority with Pinal Dave

  6. Another way for deleting duplicate records…
    Delete From test1
    Where test1.%%physloc%% Not In(Select MIN(b.%%physloc%%)
    From test1 b Group By b.id,b.name)

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s