• Michael Ebaya (11/4/2010)


    sql.monkey (11/4/2010)


    select @min-2 = min(primarykey) where datecol => 'begindate'

    select @max-2= max(primarykey) where datecol <= 'enddate'

    select primarykey, datecol, x,y,z from table where primarykey between @min-2 and @max-2

    works for me

    Yes, because you've missed the entire point, and your query is nothing close to what we're even discussing here. You're finding ONE date column between TWO static values. A B-tree index works fine for that. Finding one STATIC value between two date columns is an entirely different problem.

    Further, if your column isn't indexed and you have "billions of rows", you're going to be table scanning, which means performance is not going to be acceptable. Either you have an index on the column that you don't know about, or the table is orders or magnitude smaller than "billions", or the entire scenario was fabricated to make a nice-sounding post.

    The point was to use the index on the primary key column, which is usually a clustered index, and actually the tables I work with have tens of billions of rows.

    I may have missed the point about the original discussion youre right.