SQL Server: Maximum number of records to return

We ha开发者_如何学Cve 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!

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

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.

On application start up, reset RowCount by executing the following command:

command.CommandText = "SET ROWCOUNT 0;";

When the specific mode is activated, set the RowCount to the needed value by executing the following command:

command.CommandText = "SET ROWCOUNT " + rowCount + ";";

When the specific mode is deactivated, reset RowCount again.

Refer to SET ROWCOUNT (Transact-SQL)