Recently, Red Gate Software released a new version of their product called SQL Storage Compress 5.0.  This is an alternative to using the native Data Compression and Backup Compression features introduced in SQL Server 2008.  In order to utilize Data Compression, you must be running Enterprise Edition or above (and yes, for the nit-pickers, I know this includes Developer and Evaluation, but these aren't real production editions).  For Backup Compression, this was relaxed in SQL Server 2008 R2 to also include Standard Edition, however Data Compression remains a big ticket item.  So certainly on the surface, SQL Storage Compress seems to be a decent solution for people who:

(a) run SQL Server Standard Edition, and can't take advantage of the native data compression features;
(b) have a mix of editions, and want to use the same compression solution everywhere; or,
(c) commonly perform backup/restore operations between different editions.

Setup

Setting it up is quite easy - you run the installer, it installs a service and a configuration tool, and basically you only have to change a few things in your normal routines in order to take advantage of compression from the product.

• If you want to compress data (resulting in smaller MDF/NDF files), you simply have to create or restore databases with .mdfx / .ldfx / .ndfx extensions instead of .mdf / .ldf / .ndf.  The background service will actively compress these data files in real time.
   
  
• If you want to compress backups (resulting in smaller backup files), you simply have to change your native backup statements to save to .hbc or .hbc2 filenames instead of the traditional .BAK extension.  (Alternatively, you can configure the service to also compress .BAK files, however I don't recommend this as it could become confusing in your environment.)  For anyone who has used HyperBac, some of this may sound familiar, and that is because Red Gate acquired HyperBac earlier this year.  In fact much of the branding remains:
  
  
   
  
• SQL Storage Compress also supports encryption options, but I'll leave those for another day.

At first I was surprised that nothing really happened at the end of setup, except that the activation screen was raised.  To get to the configuration tool, go to Start > Programs > Red Gate > HyperBac > HyperBac Configuration Manager.  This is where you can change settings, most commonly which extensions should actively be compressed by the service:

On the other tabs, you can also start and stop the HyperBac service, inspect its status, and configure locations for log and index files.  Red Gate has published a detailed walk-through which helps you learn how to utilize the product once you have it up and running.
 

The Tests

To keep things simple, I wanted to create new databases from scratch, mark them as compressed, and then populate them with data.  I wanted to measure the following aspects:

• speed of initial load operations
  
• speed of read operations
  
• speed of write operations
  
• size of data and log files
  
• size of backup files
  

Of course I wanted to compare these metrics using both native and Red Gate compression, and use a baseline where no compression was used at all.

Rather than repeat all of the boring test setup and sample data, I will point you to this post of mine from last year:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/08/23/sql-server-2008-r2-digging-deeper-into-unicode-compression.aspx

The tests I ran this week were essentially identical to the tests from the above blog post;there were a few exceptions, for example to set up the Red Gate compressed database, I had to restore an existing backup using WITH MOVE to .  I haven't yet tested whether the service works equally on a database where the CREATE DATABASE statement uses the modified extensions; I am not 100% clear on whether the service only listens for RESTORE statements and doesn't pay attention to CREATE DATABASE.  More tests later.

The machine, also, is the same - an 8GB VM with 4 cores.  The hardware isn't an essential detail of the tests, because they're all running on the same box - but keep in mind that these are local virtual disks, so the I/O certainly doesn't represent what you might be dealing with on a real server with a decent disk subsystem.  As always, I recommend you do your own performance tests in your own environment!
 

The Results

 Duration, in seconds, of identical workload replay

Next I compared the data and log file sizes.  SQL Storage Compress certainly squeezed the same data into the smallest data file, by almost 50% compared to native data compression.  However, this came at quite a cost in log space: the same workload produced almost 300 MB of log compared to about 40 MB of log in both the native compressed and non-compressed databases:

 Size, in MB, of compressed and non-compressed data and log files

Finally, I ran some backups to test eventual backup size.  I ran four different tests: simple backup with no compression, a simple backup with native compression, and the two HyperBac compression modes (normal and fast).  The timings for these backups were irrelevant, because they all completed within one or two seconds (just not enough data to perform a meaningful comparison).  But the resulting sizes had a few surprises:

 Backup size, in MB, of compressed and non-compressed data

Note that even though the original size of the SQL Storage Compressed database was the smallest, taking a normal, non-compressed backup actually yields a much larger file - relatively equivalent to a non-compressed backup of non-compressed data.  As of SQL Server 2008 R2, backup compression is now included in Standard Edition and above, so whether or not you have SQL Storage Compress installed, most of you will be able to compress your backups in one way or another - so this is unlikely to hit anyone in practice.  Without thinking too deeply about what is in this backup file, the result is peculiar to me.  Other than that, the chart speaks for itself - for compressed or non-compressed data, native backup compression and HyperBac compression provide almost identical compression ratios.  Native compression has the edge if you are in a situation where you need to restore databases to servers without the SQL Storage Compress service running.  I hope to do more tests in the future comparing speed and resource usage as well as size on much larger databases.
 

Caveats

As mentioned before, this was a pretty basic test on low-end hardware.  This is in a virtual machine with a single C:\, so the lack of I/O distribution probably impacted both solutions negatively when compared to a real-world scenario.  Could the SQL Storage Compress performance been optimized?  Possibly, especially if I had consulted Red Gate.  In general, though, the test was apples-to-apples... and it was quite true to what a typical user might experience when trying out the product for the first time.  I'll have to put some thought into making some higher-octane hardware available for further and more realistic performance tests.
 

Questions

As I was running the tests, some questions occurred to me.  Three I have answers for; one I do not.

1. What if I only want to compress some of my data?
   
   In the native Data Compression solution, you can pick and choose which objects you want to use compression, and you can turn these on and off more or less ad hoc as well.  You can do this with SQL Storage Compress as well, but you need to be much better prepared up front: since the Red Gate solution works on a file basis, you would have to place objects on specific physical files within different filegroups in order to compress some objects but not others.  This is another thing I plan to play with in future tests.
    
   
2. What if my trial expires?
   
   Here they explain how to recover your database if you have compressed it and the trial has expired:
   
           http://www.red-gate.com/SQLStorageCompress/ExpiredTrial
   
   
3. What if the service stops?
   
   If the background service stops, your database will become inaccessible.  Personally I think there should be a much bigger warning on the Stop button in the Configuration Manager.  If you stop the service and re-start it, you will get this error message if you try to use the database:
   

   Msg 945, Level 14, State 2, Line 1 Database 'RGCompression' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.

   Management Studio still shows the database in the tree, but it is offline (not explicitly, but for all intents and purposes):
   
          
           A compressed database in SSMS if the SQL Storage Compress service has stopped
   
   And as the error message suggests, there are plenty of errors in the SQL Server error log, all of which - at least on first glance - point to "database gone, no paddle" (click to embiggen):
   
          
           Error log after SQL Storage Compress service stops (click to embiggen)
   
   This aspect looks pretty brittle to me so far - I'll have a follow-up post that explains how to recover your databases should this happen to you.
   


4. Does the HyperBac service need to be a dependency in a cluster?
   
   I would expect so, and if it isn't a requirement, it is probably still a good idea, especially given the above.  But I did not have a cluster available to run these initial tests.  If you have any information about this, please let me know.
   
   

I've either realized or further cemented a few ideas in the process of conducting these tests.  In no particular order:

• Whether or not you use native or 3rd party compression for your data files, you should always use backup compression if possible.  You get tremendous space savings, at a very low cost of additional overhead - and in all the tests I've performed with various products, almost always faster.  Again it is not really important whether you use the native backup compression solution or a 3rd party product, as long as you can restore the database on all the required instances.
   
  
• SQL Trace can be a real pain sometimes.  I set up several traces so that I could include more pretty graphs, but they all ended up with 2 useless rows.  I obviously boneheaded something, but was too tired to start over.  I should probably have SQL Sentry installed in this VM.  Again, something to work out for more elaborate tests in the future.
   
  
• SQL Storage Compress 5.0 can provide some advantages over native compression methods.  So, it could be a viable alternative for those running Standard Edition and lower, and even for those with the ability to use native compression, depending on priorities.  I'll mention again that you shouldn't take my graphs above as gospel; if you want to evaluate how the solution works for you, I strongly recommend you download the 14-day trial edition and conduct your own tests.  For backup compression specifically, you should also consider the following products:
  
  
• Red Gate SQL Backup (you can compare SQL Backup Pro and SQL HyperBac here and here)
• Quest LiteSpeed
• Idera SQL safe / SQL safe lite
• Sonasoft SonaSQL
  
  
• The two issues with this product that stand out to me are:
   
  
• Why are the log files so big?
• How do we recover a database if the service has stopped?

EDIT: I address these questions to some degree in a follow-up post.

In a lot of my projects, the issue of naming/coding conventions comes up.  I am a big fan of PascalCase/plural for tables and views (e.g. dbo.CustomerAddresses, dbo.OrderDetails), PascalCase for column names (e.g. FirstName, HomePhone); and for stored procedures, I am partial to the Entity_Action naming convention (e.g. dbo.Customer_Create, dbo.Customer_Update).  I know some like to separate out their words with underscores, but with the exception of stored procedures, I find this tedious. 

One of the stickier points in several cases has been how to case short suffixes, such as the ID in CustomerID.  Let's go back to what the ID is; an identifier.  I often liken it to an identification card, which usually contains either a visible or a bar-coded identifier of some sort (SSN, driver's license #, etc).  While sometimes this card is called an I.D. and sometimes an ID, I have yet to see it called an Id or an id.

For short suffixes that are actually acronyms, I prefer all upper-case.  Most developers I've come across seem to like to lower-case the remainder of the acronym.  And in some cases they have to; e.g. in JavaScript you refer to element.innerHtml; if you try element.innerHTML you get an error message.  I can still tell what Html and Xml are when they aren't in upper-case, but it can be cumbersome to read that way, particularly with the Id / ID suffix.  Because often "CustomerId" can look like "Customerld" - the difference is only slightly more clear in a fixed width, code-ish font:

CustomerId vs. Customerld

Among other things, using an upper-case ID ensures that everyone knows we aren't talking about the customer's "pleasure principle" (according to Freud).  And isn't this easier to read? 

CustomerID

The "I dee" part rings out much better for me when the acronym is upper case.  And when I find queries written by others where they have used their convention, I always re-write to conform to my convention.  And while it is as weak an argument as writing standards-compliant code for fear that you will ever port to a different RDBMS, I find it hard to justify leaving code as is when it will fail if we ever have to use a case sensitive collation.

Even when I will always name the database entities in this way, if you want to call it CustomerId in the application code, feel free.  Just be consistent.  And don't expect me to leave it that way if you hand control of the code over to me.

What are your thoughts?  Are you a proponent of CustomerId?  In the application code only, or in both the app and the database?  If so, why?

The SQL Server Release Services team recently announced that they have released a new Cumulative Update for SQL Server 2008 SP1:

http://blogs.msdn.com/b/sqlreleaseservices/archive/2010/07/19/cumulative-update-9-for-sql-server-2008-service-pack-1.aspx

KB #2083921 : Cumulative update package 9 for SQL Server 2008 Service Pack 1

Note that if you have installed the SQL Server 2008 SP2 CTP (which I talked about previously), you should not attempt to install this update.  It is likely that shortly after SP2 is officially released, there will be a follow-up Cumulative Update that will include all of the fixes from CU #9.

Last August, I wrote a lengthy post about how I handle splitting up a list of integers that are passed into a procedure as a comma-separated list. 

I am still using this method today.  But since almost a year has passed, and because there have been plenty of articles written about splitting in the meantime, I've decided to try out a couple of other methods -- to make sure I was still doing "the right thing."  First I wanted to test out Adam Machanic's CLR approach.  I also took a great interest to the XML splitting methods publicized via a T-SQL tennis match between MVPs Brad Schulz and Adam Haines (part I and part II).  And I wanted to test Peter Larsson's derived table approach.  I also thought it only fair to compare a few of the methods I've used in the past before coming across these faster and/or more scalable solutions, if for nothing else, to illustrate how far we've come.

I'm not going to revisit why you may want to process a list of integers instead of handling each integer individually.  I'm going to assume that, like me, you have cases where you simply don't know how many values might be passed in a parameter list... causing you to ditch the approach by Celko where he has n individual parameters so he can enjoy type safety at the cost of code that is long and horribly hard to maintain (see one of his illogical rants on this).

The Functions

RBAR 1

This is the solution I see most often out in the wild.  What does RBAR mean?  "Row by agonizing row."  I'm not sure where I first picked up that term, but because it sounds so much like "FUBAR," I really like it.  While most solutions do use loops of some sort, this is the worst kind... take a chunk off the list, do some stuff with it, insert it into the table, assign a new value to the list, then move on to the next part of the list. Here is the function:

CREATE FUNCTION dbo.SplitInts_RBAR_1 (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS @Items TABLE (    Item INT ) AS BEGIN    DECLARE        @Item VARCHAR(12),        @Pos  INT;    WHILE LEN(@List)>0    BEGIN        SET @Pos = CHARINDEX(@Delimiter, @List);        IF @Pos = 0            SET @Pos = LEN(@List)+1;        SET @Item = LEFT(@List, @Pos-1);        INSERT @Items SELECT CONVERT(INT, LTRIM(RTRIM(@Item)));        SET @List = SUBSTRING(@List, @Pos + LEN(@Delimiter), LEN(@List));               IF LEN(@List) = 0 BREAK;    END    RETURN; END

RBAR 2

This is a very slight variation on RBAR1 (can you spot the differences?), but results in about twice as many reads when just outputting the data (the reads are roughly the same when using the data for more practical purposes, like joining with existing tables).  For those wishing to stick with RBAR for whatever reason (remember what it sounds like), you may want to compare RBAR 1 and RBAR 2 and see if you can ensure that your functions are not suffering from performance problems you might not know about.

CREATE FUNCTION dbo.SplitInts_RBAR_2 (    @List VARCHAR(MAX),    @Delimiter CHAR(1) ) RETURNS @Items TABLE (    Item INT ) AS BEGIN    DECLARE        @Item VARCHAR(12),        @Pos  INT;           SELECT @Pos = 1;         WHILE @Pos > 0    BEGIN        SELECT @Pos = CHARINDEX(@Delimiter, @List);        IF @Pos > 0            SELECT @Item = LEFT(@List, @Pos - 1);        ELSE            SELECT @Item = @List;                   INSERT @Items SELECT CONVERT(INT, LTRIM(RTRIM(@Item)));               SELECT @List = RIGHT(@List, LEN(@List) - @Pos);        IF LEN(@List) = 0 BREAK;    END       RETURN; END

CTE 1

This function uses a recursive CTE, with the first element and the remainder functioning as the anchor query, whittling down each element in turn.  It inserts into the @table variable until you reach the last element.

CREATE FUNCTION dbo.SplitInts_CTE_1 (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS @Items TABLE (    Item INT ) AS BEGIN    WITH ints(item, remainder)    AS    (        SELECT item = SUBSTRING(@List, 1, CHARINDEX(@Delimiter, @List)-1),            remainder = LTRIM(RTRIM(SUBSTRING(@List, CHARINDEX(@Delimiter, @List) + 1, LEN(@List))))        UNION ALL        SELECT item = SUBSTRING(remainder, 1, CHARINDEX(@Delimiter, remainder)-1),            remainder = LTRIM(RTRIM(SUBSTRING(remainder, CHARINDEX(@Delimiter, remainder) + 1, LEN(remainder))))            FROM ints            WHERE CHARINDEX(@Delimiter, remainder) > 0    )    INSERT @Items SELECT item FROM ints    OPTION (MAXRECURSION 0);       RETURN; END

CTE 2

This function is similar to the above recursive query, however it uses the positions in the string and fewer LEN() calls to extract each value.

CREATE FUNCTION dbo.SplitInts_CTE_2 (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS @Items TABLE (Item INT) AS BEGIN    DECLARE @Len INT = LEN(@List) + 1;    WITH a AS    (        SELECT            [start] = 1,            [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len),            [value] = LTRIM(RTRIM(SUBSTRING(@List, 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, 1), 0), @Len)-1)))        UNION ALL        SELECT            [start] = CONVERT(INT, [end]) + 1,            [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len),            [value] = LTRIM(RTRIM(SUBSTRING(@List, [end] + 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + 1), 0), @Len)-[end]-1)))        FROM a        WHERE [end] < @len    )    INSERT @Items SELECT [value]    FROM a    WHERE LEN([value]) > 0    OPTION (MAXRECURSION 0);    RETURN; END

