恢复SQLSERVER被误删除的数据 曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from
曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据
这里有一篇文章做到了，不过似乎不是所有的数据类型都支持
以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/
在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”
现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据
（注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）
让我来用demo来解释一下我是怎么做到的
<span>USE</span><span> master
</span><span>GO</span>
<span>--</span><span>创建数据库</span>
<span>CREATE</span> <span>DATABASE</span><span> test
</span><span>GO</span>
<span>USE</span> <span>[</span><span>test</span><span>]</span>
<span>GO</span>
<span>--</span><span>创建表</span>
<span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span><span>(
</span><span>[</span><span>id</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>NOT</span> <span>NULL</span><span>,
</span><span>[</span><span>NAME</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>200</span>) <span>NULL</span><span>
) </span><span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span>
<span>GO</span>
<span>--</span><span>插入测试数据</span>
<span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span><span>
( </span><span>[</span><span>NAME</span><span>]</span><span> )
</span><span>SELECT</span> <span>'</span><span>你好</span><span>'</span>
<span>GO</span>
<span>--</span><span>删除数据</span>
<span>Delete</span> <span>from</span><span> aa
</span><span>Go</span>
<span>--</span><span>验证数据是否已经删除</span>
<span>Select</span> <span>*</span> <span>from</span><span> aa
</span><span>Go</span>
现在你需要创建一个存储过程来恢复你的数据
<span>--</span><span> Script Name: Recover_Deleted_Data_Proc</span><span>
--</span><span> Script Type : Recovery Procedure </span><span>
--</span><span> Develop By: Muhammad Imran</span><span>
--</span><span> Date Created: 15 Oct 2011</span><span>
--</span><span> Modify Date: 22 Aug 2012</span><span>
--</span><span> Version : 3.1</span><span>
--</span><span> Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS.</span>
<span>CREATE</span> <span>PROCEDURE</span><span> Recover_Deleted_Data_Proc
</span><span>@Database_Name</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
</span><span>@SchemaName_n_TableName</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
</span><span>@Date_From</span> <span>DATETIME</span> <span>=</span> <span>'</span><span>1900/01/01</span><span>'</span><span> ,
</span><span>@Date_To</span> <span>DATETIME</span> <span>=</span> <span>'</span><span>9999/12/31</span><span>'</span>
<span>AS</span>
<span>DECLARE</span> <span>@RowLogContents</span> <span>VARBINARY</span>(<span>8000</span><span>)
</span><span>DECLARE</span> <span>@TransactionID</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@AllocUnitID</span> <span>BIGINT</span>
<span>DECLARE</span> <span>@AllocUnitName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
</span><span>DECLARE</span> <span>@Compatibility_Level</span> <span>INT</span>
<span>SELECT</span> <span>@Compatibility_Level</span> <span>=</span><span> dtb.compatibility_level
</span><span>FROM</span> master.sys.databases <span>AS</span><span> dtb
</span><span>WHERE</span> dtb.name <span>=</span> <span>@Database_Name</span>
<span>IF</span> <span>ISNULL</span>(<span>@Compatibility_Level</span>, <span>0</span>) <span> <span>80</span>
<span>BEGIN</span>
<span>RAISERROR</span>(<span>'</span><span>The compatibility level should be equal to or greater SQL SERVER 2005 (90)</span><span>'</span>,<span>16</span>,<span>1</span><span>)
</span><span>RETURN</span>
<span>END</span>
<span>IF</span> ( <span>SELECT</span> <span>COUNT</span>(<span>*</span><span>)
</span><span>FROM</span><span> INFORMATION_SCHEMA.TABLES
</span><span>WHERE</span> <span>[</span><span>TABLE_SCHEMA</span><span>]</span> <span>+</span> <span>'</span><span>.</span><span>'</span> <span>+</span> <span>[</span><span>TABLE_NAME</span><span>]</span> <span>=</span> <span>@SchemaName_n_TableName</span><span>
) </span><span>=</span> <span>0</span>
<span>BEGIN</span>
<span>RAISERROR</span>(<span>'</span><span>Could not found the table in the defined database</span><span>'</span>,<span>16</span>,<span>1</span><span>)
</span><span>RETURN</span>
<span>END</span>
<span>DECLARE</span> <span>@bitTable</span> <span>TABLE</span><span>
(
</span><span>[</span><span>ID</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>Bitvalue</span><span>]</span> <span>INT</span><span>
)
</span><span>--</span><span>Create table to set the bit position of one byte.</span>
<span>INSERT</span> <span>INTO</span> <span>@bitTable</span>
<span>SELECT</span> <span>0</span><span> ,
</span><span>2</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>1</span><span> ,
</span><span>2</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>2</span><span> ,
</span><span>4</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>3</span><span> ,
</span><span>8</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>4</span><span> ,
</span><span>16</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>5</span><span> ,
</span><span>32</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>6</span><span> ,
</span><span>64</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>7</span><span> ,
</span><span>128</span>
<span>--</span><span>Create table to collect the row data.</span>
<span>DECLARE</span> <span>@DeletedRecords</span> <span>TABLE</span><span>
(
</span><span>[</span><span>Row ID</span><span>]</span> <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span><span>) ,
</span><span>[</span><span>RowLogContents</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
</span><span>[</span><span>AllocUnitID</span><span>]</span> <span>BIGINT</span><span> ,
</span><span>[</span><span>Transaction ID</span><span>]</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
</span><span>[</span><span>FixedLengthData</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>TotalNoOfCols</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>NullBitMapLength</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>NullBytes</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
</span><span>[</span><span>TotalNoofVarCols</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>ColumnOffsetArray</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
</span><span>[</span><span>VarColumnStart</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>Slot ID</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>NullBitMap</span><span>]</span> <span>VARCHAR</span>(<span>MAX</span><span>)
)
</span><span>--</span><span>Create a common table expression to get all the row data plus how many bytes we have for each row.</span>
<span>;
</span><span>WITH</span><span> RowData
</span><span>AS</span> ( <span>SELECT</span> <span>[</span><span>RowLog Contents 0</span><span>]</span> <span>AS</span> <span>[</span><span>RowLogContents</span><span>]</span><span> ,
</span><span>[</span><span>AllocUnitID</span><span>]</span> <span>AS</span> <span>[</span><span>AllocUnitID</span><span>]</span><span> ,
</span><span>[</span><span>Transaction ID</span><span>]</span> <span>AS</span> <span>[</span><span>Transaction ID</span><span>]</span>
<span>--</span><span>[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)</span>
<span> ,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>AS</span> <span>[</span><span>FixedLengthData</span><span>]</span> <span>--</span><span>@FixedLengthData</span>
<span>--</span><span> [TotalnoOfCols] = Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)</span>
<span> ,
</span><span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>AS</span> <span>[</span><span>TotalNoOfCols</span><span>]</span>
<span>--</span><span>[NullBitMapLength]=ceiling([Total No of Columns] /8.0)</span>
<span> ,
</span><span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span>)) <span>AS</span> <span>[</span><span>NullBitMapLength</span><span>]</span>
<span>--</span><span>[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )</span>
<span> ,
</span><span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span><span>,
</span><span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span>))) <span>AS</span> <span>[</span><span>NullBytes</span><span>]</span>
<span>--</span><span>[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )</span>
<span> ,
( </span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span>, <span>1</span>, <span>1</span>) <span>IN</span><span> (
</span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
</span><span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
<span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
</span><span>ELSE</span> <span>NULL</span>
<span>END</span> ) <span>AS</span> <span>[</span><span>TotalNoofVarCols</span><span>]</span>
<span>--</span><span>[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )</span>
<span> ,
( </span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span>, <span>1</span>, <span>1</span>) <span>IN</span><span> (
</span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
</span><span>THEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
<span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span><span>))
</span><span>+</span> <span>2</span><span>,
( </span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>1</span>, <span>1</span>) <span>IN</span> ( <span>0x10</span><span>,
</span><span>0x30</span>, <span>0x70</span><span> )
</span><span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
<span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
</span><span>ELSE</span> <span>NULL</span>
<span>END</span> ) <span>*</span> <span>2</span><span>)
</span><span>ELSE</span> <span>NULL</span>
<span>END</span> ) <span>AS</span> <span>[</span><span>ColumnOffsetArray</span><span>]</span>
<span>--</span><span> Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)</span>
<span> ,
</span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span>, <span>1</span>, <span>1</span>) <span>IN</span><span> (
</span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
</span><span>THEN</span> ( <span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>4</span>
<span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span><span>))
</span><span>+</span> ( ( <span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>1</span>, <span>1</span>) <span>IN</span> ( <span>0x10</span><span>,
</span><span>0x30</span>, <span>0x70</span><span> )
</span><span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
<span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CEILING</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>CONVERT</span>(<span>SMALLINT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
</span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
</span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
</span><span>ELSE</span> <span>NULL</span>
<span>END</span> ) <span>*</span> <span>2</span><span> ) )
</span><span>ELSE</span> <span>NULL</span>
<span>END</span> <span>AS</span> <span>[</span><span>VarColumnStart</span><span>]</span><span> ,
</span><span>[</span><span>Slot ID</span><span>]</span>
<span>FROM</span> sys.fn_dblog(<span>NULL</span>, <span>NULL</span><span>)
</span><span>WHERE</span> AllocUnitId <span>IN</span><span> (
</span><span>SELECT</span> <span>[</span><span>Allocation_unit_id</span><span>]</span>
<span>FROM</span><span> sys.allocation_units allocunits
</span><span>INNER</span> <span>JOIN</span> sys.partitions partitions <span>ON</span> ( allocunits.type <span>IN</span><span> (
</span><span>1</span>, <span>3</span><span> )
</span><span>AND</span> partitions.hobt_id <span>=</span><span> allocunits.container_id
)
</span><span>OR</span> ( allocunits.type <span>=</span> <span>2</span>
<span>AND</span> partitions.partition_id <span>=</span><span> allocunits.container_id
)
</span><span>WHERE</span> <span>object_id</span> <span>=</span> <span>OBJECT_ID</span>(<span>''</span>
<span>+</span> <span>@SchemaName_n_TableName</span>
<span>+</span> <span>''</span><span>) )
</span><span>AND</span> Context <span>IN</span> ( <span>'</span><span>LCX_MARK_AS_GHOST</span><span>'</span>, <span>'</span><span>LCX_HEAP</span><span>'</span><span> )
</span><span>AND</span> Operation <span>IN</span> ( <span>'</span><span>LOP_DELETE_ROWS</span><span>'</span><span> )
</span><span>AND</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span>, <span>1</span>, <span>1</span>) <span>IN</span> ( <span>0x10</span><span>,
</span><span>0x30</span>, <span>0x70</span><span> )
</span><span>/*</span><span>Use this subquery to filter the date</span><span>*/</span>
<span>AND</span> <span>[</span><span>TRANSACTION ID</span><span>]</span> <span>IN</span><span> (
</span><span>SELECT</span> <span>DISTINCT</span>
<span>[</span><span>TRANSACTION ID</span><span>]</span>
<span>FROM</span> sys.fn_dblog(<span>NULL</span>, <span>NULL</span><span>)
</span><span>WHERE</span> Context <span>IN</span> ( <span>'</span><span>LCX_NULL</span><span>'</span><span> )
</span><span>AND</span> Operation <span>IN</span> ( <span>'</span><span>LOP_BEGIN_XACT</span><span>'</span><span> )
</span><span>AND</span> <span>[</span><span>Transaction Name</span><span>]</span> <span>IN</span> ( <span>'</span><span>DELETE</span><span>'</span><span>,
</span><span>'</span><span>user_transaction</span><span>'</span><span> )
</span><span>AND</span> <span>CONVERT</span>(<span>NVARCHAR</span>(<span>11</span>), <span>[</span><span>Begin Time</span><span>]</span>) <span>BETWEEN</span> <span>@Date_From</span>
<span>AND</span>
<span>@Date_To</span><span> )
),
</span><span>--</span><span>Use this technique to repeate the row till the no of bytes of the row.</span>
<span> N1 ( n )
</span><span>AS</span> ( <span>SELECT</span> <span>1</span>
<span>UNION</span> <span>ALL</span>
<span>SELECT</span> <span>1</span><span>
),
N2 ( n )
</span><span>AS</span> ( <span>SELECT</span> <span>1</span>
<span>FROM</span> N1 <span>AS</span><span> X ,
N1 </span><span>AS</span><span> Y
),
N3 ( n )
</span><span>AS</span> ( <span>SELECT</span> <span>1</span>
<span>FROM</span> N2 <span>AS</span><span> X ,
N2 </span><span>AS</span><span> Y
),
N4 ( n )
</span><span>AS</span> ( <span>SELECT</span> ROW_NUMBER() <span>OVER</span> ( <span>ORDER</span> <span>BY</span><span> X.n )
</span><span>FROM</span> N3 <span>AS</span><span> X ,
N3 </span><span>AS</span><span> Y
)
</span><span>INSERT</span> <span>INTO</span> <span>@DeletedRecords</span>
<span>SELECT</span><span> RowLogContents ,
</span><span>[</span><span>AllocUnitID</span><span>]</span><span> ,
</span><span>[</span><span>Transaction ID</span><span>]</span><span> ,
</span><span>[</span><span>FixedLengthData</span><span>]</span><span> ,
</span><span>[</span><span>TotalNoOfCols</span><span>]</span><span> ,
</span><span>[</span><span>NullBitMapLength</span><span>]</span><span> ,
</span><span>[</span><span>NullBytes</span><span>]</span><span> ,
</span><span>[</span><span>TotalNoofVarCols</span><span>]</span><span> ,
</span><span>[</span><span>ColumnOffsetArray</span><span>]</span><span> ,
</span><span>[</span><span>VarColumnStart</span><span>]</span><span> ,
</span><span>[</span><span>Slot ID</span><span>]</span>
<span>--</span><span>-Get the Null value against each column (1 means null zero means not null)</span>
<span> ,
</span><span>[</span><span>NullBitMap</span><span>]</span> <span>=</span> ( <span>REPLACE</span>(<span>STUFF</span>(( <span>SELECT</span>
<span>'</span><span>,</span><span>'</span>
<span>+</span> ( <span>CASE</span>
<span>WHEN</span> <span>[</span><span>ID</span><span>]</span> <span>=</span> <span>0</span>
<span>THEN</span> <span>CONVERT</span>(<span>NVARCHAR</span>(<span>1</span>), ( <span>SUBSTRING</span><span>(NullBytes,
n, </span><span>1</span>) <span>%</span> <span>2</span><span> ))
</span><span>ELSE</span> <span>CONVERT</span>(<span>NVARCHAR</span>(<span>1</span>), ( ( <span>SUBSTRING</span><span>(NullBytes,
n, </span><span>1</span><span>)
</span><span>/</span> <span>[</span><span>Bitvalue</span><span>]</span><span> )
</span><span>%</span> <span>2</span><span> ))
</span><span>END</span> ) <span>--</span><span>as [nullBitMap]</span>
<span>FROM</span> N4 <span>AS</span><span> Nums
</span><span>JOIN</span> RowData <span>AS</span> C <span>ON</span> n <span><span> NullBitMapLength
</span><span>CROSS</span> <span>JOIN</span> <span>@bitTable</span>
<span>WHERE</span><span>
C.</span><span>[</span><span>RowLogContents</span><span>]</span> <span>=</span> D.<span>[</span><span>RowLogContents</span><span>]</span>
<span>ORDER</span> <span>BY</span> <span>[</span><span>RowLogContents</span><span>]</span><span> ,
n </span><span>ASC</span>
<span>FOR</span><span>
XML PATH(</span><span>''</span><span>)
), </span><span>1</span>, <span>1</span>, <span>''</span>), <span>'</span><span>,</span><span>'</span>, <span>''</span><span>) )
</span><span>FROM</span><span> RowData D
</span><span>IF</span> ( <span>SELECT</span> <span>COUNT</span>(<span>*</span><span>)
</span><span>FROM</span> <span>@DeletedRecords</span><span>
) </span><span>=</span> <span>0</span>
<span>BEGIN</span>
<span>RAISERROR</span>(<span>'</span><span>There is no data in the log as per the search criteria</span><span>'</span>,<span>16</span>,<span>1</span><span>)
</span><span>RETURN</span>
<span>END</span>
<span>DECLARE</span> <span>@ColumnNameAndData</span> <span>TABLE</span><span>
(
</span><span>[</span><span>Row ID</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>Rowlogcontents</span><span>]</span> <span>VARBINARY</span>(<span>MAX</span><span>) ,
</span><span>[</span><span>NAME</span><span>]</span><span> SYSNAME ,
</span><span>[</span><span>nullbit</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>leaf_offset</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>length</span><span>]</span> <span>SMALLINT</span><span> ,
</span><span>[</span><span>system_type_id</span><span>]</span> <span>TINYINT</span><span> ,
</span><span>[</span><span>bitpos</span><span>]</span> <span>TINYINT</span><span> ,
</span><span>[</span><span>xprec</span><span>]</span> <span>TINYINT</span><span> ,
</span><span>[</span><span>xscale</span><span>]</span> <span>TINYINT</span><span> ,
</span><span>[</span><span>is_null</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>Column value Size</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>Column Length</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>hex_Value</span><span>]</span> <span>VARBINARY</span>(<span>MAX</span><span>) ,
</span><span>[</span><span>Slot ID</span><span>]</span> <span>INT</span><span> ,
</span><span>[</span><span>Update</span><span>]</span> <span>INT</span><span>
)
</span><span>--</span><span>Create common table expression and join it with the rowdata table</span><span>
--</span><span> to get each column details</span><span>
/*</span><span>This part is for variable data columns</span><span>*/</span>
<span>--</span><span>@RowLogContents, </span><span>
--</span><span>(col.columnOffValue - col.columnLength) + 1,</span><span>
--</span><span>col.columnLength</span><span>
--</span><span>)</span>
<span>INSERT</span> <span>INTO</span> <span>@ColumnNameAndData</span>
<span>SELECT</span> <span>[</span><span>Row ID</span><span>]</span><span> ,
Rowlogcontents ,
NAME ,
cols.leaf_null_bit </span><span>AS</span><span> nullbit ,
leaf_offset ,
</span><span>ISNULL</span>(syscolumns.length, cols.max_length) <span>AS</span> <span>[</span><span>length</span><span>]</span><span> ,
cols.system_type_id ,
cols.leaf_bit_position </span><span>AS</span><span> bitpos ,
</span><span>ISNULL</span>(syscolumns.xprec, cols.<span>precision</span>) <span>AS</span><span> xprec ,
</span><span>ISNULL</span>(syscolumns.xscale, cols.scale) <span>AS</span><span> xscale ,
</span><span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span>, cols.leaf_null_bit, <span>1</span>) <span>AS</span><span> is_null ,
( </span><span>CASE</span> <span>WHEN</span> leaf_offset <span> <span>1</span>
<span>AND</span> <span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span><span>, cols.leaf_null_bit,
</span><span>1</span>) <span>=</span> <span>0</span>
<span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
</span><span>ELSE</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>END</span><span> )
</span><span>END</span> ) <span>AS</span> <span>[</span><span>Column value Size</span><span>]</span><span> ,
( </span><span>CASE</span> <span>WHEN</span> leaf_offset <span> <span>1</span>
<span>AND</span> <span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span><span>, cols.leaf_null_bit,
</span><span>1</span>) <span>=</span> <span>0</span>
<span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span> <span>30000</span>
<span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
</span><span>35</span>, <span>34</span>, <span>99</span> ) <span>THEN</span> <span>16</span>
<span>ELSE</span> <span>24</span>
<span>END</span><span> )
</span><span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
<span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
</span><span>35</span>, <span>34</span>, <span>99</span> ) <span>THEN</span> <span>16</span>
<span>ELSE</span> <span>24</span>
<span>END</span> ) <span>--</span><span>24 </span>
<span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span> <span>30000</span>
<span>THEN</span> ( <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span><span>) )
</span><span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
<span>THEN</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
</span><span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span><span>)
</span><span>END</span><span> )
</span><span>END</span> ) <span>AS</span> <span>[</span><span>Column Length</span><span>]</span><span> ,
( </span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span>, cols.leaf_null_bit, <span>1</span>) <span>=</span> <span>1</span>
<span>THEN</span> <span>NULL</span>
<span>ELSE</span> <span>SUBSTRING</span><span>(Rowlogcontents,
( ( </span><span>CASE</span> <span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
</span><span>ELSE</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>END</span><span> )
</span><span>-</span> ( <span>CASE</span> <span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span> <span>30000</span>
<span>THEN</span> ( <span>CASE</span>
<span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
</span><span>35</span>, <span>34</span>, <span>99</span><span> )
</span><span>THEN</span> <span>16</span>
<span>ELSE</span> <span>24</span>
<span>END</span> ) <span>--</span><span>24 </span>
<span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
<span>THEN</span> ( <span>CASE</span>
<span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
</span><span>35</span>, <span>34</span>, <span>99</span><span> )
</span><span>THEN</span> <span>16</span>
<span>ELSE</span> <span>24</span>
<span>END</span> ) <span>--</span><span>24 </span>
<span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span> <span>30000</span>
<span>THEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span><span>)
</span><span>WHEN</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span> <span>30000</span>
<span>AND</span> <span>ISNULL</span>(<span>NULLIF</span>(<span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> ( ( leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
</span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
</span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
<span>THEN</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
</span><span>+</span> <span>CONVERT</span>(<span>INT</span>, <span>CONVERT</span>(<span>BINARY</span>(<span>2</span>), <span>REVERSE</span>(<span>SUBSTRING</span>(<span>[</span><span>ColumnOffsetArray</span><span>]</span><span>,
( </span><span>2</span>
<span>*</span><span> leaf_offset
</span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
</span><span>-</span> <span>ISNULL</span>(<span>NULLIF</span></span></span></span></span></span></span></span></span></span></span></span></span>
