title: Leaderboard design using SQL Server
body: <p>I am building a leaderboard for some of my online games. Here is what I need to do with the data: </p>
  
  <ul>
  <li>Get rank of a player for a given game across multiple time frame (today, last week, all time, etc.)</li>
  <li>Get paginated ranking (e.g. top score for last 24 hrs., get players between rank 25 and 50, get rank or a single user)</li>
  </ul>
  
  <p>I defined with the following table definition and index and I have a couple of questions.</p>
  
  <p><strong>Considering my scenarios, do I have a good primary key?</strong> The reason why I have a clustered key across gameId, playerName and score is simply because I want to make sure that all data for a given game is in the same area and that score is already sorted. Most of the time I will display the data is descending order of score (+ updatedDateTime for ties) for a given gameId. Is this a right strategy? In other words, I want to make sure that I can run my queries to get the rank of my players as fast as possible.</p>
  
  <pre><code>CREATE TABLE score (
      [gameId]            [smallint] NOT NULL,
      [playerName]        [nvarchar](50) NOT NULL,
      [score]             [int] NOT NULL,
      [createdDateTime]   [datetime2](3) NOT NULL,
      [updatedDateTime]   [datetime2](3) NOT NULL,
  PRIMARY KEY CLUSTERED ([gameId] ASC, [playerName] ASC, [score] DESC, [updatedDateTime] ASC)
  
  CREATE NONCLUSTERED INDEX [Score_Idx] ON score ([gameId] ASC, [score] DESC, [updatedDateTime] ASC) INCLUDE ([playerName])
  </code></pre>
  
  <p>Below is the first iteration of the query I will be using to get the rank of my players. However, I am a bit disappointed by the execution plan (see below). <strong>Why does SQL need to sort?</strong> The additional sort seem to come from the RANK function. But isn’t my data already sorted in descending order (based on the clustered key of the score table)? I am also wondering if I should normalize a bit more my table and move out the PlayerName column in a Player table. I originally decided to keep everything in the same table to minimize the number of joins.</p>
  
  <pre><code>DECLARE @GameId AS INT = 0
  DECLARE @From AS DATETIME2(3) = '2013-10-01'
  
  SELECT DENSE_RANK() OVER (ORDER BY Score DESC), s.PlayerName, s.Score, s.CountryCode, s.updatedDateTime
  FROM [mrgleaderboard].[score] s
  WHERE s.GameId = @GameId 
    AND (s.UpdatedDateTime &gt;= @From OR @From IS NULL)
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/6Wt8b.png" alt="enter image description here"></p>
  
  <p>Thank you for the help!</p>