Version 4.2.100 and 4.2.101 (November 13^th, 2018)

New Functionality

Improved

• GENERAL:
  • Reduced size of main SQL# assembly.
• Installation Script:
  • Created separate User (Database-level principal) to “own” SQL#.FileSystem and SQL#.DotNetZip Assemblies. This will allow the Assemblies to be set to UNRESTRICTED (level 3) and be isolated in their own App Domain, something that is required if using longpaths (over 259 characters).
• Agg_Join and Agg_Median:
  • Handle more data internally. Previously was limited to 8000 bytes in order to be compatible with SQL Server 2005. Now, SQL Server 2005 version still has that limit, but when installed on SQL Server 2008 or newer, limit will be 2 GB.
• Convert_HtmlToXml:
  • When accessing URLs, automatically handle TLS 1.1 and 1.2 when required.
• DB_BulkExport:
  • Added parameters (@OutputFormats and @OutputFormatsDelimiter) and functionality to control, per-column, the output format, including minimum width (to support fixed-width exports). This is a delimited list of .NET composite format strings. This list can be sparsely populated and does not need to contain definitions for all column; columns not in the list will be given the default / standard format. Default delimiter is "|".
• All File functions:
  • Added error messaging to detect if error is due to long paths, and if so, display appropriate info.
• File_CopyMultiple and File_MoveMultiple:
  • Updated to handle (i.e. create) empty folders. Changed datatype of @Recursive parameter from BIT to TINYINT. This change is backwards compatible: 0 still means “not recursive”, and 1 still means “recursive”. But, now passing in 2 means “recursive, including empty folders”.
• File_CurrentEncoding:
  • Returns proper error message if @FilePath is an empty string.
• File_GetDirectoryListing:
  • Checks for folder structures over 259 characters in length. When found, the appropriate longpath prefix is added to the path: "\\?\" for local paths, and "\\?\UNC\" for UNC paths.
• File_GetLineCount:
  • Returns proper error message if @FilePath is an empty string.
• File_GUnzip:
  • Improved error message when SQL#.DotNetZip Assembly wasn’t at correct security level.
• File_Move:
  • Now supports ability to move file across volumes (i.e. drives / shares).
  • (Behavior Change) No longer supports moving / renaming a directory or directory structure. Please use the new File_MoveDirectory function to do either of those operations.
• File_SplitIntoFields:
  • Added option to set column names based on first row in file: @FirstRowContainsColumnNames input parameter (optional parameter; default = 0 / false for backwards compatibility / to be consistent with prior behavior)
  • Made @RegExDelimiter an optional parameter
• File_WriteFile:
  • Returns proper error message if @FilePath is NULL or empty string.
  • Assumes "false" / 0 if @AppendData is NULL (instead of erroring).
  • Creates empty file if @FileData is either NULL or empty string (instead of erroring).
• File_WriteFileBinary:
  • Creates empty file when passing in NULL for @FileData (instead of erroring).
  • Error message for invalid @FileMode parameter value now mentions the three valid values.
• All INET_FTP* functions:
  • Updated to have better error handling / messaging.
• INET_GetHostName:
  • Returns NULL if NULL passed in.
  • Better error handling.
• INET_GetIPAddress:
  • Better error handling.
• INET_GetIPAddressList:
  • Returns an empty result set if NULL is passed in (only for @HostName)
  • Better error handling.
• INET_GetWebPages:
  • Handle AutomaticDecompression header (valid options: gzip, deflate, or both separated by comma or pipe).
  • Handle Authorization header (requires both "username" and "password" headers).
  • Passing in NULL for MaximumResponseHeadersLength no longer causes an error.
  • Changed datatype of @SplitLines input parameter from BIT to SMALLINT. This change is backwards compatible: values 0 and 1 still cause the same behavior as they previously did (0 = do not split text content; 1 = split text content). New option of -1 allows for skipping content download entirely. The effect of this is getting a very quick response containing the status, headers, content-type, response URI, LastModified date, and sometimes the content-length (this value is not always sent). Downloading an 11 MB file, including content (no split) was taking 1200 - 2100 ms. Using the new -1 option to skip downloading the content was returning in 90 - 160 ms. This option allows for:
    • quickly checking status / existence.
    • get file size.
    • etc.
  • Returns actual "content_length" instead of "-1" for non-error responses, even if the "ContentLength" header isn't in the response.
• INET_Ping:
  • Returns an empty result set if NULL is passed in (only for @HostName).
  • Increased max allowed TTL to 10240 (to match INET_PingTime).
  • Better error handling.
  • SocketError returns single row of "num" = -1, "status" = SocketError, and "address" = {message}
• INET_PingTime:
  • Returns NULL if NULL passed in.
  • Better error handling.
  • Returns -2 for SocketError (typically DNS cannot resolve host).
• INET_SplitIntoFields:
  • Added option to set column names based on first row in file: @FirstRowContainsColumnNames input parameter (optional parameter; default = 0 / false for backwards compatibility / to be consistent with prior behavior)
  • Made @Timeout and @RegExDelimiter optional parameters
  • Better error handling.
• Sys_LockResource:
  • Added handling of: COMPRESSED_ROW, FILE, FULLTEXT_INDEX, METADATA (AUDIT, AUDIT_SPECIFICATION {Server and Database}, DATABASE, DB_MIRRORING_SESSION, INDEXSTATS , SERVER_PRINCIPAL, PASSWORD_POLICY, and STATS)
  • Added various properties of the resource to the return value to help identify it more quickly and provide additional insight to hopefully avoid additional manual queries required to trouble-shoot. For example, objects now include OBJECT_TYPE, indexes include INDEX_TYPE, and so on.
  • Schema-based items are now prefixed with the schema name
• Twitter:
  • All functions automatically handle TLS 1.1 and 1.2 when required.
  • All functions updated to send and receive extended tweets (up to 280 characters instead of 140)
  • Better handling of retweets and quoted tweets. New status result set fields: [StatusType] NVARCHAR(20), [QuotedStatus] XML, [QuotedStatusCreatedAt] DATETIME, [QuotedStatusCreatedAtLocalTime] DATETIME, and [CreatedLocalTime] DATETIME.
    • [StatusType] = 'Original', 'Retweet', or 'Quoted'.
    • [QuotedStatus] = XML of quoted status when [StatusType] is 'Quoted'.
    • [QuotedStatusCreatedAt] = UTC time of quoted/retweeted status.
    • [QuotedStatusCreatedAtLocalTime] = local server time of quoted/retweeted status.
    • [CreatedLocalTime] = local server time of current status.
  • At all times, the [StatusID] and [Created] fields describe the current status record. Additionally, when the status is a retweet: a) [QuotedStatusID] is the ID of the original status being retweeted in the current status record, and b) [QuotedStatusCreatedAt] / [QuotedStatusCreatedAtLocalTime] are the creation times of the original status being retweeted in the current status record, and c) the rest of the fields describe the retweeted status, not the current status. Please see https://developer.twitter.com/en/docs/tweets/datadictionary/overview/intro-to-tweet-json#retweet for additional details.
• Type_HashTable:
  • Added new methods:
    • ValuesLength: returns the number of 2-byte characters across all Values in the HashTable.
    • GetTableXML: returns an XML document containing the contents of the HashTable.
  • Better error handling when passing in empty / white-space-only string for initialization or AddData() method.
  • Added ability to unescape URI encoded values, for initialization and AddData() method, so "&" and "=" characters can be passed in (those two are key-value pair delimiters). This works for both "Key" and "Value".

Fixed

• Agg_Join:
  • Now returns NULL instead of an empty string when all rows in a group are NULL
• DB_BulkCopy:
  • “Object not set to a reference of an object” message would be returned instead of actual error message if an error occured in initial connection or SQL execution. Now source error message is returned.
• DB_BulkExport:
  • (Behavior Change) Fixed default formatting of certain datatypes to match BCP output (meaning: it can be imported by BCP without modification):
    • DATE, SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET now include milliseconds
    • BINARY, VARBINARY, IMAGE, and ROWVERSION / TIMESTAMP now exclude the "0x" prefix.
• File_GUnzip:
  • Updated to use case-insensitive comparison when checking if file extension is ".gz"
• INET_HTMLDecode:
  • Handle all 2125 HTML 5 named character entity references (not just 102 of them).
  • Handle &#{decimal_code_point}; and &#x{hex_code_point}; numeric character references.
  • INET_HTMLEncode will be updated to also handle all 2125 HTML 5 named character entity references in SQL# version 5.0.
• RegEx_CaptureGroup4k:
  • Changed @NotFoundReplacement datatype from NVARCHAR(MAX) to NVARCHAR(4000)
• String_Unescape:
  • Fixed octal escape sequences that are 2 - 3 digits and begin with a 0 (e.g. \0x or \0xx)
• Sys_AllAssemblies:
  • No longer errors on "invalid column is_user_defined" on SQL Server 2005
• Twitter:
  • Some Twitter functions getting "error parsing ..." due to newer, larger IDs. Proper handling of BIGINT / Int64 numbers that are large enough to default to DECIMAL instead of Int64 (i.e. long) when implicitly converted.
• Type_HashTable:
  • Now returns NVARCHAR(MAX) instead of NVARCHR(4000) for the following methods: GetValue, GetValueByKeyPattern, and ToString.
  • ValuesDataLength method now returns the total number of bytes instead of the number of characters.
  • URI encode (i.e. percent-encode) "&" and "=" characters when using the ToString() method. These two characters need to be escaped because they are used as key-value pair delimiters.

Version 4.1.98 and 4.1.99 (February 8^th, 2018)

New Functionality

Improved

• 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 should 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
• 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 to set the URI's Connection Limit
• 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)
• Convert_BinarySidToSddl:
  • Slight performance improvement
• SQLsharp_Download, SQLsharp_IsUpdateAvailable, and SQLsharp_WebSite:
  • Updated URLs to be "https"

Fixed

• Util_Print:
  • Handles various types of newlines ( \r, \n, and \r\n ), including accounting for bug in SSMS – discovered while testing Util_Print – related to \r\n at the end of the line being printed (see: SSMS ignores final \r\n / CRLF / Carriage Return + Line Feed in PRINT and RAISERROR )
  • Properly handles lines that are the exact same length as @MaxLength
  • Added "-" as a default word-wrap character
  • Works with Supplementary Characters! (better than PRINT and RAISERROR !) Doesn’t split the surrogate pair in two when the first half of it is the final "character" of the line.
• Twitter (all functions sending a string value):
  • Support sending of all UTF-8 characters
• File_CopyMultiple, File_GetDirectoryListing, File_DeleteMultiple, and File_MoveMultiple:
  • Fixed error that would occur only if the Database containing SQL# had a Collation that was not case-insenstive, accent-sensitive, and having the same LCID as the OS

