0

The documentation for SQL Server's T-SQL FORMAT function implies calls are forwarded to .NET's ToString methods on .NET types corresponding to T-SQL types, and TRY_PARSE and PARSE's documentation is quite explicit about it using the CLR:

https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql

FORMAT( value, format, culture )

format: The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)"). Composite formatting is not supported.

culture: culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server. If the culture argument is not valid, FORMAT raises an error.

and:

https://learn.microsoft.com/en-us/sql/t-sql/functions/parse-transact-sql?view=sql-server-ver16

PARSE( string_value AS data_type [ USING culture ] )

Remarks

  • Keep in mind that there is a certain performance overhead in parsing the string value.
  • PARSE relies on the presence of the .NET Framework Common Language Runtime (CLR).
  • This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

So using FORMAT( intValue, 'N2', 'en-US' ) and PARSE( '2023-02-21' AS date ) in a T-SQL SELECT projection will cause SQL Server to somehow invoke .NET's Int32.ToString( "N2", CultureInfo.GetCultureInfo( "en-US" ) ) and DateTime.Parse( "2023-02-21" ) methods at runtime.

...which strikes me as introducing considerable overhead, as SQL Server would need to use .NET's runtime type/value marshalling to convert from SQL Server's own internal representation of varchar(n) and nvarchar values to .NET's UTF-16-based strings, invoking those .NET methods (which, in many cases, are forwarded-out to non-CIL native code within the CLR... thus making the CLR part pointless, no?)

But more profoundly, Azure SQL notably disabled SQL-CLR shortly after launch for security reasons and there's no indication Microsoft will bring the feature back, except in their Managed Instance service, so if Azure SQL has the SQL-CLR disabled, how is it that our T-SQL scripts have no problem with using .NET-based FORMAT/PARSE functions?


