About 6 months ago, SQL Server magazine (SQLMag.com) came out with a list of free downloads for SQL Server; it included both totally free as well as trial downloads, all of which were lots of neat third party add-ins for SQL Server. I thought it would be cool to review a bunch of them to see which ones might be really great to have in my toolbox, so I'm going to start a little blog series on them. SQL Mag listed them, but really didn't talk much about them, other than a brief comment. I'll go further than SQLMag by looking into them a little more in depth, and actually investigate some of them. I hope that you find this an interesting series.
And, thank goodness as well for my Hyper-V setup at home. I am loading all of this stuff up on a .vhd that I can trash at any given time with the help of the delete key; I advise that you set the freebies up on a dev box so that if you don't like any of them, you don't have to worry about them not uninstalling cleanly. Leaving registry keys, files, folders, and a trail of dead bodies behind is never any good. Ok, let's get going!
The first one that I chose at random is SQLsharp[1] (SQL#), and downloaded at http://www.sqlsharp.com/ is a set of precompiled CLR functions, including scalar, table-valued, and aggregate, for the SQL DBA or Dev looking to expand his or her toolset but either doesn't know c# or vb.net to write them, or finds value in them being already written. I write CLR stuff for sure, but regardless, I'd definitely use some of these - why reinvent the wheel when the author has done a good job writing them for you?
The first thing to do is go look at the well written .pdf manual [2] that they have created. This will give you a list of the objects and how they are used, so you can get a feel of which ones might be of value to you. Some that I fired up were some of the Util table-valued functions such as GenerateDateTimes and GenerateInts, some of the scalar-valued functions including DaysLeftInYear, Date_Age, IsValidIPAddress, and several of the string and aggregate functions. I found them to be accurate and easy to use. The documentation is also clear well written. A nice to have, though, would be a help file rather than a .pdf as a suggestion for the future.
Because there are so many handy functions, I can't review each, but one that I wanted to mention one in particular from the user-defined types group - NVarcharArray. Because we don't an array in TSQL, I found it really cool. Ken Henderson, in one of his early books, The Guru's Guide to Transact-SQL if memory serves me, wrote one in an xproc (extended stored procedure). Ken's was very cool, and the one written by the SQL# is nice as well. It was probably a considerable effort to write, with a bunch of code and logic behind it (if it's anything like Ken's). Great job on this one! Here's a script to see what some of the methods were for this array data type. I'm going to use columns in a table as my data, so if you want to follow along create the following:
CREATE TABLE [dbo].[STAGE_ITM](
[LINETYPE] [varchar](50) NULL,
[LINENUM] [varchar](50) NULL,
[STORENUM] [varchar](50) NULL,
[INVOICENUM] [varchar](50) NULL,
[PRODUCTNUM] [varchar](50) NULL,
[PRODUCTDESC] [varchar](50) NULL,
[QTY] [varchar](50) NULL,
[AMT] [varchar](50) NULL
) ON [PRIMARY]
GO
Now run some of the methods for the NVarcharArray data type. I result set after each line of code commented for you.
--Get Some Data
DECLARE @strVar varchar (100)=''
SELECT @strVar = column_name+',' + @strVar from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'STAGE_ITM'
SELECT @strVar=LEFT(@strVar, LEN(@strVar)-1)
--Use Type_NVarcharArray
DECLARE @strVar2 SQL#.Type_NVarcharArray
SELECT @strVar2=@strVar
SELECT @strVar2.Count
-- 8
SELECT @strVar2 = @strVar2.AddData(2,'NewCol').ToString()
SELECT @strVar2.ToString()
--AMT,NewCol,QTY,PRODUCTDESC,PRODUCTNUM,INVOICENUM,STORENUM,LINENUM,LINETYPE
SELECT @strVar2 = @strVar2.Sort()
SELECT @strVar2.ToString()
--AMT,INVOICENUM,LINENUM,LINETYPE,NewCol,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2.ContainsItem('NewCol')
--1
SELECT @strVar2.GetAt(5)
--NewCol
SELECT @strVar2.ContainsItem('PRODUCTNUM')
--1
SELECT @strVar2 = @strVar2.RemoveAt(1).ToString()
SELECT @strVar2.ToString()
--INVOICENUM,LINENUM,LINETYPE,NewCol,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2= @strVar2.RemoveItem('NewCol').ToString()
select @strVar2.ToString()
--AMT,INVOICENUM,LINENUM,LINETYPE,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
SELECT @strVar2 = @strVar2.RemoveRange(1,2).ToString()
select @strVar2.ToString()
--LINENUM,LINETYPE,PRODUCTDESC,PRODUCTNUM,QTY,STORENUM
select @strVar2.Clear().ToString()
--
I tested these methods - Count, ToString(), AddData(), Sort(), ContainsItem, RemoveAt(), RemoveItem(), RemoveRange(), and Clear() in the above examples. All worked very well, just as you would expect if you were coding in c# rather than in the database. Cool!
Overall, I think SQLSharp is really slick, and I recommend downloading and testing it out. Look into it closely and see if there aren't a few functions that you could use in your shop...I'm betting that there are some. As with several of the other free tools, there is a fee for an expanded version, but I did not investigate what the pay version gave you. Go to their site and read up on them, and let me know if there is one that you get stoked about.
If you're looking to expand the functionality of TSQL, and don't want to write a bunch of functions in TSQL or c#/vb.net, look to this library for some nice additions to your arsenal. To the guys at SQL #, well done.
Thanks for reading,
Lee Everest
References
[1]. SQLSharp. Retrieved on 3/20/2010 from http://www.sqlsharp.com/
[2] Manual. Retrieved on 3/20/2010 from http://www.sqlsharp.com/download/SQLsharp_Manual.pdf
[3] facebook page at http://www.facebook.com/pages/SQL/97562289644
