SQL# Version 4.1 is released!!

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:

      BEGIN TRAN; CREATE TABLE #Test (Col1 INT); SELECT tl.[request_session_id], db.[name] AS [DatabaseName], SQL#.Sys_LockResource(tl.[resource_type], tl.[resource_subtype], tl.[resource_database_id], tl.[resource_description], 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]; ROLLBACK TRAN; 

     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 id 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( N'0001000000000001000000000000000000000000000'); -- 549890031616 SELECT SQL#.Convert_Base2ToBase10( N'1111111110111111111011111111111100000000000000000000000000000000' ); -- -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

Improvements

• GENERAL
  • 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( 'some_URI', 1, 1, -1, -1, -1, N'ConnectionLIMIT=50', 'post', N'hello there', N'auto' ); 

• String_Contains
  • @SearchValue input parameter is now NVARCHAR(MAX), something LIKE cannot do (please see “Overcome LIKE character length limitation” on DBA.StackExchange for details)

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 (2^nd 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:

 0101000000000000000000000000000000000000000000000000000000001000 ^ ^ | | \_ 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").