Limit the number of rows returned on the server side (forced limit)

Question

So we have a piece of software which has a poorly written SQL statement which is causing every row from a table to be returned. There are several million rows in the table so this is causing serious memory issues and crashes on our clients machine. The vendor is in the process of creating a patch for the issue, however it is still a few weeks out. In the mean time we were attempting to figure out a method of limiting the number of results returned on the server side just as a temporary fix.

I have no real hope of there being a solution, I've looked around and don't really see any ways of doing this, however I'm hoping someone might have an idea.

Thank you in advance.

EDIT

I forgot an important piece of information, we have no access to the source code so we can not change this on the client side where the SQL statement is formed. There is no real server side component, the client just accesses the database directly. Any solution would basically require a procedure, trigger, or some sort of SQL-Server 2008 setting/command.

Answer 1

There got to be a command for it.

I know in MYSQL it is "LIMIT(firstindex,lastindex)" at the end of the sql statement.

I think I heard in MSSQL you can write :

Select TOP 10,20 ... or something like this

That would mean you select 20 rows with 10 beginning i think

Answer 2

you can use select top

SELECT TOP 50 PERCENT * FROM Persons(http://www.w3schools.com/sql/sql_top.asp)

or

Do the paging like this will help you

CREATE PROCEDURE [dbo].[GetRequestedRecordByPage] 
 @FromList nvarchar(200)              -- Table Name  
,@SortingCol nvarchar(200)         -- Sorting column Name
,@SelectList nvarchar(200) = '*'         -- Select columns list
,@WhereClause nvarchar(200) = ''        -- Where clause i.e condition
,@PageNum int = 1                           -- Requested page number
,@PageSize int = 5                 -- No of record in page
,@TotalNoOfRecord int output         -- Total no of selected records
AS 
Begin
  SET NOCOUNT ON 
  DECLARE @Query nvarchar(max)         -- query going to be execute

  IF rtrim(ltrim(@WhereClause)) <> '' 
  BEGIN
      SET @Query ='SELECT   @TotalNoOfRecord = COUNT(*) 
                      FROM     ' + @FromList + ' 
        WHERE    ' + @WhereClause 
  END
  ELSE 
  BEGIN 
      SET @Query ='SELECT   @TotalNoOfRecord = COUNT(*) 
                      FROM     ' + @FromList 
  END

    /* Count no. of record */
       EXEC sp_executeSQL 
        @Query, 
        @params = N'@TotalNoOfRecord INT OUTPUT', 
         = @TotalNoOfRecord OUTPUT 

DECLARE @lbound int, @ubound int 




/* Calculating upper and lower bound */
        SET @lbound = ((@PageNum - 1) * @PageSize) 
        SET @ubound = @lbound + @PageSize + 1 


/* Get list of record(s) */
        SELECT @Query =  ''
        SELECT @Query =  'SELECT  * 
                          FROM    ( 
SELECT  ROW_NUMBER() OVER(ORDER BY ' + @SortingCol  + ') AS rownumber,' +@SelectList  +   
                                        ' FROM    ' + @FromList 

        IF rtrim(ltrim(@WhereClause)) <> '' 
        BEGIN
            SELECT @Query = @Query + ' WHERE   ' + @WhereClause 
        END

            SELECT @Query = @Query + '     ) AS tbl 
WHERE rownumber > ' + CONVERT(varchar(9), @lbound) + 
      ' AND rownumber < ' + CONVERT(varchar(9), @ubound) 

       EXEC (@Query)                 
End

Answer 3

One possible solution could be to

• rename the offending table
• create an updatable view with the original tablename
• do a SELECT TOP x * FROM OffendingTable as your view definition

As such, the client isn't aware of the change when selecting the data.

---

Use the query governor

If you don't mind returning no data at all for the offending query, the query governor allows you to do so.

Answer 4

If the client is using TCP to connect to the database, you can insert a mostly transparent TCP proxy server between the client and database server on the server side. You can then rewrite any offending queries that come from the client (using TOP or some means of improving the query).

You then configure SQL Server to run on a different port, start up your proxy to serve on the original port and have it connect to SQL server on the new port. If you know the client's originating IP address, you could use port forwarding so that you could direct just them to the proxy, and leave the database server configured as is.

I could write and test this in an hour, but it does take some knowledge of socket programming.

Answer 5

You could drop all but X records from the table and store them somewhere else