Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How can I get row's index from a table in SQL Server?

I have a Toplist table and I want to get a user's rank. How can I get the row's index?

Unfortunately, I am getting all rows and checking in a for loop the user's ID, which has a significant impact on the performance of my application.

How could this performance impact be avoided?

like image 601
Tugrul Emre Atalay Avatar asked Nov 06 '13 08:11

Tugrul Emre Atalay


People also ask

How do I get index details in SQL Server?

sp_helpindex is a system stored procedure which lists the information of all the indexes on a table or view. This is the easiest method to find the indexes in a table. sp_helpindex returns the name of the index, description of the index and the name of the column on which the index was created.

How do I find an index on a specific table?

To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT TABLE_NAME, INDEX_NAME FROM INFORMATION_SCHEMA.

How do I get a list of index names in SQL Server?

You can use the sp_helpindex to view all the indexes of one table. And for all the indexes, you can traverse sys. objects to get all the indexes for each table. Only problem with this is that it only includes the index key columns, not the included columns.


2 Answers

You may also do something like this:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS MyIndex
FROM TopList
like image 141
BICube Avatar answered Sep 20 '22 18:09

BICube


You can use ROW.NUMBER

This is a example syntax for MySQL

SELECT  t1.toplistId, 
        @RankRow := @RankRow+ 1 AS Rank
FROM    toplist t1
JOIN    (SELECT @RankRow := 0) r;

This is a example syntax for MsSQL

SELECT ROW_NUMBER() OVER(ORDER BY YourColumn) AS Rank,TopListId
FROM TopList
like image 39
Mad Dog Tannen Avatar answered Sep 20 '22 18:09

Mad Dog Tannen