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 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?
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.