Looping through a table to find records

I assume your table has a indexed time column as SQL Server does not always store records in the order they are created.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

djj55 addresses the most important counter question: In which order? Chronological order seems plausible considering this has to do with LOGIN and LOGOUT events.

Typically any sql query is an iteration of data, it just needs an ORDER BY sometime and where clauses for lower and upper bound (WHERE sometime>X AND sometime<Y) to get the portion of data you want.

Bye, Olaf.

Here is the worst part- the data is coming from a view which is gigantic to say the least. So, the view does NOT have an index on it.

Not at all shocking, because a view is a query on a table. So what's the query? You can find out, modify it and do the modified query - or create a new view - or simply select from the view in as if it was a table.

FYI: You jump on false conclusions, if you think you can't make use of table indexes when querying a view, just because thew view itself is not materialized and has no index.

1. I have two tables headtable and detailtable and did the following query, which is optimized by indexes:

2. I created a view (view_all) with this query:

3. I used this query on the view and it is optimized on the same indexes as the original query is:

So indeed querying a view you make use of all indexes the underlying tables have, as a view is nothing but a query and queries are optimized, if you use a view as a table it's just like using a CTE, and CTEs also make use of indexes. Views themselves have no indexes, because they have no permanent data (unless you create a 'materialized view, but that's not applicable for such log data). You have all the advantages (and disadvantages) of the optimization of queries with a view or a query including a view. You can imagine the view query used as aliased (named) subquery and the overall query is analyzed and optimized.

Bye, Olaf.