CLR stands for Common-Language Runtime. This is what makes .NET function. When code is written in any .NET language (e.g. C#, VB.Net, Visual J#, or Visual C++), it gets compiled down to a common, intermediate language called p-code (originally called Microsoft Intermediate Language [MSIL], then renamed to Common Language [CL], but still referred to as MSIL in some documentation). The .NET CLR runs this MSIL / CL on the Operating System. This is why you need to have the .NET run-time on a machine in order to run .NET programs. It is also why all of those languages mentioned can make [mostly] equal use of the .NET Framework and resources. Here is a short video from Microsoft with an overview of SQLCLR.

Microsoft included the .NET CLR inside of SQL Server starting with SQL Server 2005. This allows for Stored Procedures, User-Defined Functions, and Triggers to be able to make use of the .NET Framework via any of the .NET languages (C#, VB.Net, Visual J#, and Visual C++). No, this does NOT replace T-SQL. T-SQL is still the ONLY way to manipulate and retrieve data in SQL Server. Even if you create a SQLCLR Stored Procedure or User-Defined Function, you will still have to connect to the database inside of it and issue T-SQL commands. Granted, the connection to the database inside of a SQLCLR Procedure or Function is much more efficient than an outside call from the application since the connection already exists (as you are already in the database) and you just use it. However, you would not want to use the CLR for anything that does not have complex logic or is not procedural in nature as the most efficient mechanism for data retrieval and manipulation is still standard T-SQL via a Stored Procedure or User-Defined Function. So, the true benefits of the CLR are: a) the ability to more efficiently / quickly perform procedural logic, and b) the ability to make use of the .NET framework functionality that could never be duplicated in T-SQL anyway. THIS is where SQL# comes to the rescue. SQL# brings the useful aspects of .NET via the CLR into the T-SQL world so that now T-SQL can do so much more!

Please see the following article I wrote on SQL Server Central for a more in-depth look at what SQLCLR can do and when it makes sense to use it (free registration is required to access their content):
Stairway to SQLCLR Level 1: What is SQLCLR?

Unfortunately, no. SQL Server 2005 is the first version to incorporate the .NET CLR inside the database engine so the previous versions would have no way to make use of .NET code. If you are stuck using SQL Server 2000 (or before) for a while and need to do complicated, procedural logic, the only way is to create a COM DLL and register it as an Extended Stored Procedure (xp). See Books Online (BOL) for more detailed information on creating and registering xp DLLs in SQL Server 2000. Please note that Extended Stored Procedures are deprecated as of SQL Server 2005.

SQL# works with most versions of Microsoft SQL Server released after 2000, specifically:

• On-prem / stand-alone: 2005, 2008, 2008 R2, 2012, 2014, 2016, 2017, and 2019
• Microsoft Azure: Azure SQL Managed Instance
• Amazon RDS: 2012, 2014, and 2016

The only SQL Server offerings that do not support SQLCLR (and by extension, SQL#) are:

• SQL Server 2000 or prior
• Azure SQL Database (Single DB)
• Azure SQL Edge
• Amazon RDS (a feature of AWS)

SQL# is as safe as you want it to be. Regardless of the security level set for the SQL# Assembly, .NET CLR code has the same permissions as any other T-SQL code based on either the user running the code, or the user specified in the EXECUTE AS clause of the Stored Procedure or User-Defined Function. All SQL# Procedures and Functions are created with EXECUTE AS CALLER so that anyone who runs any SQL# Procedures or Functions will only be able to do what the DBA has already allowed them to do. The CLR canNOT be used to circumvent database security; it works WITHIN the confines of the database's security. That being said, the SQL# Assembly is loaded with a default setting of SAFE. You will see this when you get the install SQL script as it creates the Assembly. Updates to the SQL# Assembly done via the SQLsharp_Update Stored Procedure will NOT change the security setting of the SQLsharp Assembly prior to the update. There are some SQL# User-Defined Functions and Stored Procedures that require EXTERNAL_ACCESS to get to the InterNet. If you want to use any of these InterNet or FileSystem functions, you will need to change the security setting via the SQLsharp_SetSecurity Stored Procedure. If you use that Procedure to set the security level of the SQL# Assembly to either EXTERNAL_ACCESS or UNSAFE, it will also set the database property of is_trustworthy_on to TRUE (who are we kidding, it’s "1" in SQL Server ;-). You can read more about security in relation to the CLR here: Security in the CLR World Inside SQL Server (https://www.devx.com/codemag/Article/31193)

Please see the following two articles I wrote on SQL Server Central for a more in-depth look at SQLCLR security:
Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)

In SQL Server 2000, COM DLLs registered as Extended Stored Procedures (xp) always point to the DLL file on the filesystem. This means that you have to keep a copy of those DLLs around so that code in the database that references those Extended Stored Procedures continue to function. In contrast, .NET Assemblies in SQL Server 2005 (and beyond) are loaded directly into the database and do not point to any location outside of the database. This has the benefit of ensuring that through backups and restores, code in the database that points to these .NET Assemblies continues to function.

That's easy. Just run the following query:

There are two ways to check for updates:

1. You can check this website and see if there is a newer version in the Download section, or
2. You can use the internal scalar Function to check for updates in one step:
   
   
   SELECT SQL#.SQLsharp_IsUpdateAvailable();

There are two ways to get the updates:

1. You can visit this website and download a new install script, or
2. You can use the internal Stored Procedure (Full version only) to download the current install script to a local drive:
   
   
   EXEC SQL#.SQLsharp_Download;
   
   Please note that if you are outside of the free update elligibility period (i.e. license has expired) then you will receive an error message directing you to the website where you can renew your license (at a discount).

I will consider most anything. Use the Contact Us form to send any suggestions.

Easily (though you shouldn't because it's so great!). Just run the following Stored Procedure (in the Database where SQL# is installed):


This will remove ALL Stored Procedures, User-Defined Functions, User-Defined Types, and User-Defined Aggregates linked with the SQL# Assemblies and then remove the SQL# Assemblies. It will also remove the SQL# Schema (if it is empty), and it will remove the SQL# User.

Starting in version 4.0, if you have installed the Instance-level objects (this happens in most installations) and want to remove those, just execute the following:

Easily. Just execute the following Stored Procedure:


This will show the current setting for both the database and the SQL#_2 Assembly.

Sure. Just execute the following sequence of Stored Procedures:


Note: If the database property of is_trustworthy_on was previously FALSE before setting the security to 2 (for EXTERNAL_ACCESS) AND you are using a version of SQL# prior to 3.0.x, it will be set to TRUE by the act of setting level 2 but will NOT be set back to FALSE when setting security level 1 (for SAFE) as there is no way to determine who set is_trustworthy_on to TRUE or why or when. If you need to set is_trustworthy_on back to FALSE, you can do that via the T-SQL command:

Sadly no. As of December 2016, Azure SQL Database (formerly named SQL Azure) does not support SQLCLR (see here). Hopefully SQLCLR will be supported by SQL Azure in the near-future.

Please note that while support for SQLCLR (SAFE Assemblies only) was added in late 2014 to Azure SQL Database V12, it was removed (supposedly temporarily) on April 15th, 2016.

YES! Microsoft SQL Server on Amazon RDS does support SQLCLR for Assemblies marked as SAFE (see Version and Feature Support on Amazon RDS) for SQL Server versions 2012, 2014, and 2016. SQL# version 4.0 has been tested on an Amazon RDS instance of SQL Server 2016 SP1 CU2.

Running SQL# on Amazon RDS requires two things:

1. If CLR integration has not already been enabled, create a new "Parameter Group" so that you can change the "CLR Enabled" option to "1" and then associate the instance with the new "Parameter Group" and reboot the instance.
2. After filling out the Database name for the USE statement in the installation script, change the @MaxAllowedAccessLevel to "0" (i.e. SET @MaxAllowedAccessLevel = 0;).

PLEASE NOTE: Custom SQLCLR assemblies (and by extension, SQL#) are (sadly) not supported for SQL Server versions 2017 or newer (this only applies to Amazon RDS!).

This greatly depends on what the exact operation is. For many simple tasks, straight T-SQL is much faster than SQLCLR. However, as operations get more complex, the efficiency of SQLCLR typically overtakes the straight T-SQL approach, especially when comparing UDFs / TVFs. I published a study — CLR Performance Testing — which shows that SQLCLR performs quite well, but as one would expect, sometimes straight T-SQL is clearly the better choice. It all comes down to testing your particular situation. However, if you are not dealing with a lot of data and/or transactions, then the differences in performance might not be noticable.

As stipulated in the License Agreement:
The licensee may not distribute the Full (Paid-For) version of SQL# to anyone who does not work for the licensee and/or who will not use it for the business of the licensee. If you are interested in a Distribution License (for an additional cost), please contact us via the contact form located at https://SQLsharp.com/contact/.

At the moment there is no option to get the source code. There had been plans to offer a Source Code License for an additional cost, but for now those plans have been put on hold indefinitely.

The Full version of SQL# is licensed per “Production” server, with a current maximum of 20 servers for the per-server pricing. 21 or more Production servers is a flat fee and is considered a Site License.

For the purposes of licensing, "Server" is defined as an individual, active instance of an Operating System, containing at least one active Instance of Microsoft SQL Server.

SQL# can be installed on any number of databases. SQL# can also be installed on any number of non-Production servers (e.g. development, testing, etc.), workstations, and Production-level passive cluster nodes, provided that the appropriate number of Production servers have been licensed.

Each license period, whether initial purchase or upgrade, is for 2 years (i.e. 24 months).

Non-beta versions never expire or stop working (this applies to both Free and Full versions). Time-limited software is greatly complicates administration and management. An expiration date would be an impediment to someone getting their job done, and the goal of Sql Quantum Lift is to provide tools that reduce complications, not add to them.

However, beta versions do have expiration dates. The reason for this is that beta versions are, by definition, not Production-ready code and so should not be installed and forgotten about. Having an expiration date (on beta versions only!) ensures that "incomplete" and/or "unverified" code is not still running 20 years from now, on a server that is sitting in a corner, and nobody who currently works for the company was around when the server was installed, and the admin who put it there left only a note stating "Critical system! Do not shut-off or remove!".

No. The only dependencies outside of the installation script are: 1) installing into a version of Microsoft SQL Server 2005 or newer, and 2) having the ability to enable CLR Integration (enabling CLR is done automatically by the installation script, but certain environments prohibit CLR from being enabled, and having the server option of “Lightweight Pooling” enabled also prevents CLR from being enabled, though it is rare that anyone even uses that option). Requiring a License Key would be an impediment to someone getting their job done, and the goal of Sql Quantum Lift is to provide tools that reduce complications, not add to them.

No. Once you have the software (either downloading the Free version, or purchasing and downloading the Full version) it is yours and will just work. The Full version does not require a License Key in order to function, and neither Free nor Full versions expire.

The License Key identifies each customer and is proof-of-purchase of the Full version of SQL#. It allows for:

downloading free updates (of the Full version) during the license period, and

the ability to upgrade / renew after the license period at a 10% discount off the current regular price for the current number of Production servers.

Yes, SQL# does indeed work with SQL Server on Linux (introduced in SQL Server 2017). The only restriction is that only SAFE Assemblies are allowed on Linux. Be sure to set the @MaxAllowedAccessLevel variable in the installation script to "1".

Yes, SQL# does indeed work with SQL Server 2017. The new "CLR strict security" server-level configuration option (enabled by default) requires that all Assemblies, even those marked as SAFE, be signed with a Certificate or Asymmetric Key, and have a corresponding signature-based Login that has been granted the UNSAFE ASSEMBLY permission. SQL# has met these conditions for many years now.

Microsoft did provide an alternate method for Assemblies that are not signed, and that is to register them as “Trusted Assemblies”. Registering Assemblies as “trusted” is entirely unnecessary for Assemblies, such as all SQL# Assemblies, that meet the conditions noted above. And in fact, the “Trusted Assemblies” feature itself is entirely unncessary and should have never been released. Please see:
SQLCLR vs. SQL Server 2017, Part 4: “Trusted Assemblies” – The Disappointment
for details.

The installer for SQL# 4.1 handles SQL Server 2017 properly. If you can, please upgrade to version 4.1. Otherwise, for SQL# versions prior to 4.1, you can do the following:

Yes! In fact, most SQL# functionality appears to work on a Managed Instance, including functionality requiring either “External Access” or “Unrestricted” permissions. So far, only functionality that accesses the network appears to not work, though it is possible that the errors could be due to firewall and/or routing configuration (more testing might be needed). Testing was done using SQL# 4.2 on Azure SQL Managed Instance 15.0.2000.

The @MaxAllowedAccessLevel variable in the installation script can be left at the default value of "3".

No. There is absolutely no support for any .NET / CLR-based functionality on Azure SQL Edge, including built-in functions such as: AT TIME ZONE, COMPRESS, DECOMPRESS, and FORMAT.