Table of Contents
恢复SQLSERVER被误删除的数据
sqlserver
delete
recover
data
恢复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>Copy after login
现在你需要创建一个存储过程来恢复你的数据
<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>Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Roblox: Grow A Garden - Complete Mutation Guide
4 weeks ago
By DDD
Roblox: Bubble Gum Simulator Infinity - How To Get And Use Royal Keys
4 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Nordhold: Fusion System, Explained
1 months ago
By 尊渡假赌尊渡假赌尊渡假赌
Mandragora: Whispers Of The Witch Tree - How To Unlock The Grappling Hook
4 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Clair Obscur: Expedition 33 - How To Get Perfect Chroma Catalysts
2 weeks ago
By 尊渡假赌尊渡假赌尊渡假赌
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
Java Tutorial
1677
14
1677
14
CakePHP Tutorial
1430
52
1430
52
Laravel Tutorial
1333
25
1333
25
PHP Tutorial
1278
29
1278
29
C# Tutorial
1257
24
1257
24
How to import mdf file into sqlserver
Apr 08, 2024 am 11:41 AM
The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.
How to solve the problem that the object named already exists in the sqlserver database
Apr 05, 2024 pm 09:42 PM
For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.
Is it true that you can be blocked and deleted on WeChat and permanently unable to be added?
Apr 08, 2024 am 11:41 AM
1. First of all, it is false to block and delete someone permanently and not add them permanently. If you want to add the other party after you have blocked them and deleted them, you only need the other party's consent. 2. If a user blocks someone, the other party will not be able to send messages to the user, view the user's circle of friends, or make calls with the user. 3. Blocking does not mean deleting the other party from the user's WeChat contact list. 4. If the user deletes the other party from the user's WeChat contact list after blocking them, there is no way to recover after deletion. 5. If the user wants to add the other party as a friend again, the other party needs to agree and add the user again.
How to check sqlserver port number
Apr 05, 2024 pm 09:57 PM
To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.
How to recover accidentally deleted database in sqlserver
Apr 05, 2024 pm 10:39 PM
If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.
What to do if the sqlserver service cannot be started
Apr 05, 2024 pm 10:00 PM
When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.
Slow Cellular Data Internet Speeds on iPhone: Fixes
May 03, 2024 pm 09:01 PM
Facing lag, slow mobile data connection on iPhone? Typically, the strength of cellular internet on your phone depends on several factors such as region, cellular network type, roaming type, etc. There are some things you can do to get a faster, more reliable cellular Internet connection. Fix 1 – Force Restart iPhone Sometimes, force restarting your device just resets a lot of things, including the cellular connection. Step 1 – Just press the volume up key once and release. Next, press the Volume Down key and release it again. Step 2 – The next part of the process is to hold the button on the right side. Let the iPhone finish restarting. Enable cellular data and check network speed. Check again Fix 2 – Change data mode While 5G offers better network speeds, it works better when the signal is weaker
How to delete sqlserver if the installation fails?
Apr 05, 2024 pm 11:27 PM
If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer
