Case Sensitivity in XML Extraction (Part II)

    In my previous post I showed an elegant way to extract case sensitive data from an XML, but what if we'd like to use that method to extract all case sensitive data?

Here is how we can do it. 

At first here is our test setup:

DECLARE @xml XML = '
<ROOT>
    <ELEMENT NAME="ElementName" VALUE="PascalCase" />
    <ELEMENT NAME="ELEMENTNAME" VALUE="UPPERCASE" />
    <ELEMENT NAME="elementname" VALUE="LOWERCASE" />
</ROOT>';

SELECT @xml;

Here is our query, which would extract ONLY a single value:

DECLARE @RequestElement SYSNAME = 'ElementName';

SELECT [Case] = 'Case sensitive via parameter'
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[@NAME=sql:variable("@RequestElement")]') AS X(C)

It returns a single row for only exact match for "ElementName" value:

Now I will change that query to disregard case sensitivity:

DECLARE @RequestElement SYSNAME = 'ElementName';
DECLARE @RequestElement_ci SYSNAME = UPPER(@RequestElement);

SELECT [Case] = 'Case-insensitive parameter'
    , [@RequestElement] = @RequestElement
    , [@RequestElement_ci] = @RequestElement_ci
    , ElementName = C.value('@NAME', 'VARCHAR(100)')
    , ElementValue = C.value('@VALUE', 'VARCHAR(100)')
FROM @xml.nodes('//ELEMENT[upper-case(@NAME)=sql:variable("@RequestElement_ci")]') 
AS X(C)

In order to extract all values, disregarding of case sensitivity or collation we just switched both values to an Upper case.

Caveats of using an Expression for SQL Script in SSRS.

Why: Most of the time, when you want a flexibility of your SQL query you can use parameterization. However there might be a situation when you'd need to build a dynamic query. In my case I used SQL query within an expression to feed it to multiple data sources targeting different servers with the exact same query.

DataSet creation: Creation of a simple dataset.

I've created a sample dataset with a sample query:

-- That is a sample query
select top 10 * 
from sys.messages

After we created the dataset we can create a Tablix in our report and test it:

Creation of an Expression: 

1. Create a new parameter called "SQLExpression"

2. Go to the "Default Values" tab select "Specify values" and choose "fx" box

3. Specify Expression value:

Assign Expression to the DataSet: Return to the Sample DataSet and Specify newly created parameter as the source for DataSet Expression.

Within the expression replace the query by the parameter:

Save changes and try your report.

If  you've done exactly like I did you get an empty report.

Troubleshooting: If you specified your parameter as "Visible" you might notice that report processor aggregated all 3 rows of our query into the one, making it a single comment

Other cases: My case was very easy, but in the most of the cases you'll usually get a weird error message for your query, while the query itself runs fine in SSMS.

Lessons Learned: When we use SQL Query Expressions we have to follow these rules:

1. Use only DOUBLE comment or do not use comments at all.

2. Put at least one space or a tabulation before the very first symbol on each line.

3. Use semicolon symbol to separate multiple SQL instructions.

4. Do not use "GO" command.

The Fix: Change the default value for "SQLExpression" parameter to following query: 

/* That is the Fixed SQL Query
 refurbished for SSRS expression usage*/
 DECLARE @MessageID INT = 101;
 SELECT *
 FROM sys.messages
 WHERE message_id = @MessageID;

Save the parameter and re-run the report.

Enjoy the result of your SQL Query Expression:

Conclusion: Use of SQL Expressions is very easy if you fallow those 4 rules. 

Please let me know if you hit any other situation in question and I'll add it for others to avoid.

Having "NaN" value for REAL and FLOAT producing severe error.

Attention: DO NOT RUN THIS IN PRODUCTION!!!

That blog post is about the real error in SQL Server.
It was reproduced on SQL Server 2016 CU10 and on SQL Server 2019 RTM.

Database in trouble has a table with FLOAT column. It's Front-End application verifies user's input and inserts the data into that column using TRY_PARSE function.
The developer's intention was that any "Not-a-Numeric" or "Out-of-Range" values will be automatically converted to NULL and it will be for user's discretion to verify and fix these values.

However, one of the application users was very educated and instead of empty space, NULL or any other bad not numeric value the user supplied data with value of "NaN" for empty cells, which simply stands for "Not a Numeric".
That action caused a database corruption!