Numbers

This is the solution I am currently using in my production instances of SQL Server 2005 and SQL Server 2008.  It uses a numbers table and, up until this week, was the fastest method I had tested to date.

CREATE FUNCTION dbo.SplitInts_Numbers (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS TABLE AS    RETURN    (        SELECT            Item = CONVERT(INT, LTRIM(RTRIM(                SUBSTRING(@List, Number,                CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number))))        FROM            dbo.Numbers WITH (NOLOCK)        WHERE            Number <= CONVERT(INT, LEN(@List))            AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter    );

Inline 1

This is a similar approach to the numbers table solution (first spotted as written by Peso but with some minor adjustments), without requiring access to a numbers table.  This does populate a local table variable in the body of the function, in order to make use of a less complex CTE to generate a sufficient set of integers.

CREATE FUNCTION dbo.SplitInts_Inline_1 (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS @Items TABLE (    Item INT ) AS BEGIN    WITH v0 AS        (            SELECT n = 0            UNION ALL SELECT 1            UNION ALL SELECT 2            UNION ALL SELECT 3            UNION ALL SELECT 4            UNION ALL SELECT 5            UNION ALL SELECT 6            UNION ALL SELECT 7            UNION ALL SELECT 8            UNION ALL SELECT 9            UNION ALL SELECT 10            UNION ALL SELECT 11            UNION ALL SELECT 12            UNION ALL SELECT 13            UNION ALL SELECT 14            UNION ALL SELECT 15        ),        v1 AS ( SELECT 16 * v0.n AS n FROM v0 ),        v2 AS ( SELECT 256 * v0.n AS n FROM v0 ),        v3 AS ( SELECT 4096 * v0.n AS n FROM v0 ),        v4 AS ( SELECT 65536 * v0.n AS n FROM v0 WHERE n < 2 )    INSERT @Items        SELECT Item = CONVERT(INT, (SUBSTRING(@Delimiter + @List + @Delimiter,        w.n + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n + 1) - w.n - 1)))    FROM    (        SELECT n = v0.n + v1.n + v2.n + v3.n + v4.n            FROM v0, v1, v2, v3, v4    ) AS w    WHERE w.n = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n)    AND w.n < LEN(@Delimiter + @List);       RETURN; END