Based on this, I have some questions, which all basically boil-down to "What's going on, eh?":

  • Computers can have multiple versions of .NET installed side-by-side, so exactly what version of the CLR and BCL are being used when you use FORMAT/PARSE/TRY_PARSE?
  • How does it work on SQL Server-for-Linux where only post-.NET Core 3.1 versions of .NET are available?
    • The ("classic") .NET Framework 4.x is very Windows-specific, and I know SQL Server-for-Linux is essentially its own operating-system, but that seems like a lot of engineering-effort to keep using .NET Framework 4.x in this situation. It boggles the mind.
    • Whereas if SQL Server is using .NET Core, or .NET 5+ instead of the .NET Framework 4.8 runtime, then how can we use .NET 5+ in our own SQL-CLR projects?
  • How does this impact parallelisation of queries and query-plan generation?
    • The article about SQL-CLR performance does not make any mention of scalar parallelisation nor UDF inlining at all, beyond saying that T-SQL built-ins are generally better than SQL-CLR, but that SQL-CLR is still far better than old-world CURSOR-based approaches.
      • ...but I assume that using a scalar SQL-CLR function in a query breaks parallelisation (as .NET doesn't expose a way to explicitly mark a method as being reentrant or thread-safe, so SQL Server would have no way of knowing that, so it would have to disable parallelisation) - which kills performance, on top of the type-marshalling overhead - it's very odd that this factor isn't mentioned in the documentation.
  • And if Azure SQL has SQL-CLR disabled, then how do FORMAT and PARSE work? Does Azure SQL have a native built-in reimplementation of these functions that avoid the CLR entirely, or does it actually have SQL-CLR enabled but only allows a small set of blessed .NET BCL functions to be called? If so, then why only those functions when there exist many other useful functions we'd also like to call from T-SQL (composite string-formatting, Calendar, Regex, etc) - the whole thing seems very arbitrarily limited.
    • And if Azure SQL does have SQL-CLR running internally but only for first-party code, why does the clr enabled option return 0 (and many other CLR-related properties/functions seem broken) unless Azure SQL's T-SQL sandboxing is doing some convoluted hiding-of-functionality?

Update

(I hate it when my initial research doesn't find anything helpful, then I post to SO/SE, and then right afterwards I search Google again with slightly different keywords and find out what I wanted to know... so here's some information I just found, but I don't want to post it as an answer just yet, maybe in a couple of weeks if no-one else can post a succint, accurate, recent and relevant answer)

  • According to https://github.com/MicrosoftDocs/sql-docs/issues/1594 and my own quick research:
    • See also: https://stackoverflow.com/a/41114654/159145
    • As of Q1 2023, all versions of SQL Server, up-to-and-including SQL Server 2022, including SQL Server for Linux, use some version of the .NET Framework 4.x.
      • On Windows Server, it uses whatever the latest version of the OS-provided .NET Framework 4.x is, which should be .NET 4.8.1.
      • On Linux, it uses a private build of .NET Framework 4.x that runs within SQL Server's Windows-abstraction layer "SQLPAL" which is also involved with sandboxing SQL-CLR user-code.
        • On Linux, the EXTERNAL_ACCESS option (which would allow SQL-CLR code to break-out of the sandbox) is not supported, ditto on Azure SQL Server Managed Instance.
  • However I haven't been able to find out how/why PARSE/FORMAT/etc works in Azure SQL despite all the indications that SQL-CLR is entirely disabled - or the performance/overhead/query-plan implications of the native-to-managed-and-back process involved in invoking .NET code from T-SQL.
4
  • And CLR does also work fine in Azure SQL Database for some allow listed assemblies - such as specific builds of tSQLt Feb 22 at 1:48
  • @MartinSmith Do you know where we can find information on how to apply to Azure to get custom SQL-CLR assemblies whitelisted then? I would really, really benefit from being able to use Regex in T-SQL.
    – Dai
    Feb 22 at 2:05
  • No - I don't know how tSQLt managed it! TBH I assume they were probably just lucky and the right people complained and managed to get listened to when previously working unit testing was suddenly broken when they yanked CLR from Azure Feb 22 at 6:10
  • 1
    @MartinSmith and Dai: From the discussion on that tSQLt GitHub issue that Martin linked to, it appears to be: 1) a single build of tSQLt (not multiple, though nothing definite was stated), and 2) not at the request of tSQLt. When SQLCLR was ripped from Azure DB, Microsoft whitelisted some assemblies across various companies that couldn't easily work around SQLCLR's removal (hence trusted_assemblies). My guess (similar to Martin's) is that tSQLt just happened to be one of them. Newer versions are not covered as there's no process to apply for whitelisting, and I don't think where will be. 😿 Feb 22 at 15:10

1 Answer 1

3
  1. String values in SQLCLR are only NVARCHAR. You cannot pass in VARCHAR (i.e. 8-bit) values. Also, NVARCHAR is UTF-16LE, same as within .NET (and Windows in general). This is not to say that there isn't overhead in passing in string values, just that an additional conversion is not necessary.
  2. Yes, FORMAT (and likely the others that were previously based on CLR) are still CLR-backed (I just tested in SQL Server 2022).
  3. I have not tested performance in several years, but the last time I did (it might have been on SQL Server 2017), the performance of the built-in FORMAT function was pretty much the same as the Date_Format function that's included in the free version of my SQL# library. And in both cases, the performance is far from ideal, so not great over millions of rows, but the functionality is certainly welcome for small datasets or when setting variables.
  4. Regarding why CLR-based built-in functions such as FORMAT work in Azure SQL DB when the server configuration option for CLR Enabled is permanently set to 0: the CLR Enabled option controlls user assemblies, not internal CLR-based functionality (which would also include the HierarchyID, Geometry, and Geography types). This is in contrast to Azure SQL Edge (the ARM-based SQL Server implementation) in which the CLR doesn't even exist internally, so the internal CLR-based functions (e.g. FORMAT) do not work (at least not when I tested it a year or two ago).
  5. Regarding the effect the CLR-based built-in functions have on parallelism: that's a good question and shouldn't be too difficult to test. However, I think it's safe to assume (unless a test proves otherwise) that CLR-based built-in functions do not prevent parallelism (like T-SQL scalar UDFs do) because user-defined SQLCLR scalar functions can work in parallel plans as long as they a) do not allow for data access and b) are marked as deterministic.
2
  • Your reputation precedes you, I had a feeling you'd reply :) - On a related note, do you have any insight into how the SQL Server folks are going to migrate to newer versions of .NET? It's really odd that the official documentation is seemingly stuck in the year 2015...
    – Dai
    Feb 21 at 23:30
  • @Dai Thank you very much for those kind words 😺. Unfortunately, I have no insight into how Microsoft will handle migration to a newer .NET version. I speculate it might never happen, or at least not for a very long time. I get the impression that the SQL Server team doesn't really like/appreciate SQLCLR and would prefer it go away entirely. There have been no updates since SQL 2012 except the horrible CLR strict security server config option and trusted_assemblies feature starting in 2017 (both of which are unnecessary and clearly show nobody on the team understands how SQLCLR works). Feb 22 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.