List of students with their classname in current year I have 3 tables: students and classes and years. In a fourth table called StudentsInClass, I'm saving students classes in each year. Now I want to show a list of students with their className in Current Year.

This is the query I use but I'm not feeling good with it:

   @StartRow int,
   @EndRow int
As
SET NOCOUNT ON;
  begin
    declare @YearID int;
    set @YearID= (select top 1 ID from Years where IsCurrent =1 );
         select * from(
    select top 10000 
              S.*,T2.Title as ClassName,Row_Number() over (order by S.ID)  as 
        ResultSetRowNumber from Students S
         left outer join (
    select SC.StudentCode,T1.Title from 
             (select * from StudentsInClass where YearID=@YearID) SC 
        left outer join Classes T1 on SC.ClassID = T1.ID
    ) T2 on S.Code= T2.StudentCode order by S.Family asc
     ) as PagedResults where ResultSetRowNumber &gt; @StartRow and ResultSetRowNumber &lt;= 
     @EndRow;
  end
Not entirely sure I got your schema right, but something like this should be possible.
(omitted a bit of ordering, and I assume you only have one current year)

select top 10000
    s.*,
    c.Title classname,
    row_number() over (order by s.id) rownumber,
from
    students s
    inner join years
        on years.iscurrent = 1
    inner join studentsinclass sc
        on s.code = sc.studentcode and sc.yearid = years.id
    inner join class c               
        on c.id = sc.classid
where
    row_number() over (order by s.id) &gt;= @StartRow
    and
    row_number() over (order by s.id) &lt;= @EndRow


You could possibly swap on years.iscurrent = 1 with on years.id = year(getdate()).

If you can, you should generally try to avoid too many subqueries. At least attempt to indent them properly.

You could also benefit from having a look at common table expressions. These can help you group up the subqueries in predefined results for your query. Makes it a lot easier to read.
http://msdn.microsoft.com/en-us/library/ms190766.aspx