Query Time out

Make it a smaller query and see it it returns without a timeout.  According to this page http://msdn.microsoft.com/en-us/library/ms180878%28SQL.100%29.aspx , '1/2008' may not be a valid date format.

May you send to us the acess path?

SET SHOWPLAN_TEXT ON
go

Select allOpen in new window

that was a typo should have been '1/1/2008'

ShowPlan results minus the object names:

  |--Compute Scalar(DEFINE:
       |--Parallelism(Gather Streams)
            |--Nested Loops(Inner Join, OUTER REFERENCES:
OPTIMIZED WITH UNORDERED PREFETCH)
                 |--Filter
                 |    |--Compute Scalar(DEFINE:(
                 |         |--Index Scan(OBJECT:
                 |--Clustered Index Seek(OBJECT:
SEEK: LOOKUP ORDERED FORWARD)

Try like this

select * from
(SELECT r.* from Records r (nolock) where $partition.Onefn(ID_Range)=1) r
where CAST(RIGHT(r.SchID,3) AS INT) BETWEEN 0 AND 15
and date between '1/1/2005' and '1/2008'

Select allOpen in new window

The query is running now for 25 mins; it times out after an hour.  I am running Ghunaima´s solution; and martenrune was correct with not using the where to access the index.

Yes, I agree.
Ghunaima´s solution use two levels of nested queries. We can use more. I need more informations to do that.
Wich index filter less rows?

select count (1) from Records r (nolock) where $partition.Onefn(ID_Range)= 1
union all
select count (1) from Records r (nolock) where where x.date between '1/1/2005' and '1/1/2008'

Select allOpen in new window

Please send us the result, thanks in advance.

I would like not to use r.*, and just the strict necessary columns. May I?
r.column1, r.column2, ..., r.column«N». (and not all columns, as in r.*) May we?

Ghunaima´s solution timed out after 1:56 mins.

This is the candidate to new anatomy. Please do not send it to the sql engine yet.

May I see the statistics first?

select r3.*
from (
       select r2.*
       from (
              select r1.*
                from (
                       SELECT r.column1, r.column2, r.column3 
                         from Records r (nolock) 
                        where $partition.Onefn (ID_Range) = 1
                      ) r1
            ) r2
       where date between '1/1/2005' and '1/1/2008'
     ) r3
where CAST(RIGHT(r.SchID,3) AS INT) BETWEEN 0 AND 15

Select allOpen in new window

select r2.*
from (
       select r1.*
         from (
                SELECT r.column1, r.column2, r.column3,
                       r.SchID
                  from Records r (nolock) 
                 where $partition.Onefn (ID_Range) = 1
                   and r.date between '1/1/2005' and '1/1/2008'
               ) r1
     ) r2
where CAST(RIGHT(r2.SchID,3) AS INT) BETWEEN 0 AND 15;

Select allOpen in new window

You can optimize this query to death, but the bottom line is that you are returning too many rows.  In other words the problem is not with SQL Server, but rather your network.

line 2: into  TheSameDatabase.Results

select r2.*
into  TheSameDatabase.Results
from (
       select r1.*
         from (
                SELECT r.column1, r.column2, r.column3,
                       r.SchID
                  from Records r (nolock) 
                 where $partition.Onefn (ID_Range) = 1
                   and r.date between '1/1/2005' and '1/1/2008'
               ) r1
     ) r2
where CAST(RIGHT(r2.SchID,3) AS INT) BETWEEN 0 AND 15;

Select allOpen in new window