SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL# Version 4.1 is released!!

SQLsharp Logo

Version 4.1 of SQL# has just been released!

New functionality available only in Full version

  1. Sys_LockResource
    • Returns the name of the specified Lock Resource given the Type, Subtype, DatabaseID, Description, and AssociatedEntityID.
    • Works similarly to the OBJECT_NAME and OBJECT_SCHEMA_NAME built-in functions in that you do not need to be in the database where the item exists in order to get the correct result
    • Currently not all lock resource types and subtypes can be translated. Hopefully over time additional types and subtypes will be able to be translated.
    • For use with Dynamic Management objects that do not return the lock resource name, such as sys.dm_tran_locks
    • Example:
      CREATE TABLE #Test (Col1 INT);
      SELECT tl.[request_session_id], db.[name] AS [DatabaseName],
                   tl.[resource_subtype], tl.[resource_database_id],
             tl.[resource_associated_entity_id]) AS [LockResource],
           tl.[request_mode], tl.[request_type], tl.[request_status],
           tl.[resource_type], tl.[resource_subtype],
           tl.[resource_description], tl.[request_owner_id]
      FROM      sys.dm_tran_locks tl
      LEFT JOIN sys.databases db
           ON db.[database_id] = tl.[resource_database_id]
      ORDER BY  tl.[request_session_id], db.[name], [LockResource];

      Returns (final three columns for “resource_subtype”, “resource_description”, and “request_owner_id” not shown):

      rqst.  Database  Lock              rqst.   rqst.  request  resource
      sesn.  Name      Resource          mode    type   status   type
      51     TestDB    TestDB            S       LOCK   GRANT    DATABASE
      52     master    sysdbreg          Sch-S   LOCK   GRANT    OBJECT
      52     master    sysobjvalues      Sch-S   LOCK   GRANT    OBJECT
      52     tempdb    #Test_..._00001F  Sch-M   LOCK   GRANT    HOBT
      52     tempdb    #Test_..._00001F  Sch-M   LOCK   GRANT    OBJECT
      52     tempdb    PRIMARY           Sch-S   LOCK   GRANT    METADATA
      52     tempdb    sysallocunits     IX      LOCK   GRANT    OBJECT
      52     tempdb    sysallocunits     X       LOCK   GRANT    KEY
      52     tempdb    sysallocunits.nc  X       LOCK   GRANT    KEY
      52     tempdb    syscolpars        X       LOCK   GRANT    KEY
  2. String_RemoveDiacritics (and “4k” version)
    • Removes accents and other diacritical marks from letters.
    • String is normalized such that letter characters have any accents, tildes, macrons, diaeresis, cedilla, etc removed (whether they are a part of the letter or combining characters added onto the letter)
    • Setting the second parameter, @UseCompatiblityForm, to “1” will break down characters into multiple basic characters if possible. For example: the single character ” ¼ ” broken into the three characters of ” 1 / 4 “
    • Examples:
      SELECT SQL#.String_RemoveDiacritics(N'sdfsd', 0);
      -- sdfsd (no change)
      SELECT SQL#.String_RemoveDiacritics(N'â', 0);
      -- a
      SELECT SQL#.String_RemoveDiacritics(N'â', 1);
      -- a (same change as above)
      DECLARE @Test NVARCHAR(20) = N'~Åa' + NCHAR(0x0344)
                               + NCHAR(0x0344) + NCHAR(0x0344) + N'~';
      SELECT @Test, SQL#.String_RemoveDiacritics(@Test, 0);
      -- ~Åä́̈́̈́~      ~Aa~
      -- (Multiple combining characters removed)
      SELECT SQL#.String_RemoveDiacritics(N'¼', 0);
      -- ¼ (no change)
      SELECT SQL#.String_RemoveDiacritics(N'¼', 1);
      -- 1 / 4 (broken into 3 characters)
  3. Util_GetBase2Bits
    • Returns both the position and integer value of each bit that is set to “1” (i.e. True / Yes / On / Enabled).
    • Please see “Binary / Base2 / BitMask Notes” section at the bottom
    • Example:
      SELECT * FROM SQL#.Util_GetBase2Bits(N'0011');
      BitNum   BitVal
      1        1
      2        2
  4. Util_UnBitMask
    • Returns both the position and integer value of each bit that is included in the masked value.
    • This is not intended to work with negative numbers, but passing in a negative number does not error. However, it might not behave as expected (unless you expect to get back the bits of the Two’s Complement representation of the negative value).
    • Please see “Binary / Base2 / BitMask Notes” section at the bottom
    • Examples:
      SELECT * FROM SQL#.Util_UnBitMask(3);
      BitNum   BitVal
      1        1
      2        2
      SELECT * FROM SQL#.Util_UnBitMask(4);
      BitNum   BitVal
      3        4
      SELECT * FROM SQL#.Util_UnBitMask(18031994990493696);
      BitNum   BitVal
      33       4294967296
      45       17592186044416
      55       18014398509481984

Also Added

“4k” versions of:

  • String_PadBoth4k
  • String_TrimChars4k
  • String_TrimEnd4k
  • String_TrimStart4k
  • RegEx_CaptureGroupCaptures4k
  • RegEx_CaptureGroups4k

