SQL Server: Maximum number of records to return

Question

We have RowCount to set number of records to return or affect. But we need to set this each time before executing sql statement. I am looking a way to set this once my application started and so it affect all queries execute via my application.

Update

I want to achieve a trial version strategy with limited records! I don't want to mess with my actual code and wish somehow database restrict number of records return in result of any query! An alternative could be pass a parameter to each store-procedure but I really don't like this one and looking for some other strategy!

Answer 1

You can parameterise TOP in your code for all commands

DECLARE @rows int
SET @row = ISNULL(@row, 2000000000)
SELECT TOP (@rows) ... FROM ..

You can have TOP defined globally for all your queries this way, say by wrapping or extending SQLCommand

SET ROWCOUNT is not a safe option: it affects intermediate result sets and has other unpredictable behaviour. And it's partially deprecated to ignored in I/U/D DML: see MSDN

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server

Answer 2

You don't mention the application language you are using, so I can't give you the code, but make a wrapper function for a database connect. In this function, after you connect to the database, issue the SET ROWCOUNT n, make n a parameter and add any logic you need to make it variable. I do a similar thing with CONTEXT_INFO, I set it in my connection function.

Remember, you'll need to connect to the database everywhere in your application (use search and replace) using this wrapper function, and as a result, you'll always have the rowcount set.