This can be loaded as SAFE. If you take the sleep out it will slow down SQL Server until it is manually stopped. As is it will slow it down for few seconds, but then other queries run unimpeded. If CLR were the equivalent of T-SQL the sleep would not be needed.

public partial class UserDefinedFunctions {
    [SqlFunction]
    public static SqlInt32 Calculate()
    {
        int ret = 0;
        while (ret < int.MaxValue)
        {
            ret++;
            System.Threading.Thread.Sleep(0);
        }
        return new SqlInt32(ret);
    }
};

Here is the T_SQL sort of equivalent. Note that when you run it other queries are unimpeded even in the instant after it starts.

CREATE FUNCTION Forever2()
RETURNS INT
AS
BEGIN
DECLARE @ret int
SET @ret = 0;
DECLARE @t int
SET @t = 0
WHILE @t=0
BEGIN
SET @ret = @ret +1
END
RETURN @ret
END



The following CLR function does not bring SQL Server to its knees, but it does slow it down quite a bit...


public partial class UserDefinedFunctions {
    [SqlFunction]
    public static SqlInt32 Forever()
    {
        int sum = 0;
        while (true)
        {
            sum++;
        }
        // Put your code here
        return sum;
    }
};


The follow T-SQL function, though it runs forever, seems to not affect SQL Server responsiveness at all...


CREATE FUNCTION Forever2()
RETURNS INT
AS
BEGIN
DECLARE @ret int
SET @ret = 0;
DECLARE @t int
SET @t = 0
WHILE @t=0
BEGIN
SET @ret = @ret +1
END
RETURN @ret
END


Do you know if the stuff to protect against infinite loops in the CLR stuff is part of B2?

Dan