Here is how to reproduce it.

At first will create a table:

USE tempdb; GO DROP TABLE IF EXISTS tbl_Real_Float_Test; GO CREATE TABLE tbl_Real_Float_Test (ID INT IDENTITY(1,1), R REAL, F FLOAT); GO

Will insert some bad data:

INSERT INTO tbl_Real_Float_Test(R,F) SELECT TRY_PARSE('0' as REAL), TRY_PARSE('NaN' as FLOAT); GO INSERT INTO tbl_Real_Float_Test(R,F) SELECT TRY_PARSE('NaN' as REAL), TRY_PARSE('0' as FLOAT); GO

Now will try to query it:

SELECT R FROM tbl_Real_Float_Test WHERE ID = 1; GO SELECT F FROM tbl_Real_Float_Test WHERE ID = 2; GO SELECT ID FROM tbl_Real_Float_Test WHERE R = 0 OR F = 0; GO

First two queries will return zeroes. The third one will produce an error:

It also will write that error into the ERRORLOG:

Error: 9100, Severity: 23, State: 2.  Possible index corruption detected. Run DBCC CHECKDB.

For the easiest reproduction of the destructive behavior of "NaN" you can run following statement:

DECLARE @r REAL = PARSE('NaN' as REAL); PRINT 'We can Assign "NaN" to a variable, but we cannot see it:'; SELECT @r;

You will get an error of:

An error occurred while executing batch. Error message is: Arithmetic Overflow.

Interestingly enough the TRY_PARSE command allows "Culture" specification, which, in some cases, works very well:

DECLARE @Russian REAL = TRY_PARSE('NaN' as REAL USING 'Ru-RU'); DECLARE @US_Real REAL = TRY_PARSE('NaN' as REAL USING 'en-US'); SELECT Russian_Real = @Russian; SELECT US_Real = @US_Real;

As you can see, the Russian culture treats the TRY_PARSE command correctly and produces "NULL" instead of the error. Besides of Russian it also works for Finnish, Swedish, Arabic, Traditional Chinese and undocumented "sr-Latn-CS".
No any other cultures treat "NaN" as it expected.

If you can reproduce that bug you can help Microsoft to fix it by voting for that bug:  https://feedback.azure.com/forums/908035-sql-server/suggestions/39278515-try-parse-and-parse-produce-an-error-converting-n

Again: DO NOT RUN IT IN PRODUCTION and do not forget to drop your test table!

T-SQL Bitwise Shifting

Unfortunately T-SQL supports only basic logical bitwise operations: AND, OR, XOR & NOT.
However, sometimes you need to do some "Rocket Science", or better say "Neurosurgery" when you need to shift value's internal bits.

When I hit that problem I came up with very easy solution with creation of a little function:

USE tempdb; GO CREATE FUNCTION dbo.fn_BitShift( @Num INT , @Shift SMALLINT    /* Positive - Right Shift, Negative - Left Shift */ , @Circular BIT      /* 0 - Not Circular Shift, 1 - Circular Shift */ ) RETURNS INT AS BEGIN        DECLARE @BigNum BIGINT = @Num;        WHILE @Shift != 0               SELECT @BigNum = CASE WHEN SIGN(@Shift) > 0                      THEN (@BigNum - (@BigNum & 1)) / 2                            + 0x80000000 * (@BigNum & 1) * @Circular                      ELSE (@BigNum - (@BigNum & 0x80000000)) * 2                            + SIGN(@BigNum & 0x80000000) * @Circular                      END, @Shift -= SIGN(@Shift);        RETURN CAST(SUBSTRING(CAST(@BigNum as BINARY(8)),5,4) as INT); END GO

How does it work.

At first, here is some theory in the beginning: "Bit shifts" & "Circular shift"

Would say we need to shift bits one time in the right direction within our integer number "4".
The binary representation of integer "4" is "00000000 00000000 00000000 00000100".
The Right shift will move digit "1" on one space right and we get following binary number: "00000000 00000000 00000000 00000010", which is integer "2".

Here is an example of doing that via the function:

/*------------------------ SELECT dbo.fn_BitShift (4, 1, 0); ------------------------*/ ----------- 2

The first parameter is "@Num", which is Integer "4".