Inline 2

Similar to the one above, but this time without the local table variable.  You'll see that a lot more numbers had to be hard-coded into the function definition, but this will potentially pay off in terms of reads (stay tuned).

CREATE FUNCTION dbo.SplitInts_Inline_2 (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS TABLE AS    RETURN    (        SELECT Item = CONVERT(INT, (SUBSTRING(            @Delimiter + @List + @Delimiter,            w.n + 1,            CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n + 1) - w.n - 1            )))        FROM        (            SELECT n = v0.n + v1.n + v2.n + v3.n                FROM                  (                    SELECT n = 0                    UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL SELECT 3                    UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL SELECT 6                    UNION ALL SELECT 7 UNION ALL SELECT 8  UNION ALL SELECT 9                    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12                    UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15                ) AS v0,                (                    SELECT n = 0                    UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48                    UNION ALL SELECT 64  UNION ALL SELECT 80  UNION ALL SELECT 96                    UNION ALL SELECT 112 UNION ALL SELECT 128 UNION ALL SELECT 144                    UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192                    UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240                ) AS v1,                (                    SELECT n = 0                    UNION ALL SELECT 256  UNION ALL SELECT 512  UNION ALL SELECT 768                    UNION ALL SELECT 1024 UNION ALL SELECT 1280 UNION ALL SELECT 1536                    UNION ALL SELECT 1792 UNION ALL SELECT 2048 UNION ALL SELECT 2304                    UNION ALL SELECT 2560 UNION ALL SELECT 2816 UNION ALL SELECT 3072                    UNION ALL SELECT 3328 UNION ALL SELECT 3584 UNION ALL SELECT 3840                ) AS v2,                (                    SELECT n = 0                    UNION ALL SELECT 4096   UNION ALL SELECT 8192   UNION ALL SELECT 12288                    UNION ALL SELECT 16384  UNION ALL SELECT 20480  UNION ALL SELECT 24576                    UNION ALL SELECT 28672  UNION ALL SELECT 32768  UNION ALL SELECT 36864                    UNION ALL SELECT 40960  UNION ALL SELECT 45056  UNION ALL SELECT 49152                    UNION ALL SELECT 53248  UNION ALL SELECT 57344  UNION ALL SELECT 61440                    UNION ALL SELECT 65536  UNION ALL SELECT 69632  UNION ALL SELECT 73728                    UNION ALL SELECT 77824  UNION ALL SELECT 81920  UNION ALL SELECT 86016                    UNION ALL SELECT 90112  UNION ALL SELECT 94208  UNION ALL SELECT 98304                    UNION ALL SELECT 102400 UNION ALL SELECT 106496 UNION ALL SELECT 110592                    UNION ALL SELECT 114688 UNION ALL SELECT 118784 UNION ALL SELECT 122880                    UNION ALL SELECT 126976 UNION ALL SELECT 131072 UNION ALL SELECT 135168                    UNION ALL SELECT 139264 UNION ALL SELECT 143360 UNION ALL SELECT 147456                ) v3        ) w        WHERE w.n = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.n)        AND w.n < LEN(@Delimiter + @List)    );

