Background: We have a large table of documents (1,000,000+ entries) with a variable number of text columns and bit columns. The variable number of columns is implemented by using a DocTextFields table and a DocCheckboxFields table where only the entries that contain data are listed. There can be over 100 of these variable number of columns. Please see attached Stored-Procedure.xlsx file for more detail about how these tables are defined.
We are looking for an efficient way to pull back the data so the following is true (see Stored-Procedure.xlsx for an exact example of how we would like the resulting table to look):
1. The FieldId of the text field or the checkbox field should be the column header of the returned table.
2. Only the requested fields are included. Typically maybe 10 of the 100 fields are needed.
3. Only the requested DocIds are included. The stored procedure needs to be in a form where we can add ROW_NUMBER() OVER and pull back only 25 docs at a time.
4. It needs to be possible to sort by the TextFields or the CheckboxFields
We are using SQL Server 2008 and have a fair amount of flexibility as to how this should be done. Any help would be greatly appreciated.
NOTE: I have included Schema-Script.sql and Data-Script.sql for an easy way to create a test database and try out various approaches.
Please let me know if you need additional information.