sql-serverssmsextended-events

When filtering events in SSMS XEvent Profiler what is the difference between Contains and Like filters?


In SQL Server Management Studio (SSMS), when filtering events in XEvent Profiler, what is the difference between the Contains operator and the Like operator? I haven't been able to find any documentation on this.

Here is the Filters dialog for the XEvent Profiler, showing the list of possible comparison operators, including Contains and Like:

The XEvent Profiler Filters dialog, showing the possible comparison operators


Solution

  • I couldn't find this documented.

    I expected that LIKE would behave as the TSQL LIKE keyword and support % as a wildcard. In reality this does not work.

    From looking at the decompiled FilterBinaryExpression class in Microsoft.SqlServer.XEventStorage.dll it looks like it produces a Regex by processing the string as follows (so supports * and ? as wildcards for arbitrary length strings and single characters respectively - analogous to % and _ in the SQL Like syntax)

          StringBuilder stringBuilder = new StringBuilder();
          stringBuilder.Append("^");
          foreach (char ch in wildcard)
          {
            switch (ch)
            {
              case '*':
                stringBuilder.Append(".*");
                break;
              case '?':
                stringBuilder.Append(".");
                break;
              default:
                stringBuilder.Append(Regex.Escape(ch.ToString()));
                break;
            }
          }
          stringBuilder.Append("$");
          this.wildcardRegex = new Regex(stringBuilder.ToString(), RegexOptions.IgnoreCase | RegexOptions.Singleline);
    

    The usage of this regex then checks

    wildcardRegex.IsMatch(str)
    

    Whereas the Contains filter just checks

    str.IndexOf(this.value, StringComparison.CurrentCultureIgnoreCase) != -1
    

    So does a case insensitive check if the string contains the literal substring.