CLR

This was an adaptation of Adam Machanic's CLR approach (as mentioned above), with very minor modifications to return a table of integers instead of strings.  I won't re-list his CLR function but will gladly share my modifications.  You may want to play with Erland's CLR version as well, which is a lot more straightforward and will potentially scale equally well, at least to moderately-sized strings.  Here is the wrapper to the function (I created a StringHelper.dll with several user-defined functions, including SplitInts()):

CREATE FUNCTION dbo.SplitInts_CLR (    @List       NVARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS TABLE (    Item INT ) EXTERNAL NAME [StringHelper].[UserDefinedFunctions].[SplitInts]; GO

XML

Again, this solution was thrown into the mix due to some heavy touting and promise indicated by Brad Schulz.  I've always been wary of XML solutions for splitting / concatenating because of the overhead of constructing and deconstructing the XML around the data.  Soon we'll see if my concerns are justified.

CREATE FUNCTION dbo.SplitInts_XML (    @List       VARCHAR(MAX),    @Delimiter  CHAR(1) ) RETURNS TABLE AS    RETURN    (        SELECT Item = CONVERT(INT, Item)        FROM        (            SELECT Item = x.i.value('(./text())[1]', 'INT')            FROM            (                SELECT [XML] = CONVERT(XML, '<i>'                     + REPLACE(@List, @Delimiter, '</i><i>')                     + '</i>').query('.')            ) AS a            CROSS APPLY            [XML].nodes('i') AS x(i)        ) AS y        WHERE Item IS NOT NULL    );

I'm leaving out error handling for brevity, and I'm also leaving out any validation that the list consists of only integers -- I'm going to assume that you can perform that kind of checking much more efficiently in your application code, and can add it to the T-SQL side if you see fit.  I'm also not worrying at this stage about removing duplicates or about preserving the order that the values were originally defined in the list. 

Finally, I wanted to ensure that each solution could handle 50,000 entries in the list; since your needs may be a lot more modest than this, you may want to pare down your entries to improve performance (for example, you don't need as many entries in "Inline 2", and your Numbers table can contain fewer rows - for some solutions, you'll need a numbers table covering 14 * <max number of elements> since an INT can be up to 12 digits and the comma and perhaps a space).  In this situation, since I knew all of my values would be <= 2 characters and I didn't have any embedded spaces, I didn't really need a Numbers table quite so large, and I didn't really need to define so many permutations in the "Inline 2" function.

First, since a few of the solutions require a numbers or tally table, I prepared the following table:

SET NOCOUNT ON; DECLARE @UpperLimit INT; SET @UpperLimit = 256000; WITH n AS (    SELECT x = 1    UNION ALL    SELECT x = x + 1      FROM n      WHERE x < @UpperLimit ) SELECT [Number] = x   INTO dbo.Numbers   FROM n   WHERE x <= 256000   OPTION (MAXRECURSION 0); GO  CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

Then I started a trace with a filter of TextData LIKE '%dbo.SplitInts%' and with the following columns: TextData, Reads, Writes, CPU, Duration. Once the trace was running, I ran the following code block three times, giving me a good sample of how each solution performs:

DECLARE    @List VARCHAR(MAX) = '1,2,3,4,5,6,7,8,9,10,',    @Delimiter CHAR(1) = ','; DECLARE c CURSOR FOR    SELECT List FROM    (        SELECT r = 1, List = REPLICATE(@List, 10)        UNION ALL SELECT r = 2, List = REPLICATE(@List, 50)        UNION ALL SELECT r = 3, List = REPLICATE(@List, 100)        UNION ALL SELECT r = 4, List = REPLICATE(@List, 500)        UNION ALL SELECT r = 5, List = REPLICATE(@List, 1000)        UNION ALL SELECT r = 6, List = REPLICATE(@List, 2500)        UNION ALL SELECT r = 7, List = REPLICATE(@List, 5000)    ) AS x    ORDER BY r; OPEN c; FETCH NEXT FROM c INTO @List; WHILE @@FETCH_STATUS = 0 BEGIN    SELECT Item FROM dbo.SplitInts_CLR (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_RBAR_1 (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_RBAR_2 (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_Numbers (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_XML (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_CTE_1 (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_CTE_2 (@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_Numbers_1(@List, @Delimiter);    SELECT Item FROM dbo.SplitInts_Numbers_2(@List, @Delimiter);       FETCH NEXT FROM c INTO @List; END DEALLOCATE c;

I ran the test 10 times, then pulled averages from the trace tables for each function and element count.

The Results

In terms of duration, CLR won every time.  Heck, CLR won every time in every category. (This might not come as a big surprise to you.  After all, just yesterday, I let the cat out of the bag by declaring that I'd finally given in and am letting the CLR into my life.)

But if you are one of many who are not able to implement CLR functions in your production environments, you may want to pay attention to some of the other methods.  In the chart below I've highlighted the winners and losers as follows: longest duration at each interval (red), shortest duration at each interval (light green), and the shortest duration other than CLR (even lighter green).  Note that "CTE 1" for 50,000 elements, at over 26,000ms total duration, is way off the chart.

Duration, in milliseconds, for splitting delimited strings of varying numbers of elements

We can see that duration for the CLR method and a few others creep up very slowly, while several of the remaining methods increase at a much more rapid pace.  There is also an inexplicable bump for the XML method at 500 and 1,000 elements, where duration is longer than found with 25,000 elements.  I thought this may be an anomaly or due to other pressure on the box, but this was repeatable time and time again.  We can see that the "CTE 1" method is the absolute worst performer at 5,000 rows and above, and that aside from the CLR method, the numbers table was the winner up to 10,000 elements; beyond that, the "Inline 2" method took over -- but granted, not by much.

As mentioned before, I examined reads, writes, CPU and duration to compare these different splitting techniques.  CPU correlated almost identically to duration, so I'm not going to bother repeating a similar chart as above.  Writes did not seem to factor in; the only method that performed any writes to speak of was the "CTE 1" function, and based on duration alone we are discarding that as an alternative in any case.  Here are the reads (again I've highlighted the winners in green and the losers in red):

Logical reads, in 8K pages, for splitting delimited strings of varying numbers of elements

I didn't graph the reads because, due to the range of values involved, I'd have to do it logarithmically to even present it on your screen, and that would take away some of the "punch" you get with these numbers.  :-)

What I've Learned

If you are going to be mainly dealing with < 100 items in your lists, then the difference between the various methods was so minuscule, it was not even worth mentioning the actual figures.  Even at 1000 items, the only real loser was the XML method (even though the reads on the CTE methods might make you think otherwise).  Once you get over 10,000 items in the list, you start to see a greater separation between the men and the boys; there is clearly a set of options that yield unacceptable performance, and a set of options that aren't all that different.  To a large degree, the duration figures correlate almost directly to the reads, but even though several methods provide the same 0-level of reads, the CLR always wins in duration.  You may nit-pick and find little things that will make various methods a little bit faster, but I don't think there is anything ground-breaking that will change any function's performance by an order of magnitude.

The major lessons to learn here are as follows:

• You should test all available solutions both with varying numbers of parameters.  In this case if I had tested only against a smaller list, I might have ended up with a solution that would have been a real performance problem should I need to deal with thousands of elements -- and without proper testing scenarios in place, this would most likely happen at an inconvenient time.  
• While this stands true for any type of performance test, I couldn't leave it out: If you are running tests on a local machine or a test server, you should test on the destination hardware as well, since there are many factors that could change performance in that environment.  Without belaboring the point too much, I had slightly different results on a local machine with less RAM and fewer processors that might have led me in a different direction if I had been less thorough.
  
• The usage of LEN() or DATALENGTH(), combined with using a @table variable inside the function to hold intermediate results, can increase the reads and overall duration in a major way; it is clear that certain functions above are not optimized for this type of implementation. 
  

Conclusion

Based on these tests, I will be proceeding with introducing CLR functions to my primary systems to help with certain string functionality.  For now, I will be limiting this to splitting, concatenation, parsing, and RegEx matching.  For splitting specifically, as time goes on, we are introducing more and more bits of functionality that require accepting application data in a list (permissions, network associations, etc.), and I am looking only slightly ahead to a day where we will need to process thousands of items in some of these lists.  While the overall duration and read savings compared to a numbers table was quite modest, centralizing this functionality for further extensibility and without the limitations of T-SQL UDFs seems to be a worthwhile investment to me.

Soon I hope to perform some tests revolving around file system access, since we have several C# command-line apps, as well as inline T-SQL using xp_cmdshell, all for the purpose of processing files.  These files are almost always processed in the same type of way; the biggest difference is either the location or the purpose of the file.

On Monday, Microsoft released two cumulative updates for SQL Server:

KB #983329 : CU #10 for SQL Server 2005 SP3 (build # = 9.00.4305)

KB #2072493 : CU #2 for SQL Server 2008 R2 (build # = 10.50.1720)

Missing is an update for SQL Server 2008 SP1.  It seems the SQL Server Release Services team has changed the schedule up a bit, as previously they were releasing 2005 SP3 updates on one cycle, and 2008 SP1 / 2008 R2 updates on another.  It would be great if they could get all three branches onto the same schedule.

Since I am still afraid of SSIS, and because I am dealing mostly with CSV files and table structures that are relatively simple and require only one of the three letters in the acronym "ETL," I find myself using BULK INSERT a lot.  I have been meaning to switch to using CLR, since I am doing a lot of file system querying using xp_cmdshell, but I haven't had the chance to really explore it yet.  I know, a lot of you are probably thinking, wow, look at all those bad habits.  But for every person thinking that way, I am sure there is someone thinking, yes, I am still using BULK INSERT as well.  So, let me tell you about two little problems I came across recently, and how I worked around them.

The Phantom Carriage Return

In one system, we get log files from a bunch of application servers running Linux.  The other day we built a new log file output mechanism using the same code (we are removing, as much as we can, the applications' dependency on the database).  However, the same code outputting the same type of log file (just containing different data) was generating the following errors when I tried to BULK INSERT any file with more than one row:

Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 1, column <last column number> (<last column name >).

I asked the developer if anything had changed with the constant he was using for carriage returns, and he said no, "\n" was still in place.  No matter how I changed the code, I was still getting the error:

... (WITH ROWTERMINATOR = '\r\n', ... ); ... (WITH ROWTERMINATOR = '\n\r', ... ); ... (WITH ROWTERMINATOR = '\n\n', ... ); ... (WITH ROWTERMINATOR = '\r', ... );

Nothing would work.  I had half a mind to go and look at the Perl code on the Linux box, but before I got to that point, and on a total lark, I tried this version of the code instead:

DECLARE @b NVARCHAR(2048); SET @b = 'BULK INSERT ... WITH (ROWTERMINATOR = ''' + CHAR(10) + ''', ... );'

Lo and behold; if I used the CHAR() code from SQL embedded in the string, it worked!  For some of you, the addition of dynamic SQL will make you wag your finger at me even more.  But for anyone who is polling for new files to load, you are probably already using dynamic SQL to construct these statements anyway.  I haven't really dug into why this worked but "\n" didn't, I just knew it was working and could move onto the next most important task.

Using BULK INSERT Against a Linked Server

When you try to use BULK INSERT against a four-part name, e.g.:

BULK INSERT [OtherServer].[MyDatabase].[dbo].[foo] ... ;

You get the following error:

Msg 208, Level 16, State 82, Line 1 Invalid object name 'foo'.

I complained about the misleading error message in Connect #525287: BULK INSERT to linked server gives misleading error message.  In that Connect item, I mentioned that I had an easy workaround, and had a follow-up from "Pbaggett" asking me about my workaround (though I just noticed the comment today), so I thought it was a perfect opportunity to make this post a little meatier.

In the system I am working in, there is a central table that describes various tables that exist on various systems in the environment.  Most of the tables that are a target for BULK INSERT are local, but a few are remote.  So, this central table contains information about the server if it exists; otherwise it is NULL.  As described above, all BULK INSERT statements are constructed dynamically anyway, since the target tables aren't always inserted to, and the file names and number of files are volatile.  Anyway, the trick here is to execute a SQL statement *on* the remote server when necessary (using a dynamically built, and then nested, sp_executesql call).  I'll illustrate what I mean here with some sample code (for brevity, I've left placeholders for error handling, loops, etc.):

DECLARE     @LinkedServer VARCHAR(32),     @Database  NVARCHAR(128),     @Table  NVARCHAR(255),     @FilePath  VARCHAR(2048),     @sql  NVARCHAR(MAX); -- this would actually be in two loops: one to get all of the target tables, -- and the other to get all of the relevant files for each target table. SELECT     @LinkedServer = '[OtherServer]',     @Database = '[MyDB]',     @Table = '[dbo].[foo]',     @FilePath = '\\FileServer\file.csv'; SET @sql = N'BULK INSERT ' + @Database + '.'  + @Table + ' FROM ''' + @FilePath + ''' ' + 'WITH (FIELDTERMINATOR = ''|'', ROWTERMINATOR = ''\n'');'; SET @sql = N'sp_executesql N''' + REPLACE(@sql, '''', '''''') + ''';'; BEGIN TRY     -- sp_testlinkedserver goes here     SET @sql = N'EXEC ' + COALESCE(@Server + '...', '') + @sql;     PRINT @sql;     -- I've commented out the EXEC here; PRINT should     -- give you the gist of what's going on.     --EXEC master..sp_executesql @sql; END TRY BEGIN CATCH     -- error handling END CATCH

Of course there are some complications here... doubling up the apostrophes and trying to debug the final SQL statement can be a pain; you also need to be sure that all of the remote servers are able to see the same network shares using the context that the linked server uses.  But so far this workaround has allowed me to keep my log polling / loading process central, even while the distribution of data has scaled out to other servers.