Sunday, December 30, 2007 10:56 AM
xmldeveloper
Pop Quiz: SET OPTIONS in Triggers
It started with this question: Let's suppose that I want override the QUERY_GOVERNOR_COST_LIMIT value for an user that connects to the server. How can I do that?
I want to customize the value per user so I won't use sp_cofigure and query governor cost limit option, as it would configure the cost limit at the instance level.
I thought that I might try to do it in a logon trigger.
So what would be the value for the QUERY_GOVERNOR_COST_LIMIT option if I connect after running this code:
USE master;
GO
CREATE TRIGGER test_logon_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET QUERY_GOVERNOR_COST_LIMIT 5;
END;
Hint: SET (Transact-SQL) [quote]... If a SET statement is run in a stored procedure or trigger, the value of the SET option is ...[/quote]
Another Hint: The default value is 0.