Sql-server – Need help in creating a query

In SQL Server 2008, converting binary to a character representation became a lot faster and easier:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS CONVERT(char(18), CONVERT(binary(8), rv), 1)
);

Notice the style 1 option on the CONVERT to char. Also, the rowversion type is equivalent to binary(8) (when not nullable, varbinary(8) otherwise).

We can now create an index on the computed column:

-- Create index on the computed column
-- Note PERSISTED is *not* required
CREATE UNIQUE INDEX i 
ON dbo.X (rvc);

Add some sample data:

-- Some rows
INSERT dbo.X
    (pk, c1)
VALUES
    (1, 100),
    (2, 200),
    (3, 300);

And display the results:

-- Show the data
SELECT
    X.pk,
    X.c1,
    X.rv,
    X.rvc
FROM dbo.X AS X;

Output example:

The task of joining to the table with rowversions in character format is now trivial.

SQL Server 2005 version

This requires a helper function:

CREATE FUNCTION dbo.Bin8ToHexStr
    (@Hex binary(8))
RETURNS char(18)
WITH SCHEMABINDING
AS
BEGIN
    RETURN 
        '0x' + 
        CONVERT(xml, N'').value('xs:hexBinary(sql:variable("@Hex"))', 'char(16)');
END;

The table definition becomes:

CREATE TABLE dbo.X
(
    pk integer PRIMARY KEY,
    c1 integer NOT NULL,
    rv rowversion NOT NULL,
    rvc AS dbo.Bin8ToHexStr(CONVERT(binary(8), rv))
);

Everything else proceeds as before, including the index.

Did CTE with RANK/OVER:

Rank3PM = RANK() OVER (ORDER BY [3PM per Game])

and in the select I ended with:

TotalPoints =  Rank3PM + RankREB + RankAST..

Thanks.