Books Online articles referencing built-in CLR functions (such as FORMAT()) have a remark similar to the following:
|
"FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR)." |
A lot of people seem to interpret this as meaning:
|
"You must enable the sp_configure option 'CLR enabled' in order to use FORMAT()." |
Some then go on and suggest you run code similar to the following before you play with these functions:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO |
The truth is, you do *not* need to enable CLR using sp_configure in order to use built-in CLR functions like FORMAT() or PARSE(), or .NET-based types like hierarchyid and geography / geometry. The functions will fail, however, if you install SQL Server and then later manage to disable or uninstall core components such as the .NET Framework (you can't install SQL Server without it, but I suppose you could try to uninstall it later).
The sp_configure setting merely relates to user-defined CLR objects that you might create and deploy yourself. Go ahead and try the following code on a test instance of SQL Server 2012 CTP3 or RC0:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'clr enabled', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO
DECLARE @d DATETIME = '20120505';
SELECT d = FORMAT(@d, 'yyyy/MMM');
GO
|
Results:
(Of course if you already had CLR enabled, you'll want to re-run the first code sample above to turn it back on.)
So, long story short, only open up the CLR surface area via sp_configure if you have to. And if you just want to take advantage of built-in CLR functionality, you don't have to.