Version 4.0.95 and 4.0.96 (March 25^th, 2017)

New Approaches / Concepts

• Group Functionality into Assemblies by Required Permissions / Security Level:
  
  Several Assemblies have code that requires a different security level than other code in that same Assembly. For example, the SQL#.Network Assembly has some code that can run in SAFE (Level 1) mode, while most of it requires EXTERNAL_ACCESS (Level 2). Using the code that requires EXTERNAL_ACCESS forces the code that can run in SAFE to be in an Assembly marked as EXTERNAL_ACCESS.
  
  Ideally, code will never be given a higher security level than it needs. The goal here is to have as much code as possible remain in Assemblies that will only ever be marked as SAFE (Level 1). And then, code requiring EXTERNAL_ACCESS (Level 2) shouldn’t be in an Assembly marked as UNSAFE. In order to accomplish this, code in each Assembly that requires a higher security level needs to be moved into a separate Assembly for just that security level. Assemblies will be named the same as the current category, but with a number at the end indiciating what security level they will need.
  
  This release has one new Assembly, SQL#_2, that is just the functionality from the SQL# Assembly that needs security level 2. In the next release, the rest of the Assemblies will be broken out into 1 or 2 additional Assemblies.
  
  
• Easier, More Customizable Installation
  
  In prior versions it was possible to disable installation of most Assemblies, as well as tailor the highest allowed security level via the @AllowExternalAccess and @AllowUnrestrictedAccess variables. However, it was not possible to skip the creation of the Asymmetric Key and Key-based Login in [master], even though those objects aren’t needed if the Assemblies will always be set to SAFE.
  
  Now, those two variables have been replaced with @MaxAllowedAccessLevel that will be set to a value of 0 to 3, where 1, 2, and 3 correspond to the same security levels used for SQLsharp_SetSecurity, and all three levels will install the [master] Database objects. A value of 0 for @MaxAllowedAccessLevel skip all server-level configuration, including attempting to enable CLR Integration. This new option of 0 is intended for environments in which you have no server-level permissions and only SAFE Assemblies are allowed (such as Amazon RDS). The details of what each level does are fully documented in the installation script itself, towards the top.
  
  The installer also does more validation now, to identify problems before installation begins.
  
  
• More Thorough Installation / Uninstallation
  
  In previous versions of SQL#, the SQLsharp_Uninstall Stored Procedure would uninstall all SQLCLR objects from the local database, as well as the SQL# Schema, but it did not remove the SQL# User from the local Database, nor did it remove the two objects in [master]: the Asymmetric Key and the Key-based Login. Now SQLsharp_Uninstall does remove the SQL# User, and there is a new, pure T-SQL Stored Procedure in [master] that drops the Key-based Login, then the Asymmetric Key, and finally itself. SQLsharp_Uninstall even prints a reminder to the “Messages” tab that you will probably need to execute [master].[dbo].[SQLsharp_InstanceUninstall]. This new Stored Procedure does not run automatically at the end of SQLsharp_Uninstall as there might be other Databases with SQL# installed that need those objects.
  
  Another issue that one might run into is when restoring a Database that has SQL# installed onto an Instance that has never had SQL# on it. In this scenario, code requiring security levels 2 or 3 will not be able to execute since those elevated permissions require that Key-based Login (and that it have the correct permission). Previously, you had to re-execute the install script to get those objects back into [master]. But now, SQLsharp_InstanceSetup – a new, pure T-SQL Stored Procedue in the local Database – will re-create those 3 objects in [master]. And, this new InstanceSetup Stored Procedure is dynamically created during installation so that it can retain the value used for @SQLsharpLogin.
  
  
• Better Local Help
  
  Previously, the SQLsharp_Help stored procedure would display all of the available commands, including their parameter options. This worked well for a relatively well for first 100 to 150 functions, but became much harder to maintain and less useful as SQL# continued to expand (this release – 4.0 – contains 350 objects!). So, starting with this release, the full PDF manual is included in the installer and is imported (as a GZipped binary) into the Database along with the Assemblies. The PDF manual will thus always be available, even if the website isn’t for any reason. You won’t have to search around for it, and it will be included in every Database backup.
  
  The embedded binary data includes additional meta-data along with the gzipped PDF file. The meta-data is properties about the PDF, such as the Document Revision Number, which will make it easy to determine if a newer version has been published, and will allow for a PDF manual update script to prevent overwriting a newer version with an older one. There are two new functions that will get this meta-data: a scalar function to get the Document Revision Number, and a TVF to display all of the meta-data. And, there is a new stored procedure that will save the PDF manual to disk, provided a path and optional filename. If no filename is specified, it defaults to the current PDF manual filename.
  
  Please note that extracting the PDF manual requires EXTERNAL_ACCESS (Security Level 2). This means that it will not be possible to extract if the install was done using a value of either 0 or 1 for @MaxAllowedAccessLevel.

Highlights

• RegEx cache: Similar to how queries are compiled and their execution plans get cached, Regular Expressions – the pattern / expression itself – gets compiled (which takes time) and is then cached to save time on subsequent executions. And, just like the plan cache, there is a limited amount of space to use for cached expressions. The default cache size is 15 expressions. If there are more than 15 patterns / expressions that you regularly use, then the system will be spending more time than it should recompiling the expressions that are getting forced out of the cache. This release includes two new functions for managing the expression cache: one to set the cache to whatever size you want (only available in the Full version) and one to display the current size of the cache (also available in the Free version). Increasing the cache size (at least a little, not too high) so that all (or at least most) of your expressions can fit in should make your system more efficient and your queries using RegEx_* functions complete in less time.
  
  There is currently no means of having a custom cache size automatically set, and so setting the cache size will need to be done each time the App Domain is loaded. If the App Domain is unloaded due to memory pressure, a custom cache size will reset to the default value of 15. But this is something that could be checked periodically in a SQL Server Agent Job that also sets the value to the desired size if it is lower than the desired size. The next release of SQL# will provide a mechanism for setting a custom cache size each time the App Domain is created.
  
  
• URI host connection limit: When accessing network resources, .NET throttles connections per each host. The default limit for any particular host is 2. When this limit is reached, additional requests are blocked and will wait until a process completes and frees up its connection. This means that if you use INET_GetWebPages to hit one or more URIs that are fairly static (the host / machine portion of the URI, that is), then you could be experiencing a lot of latency if you have a call that is made repeatedly across multiple sessions. This release includes three new scalar functions for managing this. There is a function to display the current connection limit for a particular host, one to display the current connection count to a particular host, and one to set the limit for a particular host. It is unclear what a bad upper-limit would be, but it is clear that 2 is a very low. If you have code that calls an in-house web page or web service and is called by multiple sessions at the same time, then you need to set the connection limit to something higher than 2, and then your process should complete faster.
  
  There is currently no means of having a custom connection limit automatically set, and so setting a connection limit will need to be done each time the App Domain is loaded. If the App Domain is unloaded due to memory pressure, a custom connection limit will reset to the default value of 2. But this is something that could be checked periodically in a SQL Server Agent Job that also sets the value to the desired size if it is lower than the desired size. The next release of SQL# will provide a mechanism for setting custom connection limits each time the App Domain is created.
  
  
• Splitting to native types: Quite often (perhaps most often) when needing to split a string, the string in question is a list of IDs (i.e. INT or BIGINT values). In these cases, it is much more efficient to split directly to the numeric type as opposed to splitting to strings to then convert that string into an INT or BIGINT value. This release includes a few new TVFs to allow for splitting into native types: two for splitting into BIGINT (one is only available in the Full version) and one for splitting directly into UNIQUEIDENTFIER / GUIDs (only available in the Full version). The two TVFs that are only available in the Full version allow for determining how they handle empty elements as well as invalid elements: exclude them, return NULL, or error. These same two TVFs also allow for tailoring whether a NULL input value returns an empty result set (as most other TVFs do), or returns a single row of NULL.
  
  
• Environment Variables: This release includes several functions in the OS category (Full version only) that can get, and in some cases set, environment variables. There are TVFs for getting a list of all variables and their values, and UDFs to get the value of a specific variable. You can get Machine / System variables only, User variables only, or Process variables which includes variables specific to the process running sqlservr.exe as well as the Machine and User variables.
  
  You can set the value of Process variables, and those values will persist beyond that call, for the life of that process, and will even be available to a subprocess such as xp_cmdshell (unlike setting an environment variable via xp_cmdshell which does not persist). This only requires EXTERNAL_ACCESS (Level 2). You can also set User environment variables, and those will persist beyond the life of the sqlservr.exe process. However, this requires UNRESTRICTED access (Level 3) because it writes the values to the user’s registry (which is how the value persists beyond the life of the process).
  
  Please also note that the “User” in question is the service account running the sqlservr.exe process (and this might not work if the process is running as “Local System”). There is no ability to write to the Machine / Server environment variables as that presents too great of a security risk.
  
  
• Performace Counters: This release includes several new functions in the OS (Full version only) category that allow you to interact with all Performance Counters, not just the SQL Server Instance-specific counters available via sys.dm_os_performance_counters. There is a TVF to get the full details of a sample: RawValue, BaseValue, Frequency, TimeStamp, CounterType, etc. And there is a UDF to get the calculated value of the most recent sample. Another UDF allows for adding a sample value to a counter (no, the system will not allow you to add samples to system counters). Finally, there is a function that allows for creating a new, custom Performance Counter category and counter, but it is a very rough draft / experimental: it only allows for creating unpaired counters (i.e. not requiring an additional “base” counter), and only allows for creating a single counter in the category. It is possible that it will be completed in the next release (and potentially along with an “UpdateCategory” function that allows for adding / removing counters from a category, something which is not directly supported; it requires dropping and recreating the category).
  
  
• JSON: Even though SQL Server 2016 includes native JSON support, it will be many years before everyone is using that version (there are plenty of installations still running 2005, 2008, and 2008 R2, as well as some still on SQL Server 2000 even). This release (Full version only) includes the first two functions intended to help people still using a version prior to 2016. There is a UDF to convert JSON into XML (as it can be parsed natively starting with SQL Server 2005), and one to convert XML into JSON (as XML can be natively generated using FOR XML, also starting with SQL Server 2005).
  
  Please keep in mind that JSON allows for anonymous arrays, a concept that does not exist in XML. In order to handle this, the resulting XML will use “<item>” elements to represent these anonymous / unnamed arrays.
  
  
