Using SQLCLR and Filestream (Intro)

Good afternoon everyone! Today, I will be explaining in very basic terms what SQLCLR and Filestream are and how they can be used in your project. Because this focuses on C# and VB code, I will not be including any code examples at this stage. However, at some point in the future, I may come back and expand on this subject further as it's quite interesting just very in-depth especially for a junior developer like myself!

What is CLR? 

CLR is an acronym for Common Runtime Language. It can also be referred to as .NET or .Net Framework, or simply by the coding language that is used in the application back end (typically Visual Basic .NET or C#). In my working environment, I mainly use ASP.NET and C# however I have looked at VB.NET code as well seeing as the majority of code examples I have seen are given in both languages.

So what is SQLCLR? 

SQLCLR is the use of CLR execution within the SQL Server environment. The reason why this has been made available for developers to do is that it allows the developer to go beyond the means of using simple SQL and T-SQL commands, seeing that even T-SQL code has its limits. The use of SQLCLR actually increases the boundaries to make it easier for developers to achieve the exact results that they want.

Before you begin coding in CLR, you must ensure the following:

• The SQL Server Instance must be set in order to allow for CLR code to run. 
• You must write your code in a .NET language. 
• You must compile the code to an assembly.
• You must load the assembly into SQL Server. 
• You must create the database object and point it to the assembly using DDL. (INSERT, UPDATE, DELETE). 

Objects that can be created using SQLCLR

You can create many objects using SQLCLR:

• Stored Procedures. 
• Scalar User Defined Functions. 
• Table-Valued User Defined Functions. 
• DML Triggers. 
• DDL Triggers.
• LOGON Triggers. 

However, the following you can only create using SQLCLR and you can't create using T-SQL:

• User-Defined Aggregates.
• User-Defined Types. 

In order to create User-Defined Aggregates, you need to create a brand new CLR Type, which is accomplished by using either a class or a struct. These will be covered in much more detail at a later date as they can be quite complex. 

What permissions does CLR have?

CLR can be placed within one of 3 different security groups called "permission sets":

SAFE

No access to any resources that are not found on the local database instance.

EXTERNAL ACCESS

Access to any resources that are found on absolutely any database instance.

UNSAFE 

Allows access and execute code that's non-CLR which can be found on absolutely any database instance.

What is Filestream?

Filestream is an option that can be assigned to varbinary(MAX) data type columns. It causes files to be saved in separate files rather than files found within the database, which greatly improves the performance of read and write operations. However, you do need to be careful that you don't use the Filestream for inserting very small items of data, as this can actually reduce the performance speeds quite dramatically.

Before you try to use Filestream, you have to make sure that it's enabled in SQL Server and the Configuration Manager. Otherwise, you will not have permissions and you will receive errors such as this one:



Also, note that you will have to run the Filestream Garbage Collection process in order to completely remove deleted Filestream data. This is usually run when the database checkpoint process is executed, however you can force it to run by executing the sp_filestream_force_garbage_collection stored procedure.

Overall 

Overall, looking at the chapter in the MCTS book, I think that this is a topic that can be vastly expanded on. Seeing as I'm a junior developer, providing I know about what they are and how they can be used to benefit your solution, then that's the main thing. When I get a chance in the future, I will go back over this blog post and will expand on it.

What Next...

Next, I will be looking at more advanced features of SQL referred to as extended SQL features, starting with Spatial Data Types (geometry and geography). I will then be moving on to explaining about the Full-Text Search and using the Service Broker. Stay tuned!

As A Final Word...

Thank you for reading today's blog post! If you have any questions/comments/feedback, please leave them in the comments section below and I will get back to you as soon as I can. Alternatively, please like my SQL Genius Facebook Page and leave a message on there.