There are already quite a few Addin's for SQL Server Management Studio online, most are free or donation like the SSMS Tools Pack, Allocation SQL Server Management Studio Add-in, or the SSMS Addins on Codeplex. Today I got an alert from Google Alerts for a new SQLCLR item on the web, which happens to be one of the things I like to read about. When I clicked through the alert I eventually landed on the website for a new mISV startup Devenius Software Development.
This company is the first one that I have seen to actually market Addin's for Management Studio that are for sale. I find this interesting because Microsoft doesn't support Addin's for Management Studio, which is not to say that they don't work, or don't add value. To the contrary, most Addin's for Management Studio have a lot of value adding features, and work just fine. In fact I have been working on making the Extended Events Manager a Addin, and have been working on an Addin for the Blocked Process Monitor as well. It takes a lot of work to make an Addin work in Management Studio, and the documentation available for doing so is pretty limited.
I downloaded the demo version of the SQL.CLR Addin from this company, and I have to say I am disappointed. This Addin's featured benefit is the abilitiy to:
"Generate CLR stored procedures from existing procedures in SQL Server. The stored procedure code can be generated in C# or VB.net, compiled into an assembly and even create a Visual Studio project with the generated code."
I guess at the surface the Addin delivers as promised, it will generate a CLR Stored procedure from existing TSQL procedures in SQL Server, but it isn't doing it in a manner that makes sense. The Addin simply takes the code inside of the TSQL stored procedure, makes it the CommandText for a SqlCommand in .NET and then runs it returning a SqlDataReader back to the SqlContext.Pipe which has absolutely no benefit at all. It is actually slower to run the SQL like this and requires table level permissions whereas the TSQL stored procedure can access the objects through ownership chaining. I used the usp_GetBillOfMaterials stored procedure from AdventureWorks to test how it would convert to SQLCLR and here is how it does it:
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated by a SQL.CLR tm.
// This code was generated at: 5/15/2009 10:10:17 PM
// Changes to this file may cause incorrect behavior and will be lost if
// the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace ClrProcedure
{
public class uspGetBillOfMaterials
{
[Microsoft.SqlServer.Server.SqlProcedure()]
public static void CLR_uspGetBillOfMaterials(int StartProductID, System.DateTime CheckDate)
{
using (SqlConnection conn = new SqlConnection("Context Connection=true"))
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = @"" +
" BEGIN " +
" SET NOCOUNT ON; " +
" -- Use recursive query to generate a multi-level Bill of Material (i.e. all level 1 " +
" -- components of a level 0 assembly, all level 2 components of a level 1 assembly) " +
" -- The CheckDate eliminates any components that are no longer used in the product on this date. " +
" WITH [BOM_cte]([ProductAssemblyID], [ComponentID], [ComponentDesc], [PerAssemblyQty], [StandardCost], [ListPrice], [BOMLevel], [RecursionLevel]) -- CTE name and columns " +
" AS ( " +
" SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], 0 -- Get the initial list of components for the bike assembly " +
" FROM [Production].[BillOfMaterials] b " +
" INNER JOIN [Production].[Product] p " +
" ON b.[ComponentID] = p.[ProductID] " +
" WHERE b.[ProductAssemblyID] = @StartProductID " +
" AND @CheckDate >= b.[StartDate] " +
" AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) " +
" UNION ALL " +
" SELECT b.[ProductAssemblyID], b.[ComponentID], p.[Name], b.[PerAssemblyQty], p.[StandardCost], p.[ListPrice], b.[BOMLevel], [RecursionLevel] + 1 -- Join recursive member to anchor " +
" FROM [BOM_cte] cte " +
" INNER JOIN [Production].[BillOfMaterials] b " +
" ON b.[ProductAssemblyID] = cte.[ComponentID] " +
" INNER JOIN [Production].[Product] p " +
" ON b.[ComponentID] = p.[ProductID] " +
" WHERE @CheckDate >= b.[StartDate] " +
" AND @CheckDate <= ISNULL(b.[EndDate], @CheckDate) " +
" ) " +
" -- Outer select from the CTE " +
" SELECT b.[ProductAssemblyID], b.[ComponentID], b.[ComponentDesc], SUM(b.[PerAssemblyQty]) AS [TotalQuantity] , b.[StandardCost], b.[ListPrice], b.[BOMLevel], b.[RecursionLevel] " +
" FROM [BOM_cte] b " +
" GROUP BY b.[ComponentID], b.[ComponentDesc], b.[ProductAssemblyID], b.[BOMLevel], b.[RecursionLevel], b.[StandardCost], b.[ListPrice] " +
" ORDER BY b.[BOMLevel], b.[ProductAssemblyID], b.[ComponentID] " +
" OPTION (MAXRECURSION 25) " +
" END; ";
SqlParameter[] sqlParams = new SqlParameter[2];
sqlParams[0] = new SqlParameter("@StartProductID",StartProductID);
sqlParams[1] = new SqlParameter("@CheckDate",CheckDate);
cmd.Parameters.AddRange(sqlParams);
conn.Open();
using (SqlDataReader rdr = cmd.ExecuteReader())
{
SqlContext.Pipe.Send(rdr);
}
}
}
}
}
There is no benefit at all in converting stored procedure code like this. It is in fact against Microsoft's own recommendations to do so. I am surprised that this particular Addin was created.
Now given that poor review of the SQL.CLR Addin, my opinion of their other current Addin, and interest in a forthcoming one are very different. Their other Addin currently is called SQL Encryption Assistant and it is featured as:
"simplifies the creation, modification, and deletion of encryption keys and certificates inside SQL Server. Using the object explorer you can add digital signatures to your database stored procedures, functions, triggers, and assemblies. Within this utility you can use your keys to encrypt your sensitive data stored inside SQL Server."
I don't use encryption all that much, but I have often considered writing a tool to help with managing the Keys and Certificates in SQL Server since there is only TSQL support for this in SQL Server. That is, until now, and this Addin has some definite promise to it. Their future Addin is a Service Broker Assistant, and if the Encryption Addin is any sign of things to come, it should be a worthwhile Addin as well.