The second parameter is number of shifts we want to perform. In our case it is "1".
Also, the Sign of the second parameter indicates the shift direction.
Positive values shifting our integer right and negative values shifting it left.
If we want shift our integer "4" two digits left we specify the second parameter as "-2" and will get "00000000 00000000 00000000 00010000", which is number "16".

Here is the example:

/*------------------------ SELECT dbo.fn_BitShift (4, -2, 0); ------------------------*/ ----------- 16

The third variable indicates if we want to do our shift circular. Would say we want to shift our integer "4" three times. with regular shift result will be zero. With circular, the shifted digit will not be lost, but will move in the front and we will get binary number like this: "10000000 00000000 00000000 00000000", which is "-2147483648" in decimals.

Here is the example:

/*------------------------ SELECT dbo.fn_BitShift (4, 3, 0), dbo.fn_BitShift (4, 3, 1) ------------------------*/              ----------- ----------- 0           -2147483648

Because integers are stored within 32 bits and we will try to shift a number 32 times without circulation we will just loose that number. However, with circulation a number will make a whole circle and become itself:

Here is a sample:

/*------------------------ SELECT dbo.fn_BitShift (8, 32, 1), dbo.fn_BitShift (2, -32, 1); ------------------------*/              ----------- ----------- 8           2

Integers "8" & "2" were shifted around in different directions with returning to the same numbers.

If we try to use shift number bigger than 32 it will just continue to shift further. For instance shifting on 33 positions is equivalent to shift on only one position.

Here is a sample:

SELECT dbo.fn_BitShift (8, 33, 1), dbo.fn_BitShift (8, 1, 1) , dbo.fn_BitShift (2, -33, 1), dbo.fn_BitShift (2, -1, 1);

Here are my testing samples:

/*------------------------ SELECT dbo.fn_BitShift (239623632, -32, 1) , dbo.fn_BitShift (239623632, -27, 0) , dbo.fn_BitShift (239623632, -27, 1) , dbo.fn_BitShift (239623632, 32, 1) , dbo.fn_BitShift (239623632, 25, 0) , dbo.fn_BitShift (4, 31, 1) , dbo.fn_BitShift (4, -31, 1) , dbo.fn_BitShift (4, -29, 0); ------------------------*/                                                                                      ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 239623632   -2147483648 -2139995410 239623632   7           8           2           -2147483648

The limitation for that shifting function is only 32 bits of an integer number.

Handling Forbidden XML characters in SQL Server

That is very known issue that SQL Server's XML does not accept characters "&", "<" and ">".
There are two more forbidden XML characters " ' " and " " " (single and double quotes), but SQL Server mostly accept them.

The common solution is to replace these characters by their codes.
Would say we have a silly sentence: "Anne & Robin collect > "berries" than Jane & Kevin, but < than Ivan & Lucy."

If we try to replace forbidden characters by their codes we get something like this:

It does not look like readable text.

So, here is the solution:

In case you do not care about special character coding and care ONLY about text visual representation you can replace forbidden symbols by their siblings from other Unicode pages:
"&" - "＆" (65286)
"<" - "＜" (65308)
">" - "＞" (65310)
" ' " - " ʹ " (697)
" " " - " ʺ " (698)

Then we can do replacement before converting to XML like this:

DECLARE @MyText VARCHAR(1000) = 'Anne & Robin collect > "berries" than Jane & Kevin, but < than Ivan & Lucy.'; PRINT 'My Text: "' + @MyText + '";'; SET @MyText = REPLACE(REPLACE(REPLACE(REPLACE(@MyText ,'&','&#65286;'),'<','&#65308;'),'>','&#65310;'),'"','&#698;') ; PRINT 'My Converted XML: "' + CAST(CAST('<MyXML>' + @MyText + '</MyXML>' as XML) as VARCHAR(MAX)) + '";'; SELECT CAST('<MyXML>' + @MyText + '</MyXML>' as XML);

The XML results will be like this:

If you try to open that XML in SSMS you'll see it clear:

And if you try to convert it back to VARCHAR you'll get following:

Note: when you convert XML back to VARCHAR, SQL Server will convert forbidden symbols back to the default code page, which might be very convenient.

Caveats:

