1

I have a table which contains:

ID      ID_TYPE
---------------   
 1        0
 2        1
 3        1

Now I want to get the current record number when performing:

SELECT ID
FROM IDTable
WHERE ID_TYPE = 1

I don't want to use ROW_NUMBER() OVER (ORDER BY [ID] desc), because it's very slow when using larger tables.

What are my alternatives ?

ohadinho
  • 6,894
  • 16
  • 71
  • 124
  • 9
    "I don't want to use `ROW_NUMBER() ...` because it's very small when using larget tables" What? What does this even mean? Can you give an example of the problem you have? – Mark Byers Dec 19 '12 at 14:06
  • 1
    I don't think the typo "larget" was the issue here... – Nick Vaccaro Dec 19 '12 at 14:08
  • Please explain in English what you are actually trying to do. What is the purpose of your query? That will help us determine the most efficient way of solving your problem. – dan1111 Dec 19 '12 at 14:08
  • I meant "slow". I typed too fast :).. sorry – ohadinho Dec 19 '12 at 14:28
  • What do you mean by 'current record'? – Alberto De Caro Dec 19 '12 at 14:32
  • @dan1111 the purpose is to get the current enumeration number of the select. For instance: in the example above I want to get 1 for the first result, and 2 for the second result and so on.. – ohadinho Dec 19 '12 at 14:33
  • If you are doing pagination, [you might find this article useful](http://stackoverflow.com/questions/4358253/sql-server-2008-paging-methods). – Nikola Markovinović Dec 19 '12 at 14:37
  • What makes you think that `ROW_NUMBER()` is slow? Can you show an example of a query that is fast without it but slows down when you add it? And you should include the execution plans for both queries, of course. – Pondlife Dec 19 '12 at 15:28

1 Answers1

0

Try 

select ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as number from IDTable WHERE ID_TYPE = 1
SalientBrain
  • 2,431
  • 16
  • 18