DECLARE @Temp TABLE  (
	[UserId] [bigint] NOT NULL,
	[CharacterName] [varchar](30) NOT NULL,
	[Kills] [int] NOT NULL,
	[Deaths] [int] NOT NULL,
	[Score] [int] NOT NULL,
	[LastSeen] [datetime] NOT NULL,
	[TeamId] [int] NOT NULL,
	[GamesPlayed] [int] NULL,
	[Kit] [nchar](100) NULL,
	[DayStartPosition] [int] NULL,
	[KDR] [real] NULL)

insert into @Temp (UserId,CharacterName,Kills,Deaths,Score,LastSeen,TeamId,GamesPlayed,Kit,DayStartPosition,KDR) SELECT * FROM PlayerTotals

delete from PlayerTotals_Score

insert into PlayerTotals_Score (RowId,UserId,CharacterName,Kills,Deaths,Score,LastSeen,TeamId,GamesPlayed,Kit,DayStartPosition,KDR)
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum, *           
FROM @Temp) AS RowConstrainedResult
ORDER BY RowNum 

delete from PlayerTotals_Kills

insert into PlayerTotals_Kills (RowId,UserId,CharacterName,Kills,Deaths,Score,LastSeen,TeamId,GamesPlayed,Kit,DayStartPosition,KDR)
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Kills DESC) AS RowNum, *           
FROM @Temp) AS RowConstrainedResult
ORDER BY RowNum 

delete from PlayerTotals_Deaths

insert into PlayerTotals_Deaths (RowId,UserId,CharacterName,Kills,Deaths,Score,LastSeen,TeamId,GamesPlayed,Kit,DayStartPosition,KDR)
SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY Deaths DESC) AS RowNum, *           
FROM @Temp) AS RowConstrainedResult
ORDER BY RowNum 

quote:

---

Querying the live table whilst it is also being updated is presumably bad.

---

CREATE PROCEDURE [dbo].[spGetFilteredScoreboard]
@Start int = null, 
@Side int = null,
@Kit varchar(30) = null, 
@Name varchar(30) = null ,
@OrderBy varchar(30)
AS 

declare @Max Integer
declare @Position Integer

if (@Name is not null)
begin 
	set @Position = (SELECT RowNum FROM ( SELECT ROW_NUMBER() OVER 
	( 
		ORDER BY
        CASE			
            WHEN @OrderBy = 'Score' THEN Score
        END DESC,
        CASE
            WHEN @OrderBy = 'Kills' THEN Kills
        END DESC,
        CASE
            WHEN @OrderBy = 'Deaths' THEN Deaths
        END DESC
	) AS RowNum, * FROM HeroTotals WHERE 
(@Side is null or TeamId=@Side) and 
(@Kit is null or Kit=@Kit)) AS RowConstrainedResult WHERE CharacterName = @Name)
	set @Start = @Position - 50
end

if (@Start < 1)
	set @Start = 1

set @Max = @Start + 100;

SELECT * FROM ( SELECT    ROW_NUMBER() OVER 
( 
		ORDER BY
        CASE			
            WHEN @OrderBy = 'Score' THEN Score
        END DESC,
        CASE
            WHEN @OrderBy = 'Kills' THEN Kills
        END DESC,
        CASE
            WHEN @OrderBy = 'Deaths' THEN Deaths
        END DESC
) AS RowNum, *           
FROM HeroTotals WHERE 
(@Side is null or TeamId=@Side) and 
(@Kit is null or Kit=@Kit)
  ) 
AS RowConstrainedResult WHERE   RowNum >= @Start AND RowNum < @Max 

ORDER BY RowNum