Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: Retrieving records within an index range, the nth record?

    if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?

    Possible? How can I do it?

    When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?

    I'm using SQL 2005 Express, not SQL 2000.
    Last edited by tingshen; 03-06-07 at 03:21.

  2. #2
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?
    Create index and schedule update statistics according to your requirement...

    Use comparison operator for retrieving desired data.

    i.e. Like, Between, Not Between etc.

    When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?
    Use clustered and non clustered index considering your needs.

    Indexes are for arranging, sorting & fast retrieval of the data. Each time you don't need to create index when you insert a row, just schedule update statistics job or set auto update statistics.

    Explore Books OnLine (From query analyzer -> Help Menu) for more information.
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  3. #3
    Join Date
    Oct 2006
    Posts
    115
    how do we include index as a criteria when we use normal SQL query?

  4. #4
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    how do we include index as a criteria when we use normal SQL query?
    You don't need to do such thing, SQL Server will do it for you...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  5. #5
    Join Date
    Oct 2006
    Posts
    115
    after some research, i think it's easier for me to insert row_number() into the table instead of using index. What do you think?

  6. #6
    Join Date
    Jan 2006
    Location
    USA
    Posts
    115
    Read documents / books (rather get some knowledge) before making any changes...
    Rajesh Patel

    Everybody says - mistake is the first step of success, but it's not true. The correction of the mistake is the first step of success.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Data in a relational database has no inherent order.
    Why do you think you need to add a rownumber column?

    There are several ways to get a "page" or "range" of records from a table. Here is one:

    select top 5 *
    from
    (select top 10 *
    from [YourTable]
    order by [YourColumn] asc) Subquery
    order by [YourColumn] desc

    You should not be relying on the concept of a "row number"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Oct 2006
    Posts
    115
    this is for a particular case to generate rigid report using SQL 2005.

    I need the output to be in the right order in my control, and because there is headers and footers involved, I got no choice but to fix them in certain special order.

    the output is to an excel spreadsheet.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    So throw an ORDER BY clause into your query.

    If you want the data to be ordered according to the way it was entered, then use a datetime column to record the entry date.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Oct 2006
    Posts
    115
    Order by cannot work without row_number. I have too many identical rows.

    Entry date is not accurate as the time unit used by SQL 2005 is not small enough.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then use an Identity column.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Oct 2006
    Posts
    115
    unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You're not listening...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    24
    You can also use temporary table to process desired request in memory, rather to store in table permanently,
    try just like this:

    SELECT ROWID=IDENTITY(int,1,1) , Col1
    INTO #TempTable FROM
    <UrTable List and Where Clause>

    and then retrieve from Temporary table, it will save ur time, disk space and locking issues on underlying table.

    --Riaz

    Quote Originally Posted by tingshen
    unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by rzsattar
    it will save ur time, disk space and locking issues on underlying table.
    Dunno about time but this will use more disk space than blindman's query (temp tables are not held in memory but written to tempdb) and will lock tempdb while it runs (this is due to the "select into" bit).

    You can use the OVER clause if you are really eager to use row_number().
    Code:
    
    SELECT *
    FROM--Derived TABLE - numbering rows
    (SELECT *
    , ROW_NUMBER() OVER (ORDERBY my_unique_column ASC) AS rn
    FROM dbo.MyTable) AS der_t
    WHERE rn BETWEEN 5 AND 10
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •