当前位置：Gxlcms > mysql > 恢复SQLSERVER被误删除的数据

恢复SQLSERVER被误删除的数据

时间：2021-07-01 10:21:17 帮助过：12人阅读

恢复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来解释一下我是怎么做到的

  1. <span>USE</span><span> master
  2. </span><span>GO</span>
  3. <span>--</span><span>创建数据库</span>
  4. <span>CREATE</span> <span>DATABASE</span><span> test
  5. </span><span>GO</span>
  6. <span>USE</span> <span>[</span><span>test</span><span>]</span>
  7. <span>GO</span>
  8. <span>--</span><span>创建表</span>
  9. <span>CREATE</span> <span>TABLE</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span><span>(
  10.     </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>,
  11.     </span><span>[</span><span>NAME</span><span>]</span> <span>[</span><span>nvarchar</span><span>]</span>(<span>200</span>) <span>NULL</span><span>
  12. ) </span><span>ON</span> <span>[</span><span>PRIMARY</span><span>]</span>
  13. <span>GO</span>
  14. <span>--</span><span>插入测试数据</span>
  15. <span>INSERT</span> <span>[</span><span>dbo</span><span>]</span>.<span>[</span><span>aa</span><span>]</span><span>
  16.         ( </span><span>[</span><span>NAME</span><span>]</span><span> )
  17. </span><span>SELECT</span> <span>'</span><span>你好</span><span>'</span>
  18. <span>GO</span>
  19. <span>--</span><span>删除数据</span>
  20. <span>Delete</span> <span>from</span><span> aa
  21. </span><span>Go</span>
  22. <span>--</span><span>验证数据是否已经删除</span>
  23. <span>Select</span> <span>*</span> <span>from</span><span> aa
  24. </span><span>Go</span>

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

  1. <span>--</span><span> Script Name: Recover_Deleted_Data_Proc</span><span>
  2. --</span><span> Script Type : Recovery Procedure </span><span>
  3. --</span><span> Develop By: Muhammad Imran</span><span>
  4. --</span><span> Date Created: 15 Oct 2011</span><span>
  5. --</span><span> Modify Date: 22 Aug 2012</span><span>
  6. --</span><span> Version    : 3.1</span><span>
  7. --</span><span> Notes : Included BLOB data types for recovery.& Compatibile with Default , CS collation , Arabic_CI_AS.</span>
  8.  
  9. <span>CREATE</span> <span>PROCEDURE</span><span> Recover_Deleted_Data_Proc
  10.     </span><span>@Database_Name</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
  11.     </span><span>@SchemaName_n_TableName</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
  12.     </span><span>@Date_From</span> <span>DATETIME</span> <span>=</span> <span>'</span><span>1900/01/01</span><span>'</span><span> ,
  13.     </span><span>@Date_To</span> <span>DATETIME</span> <span>=</span> <span>'</span><span>9999/12/31</span><span>'</span>
  14. <span>AS</span>
  15.     <span>DECLARE</span> <span>@RowLogContents</span> <span>VARBINARY</span>(<span>8000</span><span>)
  16.     </span><span>DECLARE</span> <span>@TransactionID</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
  17.     </span><span>DECLARE</span> <span>@AllocUnitID</span> <span>BIGINT</span>
  18.     <span>DECLARE</span> <span>@AllocUnitName</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
  19.     </span><span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>)
  20.     </span><span>DECLARE</span> <span>@Compatibility_Level</span> <span>INT</span>
  21.  
  22.  
  23.     <span>SELECT</span>  <span>@Compatibility_Level</span> <span>=</span><span> dtb.compatibility_level
  24.     </span><span>FROM</span>    master.sys.databases <span>AS</span><span> dtb
  25.     </span><span>WHERE</span>   dtb.name <span>=</span> <span>@Database_Name</span>
  26.  
  27.     <span>IF</span> <span>ISNULL</span>(<span>@Compatibility_Level</span>, <span>0</span>) <span><=</span> <span>80</span>
  28.         <span>BEGIN</span>
  29.             <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>)
  30.             </span><span>RETURN</span>
  31.         <span>END</span>
  32.  
  33.     <span>IF</span> ( <span>SELECT</span> <span>COUNT</span>(<span>*</span><span>)
  34.          </span><span>FROM</span><span>   INFORMATION_SCHEMA.TABLES
  35.          </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>
  36.        ) </span><span>=</span> <span>0</span>
  37.         <span>BEGIN</span>
  38.             <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>)
  39.             </span><span>RETURN</span>
  40.         <span>END</span>
  41.  
  42.     <span>DECLARE</span> <span>@bitTable</span> <span>TABLE</span><span>
  43.         (
  44.           </span><span>[</span><span>ID</span><span>]</span> <span>INT</span><span> ,
  45.           </span><span>[</span><span>Bitvalue</span><span>]</span> <span>INT</span><span>
  46.         )
  47. </span><span>--</span><span>Create table to set the bit position of one byte.</span>
  48.  
  49.     <span>INSERT</span>  <span>INTO</span> <span>@bitTable</span>
  50.             <span>SELECT</span>  <span>0</span><span> ,
  51.                     </span><span>2</span>
  52.             <span>UNION</span> <span>ALL</span>
  53.             <span>SELECT</span>  <span>1</span><span> ,
  54.                     </span><span>2</span>
  55.             <span>UNION</span> <span>ALL</span>
  56.             <span>SELECT</span>  <span>2</span><span> ,
  57.                     </span><span>4</span>
  58.             <span>UNION</span> <span>ALL</span>
  59.             <span>SELECT</span>  <span>3</span><span> ,
  60.                     </span><span>8</span>
  61.             <span>UNION</span> <span>ALL</span>
  62.             <span>SELECT</span>  <span>4</span><span> ,
  63.                     </span><span>16</span>
  64.             <span>UNION</span> <span>ALL</span>
  65.             <span>SELECT</span>  <span>5</span><span> ,
  66.                     </span><span>32</span>
  67.             <span>UNION</span> <span>ALL</span>
  68.             <span>SELECT</span>  <span>6</span><span> ,
  69.                     </span><span>64</span>
  70.             <span>UNION</span> <span>ALL</span>
  71.             <span>SELECT</span>  <span>7</span><span> ,
  72.                     </span><span>128</span>
  73.  
  74. <span>--</span><span>Create table to collect the row data.</span>
  75.     <span>DECLARE</span> <span>@DeletedRecords</span> <span>TABLE</span><span>
  76.         (
  77.           </span><span>[</span><span>Row ID</span><span>]</span> <span>INT</span> <span>IDENTITY</span>(<span>1</span>, <span>1</span><span>) ,
  78.           </span><span>[</span><span>RowLogContents</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
  79.           </span><span>[</span><span>AllocUnitID</span><span>]</span> <span>BIGINT</span><span> ,
  80.           </span><span>[</span><span>Transaction ID</span><span>]</span> <span>NVARCHAR</span>(<span>MAX</span><span>) ,
  81.           </span><span>[</span><span>FixedLengthData</span><span>]</span> <span>SMALLINT</span><span> ,
  82.           </span><span>[</span><span>TotalNoOfCols</span><span>]</span> <span>SMALLINT</span><span> ,
  83.           </span><span>[</span><span>NullBitMapLength</span><span>]</span> <span>SMALLINT</span><span> ,
  84.           </span><span>[</span><span>NullBytes</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
  85.           </span><span>[</span><span>TotalNoofVarCols</span><span>]</span> <span>SMALLINT</span><span> ,
  86.           </span><span>[</span><span>ColumnOffsetArray</span><span>]</span> <span>VARBINARY</span>(<span>8000</span><span>) ,
  87.           </span><span>[</span><span>VarColumnStart</span><span>]</span> <span>SMALLINT</span><span> ,
  88.           </span><span>[</span><span>Slot ID</span><span>]</span> <span>INT</span><span> ,
  89.           </span><span>[</span><span>NullBitMap</span><span>]</span> <span>VARCHAR</span>(<span>MAX</span><span>)
  90.         )
  91. </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>
  92. <span>;
  93.     </span><span>WITH</span><span>    RowData
  94.               </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> ,
  95.                             </span><span>[</span><span>AllocUnitID</span><span>]</span> <span>AS</span> <span>[</span><span>AllocUnitID</span><span>]</span><span> ,
  96.                             </span><span>[</span><span>Transaction ID</span><span>]</span> <span>AS</span> <span>[</span><span>Transaction ID</span><span>]</span>  
  97.  
  98. <span>--</span><span>[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)</span>
  99. <span>                            ,
  100.                             </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>,
  101.                                                               </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>
  102.  
  103. <span>--</span><span> [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)</span>
  104. <span>                            ,
  105.                             </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>,
  106.                                                               </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>,
  107.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  108.                                                               </span><span>2</span>)))) <span>AS</span> <span>[</span><span>TotalNoOfCols</span><span>]</span>
  109.  
  110. <span>--</span><span>[NullBitMapLength]=ceiling([Total No of Columns] /8.0)</span>
  111. <span>                            ,
  112.                             </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>,
  113.                                                               </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>,
  114.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  115.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span>)) <span>AS</span> <span>[</span><span>NullBitMapLength</span><span>]</span> 
  116.  
  117. <span>--</span><span>[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )</span>
  118. <span>                            ,
  119.                             </span><span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
  120.                                       </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>,
  121.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span><span>,
  122.                                       </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>,
  123.                                                               </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>,
  124.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  125.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span>))) <span>AS</span> <span>[</span><span>NullBytes</span><span>]</span>
  126.  
  127. <span>--</span><span>[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )</span>
  128. <span>                            ,
  129.                             ( </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> (
  130.                                         </span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
  131.                                    </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>,
  132.                                                               </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>,
  133.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
  134.                                                               <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>,
  135.                                                               </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>,
  136.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  137.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
  138.                                    </span><span>ELSE</span> <span>NULL</span>
  139.                               <span>END</span> ) <span>AS</span> <span>[</span><span>TotalNoofVarCols</span><span>]</span> 
  140.  
  141. <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>
  142. <span>                            ,
  143.                             ( </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> (
  144.                                         </span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
  145.                                    </span><span>THEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
  146.                                                   </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>,
  147.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
  148.                                                   <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>,
  149.                                                               </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>,
  150.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  151.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span><span>))
  152.                                                   </span><span>+</span> <span>2</span><span>,
  153.                                                   ( </span><span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
  154.                                                               </span><span>1</span>, <span>1</span>) <span>IN</span> ( <span>0x10</span><span>,
  155.                                                               </span><span>0x30</span>, <span>0x70</span><span> )
  156.                                                          </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>,
  157.                                                               </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>,
  158.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
  159.                                                               <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>,
  160.                                                               </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>,
  161.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  162.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
  163.                                                          </span><span>ELSE</span> <span>NULL</span>
  164.                                                     <span>END</span> ) <span>*</span> <span>2</span><span>)
  165.                                    </span><span>ELSE</span> <span>NULL</span>
  166.                               <span>END</span> ) <span>AS</span> <span>[</span><span>ColumnOffsetArray</span><span>]</span> 
  167.  
  168. <span>--</span><span>  Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)</span>
  169. <span>                            ,
  170.                             </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> (
  171.                                       </span><span>0x10</span>, <span>0x30</span>, <span>0x70</span><span> )
  172.                                  </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>,
  173.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>4</span>
  174.                                         <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>,
  175.                                                               </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>,
  176.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  177.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span><span>))
  178.                                         </span><span>+</span> ( ( <span>CASE</span> <span>WHEN</span> <span>SUBSTRING</span>(<span>[</span><span>RowLog Contents 0</span><span>]</span><span>,
  179.                                                               </span><span>1</span>, <span>1</span>) <span>IN</span> ( <span>0x10</span><span>,
  180.                                                               </span><span>0x30</span>, <span>0x70</span><span> )
  181.                                                    </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>,
  182.                                                               </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>,
  183.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>3</span>
  184.                                                               <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>,
  185.                                                               </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>,
  186.                                                               </span><span>2</span> <span>+</span> <span>1</span>, <span>2</span>)))) <span>+</span> <span>1</span><span>,
  187.                                                               </span><span>2</span>)))) <span>/</span> <span>8.0</span>)), <span>2</span><span>))))
  188.                                                    </span><span>ELSE</span> <span>NULL</span>
  189.                                               <span>END</span> ) <span>*</span> <span>2</span><span> ) )
  190.                                  </span><span>ELSE</span> <span>NULL</span>
  191.                             <span>END</span> <span>AS</span> <span>[</span><span>VarColumnStart</span><span>]</span><span> ,
  192.                             </span><span>[</span><span>Slot ID</span><span>]</span>
  193.                    <span>FROM</span>     sys.fn_dblog(<span>NULL</span>, <span>NULL</span><span>)
  194.                    </span><span>WHERE</span>    AllocUnitId <span>IN</span><span> (
  195.                             </span><span>SELECT</span>  <span>[</span><span>Allocation_unit_id</span><span>]</span>
  196.                             <span>FROM</span><span>    sys.allocation_units allocunits
  197.                                     </span><span>INNER</span> <span>JOIN</span> sys.partitions partitions <span>ON</span> ( allocunits.type <span>IN</span><span> (
  198.                                                               </span><span>1</span>, <span>3</span><span> )
  199.                                                               </span><span>AND</span> partitions.hobt_id <span>=</span><span> allocunits.container_id
  200.                                                               )
  201.                                                               </span><span>OR</span> ( allocunits.type <span>=</span> <span>2</span>
  202.                                                               <span>AND</span> partitions.partition_id <span>=</span><span> allocunits.container_id
  203.                                                               )
  204.                             </span><span>WHERE</span>   <span>object_id</span> <span>=</span> <span>OBJECT_ID</span>(<span>''</span>
  205.                                                           <span>+</span> <span>@SchemaName_n_TableName</span>
  206.                                                           <span>+</span> <span>''</span><span>) )
  207.                             </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> )
  208.                             </span><span>AND</span> Operation <span>IN</span> ( <span>'</span><span>LOP_DELETE_ROWS</span><span>'</span><span> )
  209.                             </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>,
  210.                                                               </span><span>0x30</span>, <span>0x70</span><span> )
  211.  
  212. </span><span>/*</span><span>Use this subquery to filter the date</span><span>*/</span>
  213.                             <span>AND</span> <span>[</span><span>TRANSACTION ID</span><span>]</span> <span>IN</span><span> (
  214.                             </span><span>SELECT</span> <span>DISTINCT</span>
  215.                                     <span>[</span><span>TRANSACTION ID</span><span>]</span>
  216.                             <span>FROM</span>    sys.fn_dblog(<span>NULL</span>, <span>NULL</span><span>)
  217.                             </span><span>WHERE</span>   Context <span>IN</span> ( <span>'</span><span>LCX_NULL</span><span>'</span><span> )
  218.                                     </span><span>AND</span> Operation <span>IN</span> ( <span>'</span><span>LOP_BEGIN_XACT</span><span>'</span><span> )
  219.                                     </span><span>AND</span> <span>[</span><span>Transaction Name</span><span>]</span> <span>IN</span> ( <span>'</span><span>DELETE</span><span>'</span><span>,
  220.                                                               </span><span>'</span><span>user_transaction</span><span>'</span><span> )
  221.                                     </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>
  222.                                                               <span>AND</span>
  223.                                                               <span>@Date_To</span><span> )
  224.                  ),
  225.  
  226. </span><span>--</span><span>Use this technique to repeate the row till the no of bytes of the row.</span>
  227. <span>            N1 ( n )
  228.               </span><span>AS</span> ( <span>SELECT</span>   <span>1</span>
  229.                    <span>UNION</span> <span>ALL</span>
  230.                    <span>SELECT</span>   <span>1</span><span>
  231.                  ),
  232.             N2 ( n )
  233.               </span><span>AS</span> ( <span>SELECT</span>   <span>1</span>
  234.                    <span>FROM</span>     N1 <span>AS</span><span> X ,
  235.                             N1 </span><span>AS</span><span> Y
  236.                  ),
  237.             N3 ( n )
  238.               </span><span>AS</span> ( <span>SELECT</span>   <span>1</span>
  239.                    <span>FROM</span>     N2 <span>AS</span><span> X ,
  240.                             N2 </span><span>AS</span><span> Y
  241.                  ),
  242.             N4 ( n )
  243.               </span><span>AS</span> ( <span>SELECT</span>   ROW_NUMBER() <span>OVER</span> ( <span>ORDER</span> <span>BY</span><span> X.n )
  244.                    </span><span>FROM</span>     N3 <span>AS</span><span> X ,
  245.                             N3 </span><span>AS</span><span> Y
  246.                  )
  247.         </span><span>INSERT</span>  <span>INTO</span> <span>@DeletedRecords</span>
  248.                 <span>SELECT</span><span>  RowLogContents ,
  249.                         </span><span>[</span><span>AllocUnitID</span><span>]</span><span> ,
  250.                         </span><span>[</span><span>Transaction ID</span><span>]</span><span> ,
  251.                         </span><span>[</span><span>FixedLengthData</span><span>]</span><span> ,
  252.                         </span><span>[</span><span>TotalNoOfCols</span><span>]</span><span> ,
  253.                         </span><span>[</span><span>NullBitMapLength</span><span>]</span><span> ,
  254.                         </span><span>[</span><span>NullBytes</span><span>]</span><span> ,
  255.                         </span><span>[</span><span>TotalNoofVarCols</span><span>]</span><span> ,
  256.                         </span><span>[</span><span>ColumnOffsetArray</span><span>]</span><span> ,
  257.                         </span><span>[</span><span>VarColumnStart</span><span>]</span><span> ,
  258.                         </span><span>[</span><span>Slot ID</span><span>]</span>
  259.          <span>--</span><span>-Get the Null value against each column (1 means null zero means not null)</span>
  260. <span>                        ,
  261.                         </span><span>[</span><span>NullBitMap</span><span>]</span> <span>=</span> ( <span>REPLACE</span>(<span>STUFF</span>(( <span>SELECT</span>
  262.                                                               <span>'</span><span>,</span><span>'</span>
  263.                                                               <span>+</span> ( <span>CASE</span>
  264.                                                               <span>WHEN</span> <span>[</span><span>ID</span><span>]</span> <span>=</span> <span>0</span>
  265.                                                               <span>THEN</span> <span>CONVERT</span>(<span>NVARCHAR</span>(<span>1</span>), ( <span>SUBSTRING</span><span>(NullBytes,
  266.                                                               n, </span><span>1</span>) <span>%</span> <span>2</span><span> ))
  267.                                                               </span><span>ELSE</span> <span>CONVERT</span>(<span>NVARCHAR</span>(<span>1</span>), ( ( <span>SUBSTRING</span><span>(NullBytes,
  268.                                                               n, </span><span>1</span><span>)
  269.                                                               </span><span>/</span> <span>[</span><span>Bitvalue</span><span>]</span><span> )
  270.                                                               </span><span>%</span> <span>2</span><span> ))
  271.                                                               </span><span>END</span> ) <span>--</span><span>as [nullBitMap]</span>
  272.                                                          <span>FROM</span> N4 <span>AS</span><span> Nums
  273.                                                               </span><span>JOIN</span> RowData <span>AS</span> C <span>ON</span> n <span><=</span><span> NullBitMapLength
  274.                                                               </span><span>CROSS</span> <span>JOIN</span> <span>@bitTable</span>
  275.                                                          <span>WHERE</span><span>
  276.                                                               C.</span><span>[</span><span>RowLogContents</span><span>]</span> <span>=</span> D.<span>[</span><span>RowLogContents</span><span>]</span>
  277.                                                          <span>ORDER</span> <span>BY</span> <span>[</span><span>RowLogContents</span><span>]</span><span> ,
  278.                                                               n </span><span>ASC</span>
  279.                                                        <span>FOR</span><span>
  280.                                                          XML PATH(</span><span>''</span><span>)
  281.                                                        ), </span><span>1</span>, <span>1</span>, <span>''</span>), <span>'</span><span>,</span><span>'</span>, <span>''</span><span>) )
  282.                 </span><span>FROM</span><span>    RowData D
  283.  
  284.     </span><span>IF</span> ( <span>SELECT</span> <span>COUNT</span>(<span>*</span><span>)
  285.          </span><span>FROM</span>   <span>@DeletedRecords</span><span>
  286.        ) </span><span>=</span> <span>0</span>
  287.         <span>BEGIN</span>
  288.             <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>)
  289.             </span><span>RETURN</span>
  290.         <span>END</span>
  291.  
  292.     <span>DECLARE</span> <span>@ColumnNameAndData</span> <span>TABLE</span><span>
  293.         (
  294.           </span><span>[</span><span>Row ID</span><span>]</span> <span>INT</span><span> ,
  295.           </span><span>[</span><span>Rowlogcontents</span><span>]</span> <span>VARBINARY</span>(<span>MAX</span><span>) ,
  296.           </span><span>[</span><span>NAME</span><span>]</span><span> SYSNAME ,
  297.           </span><span>[</span><span>nullbit</span><span>]</span> <span>SMALLINT</span><span> ,
  298.           </span><span>[</span><span>leaf_offset</span><span>]</span> <span>SMALLINT</span><span> ,
  299.           </span><span>[</span><span>length</span><span>]</span> <span>SMALLINT</span><span> ,
  300.           </span><span>[</span><span>system_type_id</span><span>]</span> <span>TINYINT</span><span> ,
  301.           </span><span>[</span><span>bitpos</span><span>]</span> <span>TINYINT</span><span> ,
  302.           </span><span>[</span><span>xprec</span><span>]</span> <span>TINYINT</span><span> ,
  303.           </span><span>[</span><span>xscale</span><span>]</span> <span>TINYINT</span><span> ,
  304.           </span><span>[</span><span>is_null</span><span>]</span> <span>INT</span><span> ,
  305.           </span><span>[</span><span>Column value Size</span><span>]</span> <span>INT</span><span> ,
  306.           </span><span>[</span><span>Column Length</span><span>]</span> <span>INT</span><span> ,
  307.           </span><span>[</span><span>hex_Value</span><span>]</span> <span>VARBINARY</span>(<span>MAX</span><span>) ,
  308.           </span><span>[</span><span>Slot ID</span><span>]</span> <span>INT</span><span> ,
  309.           </span><span>[</span><span>Update</span><span>]</span> <span>INT</span><span>
  310.         )
  311.  
  312. </span><span>--</span><span>Create common table expression and join it with the rowdata table</span><span>
  313. --</span><span> to get each column details</span><span>
  314. /*</span><span>This part is for variable data columns</span><span>*/</span>
  315. <span>--</span><span>@RowLogContents, </span><span>
  316. --</span><span>(col.columnOffValue - col.columnLength) + 1,</span><span>
  317. --</span><span>col.columnLength</span><span>
  318. --</span><span>)</span>
  319.     <span>INSERT</span>  <span>INTO</span> <span>@ColumnNameAndData</span>
  320.             <span>SELECT</span>  <span>[</span><span>Row ID</span><span>]</span><span> ,
  321.                     Rowlogcontents ,
  322.                     NAME ,
  323.                     cols.leaf_null_bit </span><span>AS</span><span> nullbit ,
  324.                     leaf_offset ,
  325.                     </span><span>ISNULL</span>(syscolumns.length, cols.max_length) <span>AS</span> <span>[</span><span>length</span><span>]</span><span> ,
  326.                     cols.system_type_id ,
  327.                     cols.leaf_bit_position </span><span>AS</span><span> bitpos ,
  328.                     </span><span>ISNULL</span>(syscolumns.xprec, cols.<span>precision</span>) <span>AS</span><span> xprec ,
  329.                     </span><span>ISNULL</span>(syscolumns.xscale, cols.scale) <span>AS</span><span> xscale ,
  330.                     </span><span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span>, cols.leaf_null_bit, <span>1</span>) <span>AS</span><span> is_null ,
  331.                     ( </span><span>CASE</span> <span>WHEN</span> leaf_offset <span><</span> <span>1</span>
  332.                                 <span>AND</span> <span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span><span>, cols.leaf_null_bit,
  333.                                               </span><span>1</span>) <span>=</span> <span>0</span>
  334.                            <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>,
  335.                                                               ( </span><span>2</span>
  336.                                                               <span>*</span><span> leaf_offset
  337.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  338.                                        <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>,
  339.                                                               ( </span><span>2</span>
  340.                                                               <span>*</span><span> leaf_offset
  341.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  342.                                             </span><span>-</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
  343.                                        </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>,
  344.                                                               ( </span><span>2</span>
  345.                                                               <span>*</span><span> leaf_offset
  346.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  347.                                   </span><span>END</span><span> )
  348.                       </span><span>END</span> ) <span>AS</span> <span>[</span><span>Column value Size</span><span>]</span><span> ,
  349.                     ( </span><span>CASE</span> <span>WHEN</span> leaf_offset <span><</span> <span>1</span>
  350.                                 <span>AND</span> <span>SUBSTRING</span>(<span>[</span><span>nullBitMap</span><span>]</span><span>, cols.leaf_null_bit,
  351.                                               </span><span>1</span>) <span>=</span> <span>0</span>
  352.                            <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>,
  353.                                                               ( </span><span>2</span>
  354.                                                               <span>*</span><span> leaf_offset
  355.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  356.                                             <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>,
  357.                                                               ( </span><span>2</span>
  358.                                                               <span>*</span><span> ( ( leaf_offset
  359.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  360.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  361.                                                        </span><span>[</span><span>varColumnStart</span><span>]</span>) <span><</span> <span>30000</span>
  362.                                        <span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
  363.                                                         </span><span>35</span>, <span>34</span>, <span>99</span> ) <span>THEN</span> <span>16</span>
  364.                                                    <span>ELSE</span> <span>24</span>
  365.                                               <span>END</span><span> )
  366.                                        </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>,
  367.                                                               ( </span><span>2</span>
  368.                                                               <span>*</span><span> leaf_offset
  369.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  370.                                             <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>,
  371.                                                               ( </span><span>2</span>
  372.                                                               <span>*</span><span> ( ( leaf_offset
  373.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  374.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  375.                                                        </span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
  376.                                        <span>THEN</span> ( <span>CASE</span> <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
  377.                                                         </span><span>35</span>, <span>34</span>, <span>99</span> ) <span>THEN</span> <span>16</span>
  378.                                                    <span>ELSE</span> <span>24</span>
  379.                                               <span>END</span> ) <span>--</span><span>24 </span>
  380.                                        <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>,
  381.                                                               ( </span><span>2</span>
  382.                                                               <span>*</span><span> leaf_offset
  383.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span><</span> <span>30000</span>
  384.                                             <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>,
  385.                                                               ( </span><span>2</span>
  386.                                                               <span>*</span><span> ( ( leaf_offset
  387.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  388.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  389.                                                        </span><span>[</span><span>varColumnStart</span><span>]</span>) <span><</span> <span>30000</span>
  390.                                        <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>,
  391.                                                               ( </span><span>2</span>
  392.                                                               <span>*</span><span> leaf_offset
  393.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  394.                                               </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>,
  395.                                                               ( </span><span>2</span>
  396.                                                               <span>*</span><span> ( ( leaf_offset
  397.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  398.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  399.                                                        </span><span>[</span><span>varColumnStart</span><span>]</span><span>) )
  400.                                        </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>,
  401.                                                               ( </span><span>2</span>
  402.                                                               <span>*</span><span> leaf_offset
  403.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span><</span> <span>30000</span>
  404.                                             <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>,
  405.                                                               ( </span><span>2</span>
  406.                                                               <span>*</span><span> ( ( leaf_offset
  407.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  408.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  409.                                                        </span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
  410.                                        <span>THEN</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
  411.                                             </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>,
  412.                                                               ( </span><span>2</span>
  413.                                                               <span>*</span><span> leaf_offset
  414.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  415.                                             </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>,
  416.                                                               ( </span><span>2</span>
  417.                                                               <span>*</span><span> ( ( leaf_offset
  418.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  419.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  420.                                                      </span><span>[</span><span>varColumnStart</span><span>]</span><span>)
  421.                                   </span><span>END</span><span> )
  422.                       </span><span>END</span> ) <span>AS</span> <span>[</span><span>Column Length</span><span>]</span><span> ,
  423.                     ( </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>
  424.                            <span>THEN</span> <span>NULL</span>
  425.                            <span>ELSE</span> <span>SUBSTRING</span><span>(Rowlogcontents,
  426.                                           ( ( </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>,
  427.                                                               ( </span><span>2</span>
  428.                                                               <span>*</span><span> leaf_offset
  429.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  430.                                                    <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>,
  431.                                                               ( </span><span>2</span>
  432.                                                               <span>*</span><span> leaf_offset
  433.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  434.                                                         </span><span>-</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
  435.                                                    </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>,
  436.                                                               ( </span><span>2</span>
  437.                                                               <span>*</span><span> leaf_offset
  438.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  439.                                               </span><span>END</span><span> )
  440.                                             </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>,
  441.                                                               ( </span><span>2</span>
  442.                                                               <span>*</span><span> leaf_offset
  443.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  444.                                                           <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>,
  445.                                                               ( </span><span>2</span>
  446.                                                               <span>*</span><span> ( ( leaf_offset
  447.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  448.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  449.                                                               </span><span>[</span><span>varColumnStart</span><span>]</span>) <span><</span> <span>30000</span>
  450.                                                      <span>THEN</span> ( <span>CASE</span>
  451.                                                               <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
  452.                                                               </span><span>35</span>, <span>34</span>, <span>99</span><span> )
  453.                                                               </span><span>THEN</span> <span>16</span>
  454.                                                               <span>ELSE</span> <span>24</span>
  455.                                                             <span>END</span> ) <span>--</span><span>24 </span>
  456.                                                      <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>,
  457.                                                               ( </span><span>2</span>
  458.                                                               <span>*</span><span> leaf_offset
  459.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span>></span> <span>30000</span>
  460.                                                           <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>,
  461.                                                               ( </span><span>2</span>
  462.                                                               <span>*</span><span> ( ( leaf_offset
  463.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  464.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  465.                                                               </span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
  466.                                                      <span>THEN</span> ( <span>CASE</span>
  467.                                                               <span>WHEN</span> <span>[</span><span>System_type_id</span><span>]</span> <span>IN</span><span> (
  468.                                                               </span><span>35</span>, <span>34</span>, <span>99</span><span> )
  469.                                                               </span><span>THEN</span> <span>16</span>
  470.                                                               <span>ELSE</span> <span>24</span>
  471.                                                             <span>END</span> ) <span>--</span><span>24 </span>
  472.                                                      <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>,
  473.                                                               ( </span><span>2</span>
  474.                                                               <span>*</span><span> leaf_offset
  475.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span><</span> <span>30000</span>
  476.                                                           <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>,
  477.                                                               ( </span><span>2</span>
  478.                                                               <span>*</span><span> ( ( leaf_offset
  479.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  480.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  481.                                                               </span><span>[</span><span>varColumnStart</span><span>]</span>) <span><</span> <span>30000</span>
  482.                                                      <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>,
  483.                                                               ( </span><span>2</span>
  484.                                                               <span>*</span><span> leaf_offset
  485.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  486.                                                           </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>,
  487.                                                               ( </span><span>2</span>
  488.                                                               <span>*</span><span> ( ( leaf_offset
  489.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  490.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  491.                                                               </span><span>[</span><span>varColumnStart</span><span>]</span><span>)
  492.                                                      </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>,
  493.                                                               ( </span><span>2</span>
  494.                                                               <span>*</span><span> leaf_offset
  495.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span>)))) <span><</span> <span>30000</span>
  496.                                                           <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>,
  497.                                                               ( </span><span>2</span>
  498.                                                               <span>*</span><span> ( ( leaf_offset
  499.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span><span> ) )
  500.                                                               </span><span>-</span> <span>1</span>, <span>2</span>)))), <span>0</span><span>),
  501.                                                               </span><span>[</span><span>varColumnStart</span><span>]</span>) <span>></span> <span>30000</span>
  502.                                                      <span>THEN</span> <span>POWER</span>(<span>2</span>, <span>15</span><span>)
  503.                                                           </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>,
  504.                                                               ( </span><span>2</span>
  505.                                                               <span>*</span><span> leaf_offset
  506.                                                               </span><span>*</span> <span>-</span><span>1</span> ) <span>-</span> <span>1</span>, <span>2</span><span>))))
  507.                                                           </span><span>-</span> <span>ISNULL
  508.                   
  509.  
  510.                     <div class="">
  511.                         <ul class="m-news-opt fix">
  512.                             <li class="opt-item">
  513.                                 <a href="/mysql-310510.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">MySQL优化（八）</p></a>
  514.                             </li>
  515.                             <li class="opt-item ta-r">
  516.                                  <a href="/mysql-310512.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">SqlServer横向扩展负载均衡终极利器SqlServerProxy</p></a>
  517.                             </li>
  518.                         </ul>
  519.                     </div>
  520.                     
  521.                     
  522.                     
  523.                     
  524.                 </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！