If you’ve ever tried to parse a wide character column in T-SQL, you know two things:

1. It’s a pain to do, and
2. It’s a pain to do.

A lot of the data I deal with comes in syslog format, which can come in one of two formats: positional (the location of the data element is related to the type of data), and named attributes (which usually only include delimiters for complex strings).  Although I haven’t had much luck automating positional parsing, I’ve recently begun using Excel to help me with the named attributes. 

Here’s an example; I have a table with a message column that is pulling over syslog data from a firewall.  In a given day, I may have millions of rows like the following:

sn=AA17D5028EAA time="2011-01-26 13:40:14 UTC" fw=10.1.100.1 pri=1 c=512 m=522 msg="Malformed or unhandled IP packet dropped" n=1 src=10.1.1.23:32795:X1: dst=10.1.1.1:514:: proto=udp/17

Note that each attribute of this particular syslog message is identified with an attribute name (eg, sn, time, fw, etc).  In order to break out each of the elements in T-SQL, we can split the string using a combination of SUBSTRING and CHARINDEX, like so:

SELECT TOP 1
        m = CONVERT(INT, SUBSTRING(MESSAGE, CHARINDEX(' m=', MESSAGE) + 3,
                                   CHARINDEX(' ', MESSAGE, CHARINDEX(' m=', MESSAGE) + 3) - ( CHARINDEX(' m=', MESSAGE)
                                                                                              + 3 )))
      , time = CONVERT(DATETIME, SUBSTRING(MESSAGE, CHARINDEX(' time="', MESSAGE) + 7,
                                           CHARINDEX('UTC"', MESSAGE, CHARINDEX(' time="', MESSAGE) + 7)
                                           - ( CHARINDEX(' time="', MESSAGE) + 7 )))
      , fw = CONVERT(VARCHAR(20), SUBSTRING(MESSAGE, CHARINDEX(' fw=', MESSAGE) + 4,
                                            CHARINDEX(' ', MESSAGE, CHARINDEX(' fw=', MESSAGE) + 4) - ( CHARINDEX(' fw=',
                                                                                                        MESSAGE) + 4 )))
FROM    syslogng (NOLOCK)

Note the repetition for each column; you need to find the position of a starting delimiter, the position of an ending delimiter, and supply to the SUBSTRING function the position of the starting delimiter, and the difference between the two.  You also need to determine the lingth of the starting identifier, and then I CONVERT to a specific data type.  Whee!

It gets even more fun when the attributes are optional; some syslog messages may have a proto code, and some may not.   When faced with this, you need to include a CASE option, like so:

SELECT TOP 1
        proto = CONVERT(VARCHAR(20), CASE WHEN CHARINDEX(' proto=', MESSAGE) = 0 THEN NULL
                                          ELSE SUBSTRING(MESSAGE, CHARINDEX(' proto=', MESSAGE) + 7,
                                                         CHARINDEX(' ', MESSAGE, CHARINDEX(' proto=', MESSAGE) + 7)
                                                         - ( CHARINDEX(' proto=', MESSAGE) + 7 ))
                                     END)
FROM    syslogng (NOLOCK)


One of our developers is working on a syslog parser in .NET code, but I needed a proof-of-concept, and I didn’t want to keep cutting and pasting to see if it was working.  Looking at the parsing, it’s very formulaic SQL.  When I think formulas, I think Excel, and so I whipped out the following:

Note that I have several input columns:

• start, the starting delimiter
• end, the ending delimiter (usually a space)
• colname, the column name I want to use; usually the same as start, but stripped of extra characters.
• type, the SQL type I want to convert the data to, and
• optional, a column to decide if the attribute is optional per row or not.

I also have a hidden column (column F), which generates most of the SQL code:

=CONCATENATE("SUBSTRING(message, CHARINDEX(‘", A2, "’, message)+ ", LEN(A2), ", CHARINDEX(‘", B2, "’, message, CHARINDEX(‘", A2, "’, message)+", LEN(A2), ") – (CHARINDEX(‘", A2, "’, message)+", LEN(A2), "))")

This takes the starting and ending delimiters, the length of the starting delimiter, and plugs those values into a valid SQL statement.  I then create a SQL column, using the following formula:

=CONCATENATE(", ", C2,"CONVERT(", D2, ", ",  IF(E2="Y", CONCATENATE("CASE WHEN CHARINDEX(‘", A2, "’, message) = 0 THEN NULL ELSE ",F2, " END"), F2), ")")

