We are using the scheduleuserid in most of the cases in where clause and sometimes questionId along with that.
and currently we have only one file group .
based on you reply, I would recommend
> creating at least one new filegroup (with associated physical file);
> build the partition function using the column most frequently used in range-based searches (scheduleuserid), using, for example, the ranges as shown in my first post on this subject;
> create the partition scheme to link the partition function to the new filegroup;
> build the table on the new partition scheme;
> create the clustered index on the partitioned column, also on the new partition scheme;
> Refer to books online with regard to the rules governing indexes on partitioned tables: http://msdn.microsoft.com/en-US/library/ms188706(v=SQL.90).aspx
or Kimberly Tripp's article: http://msdn.microsoft.com/en-us/library/ms345146(SQL.90).aspx
Space, the final frontier? not any more...
All limits henceforth are self-imposed.“libera tute vulgaris ex”