コミュニティ
学ぶ
ツールライブラリ
AIツール
レジャー
検索
日本語
ホームページ > データベース > mysql チュートリアル > 恢复SQLSERVER被误删除的数据

恢复SQLSERVER被误删除的数据

WBOY
リリース： 2016-06-07 15:26:12
オリジナル
1798 人が閲覧しました

恢复SQLSERVER被误删除的数据 曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据 这里有一篇文章做到了，不过似乎不是所有的数据类型都支持 以下为译文： http://raresql.com/2011/10/22/how-to-recover-deleted-data-from

恢复SQLSERVER被误删除的数据

曾经想实现Log Explorer for SQL Server的功能，利用ldf里面的日志来还原误删除的数据

这里有一篇文章做到了，不过似乎不是所有的数据类型都支持

以下为译文：http://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/

 

在我使用SQLSERVER的这些年里面，大部分人都会问我一个问题：“能不能恢复被删除的数据？？”

现在，从SQLSERVER2005 或以上版本能很容易能够恢复被删除的数据

（注意：这个脚本能恢复下面的数据类型的数据 而且兼容CS 排序规则）

  • image
  • text
  • uniqueidentifier
  • tinyint
  • smallint
  • int
  • smalldatetime
  • real
  • money
  • datetime
  • float
  • sql_variant
  • ntext
  • bit
  • decimal
  • numeric
  • smallmoney
  • bigint
  • varbinary
  • varchar
  • binary
  • char
  • timestamp
  • nvarchar
  • nchar
  • xml
  • sysname

 

 

让我来用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>
ログイン後にコピー

 

现在你需要创建一个存储过程来恢复你的数据

恢复SQLSERVER被误删除的数据恢复SQLSERVER被误删除的数据 

<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>
ログイン後にコピー
関連ラベル：
sqlserver 消去 回復する データ
ソース：php.cn
前の記事：18句话入门SQLServer XML查询(转) 次の記事：oracle中删除重复的行
このウェブサイトの声明
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
著者別の最新記事
最新の問題
Reactjsで複数のチェックボックスを選択する方法 複数のチェックボックスを選択したいオプションのリストがありますが、選択することができません。このコードの何が問題なのでしょうか?サンドボックスの URL
から 2024-04-06 18:55:21
0
1
396
エンティティ ウェアハウスは JpaRepository<Entity, Id> および JpaSpecificExecutor<Entity> を拡張し、EntitySpec は Specific<Entity> を拡張します。 これは私のエンドポイントです http://localhost:8080/country/all 返信はすべての国に対して問題ありません。フィルターしたい場合は、空の国が見つからず...
から 2024-04-06 17:11:03
0
1
421
MERN スタックの検索ボックスとチェックボックスの正規表現フィルター 私は実行しながら学習することで MERN スタックがどのように連携するかを理解しようとしており、bezcoder の次のチュートリアルに従っています: Node.js/Expres...
から 2024-04-06 14:53:12
0
1
425
送信後に選択したチェックボックスの値を印刷する方法 これは、データを送信するために必要なチェックボックスとボタンのリストです。<label>AccessLocations</label><br/>...
から 2024-04-06 13:07:43
0
2
337
画像と名前の位置を修正 Web サイトをコーディングしようとしていますが、画像と名前 (Administracija) が左側に表示されますが、検索バーがある場所に表示されるはずです。 @importur...
から 2024-04-05 10:41:36
0
1
3578
関連トピック
詳細>
人気のおすすめ
人気のチュートリアル
詳細>
関連するチュートリアル
人気のおすすめ
最新のコース
最新のダウンロード
詳細>
ウェブエフェクト
公式サイト
サイト素材
フロントエンドテンプレート