Indexing on frequently updated table

I was asked a question in an interview, where I was given a situation which is:
I have registered to an online gaming website. It displays my rank on the basis of my score when I play a game.
Database table has 2 columns: 1. UserName 2. Score.
Since the login page was loading very slowly so the programmers created an non-clustered index on UserName and now the welcome page problem is solved. But since millions of users are playing games , their scores get updated quite frequently. So the user rank calculation is performed quite slow. Now another non-clustered index is created on Score but its getting frequently updated so the performance is not improved.
How to come out of this dilemma?

mahmoodbutt
Profile Answers by mahmoodbutt Questions by mahmoodbutt
Apr 23rd, 2013
3
10636

Questions by mahmoodbutt

SQL Server

Answer

Showing Answers 1 - 3 of 3 Answers

Bhanu

Sep 1st, 2014

Why don't you create both non clustered indexes in one non clustered index.Please let me know on your performance.

nabin

Mar 25th, 2015

create clustered index on name. Reason is clustered index should be applied on columns which do not change frequently. Since the user will register once and will not change name that frequently it is good to apply on name. Since the question does not hint on if the table has any other primary key, we can put an clustered index on name. Usually we should look to apply clustered index on columns which are unique, do not change frequently. Non-clustered index are generally applied on columns which appear on WHERE, JOIN clauses.

Yaman

Aug 9th, 2015
First of all table is not well designed. Table should have a clustered index. Right now its a heap and if we create a clustered index on username its a bad idea as data gets physically sorted everytime the row is inserted and exclusive lock is held on a table. I would suggest creating a covering non clustered index which will have an index key on username and score in the included part of the index.
Code
 