SQL CLR Library , SQLCLR , CLR Routines , CLR Library , SQL Server CLR , Bulk Export , Regular Expressions , HTML Export , Generate Insert Statements , Median , Automation , RegEx 2017-3-27 8-25
SQL# / SQLsharp                   SQL #         Expanding the
capabilities of T-SQL

       Home       Features       Benefits       F.A.Q.       Contact Us 
     Full Version       Free Version       Documentation 
❗❗ Version 4.0 Announcement ❗❗
  SQL CLR Library , SQLCLR , CLR Routines , CLR Library , SQL Server CLR , Bulk Export , Regular Expressions , HTML Export , Generate Insert Statements , Median , Automation , RegEx

So, what exactly is this "SQLCLR"?
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.

What does the .NET CLR Integration feature (starting in Microsoft SQL Server 2005) do? Does it replace T-SQL?
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?

Does SQL# (SQLsharp) work with Microsoft SQL Server 2000 (or before)?
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

What about security? Is SQL# safe?
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 (http://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 (free registration is required to access their content):
Stairway to SQLCLR Level 3: Security (General and SAFE Assemblies)
Stairway to SQLCLR Level 4: Security (EXTERNAL and UNSAFE Assemblies)

What happens when I backup and restore the database? Do I also need to keep a copy of SQL# (SQLsharp) on the filesystem?
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.

How do I know what version I have installed?
That's easy. Just run the following query:
SELECT SQL#.SQLsharp_Version();

How do I know when upgrades are available?
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();

How do I get these upgrades?
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

I have an idea for a new SQL# function. Will you include it?
I will consider most anything. Use the Contact Us form to send any suggestions.

I don't want SQL# installed anymore. How do I get rid of it?
Easily (though you shouldn't because it's so great!). Just run the following Stored Procedure:
EXEC SQL#.SQLsharp_Uninstall
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.

How do I check my current security settings?
Easily. Just run the following Stored Procedure:
EXEC SQL#.SQLsharp_SetSecurity 0;
This will show the current setting for both the database and the SQL# Assembly.

I don't want to set EXTERNAL_ACCESS but I still would like to be able to update via SQLsharp_Download. Is this possible?
Sure. Just run the following sequence of Stored Procedures:
EXEC SQL#.SQLsharp_SetSecurity 2 -- open up InterNet access (2 = EXTERNAL_ACCESS)
EXEC SQL#.SQLsharp_Download -- get the update now that we temporarily opened up InterNet access
EXEC SQL#.SQLsharp_SetSecurity 1 -- close any type of external resource access (1 = SAFE)
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:

Does SQL# work with Azure SQL Database (Microsoft's cloud-based SQL Server)?
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.

Are SQLCLR Stored Procedures and Functions slower than using T-SQL? 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.

Can I distribute (i.e. include) SQL# with my application?
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 http://www.SQLsharp.com/contact/.

Is the source code available?
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.

SQL CLR Library , CLR Routines , CLR Library , SQL Server CLR , Bulk Export , Regular Expressions , HTML Export , Generate Insert Statements 2017-3-27 8-25