I heard that when SQL Server 2005 ships, SQLCLR (the ability to execute .NET code in SQL Server) will be disabled by default. This is part of an "off-by-default" philosophy that goes "because there are so many revolutionary new features in SQL Server 2005, and DBAs and programmers may not have had time to digest the ramifications of using all of them, you'll have to enable them, one at a time, when you understand them. They'll be off until you make a conscious decision to turn them on". IIS 6.0 works this way with its programming extensions; for example, you must enable using ASP.NET or ASP classic.

Thought I'd better look up how to turn it on and off:

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
-- turn it on
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- or turn it off
EXEC sp_configure 'clr enabled', 0
GO
RECONFIGURE
GO

That was easy. I turned it off and restarted SQL Server wanting to see what error message it would produce. Some folks I've spoken to claim to want it off even though they understand it, because SQLCLR loads the .NET runtime. This takes about 10 meg of memory, from SQL Server's "normal" memory pool (it does not use the MEM-to-leave pool as in-SQL Server COM components do). Turning SQLCLR off did not cause the CLR to be unloaded (or at least it didn't produce a log message to that effect). So I wanted to see if I could get the .NET runtime to load if SQLCLR is disabled. Started with:

CREATE ASSEMBLY foo FROM 'c:\foo.dll'
GO

Even though foo.dll doesn't exist on my machine, this statement normally causes to .NET runtime to load. Even though it errors out with the message:

Msg 6501, Level 16, State 7, Line 1
CREATE ASSEMBLY failed because it could not open the physical file 'c:\foo.dll': 2(The system cannot find the file specified.).

The reason that the .NET runtime is loaded in this case in that SQLCLR internally calls Assembly.Load("c:\foo.dll") (or some variation) to load the assembly and validate it using the reflection APIs. Both Assembly.Load and reflection are, of course, managed code, requiring the runtime.I expected a different error this time, because SQLCLR is disabled. Same error. And the log revealed that the .NET runtime had been loaded. Even though SQLCLR is disbaled. Hmm....didn't expect that. Just to see how far I could go, I got out a real assembly and ran:

CREATE ASSEMBLY MetricConverter
 FROM 'c:\types\metricconverter.dll'

This succeeded, I'd cataloged my assembly. Hmm... How about:

CREATE FUNCTION convertme(@a FLOAT)
RETURNS FLOAT
AS EXTERNAL NAME MetricConverter.[DM.EssentialYukon.MetricConverter].KilometersToMiles

This succeeded too. At this point I started to doubt that I'd actually turned SQLCLR off. Then I ran:

DECLARE @f FLOAT
SET @f = dbo.convertme(42)
PRINT @f

This failed as expected:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled.

So the 'clr enabled' option disables *running* user CLR code in SQL Server 2005. It doesn't diable loading the runtime. Or cataloging database objects, like assemblies and UDFs, that use SQLCLR. It's a convenience to allow DBAs (usually the only ones with this permission) to catalog these objects before allowing the actual user CLR code to be executed. If you're really concerned about the 10 meg, don't use the DDL. Note to self: test *everything* before making assumptions. I guess that goes along with “off-by-default”.