If I were better at Excel, I’d use named ranges, but for my purposes, this is OK.   I append a column to the beginning, specify the type, and include a CASE statement based on whether or not my optional column includes a “Y”.

It took me longer to write this blog post than it did to generate a proof-of-concept, parsing each of the named attributes out from a syslog message.

So, it’s been a while since I’ve posted, but I’ve finally managed to eke out some time after Summit 2010, and wanted to follow up on a conversation that Tim Mitchell, Arnie Rowland, and I had at the Friends of Red Gate dinner.  We were doing a SQL Server oriented trivia contest which asked the following question:

How many nonclustered indexes does SQL Server 2008 support on a single table?

And the answer is: 999, which is enough to cause most DBA’s to grumble under their breath abut the consequences of setting up that many indexes and what they would do to if they ever found that many indexes on a table.  Of course, being the amateur math geek that I am, I casually asked Tim and Arnie:

What’s the smallest table (in terms of amount of columns) that would support 999 indexes?

After knocking it around for a bit, we came up a estimate of 6, which actually isn’t too far off; however, our method of getting there was mostly intuitive, and I wanted to figure out the actual formula for calculating that number.  I knew it had to with factorials, but I wasn’t exactly sure how to get there.  After searching the internet, I finally figured out the following principles:

• Column order matters when building indexes, so when choosing pairs from a set of columns, a set of ab <> ba.
• The more columns on the table, the wider the indexes could be; choosing columns from a wider set would require iteration.  In other words, if you have 3 columns on a table, you would have 3 single-column indexes, 6 double-column indexes, and 6 triple-column indexes.

The formula that represents this is SUM(n!/(n-k)!), where n represents the number of columns in the table and k represents the number of columns in the index.  Plugging this into an spreadsheet, you get the following matrix:

At first glance, we’re done; it looks like 6 was the right answer, because with only 6 columns in a table, you have a whopping 1,956 possible indexes to choose from.  However, there’s more to the story: SQL Server 2005 introduced the INCLUDE option to indexes, which throws a kink in the above formula. 

At first, I thought it was relatively simple; you had two subsets for each n, where the elements in each subset couldn’t be in the other one, but it’s a little more deceptive.  Here’s the principles for generating it:

• For a set (n) of possible columns, there are two mutually exclusive subsets: the base (k) and the included columns (l).  The number of elements in the two subsets must be less than or equal to the number of elements in the master set.
• Column order matters in the base columns, but not the included columns, so the formula above can work for a base set of columns, but iterating through the included columns requires only the unique set of elements.

And here’s the part where my brain exploded; I couldn’t figure out a way to mathematically demonstrate the two relationships, so I built a SQL script, iterating through a set of 5 columns; all in all I ended up with a listing of 845 possible combinations, which means that 6 still stands as the minimum number of columns on a table needed to generate the maximum number of nonclustered indexes.

The point to this story?  None, really.  Just a silly geek exercise.  However, I think it does point out that index strategy is a complex problem, and there are multiple ways to index any given table.  Choosing the right one is more difficult than it looks.


DECLARE @c TABLE ( NAME VARCHAR(100) ) ; INSERT  INTO @c
        ( NAME )
VALUES  ( 'a' ),
        ( 'b' ),
        ( 'c' ),
        ( 'd' ),
        ( 'e' )
       SELECT  n = 1
      , k = 1
      , l = 0
      , NAME
      , INCLUDE = NULL
INTO    #tmp
FROM    @c
UNION ALL
SELECT  n = 2
      , k = 2
      , l = 0
      , NAME = c1.NAME + ',' + c2.NAME
      , INCLUDE = NULL
FROM    @c c1
        CROSS JOIN @c c2
WHERE   c1.name <> c2.name
UNION ALL
SELECT  n = 2
      , k = 1
      , l = 1
      , NAME = c1.NAME
      , INCLUDE = c2.NAME
FROM    @c c1
        CROSS JOIN @c c2
WHERE   c1.name <> c2.name
UNION ALL
SELECT  n = 3
      , k = 3
      , l = 0
      , NAME = c1.NAME + ',' + c2.NAME + ',' + c3.NAME
      , INCLUDE = NULL
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
WHERE   c1.name <> c2.name
        AND c2.name <> c3.name
        AND c1.name <> c3.name
UNION ALL
SELECT  n = 3
      , k = 2
      , l = 1
      , NAME = c1.NAME + ',' + c2.name
      , INCLUDE = c3.name
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
WHERE   c1.name <> c2.name
        AND c2.name <> c3.name
        AND c1.name <> c3.name