New functionality in both Free and Full versions

  1. Convert_Base2ToBase10
    • Converts a Base 2 value (a string of 1 – 64 “0”s and “1”s) into the equivalent integer (i.e. BIGINT) value.
    • Leading / left-most bits / characters not passed-in are assumed to be “0”.
    • Negative numbers use Two’s Complement method, and require that all 64 bits / characters must be passed in, with “1” as the leading / left-most digit
    • Please see “Binary / Base2 / BitMask Notes” section at the bottom
    • Examples:
      SELECT SQL#.Convert_Base2ToBase10(N'11');
      -- 3
      SELECT SQL#.Convert_Base2ToBase10(N'0011');
      -- 3
      SELECT SQL#.Convert_Base2ToBase10(
      -- 549890031616
      SELECT SQL#.Convert_Base2ToBase10(
      -- -18031994990493696
  2. Convert_Base10ToBase2
    • Converts an integer value into the equivalent Base 2 value (a string of “0”s and “1”s).
    • Negative numbers use Two’s Complement method
    • Please see “Binary / Base2 / BitMask Notes” section at the bottom
    • Examples:
      SELECT SQL#.Convert_Base10ToBase2(3);
      -- 0000000000000000000000000000000000000000000000000000000000000011
      SELECT SQL#.Convert_Base10ToBase2(549890031616);
      -- 0000000000000000000000001000000000001000000000000000000000000000
      -- Bit #s: 4, 61, 63
      SELECT SQL#.Convert_Base10ToBase2(5764607523034234888);
      -- 0101000000000000000000000000000000000000000000000000000000001000
      SELECT SQL#.Convert_Base10ToBase2(-18031994990493696);
      -- 1111111110111111111011111111111100000000000000000000000000000000

Also Added

“4k” versions of:

  • String_Trim4k
  • RegEx_Escape4k
  • RegEx_Index4k
  • RegEx_Match4k
  • RegEx_Matches4k
  • RegEx_Split4k
  • RegEx_Unescape4k


    • Greatly reduced size (by approx. 310 kb) of main SQL# Assembly by moving LookUp category into its own Assembly: SQL#.LookUps. This will improve initial load times and won’t waste much memory when not using the LookUp functions.
  • Installation Script
    • Account for security changes related to SQL Server 2017 (i.e. “CLR strict security“) using a Certificate (flexible, clean) instead of the new “Trusted Assemblies” (inflexible, messy).
  • Networking
    • Added explicit support for TLS 1.1 and TLS 1.2 protocols
    • Increased default “Connection Limit” for URIs to 20 from the .NET default of 2. This will reduce performance bottlenecks from concurrent access to the same URI.
  • Twitter
    • All functions now have a concurrent connection limit of 25 instead of the .NET default of 2
    • Support sending of all UTF-8 characters
  • INET_DownloadFile
    • Set “User-Agent” HTTP header (required by some sites)
    • Improved error message when SQL#.Network Assembly wasn’t at correct security level.
  • INET_GetWebPages
    • Added support for “Keep-alive” HTTP header
    • Added support for “ConnectionLimit” pseudo-HTTP header (not case-sensitive) to set the URI’s Connection Limit. For example:
      SELECT *
      FROM   SQL#.INET_GetWebPages(
                 1, 1, -1, -1, -1,
                 N'hello there',
  • String_Contains

For the full list of changes, please see the Change Log

Binary / Base2 / BitMask Notes

The following notes apply to the 4 new functions: Util_GetBase2Bits (Full version only), Util_UnBitMask (Full version only), Convert_Base2ToBase10, and Convert_Base10ToBase2.

  1. All values are 64-bit
    • Base10 type is BIGINT
    • Base2 type is a string of 1 – 64 “0”s and “1”s
  2. BitNum(ber)
    • Identifies a particular bit
    • A value between 1 and 64
    • Bit “number” 1 is on the far right (i.e. “…001”), while “number” 64 is on the far left (i.e. “100…”)
  3. BitVal(ue)
    • The bit “value”, as an integer, for the corresponding bit “number” (i.e. 2(BitNum-1) )
    • Bit “number” 1 (far right) = 1, while “number” 63 (2nd from far left) = 4611686018427387904
    • Bit “number” 64 (far left) is detected properly, but due to both the max value allowed in BIGINT and the left-most position indicating a negative value in Two’s Complement, its value is: -9223372036854775808
  4. Negative values are represented using the Two’s Complement method

To look at it another way:

^                                                              ^
|                                                              |
\_ Bit Number 64                                 Bit Number 1 _/

Bit Number 1's value = 1
Bit Number 2's value = 2
Bit Number 3's value = 4
Bit Number 4's value = 8

Base2 value 00000...00101 = Bit Number's 1 and 3 are "on".

Bit 1 value of "1" + Bit 3 value of "4" = 5.

Base10 value of 00000...00101 = 5.

Base10 value of 00000...00111 = 7 (added Bit 2 value of "2").

Base10 value of 00000...01000 = 8 (only Bit Number 4 is "on").

SQL Quantum Leap

Solomon Rutzky has been working with computers since the mid 1980s. He has experience with a variety of hardware platforms, OSs, programming languages, and RDBMSs. He has focused on SQL Server since 2002, and has written articles for SQL Server Central, including the Stairway to SQLCLR series, and Simple Talk. Solomon is the founder / owner of Sql Quantum Lift, and is the creator of the popular SQL# SQLCLR library. He answers questions on a few StackExchange sites and Ask.SqlServerCentral.com.


Leave a comment on the original post [sqlquantumleap.com, opens in a new window]

Loading comments...