Will Non-Clustered Index used every time by SQL Server Engine? HCL/Unitedhealth Group

Note: Don't be confuse by GO 999990  keyword in below query (just used for insert multiple records)... if you have no idea about this t...

Note: Don't be confuse by GO 999990 keyword in below query (just used for insert multiple records)... if you have no idea about this then must read my previous article first..

This questions asked during HCL and United Health Group SQL SERVER Interview
Suppose that you have a table named "tblCity" with column "CityID" and "CityName"
In this table there are only two cities currently exist.

"Gurgaon" city appear 999990 times
"New York" city appear 10 times
And you have created  Non-Clustered Index on "CityName" column



Now lets come on question:
Questions:  Non-Clustered Index would be used by both of below query or not if not then why?
Means execution plan would be same for both query or not?
SELECT TOP 10 * FROM tblCity WHERE CityName = 'Gurgaon'
SELECT  TOP 10 * FROM tblCity WHERE CityName = 'New York'

Ans: No, Execution would be differ, Because SQL Server engine use Non-Clustered index based on column density. Means if a records appear very randomly then it will not use Non-Clustered index,
But if record appear less time then it will use Non-clustered index.

In this example you can see below Non-clustered index not used for first query but it is used for second query



This questions was asked during HCL and United Health Group Sql Server DBA/Developer Interview.

Related

SQL SERVER PERFORMANCE TUNING INTERVIEW QUESTIONS 8359276657706811315

Post a comment

emo-but-icon

Follow Us

Recent

Comments

Donate

Side Ads

Text Widget

Connect Us

Sql Server 2017 Interview Questions
Sql Server Basic Interview Query Set-1
Sql Server Basic Interview Query Set-2
Sql Server Date-Time Interview Query SET-3
Sql Server Salary Interview Query SET-4
Sql Server Group By Interview Query SET-5
Sql Server Join Interview Query SET-6
Sql Server Tricky Join Interview Query SET-7
Sql Server DDL Interview Query SET-8
Small but very Tricky Sql Server Query SET-9
Very Much Tricky Query (not 4 fresher)SET-10
Sql Server Complex Interview Query SET-11
Sql Server Datatype Interview Questions
Sql Server View Interview Questions
Sql Server Index Interview Questions
TCS Tricky Sql Interview Queries
HCL Sql Interview Queries
Sql Server SP Interview Questions
Sql Server Trigger Interview Questions
Sql Server Temp Table Interview Questions
Sql Server 2016 Interview Questions
Sql Server Performance Tuning Interview Q.
Sql Server Constraints Interview Questions
Sql Server Storage/Size Interview Questions
Sql Server Very Basic Interview Questions
Sql Server Quiz for Fresher
Sql Server Icon Quiz for All
Sql Server 300+ Theoretical Interview Questions
Sql Server Complete Set

ASP.NET AND JQUERY INTERVIEW QUESTION
ASP.NET: Web Config Interview Question
ASP.NET: View State Interview Question
ASP.NET: Session Interview Question
ASP.NET: Session Interview Question 
ASP.NET: Security Interview Question
ASP.NET: Catching Interview Question
C# OOPS Interview Question
MORE Interview Question
JQUERY: Interview Question Set-1
JQUERY: Interview Question Set-2
JAVA-SCRIPT: Interview Question Set-1
JAVA-SCRIPT: Interview Question Set-2
ASP.NET MVC: Interview Question Set-1

POPULAR POSTS
10 MOST DIFFICULT INTERVIEW QUESTIONS
5 BEST INTERVIEW TIPS
YOUR 10 MISTAKE DURING INTERVIEW
2016 TOP 20 GROUP DISCUSSION TOPICS
HOW SQL PERFORMANCE AFFECTED BY DATATYPE
item