Wednesday, February 12, 2020

Use of Uniqueidentifier in Persisted calculated column.

This post is for you in case you decide to use Uniqueidentifier column in your table and then you think about including it into a Persisted calculated column.
You also might see it useful if you like weird or funny SQL Server behavior.

At first, here is the SQL Server version I have that problem on.
After a while Microsoft might fix that bug.
Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) 

To demonstrate the problem in the most simple way you can run following script:
DROP TABLE IF EXISTS #tbl_Error_Test;
GO
CREATE TABLE #tbl_Error_Test(
       GuidOne UNIQUEIDENTIFIER,
       GuidTwo as CAST(GuidOne as CHAR(36)) PERSISTED
  );
GO
INSERT INTO #tbl_Error_Test(GuidOne) VALUES (NewID()), (NewID());
GO
SELECT * FROM #tbl_Error_Test;

GO

It will return something like this:

As you can see, columns GuidOne and GuidTwo are different.
Moreover, if you run following command you will get very unpleasant error:
DBCC CHECKTABLE('#tbl_Error_Test') WITH NO_INFOMSGS, EXTENDED_LOGICAL_CHECKS;

Msg 2537, Level 16, State 106, Line 152
Table error: object ID -1485216446, index ID 0, partition ID 6052840780930088960, alloc unit ID 2738196559872000000 (type In-row data), page (4:1079768), row 0. The record check (valid computed column) failed. The values are 2 and 0.
Msg 2537, Level 16, State 106, Line 152
Table error: object ID -1485216446, index ID 0, partition ID 6052840780930088960, alloc unit ID 2738196559872000000 (type In-row data), page (4:1079768), row 1. The record check (valid computed column) failed. The values are 2 and 0.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table '#tbl_Error_Test_____________________________________________________________________________________________________000000001926' (object ID -1485216446).

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (tempdb.dbo.#tbl_Error_Test_____________________________________________________________________________________________________000000001926).

If you try to check what the correct value should be, it won't make any good:
SELECT GuidOne, GuidThree = CAST(GuidOne as CHAR(36))
FROM #tbl_Error_Test;


The SQL Server is smart enough to recognize same function and instead of calculating the value it will return you the value stored in the table.

You can only do a trick if you change the function and convert Guid to 37 chars, instead of 36:
SELECT GuidOne, GuidFour = CAST(GuidOne as CHAR(37))

FROM #tbl_Error_Test;

Now you got the right result, but how to fix it in the table?

It is not easy, but possible. You just have to replace Guid column by itself, but from another table:
UPDATE G1 SET GuidOne = G2.GuidOne
FROM #tbl_Error_Test as G1
INNER JOIN #tbl_Error_Test as G2
       ON G1.GuidOne = G2.GuidOne;
GO
SELECT * FROM #tbl_Error_Test;

GO


It is fixed now and it also fixes DBCC CHECKDB error.

If you see the same behavior on your SQL Server you can vote for that bug to be fixed at Microsoft site:
https://feedback.azure.com/forums/908035-sql-server/suggestions/39694663-use-of-guid-column-in-persisted-calculated-column

Friday, December 20, 2019

Recovering from Severe Database corruption error 9100

In my previous post I outlined very dangerous SQL Server problem, caused by usually not very harmful commands "PARSE" and "TRY_PARSE": Having "NaN" value for REAL and FLOAT producing severe error.

This post will be about a major issue it causes and on how to fight it.

Will start from generating the problem.

Attention: Do not run that in production!!!
Here is a script to generate the issue:
DROP TABLE IF EXISTS tbl_Real_Test;
GO
CREATE TABLE tbl_Real_Test (ID UNIQUEIDENTIFIER, R REAL, F FLOAT);
GO
INSERT INTO tbl_Real_Test (ID, R, F)
SELECT NewID(), TRY_PARSE(r as REAL), TRY_PARSE(f as FLOAT) 
FROM (VALUES
       ('0','434534776544')
       ,('0.0000323', 'NaN')
       ,('3.3881317890172E+17','')
       ,('','000000000000x000000000000')
       ,('NULL','ABCDEFG')
       ,('Null','--------------------')
       ,('null','!!!!!!!!!!!!!!!!!!!!')
       ,('ABC','345435467655665676545')
       ,('NaN','5.3881317890172E+47')
       ,('Nan','0.0000000000345')
       ,('nan','434.34543543')
) as R(r,f);
GO
SELECT * FROM tbl_Real_Test
WHERE R Is not Null ;

GO

That script will generate following error:
Msg 9100, Level 23, State 2, Line 57
Possible index corruption detected. Run DBCC CHECKDB.

Troubleshooting:

You might try to run "DBCC CHECKDB" against that database like this:
DBCC CHECKDB('Slava_Temp_Test');

DBCC CHECKDB resulted couple of errors and besides of that it made an error Dump into an ERRORLOG file and created four other DUMP files:
Msg 2570, Level 16, State 3, Line 62
Page (1:16200), slot 1 in object ID 2002106173, index ID 0, partition ID 72057594042843136, alloc unit ID 72057594048479232 (type "In-row data"). Column "F" value is out of range for data type "float".  Update column to a legal value.
Msg 2570, Level 16, State 3, Line 62
Page (1:16200), slot 8 in object ID 2002106173, index ID 0, partition ID 72057594042843136, alloc unit ID 72057594048479232 (type "In-row data"). Column "R" value is out of range for data type "real".  Update column to a legal value.
DBCC results for 'tbl_Real_Test'.
There are 9 rows in 1 pages for object "tbl_Real_Test".

CHECKDB found 0 allocation errors and 2 consistency errors in table 'tbl_Real_Test' (object ID 2002106173).

Fixing attempt:

To fix the issue you might try following commands
DBCC CHECKDB ('Slava_Temp_Test' , REPAIR_REBUILD) WITH ALL_ERRORMSGS;
GO
DBCC CHECKDB ('Slava_Temp_Test' , REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

GO

However, in order to perform the fix DBCC CHECKDB requires the database to be in a single user mode:
Msg 7919, Level 16, State 3, Line 141
Repair statement not processed. Database needs to be in single user mode.

The problem is that it might be not possible to switch database into a single user mode, because it is part of an Availability Group and experiencing very heavy user activity. The use of conventional SQL Server tool would cause disruption in Production for who knows for how much time.

Solution:

To solve that problem we do not have to switch database in single mode or restore it from a previous backup to eliminate the issue. You just run an update script over your problematic table and columns to replace bogus values, which SQL Server can't interpret correctly, by NULLs:
UPDATE tbl_Real_Test
SET R = CASE CAST(R as BINARY(4))
       WHEN CAST( PARSE('NaN' as REAL) as BINARY(4))
       THEN Null Else R END
       , F = CASE CAST(F as BINARY(8))
       WHEN CAST( PARSE('NaN' as FLOAT) as BINARY(8))
       THEN Null Else F END;
GO
SELECT * FROM tbl_Real_Test
WHERE R Is not Null OR F Is not Null;

GO

The result of this query should be like this:

Disclaimer and Advertisement:

There are probably a lot of other cases, which might cause "Database corruption error 9100" and if that particular solution does not apply to your environment you can contact me for the resolution.


Tuesday, December 17, 2019

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!

Monday, December 16, 2019

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.