• XML: While prior versions of SQL# allowed for saving XML data to a file, that XML data first had to be converted to NVARCHAR(MAX). That, however, does not provide a true XML document since the XML datatype does not store either the XML declaration (i.e. the “<?xml ... ?>” node on the first line) or any whitespace. This release includes a new function (Full version only) that can save XML data as actual XML. Not only can you specify any encoding you want (such as UTF-8, the most common Unicode encoding that wasn’t supported in SQL Server until 2016), but if you include the XML declaration, you then have the option of having it include the “encoding=” property set to the name of the encoding being used to write the file. There also options for having the output indented as well as optionally placing each attribute on its own line (instead of the default behavior of placing all attributes of an element on the same line).
  
  
• Pagination: The need to page through a large result set, X rows at a time, is nearly universal. And, often enough, this need also includes the requirement to get the total number of rows of the entire, non-paged result set (in order to determine how many pages exist). One option is to use the OFFSET and FETCH options of the ORDER BY clause. That is a good option for getting one page of the result set, but it doesn’t get the total number of rows, and it doesn’t apply to anyone using a version prior to 2012. Another option is to dump the entire result set into a temporary table, get the total row count via @@ROWCOUNT, and then just select the subset of rows from the temp table. But, while you don’t need to run the query twice, that is a lot of IO writing to the temp table plus the transaction log activity. Also, if you don’t have control over the query, but instead just have a Stored Procedure to execute, then you can’t use OFFSET and FETCH, and instead need to dump the results to a temp table via INSERT...EXEC. But then you need to be careful not to use the INSERT...EXEC construct within that Stored Procedure as you will get the “INSERT EXEC statement cannot be nested” error.
  
  This release includes a Stored Procedure (Full version only) that addresses this need. It executes any query (including a Stored Procedure), and allows for skipping a specified number of rows while returning a specified number of rows. It optionally allows for using an OUTPUT parameter to get the total number of rows for the entire non-paged result set (without executing the query again!). Beyond that, it allows for optionally including one or two extra columns at the end (i.e. far right): the row count within the current page of results and / or the row count within the entire non-paged result set. And if that wasn’t enough, there is also an optional parameter to override the field names of the result set.
  
  This Stored Procedure does not load the entire result set into memory (or anywhere), so it only ever has the current row in memory and thus should not take up any more system resources for a 10 million row result set than it does for a 10 row result set. It also will not get the “INSERT EXEC statement cannot be nested” error, so you can execute Stored Procedures that use that construct.
  
  
• Instance-wide Assembly info: Some DMVs, such as sys.dm_db_index_operational_stats, report data across the entire Instance, but only provide IDs that need to be translated into names. The OBJECT_NAME built-in function can take an optional parameter for “database_id” so it can map names across the entire instance. The OBJECT_SCHEMA_NAME built-in function also has a parameter for “database_id”. But, there is no built-in function for getting names across the entire Instance that are not in sys.objects or sys.schemas. The Sys_IndexName function was added in Version 3.1 (early 2014) to fill this void when using the Index DMVs. In this release, two functions (Full version only) have been added to fill a similar void with Assemlby DMVs, namely sys.dm_clr_loaded_assemblies. One function returns sys.assemblies across all accessible Databases, and even includes additional properties related to the “principal_id” – name, type, and SID – as they also exist on a per-Database basis. The other function simply returns the Assembly name given the IDs for the Database and Assembly.
  
  
• Credit Card Number validation: This is one of those subjects where there is more misinformation than correct information floating around related to how to properly determine what type of credit card it is based on the first 6 digits (many posts and RegEx patterns only check the first 4 digits). A good deal of research went into the improvements in this area in this release. The improvements in this release include handling many more card types (even the new 222300-prefixed MasterCard range) as well as a new function to simply validate the number (regardless of card type). There are two additional functions (Full version only): one that returns the type of card (with or without validating the number at the same time), and a TVF that, given a card number, returns the type and whether or not it is a valid number. Please note that “valid” and “invalid” are not in any way related to “active” and “inactive” as it is impossible to determine that active status of a card.
  
  There will eventually be a blog post detailing the research that went into this.
  
  
• Retrieve large quantities of Twitter Users: The original functions to get the various lists of Users (i.e. Followers, Friends, Mutes, Blocks, etc) returned 20 by default and could go up to 200 when passing in an @OptionalParameter. The problems here were: a) that not all of the SQL# Twitter functions were able to pass in the @OptionalParameters, b) no SQL# Twitter functions supported cursors, and c) some of the lists are tens of thousands of Users long.
  
  This release includes not only additional properties (i.e. result set columns) for both User and Status TVFs, it also includes: a) the ability to pass in @OptionalParamaters for the remaining functions that can support them, b) the ability to get and pass along Twitter cursor valus (so that pages of data can be stepped through), and c) a SQL#-specific option for the “count” optional parameter whereby a value of “all” will loop through the cursor internally so that the entire list (or as much as can be retrieved within the rate-limit window) can be retrieved in a single execution, and returned as a single result set. Also, new functions (Full version only) have been added to get lists of IDs to be looked up using another new function to get a list of Users based on those IDs. The functions that get the list of IDs are not limited to getting only 200 at a time, and can instead get thousands per each call.
  
  
• String Escape Sequences: T-SQL does not have the concept of string escape sequences (e.g. \n or \r\n = newline, \t = tab, etc) so instead we have to use CHAR(13) + CHAR(10) for \r\n, CHAR(9) for \t, and so on, and they have to be concatenated in as opposed to the escape sequences that are done inline. This release contains a function (Full version only) that will “un”escape all .NET string escape sequences, including “\x” followed by 1 to 4 hex digits, “\u” followed by 4 hex digits, and others.
  
  This functionality has been included in the handling of certain input parameters of various functions and stored procedures where it is more common to use string escape sequences. RegEx replacement strings as well as some of the export stored procedures in the DB category (e.g. DB_HTMLExport) now have this ability built in.
  
  
• Banker’s Rounding: Typical rounding behavior – the type of rounding that the built-in T-SQL ROUND function does – is to round “away from zero”. Meaning, positive numbers go up (increase) and negative numbers go down (decrease). But there is another type of rounding called “to even” that, as the name implies, rounds to the closest even number, which might be an increase or decrease, depending on the digit being rounded. Using this method, a value of 8.45, rounded to a single decimal place, would result in a value of 8.4 instead of the 8.5 that would be returned from the ROUND function. This is commonly referred to as “Banker’s Rounding” due to it being used in finances.
  
  There are two UDFs that handle this: one using DECIMAL input and return value, and one using FLOAT input and return value. DECIMAL is slower but more precise, and FLOAT is faster and less precise. Ideally, financial calculations should be done using DECIMAL datatypes instead of FLOAT, since their need to be accurate is more important than performance (end-users don’t tend to appreciate faster calculations that are also incorrect).
  
  
• Amortization Schedules: Ideally there would be one approach to calculating an amortization schedule and everyone would follow it. Unfortunately some programmers use FLOAT (Double in .NET) datatypes and standard rounding instead of banker’s rounding (among other mistakes, such as when rounding is applied). An unfortunate example of these two particular mistakes is SQL#’s original CompoundAmortizationSchedule TVF (but that was added 10 years ago!). Even more unfortunately than that is the fact that its results were validated against an actual amortization schedule given to me as part of a refinance I did through a reputable lender. However, in this release there is a new, alternate CompoundAmortizationSchedule TVF that uses DECIMAL datatypes and banker’s rounding. The original TVF is being kept as its results do match those of software being used by banks. But if there is ever a choice, the new TVF is preferred as it is more accurate (its results have also been validated against another amortization schedule given to me as part of another refinance I did through another reputable lender.
  
  There will eventually be a blog post detailing the research that went into this.
  
  
• Improved LevenshteinDistance Performance: The basic algorithm for the four Levenshtein Distance functions compares each character of one sting to each character of the other string. The number of comparisons is the length of one string multiplied by the length of the other. Hence it is very easy for these functions to slow down as the strings get longer. This release introduces a few new shortcuts to reduce time spent doing unnessary comparisons: if the two strings are equal in length then check if they are the same, if one is shorter than the other then check if it is a substring of the longer one, etc. I will test one or two other options for improving performance for the next release.
  
  Additionaly, all four Levenshtein Distance functions now have a @MaxDistance parameter that can be set to a value at which to exit the calculation if that value is reached, rather than proceeding to get the actual distance. The SQL# implementation of the Levenshtein and Damerau-Levenshtein distance algorithms is now one of the very few that truly short-circuit (i.e. exit early) rather than merely reducing how much of the strings to compare.
  
  There will eventually be a blog post detailing the research that went into this.
  
  
• Improved Code Page and Unicode Support: In prior versions of SQL#, functions that allowed for specifying a file’s encoding only allowed for specifying the six named .NET encodings: ASCII, UNICODE [implied Little Endian], UTF7, UTF8, UnicodeBigEndian, and UTF32 [implied Little Endian]. As of this release, aliases have been added to make your code easier to read for those who are not aware of Microsoft using “Unicode” to mean UTF-16 Little Endian, as well as the new ability to specify UTF-32 Big Endian.
  
  Additionally, there are several new encoding names that allow for saving files without a Byte Order Mark (BOM) for the few encodings that have BOMs. By default, the named .NET encodings include the BOM when saving files, but there are times when this behavior is undesirable.
  
  AND, it is now possible to pass in a number instead of an encoding name. Any value that can be converted to an Int will be interpreted as a Code Page.
  
  

New Functionality

Improved