You might try to replace problematic symbols directly, but in this case you would have to use NVARCHAR data type instead of VARCHAR to preserve Unicode symbols from being auto-converted back to the default code page:

DECLARE @MyText NVARCHAR(1000) = 'Anne & Robin collect > "berries" than Jane & Kevin, but < than Ivan & Lucy.'; SET @MyText =        REPLACE(REPLACE(REPLACE(REPLACE(@MyText,'&',N'＆'),'<',N'＜'),'>',N'＞'),'"',N'ʺ'); SELECT @MyText FOR XML PATH('MyXML');

In some of the cases you'd be forced to use direct Unicode characters to avoid placement of escape codes. I'd recommend use of NCHAR command to convert your symbols, then you won't loose "Unicode siblings" if you decide to store your SQL Script in a file:

DECLARE @MyText NVARCHAR(1000) = 'Anne & Robin collect > "berries" than Jane & Kevin, but < than Ivan & Lucy.'; SET @MyText = REPLACE(REPLACE(REPLACE(REPLACE(@MyText ,'&',NCHAR(65286)),'<',NCHAR(65308)),'>',NCHAR(65310)),'"',NCHAR(698)) ; SELECT 1 as Tag, Null as Parent,        @MyText as [MyXML!1!MyText] FOR XML EXPLICIT;

Here are the results of that code.

Hope you can use that solution in your work.
Please let me know if you hit any other unexpected issues related to XML symbols, I'd be glad to include more solutions in my blog.

SQL Server 2019. Use of UTF8 vs Unicode.

SQL Server 2019 is life and it is time to play around and discover some interesting features of it.

Will start from UTF8 and how it is better than UNICODE.

Here is a simple script to demonstrate abilities of newest SQL Server UTF8 feature:

GO USE tempdb GO DROP TABLE IF EXISTS dbo.tbl_Use_Of_UTF8 GO CREATE TABLE dbo.tbl_Use_Of_UTF8 (        ID INT IDENTITY (10,10)        , UnicodeColumn NVARCHAR(4000)        , UTF8Column VARCHAR(4000) collate LATIN1_GENERAL_100_CI_AS_SC_UTF8        , UTF8_Ukr_Column VARCHAR(4000) collate Ukrainian_100_CI_AS_SC_UTF8 ) GO INSERT dbo.tbl_Use_Of_UTF8 (UnicodeColumn) VALUES (N'༈༉ШЧݪݫݬݭܕܖܗܘܙऔकखഈ㔃㔄刀刁⠴⠵ȀȁȂȃꔂꔃՈՉ') ,(N'abcde༈༉ШЧݪݫݬݭܕܖܗܘܙऔकखഈ㔃㔄刀刁⠴⠵ȀȁȂȃꔂꔃՈՉ') ,(N'абвгдеАБВГДЕ') ,(N'abcdefghABCDEFGHабвгдеАБВГДЕ') ,(N'abcdefghABCDEFGH'); GO UPDATE tbl_Use_Of_UTF8 SET UTF8Column = UnicodeColumn, UTF8_Ukr_Column = UnicodeColumn; GO SELECT Chars = LEN(UnicodeColumn)        , UnicodeSize = DATALENGTH(UnicodeColumn)        , UTF_Size = DATALENGTH(UTF8Column)        , Ukr_Size = DATALENGTH(UTF8_Ukr_Column)        , UTF8Column, UTF8_Ukr_Column FROM dbo.tbl_Use_Of_UTF8; GO

The result will be like this:

From that result we can make following observations:
1. Use of multiple languages in UTF8 column takes more space than Unicode column. (First and Second rows)
2. Use of single non-Latin language in UTF8 field does not provide any size reduction at all. (Third row)
3. Use of a combination of Latin and a single non-Latin language bring some size reduction.(Fourth row)
4. Use of only Latin characters in UTF8 column gives us 50% size reduction over Unicode.

Conclusion

UTF8 is not suitable to store purely non-Latin languages.
However, it can be extremely useful when you store only Latin characters, but have in mind a possibility of storing small number of records, which contain non-Latin characters.

The most useful case for that can be database's "Address" field to store names of streets and cities in the US.
99.9% of which, will be in Latin, but names like "Doña Ana", "Lindström" or "Utqiaġvik" would require use of Unicode. With UTF8 you can save exactly 50% of your disk space and eventually have some performance improvements.