UNION ALL
SELECT  n = 3
      , k = 1
      , l = 2
      , NAME = c1.NAME
      , INCLUDE = c2.NAME + ',' + c3.name
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c2.name < c3.name
UNION ALL
SELECT  n = 4
      , k = 4
      , l = 0
      , NAME = c1.NAME + ',' + c2.NAME + ',' + c3.NAME + ',' + c4.name
      , INCLUDE = NULL
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
WHERE   c1.name <> c2.name
        AND c2.name <> c3.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name <> c4.NAME
        AND c3.name <> c4.name
UNION ALL
SELECT  n = 4
      , k = 3
      , l = 1
      , NAME = c1.NAME + ',' + c2.NAME + ',' + c3.NAME
      , INCLUDE = c4.name
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name <> c3.name
        AND c2.name <> c4.NAME
        AND c3.name <> c4.name
UNION ALL
SELECT  n = 4
      , k = 2
      , l = 2
      , NAME = c1.NAME + ',' + c2.NAME
      , INCLUDE = c3.name + ',' + c4.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name <> c3.name
        AND c2.name <> c4.NAME
        AND c3.name < c4.name
UNION ALL
SELECT  n = 4
      , k = 1
      , l = 3
      , NAME = c1.NAME
      , INCLUDE = c2.name + ',' + c3.NAME + ',' + c4.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name < c3.name
        AND c2.name < c4.NAME
        AND c3.name < c4.name
UNION ALL
SELECT  n = 5
      , k = 5
      , l = 0
      , NAME = c1.NAME + ',' + c2.name + ',' + c3.NAME + ',' + c4.NAME + ',' + c5.NAME
      , INCLUDE = NULL
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
        CROSS JOIN @c c5
WHERE   c1.name <> c2.name
        AND c2.name <> c3.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name <> c4.NAME
        AND c3.name <> c4.name
        AND c1.name <> c5.NAME
        AND c2.name <> c5.NAME
        AND c3.name <> c5.name
        AND c4.name <> c5.name
UNION ALL
SELECT  n = 5
      , k = 4
      , l = 1
      , NAME = c1.NAME + ',' + c4.name + ',' + c3.NAME + ',' + c2.NAME
      , INCLUDE = c5.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
        CROSS JOIN @c c5
WHERE   c1.name <> c2.name
        AND c2.name <> c3.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c2.name <> c4.NAME
        AND c3.name <> c4.name
        AND c1.name <> c5.NAME
        AND c2.name <> c5.NAME
        AND c3.name <> c5.name
        AND c4.name <> c5.name
UNION ALL
SELECT  n = 5
      , k = 3
      , l = 2
      , NAME = c1.NAME + ',' + c2.name + ',' + c3.NAME
      , INCLUDE = c4.NAME + ',' + c5.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
        CROSS JOIN @c c5
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c1.name <> c5.NAME
        AND c2.name <> c3.name
        AND c2.name <> c4.NAME
        AND c2.name <> c5.NAME
        AND c3.name <> c4.name
        AND c3.name <> c5.name
        AND c4.name < c5.name
UNION ALL
SELECT  n = 5
      , k = 2
      , l = 3
      , NAME = c1.NAME + ',' + c2.name
      , INCLUDE = c3.NAME + ',' + c4.NAME + ',' + c5.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
        CROSS JOIN @c c5
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c1.name <> c5.NAME
        AND c2.name <> c3.name
        AND c2.name <> c4.NAME
        AND c2.name <> c5.NAME
        AND c3.name < c4.name
        AND c3.name < c5.name
        AND c4.name < c5.name
UNION ALL
SELECT  n = 5
      , k = 1
      , l = 4
      , NAME = c1.NAME
      , INCLUDE = c2.name + ',' + c3.NAME + ',' + c4.NAME + ',' + c5.NAME
FROM    @c c1
        CROSS JOIN @c c2
        CROSS JOIN @c c3
        CROSS JOIN @c c4
        CROSS JOIN @c c5
WHERE   c1.name <> c2.name
        AND c1.name <> c3.name
        AND c1.name <> c4.NAME
        AND c1.name <> c5.NAME
        AND c2.name < c3.name
        AND c2.name < c4.NAME
        AND c2.name < c5.NAME
        AND c3.name < c4.name
        AND c3.name < c5.name
        AND c4.name < c5.name SELECT n, COUNT(*)
FROM #tmp
GROUP BY n
ORDER BY n DROP TABLE #tmp