• GENERAL:
  • Reviewed and cleaned up a lot of the early code / technical debt.
  • Streamlined build process.
  • Most functions requiring elevated permissions now display an error message containing the exact statement to execute to fix the problem. The remaining functionality missing this detailed error message will be addressed in the next release.
  • Reduced size of main SQL# assembly by 47 KB, while at the same time adding functionality.
  • REMOVED DB_XOR as it was unnecessary. Instead use built-in " ^ (Bitwise Exclusive OR)" operator.
  • The @FileEncoding parameter of any Function or Stored Procedure now accepts all encodings found here ( https://msdn.microsoft.com/en-us/library/system.text.encoding.aspx#Anchor_5 ). Values can be from the "Code Page" column or the "Name" column. Additionally, you can use the following values, some being SQL#-specific (use one of the “NoBOM” values if you are writing to a file and need to omit the Byte Order Mark / BOM):
    • ASCII
    • UTF7
    • UTF8 / UTF8NoBOM
    • UTF16 / UTF16Le / Unicode
    • UTF16NoBOM / UTF16LeNoBOM / UnicodeNoBOM
    • UTF16Be / BigEndianUnicode
    • UTF16BeNoBOM / BigEndianUnicodeNoBOM
    • UTF32 / UTF32Le
    • UTF32NoBOM / UTF32LeNoBOM
    • UTF32Be, UTF32BeNoBOM
• Installation Script:
  • Removed “GO” after “USE” statement so that any error with “USE” (which will be a parse error) will fail the entire script. Previously (with the “GO” statement) if there was an error with the “USE” statement, that batch would fail, but the script would proceed to install SQL# into whatever database was current / active.
  • Replaced @AllowExternalAccess and @AllowUnrestrictedAccess with @MaxAllowedAccessLevel:
    • Level 3 is same as @AllowUnrestrictedAccess = 1
    • Level 2 is same as @AllowExternalAccess = 1
    • Level 1 is the same as both previous variables being 0
    • Level 0 is a new concept that skips Instance-level configuration (Asymmetric Key, Key-based Login, and new dbo.SQLsharp_InstanceUninstall Stored Procedure in [master]) and doesn't install Assemblies that cannot be used in SAFE mode.
  • Validate that the database that SQL# is being installed into has the same owner SID in both sys.databases and {install_db}.sys.database_principals. If there is a mismatch between those two records, then the install script will abort and provide the appropriate steps to remedy the situation, specific to the version of SQL Server (SQL Server 2005 is different than post-2005). The situation is usually caused during a restore operation. When the SID values do not match, then attempting to set any Assembly to either EXTERNAL_ACCESS or UNSAFE will result in error 10314 and/or 33009.
  • Validate @SQLsharpLogin to ensure that it isn't a server- or database- principal that already exists and is a non-Asymmetric Key-based principal (e.g. “dbo”, “sa”, etc).
• (BREAKING CHANGE ☹) String_LevenshteinDistance, String_LevenshteinDistancePlus, String_DamerauLevenshteinDistance, and String_DamerauLevenshteinDistancePlus:
  • Added @MaxDistance input parameter to allow for better efficiency by stopping processing once max value has been reached (suggested by Kirby Moyers).
• (BREAKING CHANGE ☹) String_LevenshteinDistancePlus and String_DamerauLevenshteinDistancePlus:
  • Added input parameter for @LCID (i.e. Locale / Culture) with 0 = database_default and -1 = Invariant Culture (previously behavior used only the Invariant Culture); @CompareOptions param now allows for 'DATABASE_DEFAULT' (value is case-INsensitive) which uses the sensitivity settings associated with the Database's default Collation (empty string still means "None" == everything-sensitive).
• (Behavior Change) String_LevenshteinDistance and String_DamerauLevenshteinDistance:
  • Assumes LCID (i.e. Locale / Culture) associated with Database's default Collation (previous behavior used only the Invariant Culture).
  • Compare Options now uses the sensitivity settings associated with the database's default Collation (previous behavior used "Ordinal" / Binary).
• String_LastIndexOf:
  • Returns NULL on NULL input of any parameter rather than erroring if NULL passed into @StartIndex or @ComparisonType.
  • If @StartIndex < 1, start searching at end of @StringValue, rather than erroring.
  • (Possible Behavior Change) Uses Locale / LCID of the Database's default Collation instead of the Windows OS LCID (typically these are the same LCID, but they can be different).
  • Added @CompareOption = 0 that uses the sensitivity settings (case, accent, Kana, and width) of the Database's default Collation.
  • Invalid @ComparisonType value will error rather than returning 0 (which was misleading).
• String_Newline:
  • Returns current environment's newline when NULL passed in (used to error)
• Util_CRC32, Util_GenerateDateTimeRange, Util_GenerateDateTimes, Util_Hash, and Util_HashBinary:
  • Better performance.
• Util_GenerateIntRange, Util_GenerateDateTimes, Util_GenerateDateTimeRange, Util_GenerateDateTimes, Util_GenerateFloats, and Util_GenerateFloatRange:
  • Return empty result set if any input param is NULL instead of erroring.
• Util_IsValidCC:
  • More efficient (tremendous improvement!).
  • More accurate (including new Mastercard range).
  • Support several new card types: JCB, Carte Blanche, PayPal, Union Pay, MIR, UATP, Dankort, InterPayment, and Maestro.
  • Returns NULL on NULL input (either parameter) instead of erroring.
• Math_Cosh, Math_Sinh, and Math_Tanh:
  • Returns NULL on NULL input instead of erroring.
• Math_IsPrime and Math_RandomRange:
  • Return NULL on any input param being NULL rather than erroring.
• Math_CompoundAmortizationSchedule:
  • Added result set columns:
    CumulativePrincipal FLOAT, CumulativeAmountPaid FLOAT, and TotalAmountPaid FLOAT.
• File_GetDirectoryListing and File_GetInfo:
  • Added result set columns for NTFS ACL info:
    SecurityDescriptor NVARCHAR, OwnerSID VARBINARY, OwnerName NVARCHAR, GroupSID VARBINARY, GroupName NVARCHAR
    (suggested by Tim Chapman)
  • GetDirectoryListing always returns NULL for OwnerName and GroupName (until they can be cached, which should be in the next release). For now, the SIDs can be translated either by using the new OS_TranslateSddlSidToName function, or by using “SUSER_SNAME(SQL#.Convert_SddlSidToBinary(OwnerSID))”.
• File_GetDirectoryListing:
  • (BREAKING CHANGE ☹) Added @IncludeSecurityInfo input parameter to toggle whether or not ACL info is retrieved as it does hurt performance (don’t include unless you need the info).
  • Better performance.
• File_PathExists:
  • Returns NULL if a NULL is passed in rather than erroring.
• File_GetFileBinary and File_WriteFileBinary:
  • Better handling of NULL @FilePath.
• File_Touch:
  • Added @WhichTime options of "Create" and "All" (meaning all 3 times: Create, Access, and Write).
• RegEx (all functions):
  • Added "Compiled" to @RegExOptions parameters
  • Returns NULL (or empty result set for TVFs) if @RegularExpression is NULL (used to error)
  • Better error handling if @StartAt parameter is NULL
• RegEx:
  • (Possible Behavior Change) Unescape the following input params (so “\n” becomes an actual newline, and so on):
    • RegEx_CaptureGroup ( @NotFoundReplacement )
    • RegEx_Replace ( @Replacement )
    • RegEx_ReplaceIfMatched ( @Replacement and @NotFoundReplacement )
• RegEx_IsMatch, RegEx_MatchLength, Replace, CaptureGroup, Index, Split, and CaptureGroups:
  • Better performance.
• RegEx_IsMatch:
  • (Behavior Change) Returns NULL if @ExpressionToValidate is NULL (** used to return 0 / "false")
• RegEx_MatchLength:
  • Better error handling if @Length is NULL
• RegEx_CaptureGroup:
  • Better error handling if @CaptureGroupNumber is NULL
  • @Length of NULL or < -1 assumed to be -1 (used to error)
• RegEx_Split:
  • @Count of NULL or < -1 assumed to be -1 (used to error)
• RegEx_Replace and RegEx_ReplaceIfMatched:
  • Better error handling if @Replacement is NULL
  • @Count of NULL or < -1 assumed to be -1 (used to error)
• Twitter (all functions):
  • Streamlined Twitter code
  • Added result set columns to all User-based functions:
    ContributorsEnabled BIT, IsTranslator BIT, FollowRequestSent BIT, NumberOfFavorites INT, ProfileImageUriHttps NVARCHAR(2048), ProfileBackgroundColor NVARCHAR(20), ProfileTextColor NVARCHAR(20), ProfileLinkColor NVARCHAR(20), ProfileSidebarFillColor NVARCHAR(20), ProfileSidebarBorderColor NVARCHAR(20), ProfileBackgroundImageUri NVARCHAR(2048),ProfileBackgroundImageUriHttps NVARCHAR(2048), ProfileUseBackgroundImage BIT, ProfileBackgroundTile BIT, DefaultProfile BIT, DefaultProfileImage BIT, PreviousCursor NVARCHAR(50), NextCursor NVARCHAR(50), RateLimitResetLocalTime DATETIME
  • Added result set columns to all Status-based functions:
    PlaceCountryCode NVARCHAR(2), PlaceAttributes XML, PlaceBoundingBox XML, PlaceURL NVARCHAR(4000), FavoriteCount INT, FilterLevel NVARCHAR(50), InReplyToScreenName NVARCHAR(100), Language NVARCHAR(20), PossiblySensitive BIT, QuotedStatusID BIGINT, RetweetCount INT, Retweeted BIT, WithheldCopyright BIT, WithheldScope NVARCHAR(20), WithheldInCountries NVARCHAR(4000), Entities XML, RateLimitResetLocalTime DATETIME
• Twitter:
  • Added @OptionalParameters input parameter to the following functions:
    • GetFollowers and GetFriends (user_id & screen_name & count & cursor)
    • GetBlocks and GetMutes (count & cursor)
• Twitter_GetBlockedUserIDs, Twitter_GetBlocks, Twitter_GetFollowers, Twitter_GetFollowerUserIDs, Twitter_GetFriends, Twitter_GetFriendUserIDs, Twitter_GetMutedUserIDs, and Twitter_GetMutes:
  • Allow for SQL#-specific Optional Parameter value of "all" for "cursor" parameter. Cycles through list until none left OR Rate Limit has been reached.
• DB_BulkCopy:
  • (Possible Breaking Change) Added optional parameter for @SourceCommandTimeout (default = 30 seconds; suggested by Gary Steffen).
  • Added parameter defaults for @BatchSize (0), @NotifyAfterRows (0), and @TimeOut (30).
• DB_BulkExport:
  • Better error messaging (Messages tab and Return Value)
  • More efficient code (at least 10% faster)
  • Added optional parameter for @SourceCommandTimeout (default = 30).
• DB_ForEach:
  • Add replacement tags – {SQL#DBList.System}, {SQL#DBList.MSApp}, and {SQL#DBList.MSDemo} – for use with @DBPattern and @DBExcludePattern.
• DB_HTMLExport:
  • (Possible Behavior Change) Unescaped input params
  • Added parameter default values for most input parameters (helps when using via EXEC)
  • (BREAKING CHANGE ☹) Added @AppendOutput input parameter (false = overwrite; default is to Overwrite, same as previous behavior).
• DB_DumpData:
  • Added parameter default values for most input parameters
  • Added optional @AppendOutput input parameter (false = overwrite; default is to Append, same as previous behavior).
• Sys_IndexName and Sys_Objects:
  • Improved performance (across repeated calls).
• SQLsharp_GrantPermissions:
  • Now auto-detects the Schema being used for SQL# objects, and so the optional input parameter @SQLsharpSchema has been removed.
  • A optional input parameter has been added for @PrintSqlInsteadOfExecute that will simply print to the "Messages" tab the SQL that would be executed without actually executing it.
• SQLsharp_Help:
  • Replaced outdated / unmaintained list of commands with info on how to extract embedded PDF manual.
• SQLsharp_Download:
  • Better error checking around input parameters.
  • Added default filename of "SQLsharp_SETUP_FullVersion.zip" if only a directory is specified.
  • Check for file and directory existence before attempting download.
• SQLsharp_Uninstall:
  • Ensure that using "dbo" Schema doesn't attempt to drop the Schema.
• SQLsharp_SetSecurity:
  • Added optional input parameter @DoNotPrintSuccessMessage to disable the default "Successfully set Assembly..." message (suggested by Kirby Moyers)
• Date_Format:
  • Return NULL on any input param being NULL rather than erroring.
• INET_GetWebPages:
  • Added result set column:
    CharacterSet NVARCHAR(100)

Fixed

• File_GetFile:
  • (BREAKING CHANGE ☹) It would only properly handle files encoded as the system default or one of the Unicode encodings (as long as it included the Byte Order Mark / BOM). Added @FileEncoding input parameter to force the encoding when no BOM is present. Leaving @FileEncoding set to NULL will cause it to behave as it previously did (i.e. will auto-detect IF a BOM is present, else will assume system default, which is most likely non-Unicode, 8-bit Extended ASCII).
• File_ChangeEncoding:
  • (BREAKING CHANGE ☹) Did not always translate correctly as source encoding was being auto-detected but not all encodings use Byte Order Marks, and the ones that do use them do not require that they be used. Added @CurrentEncoding to explicitly set the encoding of the source file.
  • No longer errors if @FilePathNew is NULL.
• File_CopyMultiple, File_DeleteMultiple, File_GetDirectoryListing, and File_MoveMultiple:
  • Properly handles C:\ (doesn’t require “C:\\\”) and \\Server\Share (doesn’t require “\\\\Server\Share”).
• File_CurrentEncoding:
  • Correctly reports encodings.
• String_LevenshteinDistance, String_LevenshteinDistancePlus, String_DamerauLevenshteinDistance, and String_DamerauLevenshteinDistancePlus:
  • Now handle spaces properly when using 'IgnoreSymbols' @ComparisonOption.
• Util_GenerateIntRange, Util_GenerateDateTimeRange, and Util_GenerateFloatRange:
  • Now include the upper-end value.
• RegEx_Split:
  • Fixed minor bug related to @StartAt parameter
  • Fixed minor bug related to @Count parameter
• Twitter (all functions returning a string value or field):
  • Fixed handling of Supplementary Characters (often used for Emoji).
• DB_BulkExport:
  • ColumnHeaderHandling of "always" did not work. No rows returned would not print column headers.
• DB_ForEach:
  • Schema name for each Table was not always correct.
• DB_BulkCopy:
  • Default destination changed: was "(local)", now correctly detects current instance, whether it is a default instance (will use "(local)"), a named instance (will use "ServerName\InstanceName"), or SQL Server Express LocalDB (will use "np:\\.\pipe\LOCALDB#xxxxxxxx\tsql\query");
  • (Behavior Change) Default database was "tempdb" and is now unspecified, hence it will use the Login's default DB (so as to not increase contention on "tempdb").
• SQLsharp_GrantPermissions:
  • Now grants EXECUTE on User-Defined Aggregates (Agg_*) and User-Defined Types (Type_*).
  • User and Role names -- passed into @GrantTo -- with embedded single-quotes are now handled properly (but who would ever do that, right?)
• Math_Truncate:
  • Now handles negative numbers correctly (was rounding down).
• INET_GetWebPages:
  • Properly handles standard HTTP headers (specifically: Content-Type, Referer, User-Agent, Connection, Transfer-encoding, and Range)
  • Properly handles PUT method:
    • Sends @PostData
    • If @PostData is NULL or empty string, will send Content-Length header as 0.
• INET_HTMLEncode:
  • Encodes apostrophes as "&apos;" instead of "&#039;".
  • Now encodes spaces (as "&nbsp;") instead of ignoring them.
• INET_HTMLDecode:
  • Decodes "&nbsp;" (as a space) instead of ignoring them.
  • Decodes "&apos;" (as an apostrophe) instead of ignoring them.

Version 4.0.93 (March 16^th, 2017)

Initial release of 4.0

• Free version only.
• Same as 4.0.95 except:
  • Missing Date_FormatOffset.
  • Minor bug with installer: error if needing to enable CLR.
  • Minor bug with INET_GetWebPages: doesn't send Content-Length header or @PostData if method is "PUT".

Version 3.3.83 and 3.3.84 (November 17^th, 2014)

New

• DB functions / procs: DeserializeResults (used with SerializeResults and SerializeResultsInChunks), NewID, SerializeResults, SerializeResultsInChunks, and TryCatch
• String functions: DamerauLevenshteinDistance, DamerauLevenshteinDistancePlus, and LevenshteinDistancePlus
• Util functions: GarageCollect and GetTotalMemory
• Twitter functions: GetMutes, MuteUser, and UnMuteUser (suggested by Joe de Silva)
• Aggregate functions: BitwiseAND, BitwiseOR, BitwiseXOR, HarmonicMean, and JoinPlus (suggested by: Martijn Evers, Jason Pierce, and Ryanne Turenhout)

Improved

• SQLsharp_Setup (only used by installer script) and the installer script: Updated all "SYSNAME" references to be "sysname" to better support case-sensitive servers as "sysname" is an alias that needs to be looked-up in the [master] database.
• Date_BusinessDays, Date_BusinessDaysAdd, and Date_IsBusinessDay:
  • Added 2 holidays--Christmas Eve (December 24th) and New Year's Eve (December 31st)--to Holidays list
  • Changed @ExcludeDaysMask input param to BIGINT from INT
• DB_BulkCopy: Added optional BIGINT OUTPUT param for @RowsCopied that is the number of rows inserted into the Destination Table (suggested by David Sumlin)
• DB_GetQueryInfo:
  • Added input param @QueryGroup NVARCHAR(100) to more easily group repeated tests / makes aggregations to find averages very easy. Default = empty string.
  • Added input param @CaptureExecutionPlans BIT to disable logging of execution plans. they can be large and if testing a loop, each query within each iteration will have its own plan. Default = "true" / 1.
  • Reduced memory consumption
• DB_CreateOrAlterQueryInfoTables:
  • Added new "QueryGroup" field to @TableNamePrefix + "ExecutionContext" table that is populated via new @QueryGroup input param on DB_GetQueryInfo.
  • Added auto-generated Stored Procedure @TableNamePrefix + "DeleteTest" that removes one or more entries from all 4 "QueryInfo" tables, based on QueryInfoID
  • Added auto-generated Stored Procedure @TableNamePrefix + "GetBasicStats" that aggregates MIN, AVG, STDEV, MAX for the captured metrics, grouping on new QueryGroup field
• DB_BulkExport: Force external (i.e. non-"Context Connection = true") connections to use Impersonation to avoid security hole.
• Twitter: Added "Language NVARCHAR(50)", "NumberOfPublicListMemberships INT", "IsGeoEnabled BIT", "Following BIT", and "Muting BIT" to all User TVFs

Fixed

• INET_GetWebPages: Properly encode XML special characters for ResponseHeaders field to prevent "&" from causing an error.
• DB_DescribeResultSets: no longer errors on SQL 2005, 2008, and 2008 R2 if there was no row available for sample data.
• Math_Convert:
  • Returns NULL if any input parameter is NULL rather than erroring
  • Improved accuracy for Computer/Digital Storage (will finish temperature and distance conversions in the next release)

Version 3.2.81 and 3.2.82 (July 30^th, 2014)

New

• RegEx functions: CaptureGroup4k, IsMatch4k, MatchSimple4k, Replace4k, ReplaceIfMatched (suggested by Matt McClellan), and ReplaceIfMatched4k
• DB functions / procs: CreateOrAlterQueryInfoTables (used with DB_GetQueryInfo), DescribeResultSets, GetQueryInfo, and ThrowException
• Math functions: BitwiseLeftShift and BitwiseRightShift
• Util stored procedure: Print
• File function: GetLineCount

Improved

• SQLsharp_Setup (only used by installer script): better error handling and more verbose output.
• Date_BusinessDays, Date_BusinessDaysAdd, and Date_IsBusinessDay: added 3 options for Veterans Day (November 11th) to Holidays list (suggested by David Sumlin)
• Type_FloatArray, Type_HashTable, and Type_NVarcharArray: updated AddData / AddItem methods so that they can be called without first initializing the variable via SET @TypeVar = '';
• RegEx_Replace: @RegularExpression of NULL returns NULL instead of erroring
• DB_BulkExport:
  • NULL or empty sting value for @Query exits instead of erroring
  • Added defaults for most input parameters
  • Changed @Query input param from NVARCHAR(4000) to NVARCHAR(MAX)
  • Added input param for @ConnectionString NVARCHAR(500); default = "Context Connection = true;" (i.e. internal / in-process connection)
  • Added input param for @TextQualifierEscape (for embedded TextQualifiers); default = NULL; NULL = @TextQualifier.
• File_GetFile:
  • Stream rows from file to SQL Server when setting @SplitLines = 1 rather than reading the entire contents of the file into memory first.
  • Added “LineLength BIGINT” field to the result set that is the number of characters (excluding newlines / returns) per each line OR all characters (including newlines / returns), if @SplitLines = 0.
  • Changed “ContentLength” field to be cumulative number of characters (excluding newlines / returns) read so far, inclusive of the current line OR all characters (including newlines / returns), if @SplitLines = 0.

Fixed

• Math_Factorial: passing in 0 returns 1 instead of 0.
• INET_GetWebPages: does not error when setting @SplitLines to 1 (issue from the previous release)

Version 3.1.79 and 3.1.80 (January 8^th, 2014)

New

• String functions: LevenshteinDistance, PadBoth, Split4k*, and TryParseToInt*
• SysInfo function: IndexName
• XML assembly with functions: EscapeContent, Transform (i.e. XSLT) (suggested by Dave Sumlin), and UnescapeContent
• Date functions: DaysInMonthFromDateTime*, DaysLeftInMonth, IsDaylightSavingTime, ToLocalTime, and ToUniversalTime
• DB function: CurrentSQLStatement

Improved

• Date_FormatTimeSpan: each TimeSpanPart can now include an optional width that will be left-padded with zeros if the actual value of that TimeSpanPart has fewer digits than the specified desired width. (suggested by Dave Sumlin)
• All Twitter functions: helpful error message displayed if SQL#.Twitterizer assembly permission level is not set to 2
• File_GetDirectoryListing: added new [ErrorMessage] field to result set. If the process does not have permission to list the contents of a directory when doing recursive, the specific error will be noted in the new field and the process will continue with the next directory; previously the process would error if permission was denied on any folder.
• INET_GetWebPages:
  • Added new [ResponseHeaders] XML field to result set (suggested by Dave Sumlin)
  • Allow "Referer" to be sent in for CustomHeaders (sets the HTTP_REFERER header)

Fixed

• DB_DumpData: Fixed issue preventing @LinkedServerName input parameter from working
• Util_GZip: no longer error on input of NULL or empty binary (0x); it now returns NULL in both cases
• Util_GUnzip: no longer error on input of NULL; it now returns NULL
• Twitter_SearchTweets function: fixed problem with OptionalParameters not being sent to Twitter
• Twitter functions no longer error on non-standard Unicode escape sequences in statuses (e.g. \ud8c3)
• INET_GetWebPages: no longer error on invalid date_modified in header; instead return 1900-01-01
• All Twitter functions that return a StatusText field now define it as NVARCHAR(300) instead of 200.
• All Twitter functions that return a UserID, RecipientID, or InReplyToUserID field now define them as BIGINT instead of INT.
• RegEx_Match: properly handles no match; returns empty result set instead of the bogus row
• Date_DaysInMonth, Date_DaysLeftInYear, Date_FromUNIXTime, Date_GetDateTimeFromIntVals, Date_IsLeapYear, Date_LastDayOfMonth, and Date_ToUNIXTime: return NULL when NULL is passed in rather than erroring

Version 3.0.72 and 3.0.73 (April 6^th, 2013)

• Fixed minor bug in SQLsharp_SetSecurity

Version 3.0.70 and 3.0.71 (March 4^th, 2013)

• Added Math functions: FormatDecimal, FormatFloat (not available in Free version), and FormatInteger (not available in Free version)
• Add / Remove assemblies:
  • Ability to install / uninstall individual assemblies. This is not automated yet but will someday soon be incorporated into the installer script.
  • Updated SQLsharp_Setup to accept new parameter @SQLsharpAssembly, which if specified, will install the wrapper functions and stored procedures for only the specified assembly. The specified assembly needs to already exist.
  • Updated SQLsharp_Uninstall to accept new parameter @SQLsharpAssembly so that the specified assembly and its wrapper functions and stored procedures can be uninstalled without affecting anything else.
• Security:
  • SQL#-specific database login created from asymmetric key.
  • All SQL# assemblies now owned / authorized by new SQL# login instead of dbo.
  • Updated SQLsharp_SetSecurity to no longer set the DB to TRUSTWORTHY ON when setting an assembly to level 2 or 3 (External Access or Unrestricted).
  • All assemblies can be disallowed from being set to either Unrestricted (but allowed for External Access) or both Unrestricted and External Access.
  • Most functionality requiring External Access for main SQL# assembly has been broken out into separate assemblies: SQL#.DB, SQL#.FileSystem (FILE_* functions), and SQL#.Network (INET_* functions). Not only does SQL# stay as Safe, but if only INET_* functions are being used and not FILE_*, then no need to set SQL#.FileSystem to External Access.
  • If you are upgrading from a pre-3.0.x version and had any of the assemblies’ permissions set to level 2 or 3 (External Access or Unrestricted), then the database where SQL# is installed had its TRUSTWORTHY setting set to ON and this might not be necessary anymore. SQL# no longer requires TRUSTWORTHY to be set to ON for External Access or Unrestricted assemblies and if you have no other need for it to be on then please run the following:
    ALTER DATABASE [{database_where_SQL#_exists}] SET TRUSTWORTHY OFF
  • If you don’t want any of the assemblies to ever be set to Unrestricted (but still be eligible to be set to External Access), then set the @AllowUnrestrictedAccess variable towards the top of the install script to 0. If you don’t want any of the assemblies to ever be set to either External Access or Unrestricted, then set both @AllowUnrestrictedAccess and @AllowExternalAccess variables to 0. Please note that the ability to restrict the level of permissions for any assembly requires that the database have its TRUSTWORTHY setting set to 0 / OFF.
• Installer:
  • Uninstall of exisiting SQL# (if it exists) and install of current version now wrapped in a transaction that will rollback if any problem occurs, leaving everything as it was before the install attempt if the install cannot complete successfully.
  • A login, based on an asymmetric key, is created as a means of allowing assemblies to be set to External Access or Unrestricted without the need for the database to have TRUSTWORTHY set to ON.
  • A user is created in the database where SQL# is being installed, based on the new login mentioned just above. This user will own the SQL# assemblies instead of “dbo”.
  • New assemblies: SQL#.DB, SQL#.FileSystem (FILE_* functions), SQL#.JsonFx (needed for Twitter_* functions), SQL#.Network (INET_* functions), and SQL#.TypesAndAggregates.
  • Variables towards the top of the install script allow for easy configuration of new SQL# login name and permissions.
  • If upgrading from a version prior to 3.0.x and the SQL# assembly was set to either External Access (2) or Unrestricted (3), then several of the new assemblies will be set to that same permission level to have no initial change in behavior. Those new assemblies are: SQL#.DB, SQL#.FileSystem, and SQL#.Network.
• Twitter:
  • Updated to use newer Twitter v1.1 JSON API instead of older v1.0 XML API (v1.0 API starts incremental end-of-life process on March 5th, 2013).
  • Requires SQL#.JsonFx and SQL#.TypesAndAggregates assemblies.
  • (BREAKING CHANGE ☹) Removed functions Twitter_GetRetweetedToMe and Twitter_GetRetweetedByMe as there is no replacement for either in the v1.1 API.
  • (BREAKING CHANGE ☹) Removed function Twitter_GetPublicTimeline as there is no exact replacement in the v1.1 API, but might replace with new “sample” call that is similar.
  • (BREAKING CHANGE ☹) Removed function Twitter_GetReplies as it was deprecated a while ago and merely pointed to Twitter_GetMentions.
  • (BREAKING CHANGE ☹) Removed function Twitter_GetFriendsTimeline as it does not exist in the v1.1 API and Twitter_GetHomeTimeline is nearly identical.
  • Added function: Twitter_SearchTweets (not available in Free version).
  • SQL Server 2005 requires Unrestricted access (level 3) for both SQL#.JsonFx and SQL#.Twitterizer. This is handled automatically in the installer. Also, it is possible that this is not required in SQL Server 2005 Enterprise Edition, but I have no easy way to verify that at the moment.
  • No signature changes in this release! All input parameters and output fields are the same to make upgrading a smoother process. BUT, in the very near future there will be at least a few changes:
    • UserIDs are now BIGINT at Twitter and the SQL# Twitter functions will be updated to reflect that in both input params and result set fields and scalar return values.
    • Most User-based table-valued functions (i.e. those returning a list of users) allow for paging through the list of results but only getting a max of 20 or 100 at a time, depending on the call. The SQL# Twitter functions will be updated to return the “previous” and “next” cursor values so that they can be sent in as Optional Parameters.
    • Twitter functions that currently do not have the @OptionalParameters input parameter where the Twitter call supports optional parameters will have the @OptionalParameters input parameter added to the signature. These include: Update, GetFollowers, GetBlocks, and GetFriends.

Version 2.17.68 and 2.17.69 (May 6^th, 2012)

• Added Date function: Format (suggested by Dietmar Müller)
• Added INET function: URIDecodePlus to extend the capabilities of URIDecode in two ways: 1) unescape %uXXYY-encoded Unicode characters which otherwise throw an error; and 2) gracefully handle errors, making set-based processing easier (suggested by Andy Krafft).
• Added SQLsharp procedure: Download (replaces Update)
• Updated INET_GetWebPages to allow "Content-Type" to be set via @CustomerHeader value. If set, the passed-in "Content-Type" will override the automatic value set when using the POST method (suggested by Michael Kuhl).
• Updated DB_BulkExport: Improved handling of binary fields: a) drastic speed increase, and b) added missing "0x" prefix
• Added String function: CompareSplitValues
• Updated Convert_BinaryToHexString: Improved performance
• Renamed String_SplitIntoFields to String_SplitResultIntoFields. String_SplitIntoFields is deprecated and will be replaced in the next version or two with a slightly different usage. Please switch any use of String_SplitIntoFields to point to String_SplitResultIntoFields.
• (BREAKING CHANGE ☹) Moved the following functions to Full version: String_SplitIntoFields, String_FixedWidthSplit, String_FixedWidthIndex, DB_BulkExport, DB_HTMLExport, and DB_ForEach.

Version 2.16.64 and 2.16.65 (October 19^th, 2011)

• Fixed error in installer that shows up when the collation setting for tempdb is not the same as the setting for the database in which SQL# is installed
• Fixed minor error with SQLsharp_Uninstall (minor in that the error is reported but the uninstall still completes) that occurs when the database in which SQL# is installed has a case-sensitive collation
• Updated INET_GetIPAddress to return NULL when NULL is passed in rather than error
• Added INET function: INET_GetIPAddressList
• Updated Date_BusinessDays and Date_IsBusinessDay to include two new holidays: Presidents' Day [US] (3rd Monday in February) (suggested by Claudio Pracilio) and Columbus Day [traditional] (October 12th)
• Added Date function: Date_BusinessDaysAdd (suggested by Victor Wang)
• Added RegEx function: RegEx_CaptureGroups (suggested by Jason Pierce)
• Updated Date_BusinessDays to allow for StartDate to be greater than EndDate which will return a negative number, similar to how DATEDIFF works (suggested by Victor Wang)
• Updated Date_BusinessDays to return NULL when any parameter is NULL rather than error
• Added String functions: FixedWidthIndex (suggested by Don Folino) and FixedWidthSplit

Version 2.15.62 and 2.15.63 (August 31^st, 2011)

• Added RegEx functions: Escape, Index, and Unescape
• Updated installer to remember security settings of previously installed assemblies
• Updated File_SplitIntoFields and String_SplitIntoFields to accept a new, optional parameter for @DataTypes. The @DataTypes parameter allows you to set the specific data type of one or more of the fields in the result set. The default is still to create each field as NVARCHAR(MAX), but if you know that certain fields will always be a particular data type, then you can have all of the SplitIntoFields stored procedures return a more strongly-typed result set (which means doing fewer conversions later).
• Added INET function: SplitIntoFields
• Added new Sys group.
• Added Sys function: Objects (server-wide view of sys.objects)
• Updated File_SplitIntoFields to make @RowsToSkip parameter optional. The default is 0.

Version 2.14.60 and 2.14.61 (March 13^th, 2011)

• Updated String_IsNumeric to allow for “d” to be double-precision as well as not requiring the + or – for scientific notation
• Added String functions: Replace, SplitKeyValuePairs, TrimChars, TrimEnd, and TrimStart
• Updated Twitter Status-related Table-Valued Functions to return 7 geo fields: GetUserTimeline, GetPublicTimeline, GetFriendsTimeline, GetReplies, GetMentions, GetFavorites, GetMessages, and GetSentMessages
• (BREAKING CHANGE ☹) Updated Twitter_Update to accept optional Longitude and Latitude values for geocoding Tweets
• Added Twitter functions: GetHomeTimeline, GetRetweetedBy, GetRetweetedByMe, GetRetweetedToMe, GetRetweets, GetRetweetsOfMe, and Retweet
• (BREAKING CHANGE ☹) Updated Twitter Status-related Table-Valued Functions to be able to pass in Twitter Optional Parameters: GetFriendsTimeline, GetFavorites, GetMentions, GetMessages, GetReplies, GetSentMessages, and GetUserTimeline as well as new functions GetHomeTimeline, GetRetweetedBy, GetRetweetedByMe, GetRetweetedToMe, GetRetweets, and GetRetweetsOfMe. See Manual entry for Twitter_GetUserTimeline for example of how to use Optional Parameters via Type_HashTable.
• Deprecated Twitter_GetReplies in favor of GetMentions
• Updated DB_BulkExport: added support for UTF7 as well as more datatypes: rowversion, date, time, datetime2, and datetimeoffset
• Updated DB_DumpData: added support for UTF7 as well as more datatypes: rowversion, date, time, datetime2, and datetimeoffset
• Updated DB_HTMLExport: added support for UTF7
• Added new RunningTotal group (not available in Free version)
• Added RunningTotal functions: Add, Get, CacheSize, and ClearCache
• Added Util functions: HashBinary and IsValidConvert
• Added Date functions: FullDateTimeString (not available in Free version) and NewDateTime
• Updated Date_FullDateString and Date_FullTimeString to return NULL if input is NULL rather than error
• Updated FILE functions to allow for full streaming and hence use much less memory: CopyMultiple, DeleteMultiple, GetDirectoryListing, and MoveMultiple
• Updated RegEx_Split to stream results out
• Updated Table-Valued RegEx functions to return NVARCHAR(MAX) for [Value] instead of NVARCHAR(4000): Match, Matches, and Split
• Added INET function: DownloadFile
• Added Math functions: CubeRoot, IEEERemainder, NthRoot, and Truncate
• Added Convert functions: DateTimeToMSIntDate and MSIntDateToDateTime
• Updated String_Split to fully stream output so it now uses less memory.

Version 2.13.55 and 2.13.56 (November 22^nd, 2010)

• Updated OS_EventLogWrite to accept NVARCHAR(MAX) for @Message as opposed to NVARCHAR(4000)
• Updated OS_EventLogRead to return NVARCHAR(MAX) for Message as opposed to NVARCHAR(4000)
• Added OS function: StartTime
• Fixed Math_IsPrime as it was falsely reporting some large numbers as Prime that were not
• Updated File_SplitIntoFields to add new parameter for @FileEncoding so that the user has full control over the encoding type.  Previously it was set to AutoDetect which did not always produce the correct result.
• Updated File_WriteFile and File_WriteFileBinary to add “UTF7” as a FileEncoding option
• Added File functions: CreateTempFile, GetDirectoryName, GetFileName, GetRootDirectory, and Touch

Version 2.12.53 and 2.12.54 (September 19^th, 2010)

• This release (2.12.x) is a Twitter API ONLY update. No other changes have been made in this release! If you do not use the Twitter functions and are on 2.11.x you do NOT need to upgrade. However, if you are using the Twitter functions then you MUST upgrade to 2.12.x for the full OAuth implementation changes! The Twitter functions in version 2.11.x will no longer work as of Monday, October 18^th, 2010.
• Please see the SQL# Twitter setup guide for details on how to set up your Twitter Application:
  https://SQLsharp.com/download/SQLsharp_TwitterSetup.pdf

Version 2.11.51 and 2.11.52 (June 19^th, 2010)

• Added Date functions: GetDateTimeFromIntVals, GetIntDate, and GetIntTime
• Updated File_GZip and File_GUnzip: Replaced built-in .NET GZip and GUnzip libraries with external DotNetZip library for better compression and ZIP64 for > 4 GB files.
• Fixed INET_URIDecode to properly translate "+" (plus-sign) into " " (space) which is not done by the built-in .NET library. (suggested by Andy Krafft)
• Updated all RegEx functions to return NULL (or empty result set for the Table-Valued Functions, or 0 for RegEx_IsMatch) if ExpressionToValidate is passed in as NULL. This behavior mirrors more closely the built-in T-SQL string functions. [thanks to Andy Krafft and Jason Pierce]
• Updated RegEx_Split: Fixed output that was misreporting StartPos and EndPos fields when the "part" was empty (nothing between the delimiters).
• Updated all String functions to return NULL (or empty result set for the Table-Valued Functions, or 0 for scalar functions that return a Boolean / BIT) if input string (or SearchValue if applicable) is passed in as NULL. This behavior mirrors more closely the built-in T-SQL string functions.
• Added String_IsNumeric to mirror the built-in T-SQL ISNUMERIC() function but that can handle more than 8000 characters and more numeric formats.
• Added RegEx_CaptureGroup which returns just the specified captured group and not the entire capture as RegEx_Match does. (suggested by Jason Pierce)
• Added Twitter functions: GetFavorites, GetBlocks, BlockUser, UnBlockUser
• Updated File_GetDirectoryListing to skip the “System Volume Information” folder when doing recursive as that always caused an error.
• Added File function: GetFileInfo
• (BREAKING CHANGE ☹) Updated INET_GetWebPages: Added new field for ContentBinary that holds that Content data IF the data is Binary (in which case the regular Content field is NULL). Also added new input parameter for ContentDetection to either hard-code Binary vs Text or Auto-Detect. If using Auto-Detect, then if the ContentType starts with "text/" then the Content field is filled out and ContentBinary is NULL. (suggested by Mitch Schroeter)
• IMPORTANT: Updated all Twitter functions to authenticate against OAuth since Basic Auth is being shut down. This is a TEMPORARY fix which keeps the SQL# Twitter API the same for easy transition to the new authentication method. However, Twitter is requiring a full move to OAuth by October so a new version of SQL# will be released in the next two months that will be an API change for ALL Twitter functions. The API change will be that UserName and Password will no longer be passed in to each function but instead a ConsumerKey and ConsumerToken. Each SQL# user who is using the Twitter functions will have to create an application on Twitter which will give you the ConsumerKey and ConsumerToken. More details will be provided as that development occurs. Just be aware that EVERYONE using the SQL# Twitter functions will have to upgrade to the next version when it is released!!

Version 2.10.43 and 2.10.44 (January 20^th, 2010)

• Added Twitter functions: CreateFavorite and DestroyFavorite
• Updated Twitter functions to return UserID INT, RateLimit INT, RateLimitRemaining INT, and RateLimitReset DATETIME in the Result Set: GetFriendsTimeLine, GetMentions, GetMessages, GetPublicTimeLine, GetReplies, GetSentMessages, GetStatus, and GetUserTimeLine
• Updated Twitter functions to return RateLimit INT, RateLimitRemaining INT, RateLimitReset DATETIME, IsVerified BIT, CreatedOn DATETIME, UTCOffset INT, and NumberOfStatuses INT in the Result Set: FollowUser, GetFollowers, GetFriends, GetUser, UnFollowUser
• Added DB function: XOR which takes two BIT fields and does a logical Exclusive-OR on them.
• Updated RegEx_Split: returns StartPos and EndPos fields in the Result Set.
• Updated RegEx_Replace: “Count” input parameter now accepts -1 to mean “unlimited” replacements.
• Updated all RegEx functions to set the “RegularExpression” input parameter to be an NVARCHAR(MAX) instead of an NVARCHAR(4000).
• Updated String_SplitIntoFields: Added optional parameter for ColumnNames that is a comma-separated list of values that will be used to create the column names of the Procs result set. If not set it will default to the prior behavior of using FieldN where N is the field number starting with 1. (suggested by Olivier Moschkowitz)
• Updated Date_Extract: Added DatePart’s that are found in the SQL Server built-in function DATEPART to be comprehensive: Year, Quarter, Month, Day, DayOfYear, Weekday, Week, Hour, Minute, Second, and Millisecond. The DatePart of ISO_WEEK was previously available as ISOWEEK but is now also aliased as ISO_WEEK to match SQL Server’s DatePart name.
• Updated SQLsharp_Setup to auto-detect if a particular Assembly has been created and if not, Setup will not attempt to create the Procs and/or Functions contained in the missing assembly. This will allow each user to determine if they want to install the SgmlReader and/or Twitterizer and/or OS Assemblies. Thanks to Scott Prugh for requesting optional Assembly loading.
• (BREAKING CHANGE ☹) Updated INET_GetWebPages: Added ResponseUri NVARCHAR(4000) to Result Set. Also added Method NVARCHAR(10) and PostData NVARCHAR(MAX) input parameters.

Version 2.9.39 and 2.9.40 (November 1^st, 2009)

• Added Convert functions: HtmlToXml (suggested by Mitch Schroeter), UUDecode, and UUEncode
• Added Twitter functions: DestroyDirectMessage, FollowUser, GetFollowers, GetFriends, GetMentions, GetStatus, GetUser, UnFollowUser
• Added Date function: NthOccurrenceOfWeekday
• Updated File_SplitIntoFields: Changed SkipFirstRow BIT into RowsToSkip INT and fixed potential memory leak. The parameter change is non-breaking as the BIT values of 0 and 1 (representing to not skip any rows and to skip the first row respectively) directly map to the new behavior of how many rows to skip with 0 still meaning not to skip any and 1 meaning to skip 1 row which is the same result as SkipFirstRow = 1. (suggested by Andy Krafft)
• Updated Twitter functions so that StatusID is now a BIGINT instead of INT: DestroyStatus, GetFriendsTimeline, GetMessages, GetPublicTimeline, GetReplies, GetSentMessages, GetUserTimeline, SendDirectMessage, and  Update
• Added INET functions: URIGetInfo (suggested by Mitch Schroeter) and URIGetLeftPart
• Updated Type_HashTable: added AddItem(@InputKey NVARCHAR(4000), @InputValue NVARCHAR(4000)) method / function.
• Updated DB_BulkExport: Added @AppendFile BIT = 0, @RowsExported INT = -1 OUTPUT parameters. This should be a non-breaking change since the two new parameters have defaults.  Therefore, existing implementations do not need to change.
• Updated INET_FTPGetFile: Changed @OverwriteExistingFile BIT parameter to be @FileHandling TINYINT (2 = Incremental / Restart). This is a non-breaking change since the old parameter BIT values of 0 and 1 directly map (i.e. implicitly convert) to the new parameter datatype of TINYINT and which cause the same behavior. Therefore, existing implementations do not need to change. (suggested by Andy Krafft)
• (BREAKING CHANGE ☹) Updated INET_FTPGet and INET_FTPGetBinary: Added @ContentOffset BIGINT parameter to support Incremental downloads / resuming. (suggested by Andy Krafft)
• (BREAKING CHANGE ☹) Updated INET_GetWebPages: Added four new fields to the result set (IsFromCache, LastModified, StatusCode, StatusDescription) and added three new input parameters (@MaximumAutomaticRedirections, @Timeout, @MaximumResponseHeadersLength, @CustomHeaders)
• IMPORTANT NOTE: Deprecated Twitter_DestroyMessage and replaced with Twitter_DestroyStatus. It is just a rename as the functionality is the same. Currently DestroyMessage points to DestroyStatus, however, please convert all references to DestroyMessage as it will be removed in the next version.

Version 2.8.32 and 2.8.33 (June 6^th, 2009)

• Minor fixes in SQLsharp_SetUp for INET_URIEncodeData and FILE_* Table-Valued Functions.
• Minor fix in DB_ForEach and addition of Replacement Tags:
  {SQL#Schema} and {SQL#FullTableName}
• Added ProcessName parameter to OS_ProcessKill

Version 2.8.28 and 2.8.29 / 2.8.30 and 2.8.31(May 27^th and 31^st, 2009)

• Updated SQL# Installer to not cause the “file has an extremely long line” warning message when opening in Management Studio (SSMS)
• Renamed main Assembly from [SQLsharp] to [SQL#].
• Added two new Assemblies: [SQL#.OS] and [SQL#.Twitterizer]
• Fixed potential memory leak in INET_GetWebPages
• Fixed output of Table-Valued Functions to be properly streaming
• Increased width of NVARCHAR fields in the result sets of File functions
• Fixed RegEx functions to not error if the ExpressionToValidate is an empty string
• Compiled RegEx patterns in Util_IsValid* functions for faster execution
• Updated SQLsharp_Setup, SQLsharp_Uninstall, and the installation scripts to allow SQL# to be installed into a user-defined Schema and then handle being uninstalled from that Schema.  In order to install into a Schema other than “SQL#” just change the value of the @SQLsharpSchema variable.
• Added RegExOptionsList parameter to all RegEx Functions
• Added TrapErrorInline BIT parameter to INET_GetWebPages Function to control whether HTTP errors (e.g. 404, 500, etc.) throw an exception or get returned in the result set.  Please note that this is a Function signature change that breaks existing uses of the Function since existing calls will not have the new parameter.  Passing in NULL (or 0) as the 3^rd parameter will cause the Function to work the same as it did previously.
• Added optional @AssemblyName NVARCHAR(4000) input parameter to SQLsharp_SetSecurity so that the various SQL# Assemblies can be dealt with individually.
• Added DB Procs: BulkCopy (suggested and tested by DM Unseen [Martijn Evers]) and ForEach
• Added INET Function: URIEncodeData
• Added new OS group (SQL#.OS Assembly)
• Added OS Functions: EventLogRead, EventLogWrite, ProcessStart, ProcessGetInfo, ProcessKill, GenerateTone, MachineName, and Uptime
• Added new Twitter group (SQL#.Twitterizer Assembly)
• Added Twitter Functions (via Twitterizer library): Update, DestroyMessage, SendDirectMessage, GetSentMessages, GetMessages, GetFriendsTimeline, GetPublicTimeline, GetUserTimeline, and GetReplies

Version 2.7.24 and 2.7.25 (August 5^th, 2008)

• Added INet functions: FTPGetBinary, FTPGetFile, FTPPutBinary, FTPPutFile, HTMLDecode, HTMLEncode, URIDecode, and URIEncode
• Added Date functions: Age, Extract, and Truncate
• Updated DB function HTMLExport:
• Translate {SQL#Column} into the column name
• Added “EncodeHTML” option
• Stopped single-quotes from being escaped to double single-quotes
• Updated DB procedure DumpData: Added parameters for Disable / Re-enable ALL Constraints and/or Triggers on each table
• Updated String function NewLine: Changed <br/> to <br /> for @EOLType = XHTML
• Updated SQLsharp procedure SetSecurity: Fixed sending in parameter of 0 when not in a DB named [SQL#]
• Updated SQLsharp procedure Update:
• Fixed error that removed SQL# Schema when calling SQLsharp_Uninstall but did not re-add it
• Added @ForceUpdate parameter; and function now checks for newer version of SQL# and will error if no newer version and @ForceUpdate is false / 0 or unset

Version 2.6.22 and 2.6.23 (May 18^th, 2008)

• Updated DB function DumpData:
• fixed handling of BINARY, VARBINARY, and IMAGE datatypes
• changed direct query output data-type from TEXT to NVARCHAR(MAX)
• added optional parameter for @LinkedServer
• added optional parameter for @FileEncoding that supports: ASCII, UTF8, Unicode, UnicodeBigEndian, and UTF32.
• Added DB functions: BulkExport and HTMLExport
• Opened up INET functions AddressToNumber, NumberToAddress, and IsValidIPAddress to Free Version of SQL#
• Added Util functions: IsValidCheckRoutingNumber, and IsValidPostalCode
• Updated SQLsharp function GrantPermissions: added optional second parameter @SQLsharpSchema
• Added File function: CurrentEncoding
• Updated File function WriteFile: @FileEncoding parameter now accepts: ASCII, UTF8, Unicode, UnicodeBigEndian, and UTF32
• Updated File function WriteFileBinary: @FileEncoding parameter now accepts: ASCII, UTF8, Unicode, UnicodeBigEndian, and UTF32

Version 2.5.20 and 2.5.21 (November 18^th, 2007)

• Added File functions: GZip, GUnzip, ChangeEncoding, SplitIntoFields
• Added INET functions: IsValidIPAddress, AddressToNumber, NumberToAddress
• Added new Convert grouping.
• Added Convert functions: ToBase64, FromBase64, ROT13, BinaryToHexString, HexStringToBinary
• Added Date functions: ToUNIXTime, FromUNIXTime
• Added String function: NthIndexOf, Cut, SplitIntoFields
• Added Utility functions: CRC32, Hash(MD5 | SHA1 | SHA256 | SHA384 | SHA512)

Version 2.4.18 and 2.4.19 (October 14^th, 2007)

• Added File Functions: GetFileBinary, WriteFileBinary, GetDriveInfo, Move, CreateDirectory, DeleteDirectory, Encrypt, Decrypt, GetDirectoryListing, Delete, Copy, DeleteMultiple, CopyMultiple, MoveMultiple
• Added new LookUp grouping
• Added LookUp Functions: GetCountryInfo, GetStateInfo

Version 2.3.16 and 2.3.17 (September 10^th, 2007)

• Fixed Constructor method for all three User-Defined Types (FloatArray, HashTable, and NVarcharArray); allow for empty string (‘’) to be passed in to initialize the Type as empty.
• Fixed Math_CompoundAmortizationSchedule: adjusted final payment calculation and minor issues with rounding

Version 2.3.14 and 2.3.15 (September 5^th, 2007)

• Added Date Functions: IsBusinessDay, FormatTimeSpan
• Updated Date_BusinessDays: added more options for ExcludeDaysMask (Friday, Good Friday [Gregorian Calendar], Easter [Gregorian Calendar], Good Friday [Julian Calendar], Easter [Julian Calendar], Thanksgiving [CANADA], Thanksgiving [CANADA – day 2, Friday before])
• Added Math Functions: Cosh, Sinh, Tanh
• Added new DB grouping
• Added DB Function: DumpData
• Added compression to: Agg_Join

Version 2.2.12 and 2.2.13 (August 19^th, 2007)

• Added Date Functions: BusinessDays, DaysInMonth, DaysLeftInYear, FirstDayOfMonth, FullDateString, FullTimeString, IsLeapYear, LastDayOfMonth

Version 2.1.10 and 2.1.11 (July 16^th, 2007)

• Added Util Functions: IsValidCC, IsValidSSN
• Added String Functions: Count, Newline
• Added User-Defined Aggregate: Random
• Added compression to: Agg_Median, Type_FloatArray, Type_NVarcharArray, Type_HashTable

Version 2.0.8 and 2.0.9 (March 18^th, 2007)

• Enhanced Installation script
• Added SQL# Schema
• Fixed result column names in Util_Generate* functions and RegEx_* functions
• Enhanced error-handling and success output of SQLsharp_SetSecurity
• Added Optimizer Hints (IsDeterministic and IsPrecise) to all functions
• Removed UseBinaryMode option from INET_FTPGet and INET_FTPPut
• Added File functions: GetFile, GetRandomFileName, GetTempPath, PathExists, WriteFile
• Added User-Defined Aggregates: GeometricAvg, Join, Median, RootMeanSqr
• Added User-Defined Types: DoubleArray, HashTable, NVarcharArray

Version 1.5.6 and 1.5.7 (February, 16^th, 2007)

• Fixed installation / setup so that it completes successfully ;-)
• Changed prefix for Miscellaneous functions to be Util_ instead of Phnx_
• Added Math_CompoundAmortizationSchedule (BETA), Util_GZip, Util_GUnzip, Util_Deflate, Util_Inflate

Version 1.1.5 (October 20^th, 2006)

• Paid-for Version
• Includes all functions
• Same as Version 1.1.4 except it includes all INET functions and SQLsharp_Update

Version 1.1.4 (October 20^th, 2006)

• Free Version
• Removed: all INET functions and SQLsharp_Update
• Added: Math_RandomRange
• Changed:
• Bug in Phnx_ToWords returned Negative Zero for -1
• Added Lower and Upper bounds checking in Phnx_ToWords
• Changed return datatype in Math_IsPrime to BIT from INT
• Added StepTypes of Quarter and Week to Phnx_GenerateDateTimes and Phnx_GenerateDateTimeRange
• Added abbreviations for StepTypes as paralleled in Books Online under DATEADD and DATEDIFF functions (ex: year = yyyy, yy)

Version 1.1.3 (not released)

• Added: INET_FTPDo, INET_FTPGet (BETA), INET_FTPPut (BETA), INET_GetIPAddress, INET_GetHostName

Version 1.0.2 (June 25^th, 2006 – Initial Release)

• String_Combine, String_Contains, String_EndsWith, String_Equals, String_IndexOf, String_InitCap, String_LastIndexOf, String_PadLeft, String_PadRight, String_Split, String_StartsWith, String_Trim, String_WordWrap
• RegEx_IsMatch, RegEx_Match, RegEx_Matches, RegEx_Replace, RegEx_Split
• Math_Constants (30 physics constants), Math_Convert (22 measurement conversions), Math_Factorial, Math_IsPrime
• INET_GetWebPages, INET_Ping, INET_PingTime
• Phnx_GenerateDateRange, Phnx_GenerateDates, Phnx_GenerateFloatRange, Phnx_GenerateFloats, Phnx_GenerateIntRange, Phnx_GenerateInts, Phnx_ToWords
• SQLsharp_GrantPermissions, SQLsharp_Help, SQLsharp_IsUpdateAvailable, SQLsharp_SetSecurity, SQLsharp_Setup, SQLsharp_Uninstall, SQLsharp_Update, SQLsharp_Version, SQLsharp_WebSite