Feeds:
Posts
Comments

Archive for October, 2012

SQL Server – How to find Who Deleted What records at What Time

Posted in Solutions, SQL SERVER, tagged , , , , on October 24, 2012| 44 Comments »

In my first article I wrote how to recover deleted data from SQL Server. I received queries inquiring, if it is possible to recover the deleted data, is it also possible to know who deleted it and when ?

Let me explain it with simple example :

Create Table tbl_Sample
([ID] int identity(1,1) ,
[Name] varchar(50))
GO
Insert into tbl_Sample values ('Letter A')
Insert into tbl_Sample values ('Letter B')
Insert into tbl_Sample values ('Letter C')

Select * from tbl_Sample

Now, you can change logins and delete records.

Given below is the code that can give you the recovered data with the user name who deleted it and the date and time as well.

-- Script Name: Recover_Deleted_Data_With_UID_Date_Time_Proc
-- Script Type : Recovery Procedure
-- Develop By: Muhammad Imran
-- Date Created: 24 Oct 2012
-- Modify Date:
-- Version    : 1.0
-- Notes      :

CREATE PROCEDURE Recover_Deleted_Data_With_UID_Date_Time_Proc
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(Max),
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS

DECLARE @RowLogContents VARBINARY(8000)
DECLARE @TransactionID NVARCHAR(Max)
DECLARE @AllocUnitID BIGINT
DECLARE @AllocUnitName NVARCHAR(Max)
DECLARE @SQL NVARCHAR(Max)
DECLARE @Compatibility_Level INT

SELECT @Compatibility_Level=dtb.compatibility_level
FROM
master.sys.databases AS dtb WHERE dtb.name=@Database_Name

Print @Compatibility_Level
--IF ISNULL(@Compatibility_Level,0)<=80
--BEGIN
--	RAISERROR('The compatibility level should be equal to or greater SQL SERVER 2005 (90)',16,1)
--	RETURN
--END

IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_SCHEMA]+'.'+[TABLE_NAME]=@SchemaName_n_TableName)=0
BEGIN
RAISERROR('Could not found the table in the defined database',16,1)
RETURN
END

DECLARE @bitTable TABLE
(
[ID] INT,
[Bitvalue] INT
)
--Create table to set the bit position of one byte.

INSERT INTO @bitTable
SELECT 0,2 UNION ALL
SELECT 1,2 UNION ALL
SELECT 2,4 UNION ALL
SELECT 3,8 UNION ALL
SELECT 4,16 UNION ALL
SELECT 5,32 UNION ALL
SELECT 6,64 UNION ALL
SELECT 7,128

--Create table to collect the row data.
DECLARE @DeletedRecords TABLE
(
[Row ID]			INT IDENTITY(1,1),
[RowLogContents]	VARBINARY(8000),
[AllocUnitID]		BIGINT,
[Transaction ID]	NVARCHAR(Max),
[FixedLengthData]	SMALLINT,
[TotalNoOfCols]		SMALLINT,
[NullBitMapLength]	SMALLINT,
[NullBytes]			VARBINARY(8000),
[TotalNoofVarCols]	SMALLINT,
[ColumnOffsetArray]	VARBINARY(8000),
[VarColumnStart]	SMALLINT,
[Slot ID]			INT,
[NullBitMap]		VARCHAR(MAX)

)
--Create a common table expression to get all the row data plus how many bytes we have for each row.
;WITH RowData AS (
SELECT

[RowLog Contents 0] AS [RowLogContents]

,[AllocUnitID] AS [AllocUnitID]

,[Transaction ID] AS [Transaction ID]

--[Fixed Length Data] = Substring (RowLog content 0, Status Bit A+ Status Bit B + 1,2 bytes)
,CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) AS [FixedLengthData]  --@FixedLengthData

-- [TotalnoOfCols] =  Substring (RowLog content 0, [Fixed Length Data] + 1,2 bytes)
,CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2)))) as  [TotalNoOfCols]

--[NullBitMapLength]=ceiling([Total No of Columns] /8.0)
,CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) as [NullBitMapLength]

--[Null Bytes] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [NullBitMapLength] )
,SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3,
CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))) as [NullBytes]

--[TotalNoofVarCols] = Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) AS [TotalNoofVarCols]

--[ColumnOffsetArray]= Substring (RowLog content 0, Status Bit A+ Status Bit B + [Fixed Length Data] +1, [Null Bitmap length] + 2 , [TotalNoofVarCols]*2 )
,(CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
SUBSTRING([RowLog Contents 0]
, CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)) + 2
, (CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END)
* 2)  ELSE null  END) AS [ColumnOffsetArray]

--	Variable column Start = Status Bit A+ Status Bit B + [Fixed Length Data] + [Null Bitmap length] + 2+([TotalNoofVarCols]*2)
,CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)
THEN  (
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 4

+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0))

+ ((CASE WHEN SUBSTRING([RowLog Contents 0], 1, 1) In (0x10,0x30,0x70) THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0],
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 3
+ CONVERT(INT, ceiling(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], CONVERT(SMALLINT, CONVERT(BINARY(2)
,REVERSE(SUBSTRING([RowLog Contents 0], 2 + 1, 2)))) + 1, 2))))/8.0)), 2))))  ELSE null  END) * 2))

ELSE null End AS [VarColumnStart]
,[Slot ID]
FROM sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))

AND Context IN ('LCX_MARK_AS_GHOST', 'LCX_HEAP') AND Operation in ('LOP_DELETE_ROWS')
And SUBSTRING([RowLog Contents 0], 1, 1)In (0x10,0x30,0x70)

/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DELETE'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)),

--Use this technique to repeate the row till the no of bytes of the row.
N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

INSERT INTO @DeletedRecords
SELECT	RowLogContents
,[AllocUnitID]
,[Transaction ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
,[Slot ID]
---Get the Null value against each column (1 means null zero means not null)
,[NullBitMap]=(REPLACE(STUFF((SELECT ',' +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(NullBytes, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(NullBytes, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

FROM
N4 AS Nums
Join RowData AS C ON n<=NullBitMapLength
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',',''))
FROM RowData D

IF (SELECT COUNT(*) FROM @DeletedRecords)=0
BEGIN
RAISERROR('There is no data in the log as per the search criteria',16,1)
RETURN
END

DECLARE @ColumnNameAndData TABLE
(
[Transaction ID]   varchar(100),
[Row ID]			int,
[Rowlogcontents]	varbinary(Max),
[NAME]				sysname,
[nullbit]			smallint,
[leaf_offset]		smallint,
[length]			smallint,
[system_type_id]	tinyint,
[bitpos]			tinyint,
[xprec]			tinyint,
[xscale]			tinyint,
[is_null]			int,
[Column value Size]int,
[Column Length]	int,
[hex_Value]		varbinary(max),
[Slot ID]			int,
[Update]			int
)

--Create common table expression and join it with the rowdata table
-- to get each column details
/*This part is for variable data columns*/
--@RowLogContents,
--(col.columnOffValue - col.columnLength) + 1,
--col.columnLength
--)
INSERT INTO @ColumnNameAndData
SELECT
[Transaction ID],
[Row ID],
Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0
THEN
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
END)
END)  AS [Column value Size],

(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0  THEN
(Case

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000
THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end)

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000
THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000
THEN (CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

END) AS [Column Length]

,(CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN  NULL ELSE
SUBSTRING
(
Rowlogcontents,
(

(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000
THEN
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) - POWER(2, 15)
ELSE
CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
END)

-
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN  (Case When [System_type_id]In (35,34,99) Then 16 else 24  end) --24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)

) + 1,
(Case When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN  (Case When [System_type_id] In (35,34,99) Then 16 else 24  end) --24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) >30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN  (Case When [System_type_id] In (35,34,99) Then 16 else 24  end) --24
When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])<30000

THEN ABS(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart]))

When CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) <30000 And
ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])>30000

THEN POWER(2, 15) +CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2))))
- ISNULL(NULLIF(CONVERT(INT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])

END)
)

END) AS hex_Value
,[Slot ID]
,0
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits On A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset<0
UNION
/*This part is for fixed data columns*/
SELECT
[Transaction ID],
[Row ID],
Rowlogcontents,
NAME ,
cols.leaf_null_bit AS nullbit,
leaf_offset,
ISNULL(syscolumns.length, cols.max_length) AS [length],
cols.system_type_id,
cols.leaf_bit_position AS bitpos,
ISNULL(syscolumns.xprec, cols.precision) AS xprec,
ISNULL(syscolumns.xscale, cols.scale) AS xscale,
SUBSTRING([nullBitMap], cols.leaf_null_bit, 1) AS is_null,
(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C WHERE cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5 AS [Column value Size],
syscolumns.length AS [Column Length]

,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING
(
Rowlogcontents,(SELECT TOP 1 ISNULL(SUM(CASE WHEN C.leaf_offset >1 And C.leaf_bit_position=0 THEN max_length ELSE 0 END),0) FROM
sys.system_internals_partition_columns C where cols.partition_id =C.partition_id And C.leaf_null_bit<cols.leaf_null_bit)+5
,syscolumns.length) END AS hex_Value
,[Slot ID]
,0
FROM @DeletedRecords A
Inner Join sys.allocation_units allocunits ON A.[AllocUnitId]=allocunits.[Allocation_Unit_Id]
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2 AND partitions.partition_id = allocunits.container_id)
INNER JOIN sys.system_internals_partition_columns cols ON cols.partition_id = partitions.partition_id
LEFT OUTER JOIN syscolumns ON syscolumns.id = partitions.object_id AND syscolumns.colid = cols.partition_column_id
WHERE leaf_offset>0
Order By nullbit

Declare @BitColumnByte as int
Select @BitColumnByte=CONVERT(INT, ceiling( Count(*)/8.0)) from @ColumnNameAndData Where [System_Type_id]=104

;With N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y),
CTE As(
Select RowLogContents,[nullbit]
,[BitMap]=Convert(varbinary(1),Convert(int,Substring((REPLACE(STUFF((SELECT ',' +
(CASE WHEN [ID]=0 THEN CONVERT(NVARCHAR(1),(SUBSTRING(hex_Value, n, 1) % 2))  ELSE CONVERT(NVARCHAR(1),((SUBSTRING(hex_Value, n, 1) / [Bitvalue]) % 2)) END) --as [nullBitMap]

from N4 AS Nums
Join @ColumnNameAndData AS C ON n<=@BitColumnByte And [System_Type_id]=104 And bitpos=0
Cross Join @bitTable WHERE C.[RowLogContents]=D.[RowLogContents] ORDER BY [RowLogContents],n ASC FOR XML PATH('')),1,1,''),',','')),bitpos+1,1)))
FROM @ColumnNameAndData D Where  [System_Type_id]=104)

Update A Set [hex_Value]=[BitMap]
from @ColumnNameAndData  A
Inner Join CTE B On A.[RowLogContents]=B.[RowLogContents]
And A.[nullbit]=B.[nullbit]

/**************Check for BLOB DATA TYPES******************************/
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @CurrentLSN INT
DECLARE @LinkID INT
DECLARE @Context VARCHAR(50)
DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @LCX_TEXT_MIX VARBINARY(MAX)

declare @temppagedata table
(
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

declare @pagedata table
(
[Page ID] sysname,
[File IDS] int,
[Page IDS] int,
[AllocUnitId] bigint,
[ParentObject] sysname,
[Object] sysname,
[Field] sysname,
[Value] sysname)

DECLARE @ModifiedRawData TABLE
(
[ID] INT IDENTITY(1,1),
[PAGE ID] VARCHAR(MAX),
[FILE IDS] INT,
[PAGE IDS] INT,
[Slot ID]  INT,
[AllocUnitId] BIGINT,
[RowLog Contents 0_var] VARCHAR(Max),
[RowLog Length] VARCHAR(50),
[RowLog Len] INT,
[RowLog Contents 0] VARBINARY(Max),
[Link ID] INT default (0),
[Update] INT
)

DECLARE Page_Data_Cursor CURSOR FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for deleted records of BLOB data type& Get its Slot No, Page ID & AllocUnit ID*/
SELECT LTRIM(RTRIM(Replace([Description],'Deallocated',''))) AS [PAGE ID]
,[Slot ID],[AllocUnitId],NULL AS [RowLog Contents 0],NULL AS [RowLog Contents 0],Context
FROM    sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Operation IN ('LOP_MODIFY_ROW') AND [Context] IN ('LCX_PFS')
AND Description Like '%Deallocated%'
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
AND [Transaction Name]='DELETE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)
GROUP BY [Description],[Slot ID],[AllocUnitId],Context

UNION

SELECT [PAGE ID],[Slot ID],[AllocUnitId]
,Substring([RowLog Contents 0],15,LEN([RowLog Contents 0])) AS [RowLog Contents 0]
,CONVERT(INT,Substring([RowLog Contents 0],7,2)),Context --,CAST(RIGHT([Current LSN],4) AS INT) AS [Current LSN]
FROM    sys.fn_dblog(NULL, NULL)
WHERE
AllocUnitId IN
(SELECT [Allocation_unit_id] FROM sys.allocation_units allocunits
INNER JOIN sys.partitions partitions ON (allocunits.type IN (1, 3)
AND partitions.hobt_id = allocunits.container_id) OR (allocunits.type = 2
AND partitions.partition_id = allocunits.container_id)
WHERE object_id=object_ID('' + @SchemaName_n_TableName + ''))
AND Context IN ('LCX_TEXT_MIX') AND Operation in ('LOP_DELETE_ROWS')
/*Use this subquery to filter the date*/
AND [TRANSACTION ID] IN (SELECT DISTINCT [TRANSACTION ID] FROM    sys.fn_dblog(NULL, NULL)
WHERE Context IN ('LCX_NULL') AND Operation in ('LOP_BEGIN_XACT')
And [Transaction Name]='DELETE'
And  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context

WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @hex_pageid AS VARCHAR(Max)
/*Page ID contains File Number and page number It looks like 0001:00000130.
In this example 0001 is file Number &  00000130 is Page Number & These numbers are in Hex format*/
SET @Fileid=SUBSTRING(@ConsolidatedPageID,0,CHARINDEX(':',@ConsolidatedPageID)) -- Seperate File ID from Page ID

SET @hex_pageid ='0x'+ SUBSTRING(@ConsolidatedPageID,CHARINDEX(':',@ConsolidatedPageID)+1,Len(@ConsolidatedPageID))  ---Seperate the page ID
SELECT @Pageid=Convert(INT,cast('' AS XML).value('xs:hexBinary(substring(sql:variable("@hex_pageid"),sql:column("t.pos")) )', 'varbinary(max)')) -- Convert Page ID from hex to integer
FROM (SELECT CASE substring(@hex_pageid, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)

IF @Context='LCX_PFS'
BEGIN
DELETE @temppagedata
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 1) with tableresults,no_infomsgs;');
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@fileid,@pageid,@AllocUnitID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata
END
ELSE IF @Context='LCX_TEXT_MIX'
BEGIN
INSERT INTO  @ModifiedRawData SELECT @ConsolidatedPageID,@fileid,@pageid,@Slotid,@AllocUnitID,NULL,0,CONVERT(INT,CONVERT(VARBINARY,REVERSE(SUBSTRING(@LCX_TEXT_MIX,11,2)))),@LCX_TEXT_MIX,@LinkID,0
END
FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID,@LCX_TEXT_MIX,@LinkID,@Context
END

CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor

DECLARE @Newhexstring VARCHAR(MAX);

--The data is in multiple rows in the page, so we need to convert it into one row as a single hex value.
--This hex value is in string format
INSERT INTO @ModifiedRawData ([PAGE ID],[FILE IDS],[PAGE IDS],[Slot ID],[AllocUnitId]
,[RowLog Contents 0_var]
, [RowLog Length])
SELECT [Page ID],[FILE IDS],[PAGE IDS],Substring([ParentObject],CHARINDEX('Slot', [ParentObject])+4, (CHARINDEX('Offset', [ParentObject])-(CHARINDEX('Slot', [ParentObject])+4))-2 ) as [Slot ID]
,[AllocUnitId]
,Substring((
SELECT
REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And
[Object] Like '%Memory Dump%'  Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
FOR XML PATH('') ),1,1,'') ,' ','')
),1,20000) AS [Value]

,
Substring((
SELECT '0x' +REPLACE(STUFF((SELECT REPLACE(SUBSTRING([Value],CHARINDEX(':',[Value])+1,CHARINDEX('†',[Value])-CHARINDEX(':',[Value])),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And Substring(B.[ParentObject],CHARINDEX('Slot', B.[ParentObject])+4, (CHARINDEX('Offset', B.[ParentObject])-(CHARINDEX('Slot', B.[ParentObject])+4)) )=Substring(C.[ParentObject],CHARINDEX('Slot', C.[ParentObject])+4, (CHARINDEX('Offset', C.[ParentObject])-(CHARINDEX('Slot', C.[ParentObject])+4)) ) And
[Object] Like '%Memory Dump%'  Order By '0x'+ LEFT([Value],CHARINDEX(':',[Value])-1)
FOR XML PATH('') ),1,1,'') ,' ','')
),7,4) AS [Length]

From @pagedata B
Where [Object] Like '%Memory Dump%'
Group By [Page ID],[FILE IDS],[PAGE IDS],[ParentObject],[AllocUnitId]--,[Current LSN]
Order By [Slot ID]

UPDATE @ModifiedRawData  SET [RowLog Len] = CONVERT(VARBINARY(8000),REVERSE(cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Length]"),0))', 'varbinary(Max)')))
FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ModifiedRawData  SET [RowLog Contents 0] =cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"),0))', 'varbinary(Max)')
FROM @ModifiedRawData Where [LINK ID]=0

Update B Set B.[RowLog Contents 0] =
(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  A.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  A.[RowLog Contents 0]
END)
,B.[Update]=ISNULL(B.[Update],0)+1
from @ModifiedRawData B
LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))
And A.[Link ID]=B.[Link ID]
LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
And C.[Link ID]=B.[Link ID]
Where  (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

Update B Set B.[RowLog Contents 0] =
(CASE WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  A.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
WHEN A.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  A.[RowLog Contents 0]
END)
--,B.[Update]=ISNULL(B.[Update],0)+1
from @ModifiedRawData B
LEFT Join @ModifiedRawData A On A.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],15+14,2))))
And A.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],19+14,2))))
And A.[Link ID]<>B.[Link ID] And B.[Update]=0
LEFT Join @ModifiedRawData C On C.[Page IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],27+14,2))))
And C.[File IDS]=Convert(int,Convert(Varbinary(Max),Reverse(Substring(B.[RowLog Contents 0],31+14,2))))
And C.[Link ID]<>B.[Link ID] And B.[Update]=0
Where  (A.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ModifiedRawData  SET [RowLog Contents 0] =
(Case When [RowLog Len]>=8000 Then
Substring([RowLog Contents 0] ,15,[RowLog Len])
When [RowLog Len]<8000 Then
SUBSTRING([RowLog Contents 0],15+6,Convert(int,Convert(varbinary(max),REVERSE(Substring([RowLog Contents 0],15,6)))))
End)
FROM @ModifiedRawData Where [LINK ID]=0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=[PAGE IDS]
AND  Convert(int,Substring([hex_value],9,2)) =B.[Link ID]
Where [System_Type_Id] In (99,167,175,231,239,241,165,98) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=
(CASE WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  B.[RowLog Contents 0]+C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NULL AND C.[RowLog Contents 0] IS NOT NULL THEN  C.[RowLog Contents 0]
WHEN B.[RowLog Contents 0] IS NOT NULL AND C.[RowLog Contents 0] IS NULL THEN  B.[RowLog Contents 0]
END)
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
LEFT JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],5,4))))=B.[PAGE IDS]  And B.[Link ID] =0
LEFT JOIN @ModifiedRawData C ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],17,4))))=C.[PAGE IDS]  And C.[Link ID] =0
Where [System_Type_Id] In (99,167,175,231,239,241,165,98)  And (B.[RowLog Contents 0] IS NOT NULL OR C.[RowLog Contents 0] IS NOT NULL)

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
And Convert(int,Substring([hex_value],3,2))=[Link ID]
Where [System_Type_Id] In (35,34,99) And [Link ID] <>0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
--,A.[Update]=A.[Update]+10
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],9,4))))=[PAGE IDS]
Where [System_Type_Id] In (35,34,99) And [Link ID] =0

UPDATE @ColumnNameAndData SET [hex_Value]=[RowLog Contents 0]
--,A.[Update]=A.[Update]+1
FROM @ColumnNameAndData A
INNER JOIN @ModifiedRawData B ON
Convert(int,Convert(Varbinary(Max),Reverse(Substring([hex_value],15,4))))=[PAGE IDS]
Where [System_Type_Id] In (35,34,99) And [Link ID] =0

Update @ColumnNameAndData set [hex_value]= 0xFFFE + Substring([hex_value],9,LEN([hex_value]))
--,[Update]=[Update]+1
Where [system_type_id]=241

CREATE TABLE [#temp_Data]
(
[FieldName]  VARCHAR(MAX),
[FieldValue] NVARCHAR(MAX),
[Rowlogcontents] VARBINARY(8000),
[Row ID] int,
[Transaction ID] VARCHAR(100),
[Deletion Date Time] DATETIME,
[Deleted By User Name] VARCHAR(Max)
)

INSERT INTO #temp_Data
SELECT NAME,
CASE
WHEN system_type_id IN (231, 239) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value)))  --NVARCHAR ,NCHAR
WHEN system_type_id IN (167,175) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value)))  --VARCHAR,CHAR
WHEN system_type_id IN (35) THEN  LTRIM(RTRIM(CONVERT(VARCHAR(max),hex_Value))) --Text
WHEN system_type_id IN (99) THEN  LTRIM(RTRIM(CONVERT(NVARCHAR(max),hex_Value))) --nText
WHEN system_type_id = 48 THEN CONVERT(VARCHAR(MAX), CONVERT(TINYINT, CONVERT(BINARY(1), REVERSE (hex_Value)))) --TINY INTEGER
WHEN system_type_id = 52 THEN CONVERT(VARCHAR(MAX), CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (hex_Value)))) --SMALL INTEGER
WHEN system_type_id = 56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(hex_Value)))) -- INTEGER
WHEN system_type_id = 127 THEN CONVERT(VARCHAR(MAX), CONVERT(BIGINT, CONVERT(BINARY(8), REVERSE(hex_Value))))-- BIG INTEGER
WHEN system_type_id = 61 Then CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (hex_Value))),100) --DATETIME
WHEN system_type_id =58 Then CONVERT(VARCHAR(MAX),CONVERT(SMALLDATETIME,CONVERT(VARBINARY(8000),REVERSE(hex_Value))),100) --SMALL DATETIME
WHEN system_type_id = 108 THEN CONVERT(VARCHAR(MAX),CONVERT(NUMERIC(38,20), CONVERT(VARBINARY,CONVERT(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- NUMERIC
WHEN system_type_id =106 THEN CONVERT(VARCHAR(MAX), CONVERT(DECIMAL(38,20), CONVERT(VARBINARY,Convert(VARBINARY(1),xprec)+CONVERT(VARBINARY(1),xscale))+CONVERT(VARBINARY(1),0) + hex_Value)) --- DECIMAL
WHEN system_type_id In(60,122) THEN CONVERT(VARCHAR(MAX),Convert(MONEY,Convert(VARBINARY(8000),Reverse(hex_Value))),2) --MONEY,SMALLMONEY
WHEN system_type_id = 104 THEN CONVERT(VARCHAR(MAX),CONVERT (BIT,CONVERT(BINARY(1), hex_Value)%2))  -- BIT
WHEN system_type_id =62 THEN  RTRIM(LTRIM(STR(CONVERT(FLOAT,SIGN(CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT)) * (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x000FFFFFFFFFFFFF) * POWER(CAST(2 AS FLOAT), -52)) * POWER(CAST(2 AS FLOAT),((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x7ff0000000000000) / EXP(52 * LOG(2))-1023))),53,LEN(hex_Value)))) --- FLOAT
When system_type_id =59 THEN  Left(LTRIM(STR(CAST(SIGN(CAST(Convert(VARBINARY(8000),REVERSE(hex_Value)) AS BIGINT))* (1.0 + (CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS BIGINT) & 0x007FFFFF) * POWER(CAST(2 AS Real), -23)) * POWER(CAST(2 AS Real),(((CAST(CONVERT(VARBINARY(8000),Reverse(hex_Value)) AS INT) )& 0x7f800000)/ EXP(23 * LOG(2))-127))AS REAL),23,23)),8) --Real
WHEN system_type_id In (165,173) THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') -- BINARY,VARBINARY
WHEN system_type_id =34 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)')  --IMAGE
WHEN system_type_id =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
WHEN system_type_id =231 THEN CONVERT(VARCHAR(MAX),CONVERT(sysname,hex_Value)) --SYSNAME
WHEN system_type_id =241 THEN CONVERT(VARCHAR(MAX),CONVERT(xml,hex_Value)) --XML

WHEN system_type_id =189 THEN (CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)') --TIMESTAMP
WHEN system_type_id=98 THEN (CASE
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=56 THEN CONVERT(VARCHAR(MAX), CONVERT(INT, CONVERT(BINARY(4), REVERSE(Substring(hex_Value,3,Len(hex_Value))))))  -- INTEGER
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=108 THEN CONVERT(VARCHAR(MAX),CONVERT(numeric(38,20),CONVERT(VARBINARY(1),Substring(hex_Value,3,1)) +CONVERT(VARBINARY(1),Substring(hex_Value,4,1))+CONVERT(VARBINARY(1),0) + Substring(hex_Value,5,Len(hex_Value)))) --- NUMERIC
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=167 THEN LTRIM(RTRIM(CONVERT(VARCHAR(max),Substring(hex_Value,9,Len(hex_Value))))) --VARCHAR,CHAR
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,Substring((hex_Value),3,20))) --UNIQUEIDENTIFIER
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=61 THEN CONVERT(VARCHAR(MAX),CONVERT(DATETIME,CONVERT(VARBINARY(8000),REVERSE (Substring(hex_Value,3,LEN(hex_Value)) ))),100) --DATETIME
WHEN CONVERT(INT,SUBSTRING(hex_Value,1,1))=165 THEN '0x'+ SUBSTRING((CASE WHEN CHARINDEX(0x,cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'VARBINARY(8000)')) = 0 THEN '0x' ELSE '' END) +cast('' AS XML).value('xs:hexBinary(sql:column("hex_Value"))', 'varchar(max)'),11,LEN(hex_Value)) -- BINARY,VARBINARY
END)

END AS FieldValue
,[Rowlogcontents]
,[Row ID]
,[Transaction ID]
,null
,null
FROM @ColumnNameAndData ORDER BY nullbit

--Find the user ID and date time
Update #temp_Data Set [Deleted By User Name]=[name]
,[Deletion Date Time] = [Begin Time]
from #temp_Data  A
Inner Join fn_dblog(NULL,NULL) B On A.[Transaction ID]= B.[Transaction ID]
Inner Join sys.sysusers  C On B.[Transaction SID]=C.[Sid]
Where B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='DELETE'

--Create the column name in the same order to do pivot table.

DECLARE @FieldName VARCHAR(max)
Declare @AdditionalField VARCHAR(max)
SET @FieldName = STUFF(
(
SELECT ',' + CAST(QUOTENAME([Name]) AS VARCHAR(MAX)) FROM syscolumns WHERE id=object_id('' + @SchemaName_n_TableName + '')
FOR XML PATH('')), 1, 1, '')

--Finally did pivot table and get the data back in the same format.

Set @AdditionalField=@FieldName + ' ,[Deleted By User Name],[Deletion Date Time]'

SET @sql = 'SELECT ' + @AdditionalField  + ' FROM #temp_Data PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ')) AS pvt'
Print @sql
EXEC sp_executesql @sql

GO
--Execute the procedure like
--Recover_Deleted_Data_With_UID_Date_Time_Proc 'Database Name','Schema.table name'
--EXAMPLE #1 : FOR ALL DELETED RECORDS
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc 'test','dbo.tbl_sample'
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Deleted_Data_With_UID_Date_Time_Proc 'test','dbo.tbl_sample' ,'2011/12/01','2012/01/30'
--It will give you the result of all deleted records with the user name and date & time of deletion.

Read Full Post »

SQL Server 2012 – List of all sequences available in the Database

Posted in Solutions, SQL SERVER, tagged , , , , , , on October 24, 2012| Leave a Comment »

SQL Server 2012 came up with multiple good features. One of them is sequence. I have discussed this feature in my earlier artcile.

In this article, we will discuss how to find list of all sequences along with  its current value & other different properties in the database. So, we can achieve this with the help of system view namely sys.sequences.

Given below are the list of properties, we need to find in all sequences in the database.

  • Sequence Name
  • object ID
  • Creation Date
  • Last Modified Date
  • Schema
  • Numeric Precision
  • Numeric Scale
  • Start Value
  • Increment Valye
  • Min Value
  • Max Value
  • Is Cycle Enabled
  • Sequence Cache Type
  • Cache Size
  • Current Value
SELECT
seq.name AS [Sequence Name],
seq.object_id AS [Object ID],
seq.create_date AS [Creation Date],
seq.modify_date AS [Last Modified Date],
SCHEMA_NAME(seq.schema_id) AS [Schema],
CAST(seq.precision AS int) AS [Numeric Precision],
CAST(seq.scale AS int) AS [Numeric Scale],
ISNULL(seq.start_value,N'''') AS [Start Value],
ISNULL(seq.increment,N'''') AS [Increment Value],
ISNULL(seq.minimum_value,N'''') AS [Min Value],
ISNULL(seq.maximum_value,N'''') AS [Max Value],
CAST(seq.is_cycling AS bit) AS [Is Cycle Enabled],
ISNULL(seq.cache_size,0) AS [Cache Size],
ISNULL(seq.current_value,N'''') AS [Current Value]
FROM
sys.sequences AS seq

Read Full Post »

SQL SERVER – Using Decode in SQL Server

Posted in Solutions, SQL SERVER, tagged , , , , on October 23, 2012| 2 Comments »

I was reading a function in Oracle namely “Decode”. It is very handy when it comes to search a variable and return the result based on search. It has been asked on multiple forums, what the EQUIVALENT of DECODE IN SQL SERVER is? Basically, we can achieve the same in SQL Server using Case and IIF statements.

Lets view the decode in Oracle.

Select DECODE(PoleDirection, 'North','N', 'South','S', 'East','E','WEST','W', 'Not Applicable') AS [Poles]
FROM TablePoles

Lets view the Equivalent Code in SQL Server.

Declare @PoleDirection as varchar(10)
Set @PoleDirection= 'North'

SELECT CASE WHEN @PoleDirection = 'North' THEN 'N'
WHEN @PoleDirection = 'South' THEN 'S'
WHEN @PoleDirection = 'East' THEN 'E'
WHEN @PoleDirection = 'WEST' THEN 'W'
ELSE 'Not Applicable' END AS [Poles]

Decode function in SQL server

Given below is the DECODE function in SQL SERVER.

Create FUNCTION [dbo].[DECODE] (
@Expression nvarchar(max),
@String nvarchar(Max))
RETURNS nvarchar(Max)
AS
Begin
Declare @Delimiter as varchar(1)
Declare @ReturnValue as nvarchar(max)
Set @Delimiter=','
Declare @Xml AS XML

Declare @Table TABLE(
[ID] int Identity(1,1),
Splitcolumn VARCHAR(MAX)
)
SET @Xml = cast(('<A>'+replace(@String,@Delimiter,'</A><A>')+'</A>') AS XML)
INSERT INTO @Table SELECT A.value('.', 'varchar(max)') as [Column] FROM @Xml.nodes('A') AS FN(A)

IF (Select Count (*) from @Table A Where @Expression=A.[SplitColumn])=1
Begin
Select top 1 @ReturnValue=B.[Splitcolumn] from @Table A
Left Join @Table B On A.[ID]<B.[ID]
Where @Expression=A.[SplitColumn]
Order By A.[ID]
END
Else
BEGIN
Select top 1 @ReturnValue=A.[Splitcolumn]  from @Table A
Order By A.[ID] DESC
END
RETURN @ReturnValue
End
GO

Syntax :

Decode (expression , search , result [, search , result]... [, default])

Examples 1:

Create table tbl_Sample1
(
[ID] varchar(6),
[Employee Name] varchar(50),
[Performance Evaluation] varchar(10)
)
GO
Insert into tbl_Sample1 values ('STD001','Bob','Excellent')
Insert into tbl_Sample1 values ('STD002','Alexander','Good')
Insert into tbl_Sample1  values ('STD003','Hosanna','Fair')

GO
SELECT [ID],[Employee Name],[Performance Evaluation]
,dbo.Decode( [Performance Evaluation],'Excellent,100,Good,80,Fair,60,Unknown')
as [Performace Index]
from tbl_Sample1
GO

Examples 2:

Create table tbl_Sample2
(
[ID] int ,
[Country] varchar(50),
[Short Name] varchar(3)
)
GO

Insert into tbl_Sample2 Values (1,'PAKISTAN','PAK')
Insert into tbl_Sample2 Values (2,'UNITED STATES OF AMERICA','USA')
Insert into tbl_Sample2 Values (3,'UNITED KINGDOM','UK')
Insert into tbl_Sample2 Values (4,'UNITED ARAB EMIRATES','UAE')
go

SELECT [ID],[Country],[Short Name]
,dbo.Decode([Short Name],'PAK,RS,USA,USD,UK,GBP,UAE,AED,Not Matched')
as [Currency]
from tbl_Sample2
GO

Read Full Post »

SQL SERVER 2012– Change Color & Theme of SQL Server Management Studio(SSMS)

Posted in SQL SERVER, Tips, tagged , , , , on October 23, 2012| Leave a Comment »

Color plays a very important role is our life. I would like to discuss this important role with respect to SQL SERVER MANAGEMENT STUDIO.

Lets do it step by step.

Step 1 :
Open your SSMS and go to Tools \ Options \ Environment \ Fonts and Colors.

Step 2 :
First, we will change the color of Gird Results and Font.

Step 3 :
In this step, we need to change the color of comments.

Step 4 :
Close your SSMS and open it again.

Step 5 :
Lets write the query, comment it and view the difference in results and comments color.

Note : If you would like to reset these changes, press the “Use Defaults” button (on top right).

Read Full Post »

SQL Server – Multiple ways to find identity column

Posted in Solutions, SQL SERVER, tagged , , , , , on October 22, 2012| 3 Comments »

Column identity is something that we come across every now and then. We use it to generate an auto number column in any table.
Today, we will discuss multiple ways to find identity column in the entire user tables.

Method 1 : (sys.columns)

Use Adventureworks
GO

Select Object_Name([object_id]) as [Table Name]
,[name] as [Column Name]
,is_identity
from sys.columns
Where is_identity=1 And Objectproperty(object_id,'IsUserTable')=1

Method 2 : (sys.objects & sys.all_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.objects A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 3 : (sys.tables & sys.all_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.tables A
Inner Join sys.all_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 4 : (sys.objects & sys.identity_columns)

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.objects A
Inner Join sys.identity_columns B
On A.[object_id]=B.[object_id]
Where A.type='U'

Method 5 : (sys.tables & sys.identity_columns) 

Use Adventureworks
GO

Select A.[name] as [Table Name]
,B.[name] as [Column Name]
,B.is_identity
from sys.tables A
Inner Join sys.identity_columns B
On A.[object_id]=B.[object_id]
Where A.type='U' And is_identity=1

Method 6 : (INFORMATION_SCHEMA.COLUMNS)

Use Adventureworks
GO

;With CTE AS (Select Table_Schema+'.'+Table_Name as [Table_Name],[Column_name] from
INFORMATION_SCHEMA.COLUMNS)
Select Table_Name
,[Column_name]
,COLUMNPROPERTY(OBJECT_ID(Table_Name),[Column_name],'IsIdentity')AS 'IsIdentity'
from CTE Where
COLUMNPROPERTY( OBJECT_ID(Table_Name),[Column_name],'IsIdentity')=1

How SQL Server checks the identity of the table

I opened a table having identity column in the design mode and at the same time opened SQL server profile to view the qurey. Given below is the query.

In this query SQL picks a lot of information but what we need to check is identity column and it is available on line # 4.

select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema
, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name
, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol,
col.is_identity
, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40)
, idc.increment_value) end
, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed,
convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl
, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published,
col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name
, schema_name(robj.schema_id) as Rul_schema, col.default_object_id
, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst
, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema
, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol
, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id
, case when(cmc.column_id is null) then null else cmc.definition end as formular
, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted
, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic
, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema
, col.is_xml_document, col.is_sparse, col.is_column_set
, CONVERT(bit, case when (ISNULL(ftc.statistical_semantics, 0) = 0) then 0 else 1 end)
as is_StatisticalSemantics, col.is_filestream
from sys.columns col
left outer join sys.types st on st.user_type_id = col.user_type_id
left outer join sys.types bt on bt.user_type_id = col.system_type_id
left outer join sys.objects robj on robj.object_id = col.rule_object_id
and robj.type = 'R'
left outer join sys.objects dobj on dobj.object_id = col.default_object_id
and dobj.type = 'D'
left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id
and defCst.parent_column_id = col.column_id
left outer join sys.identity_columns idc on idc.object_id = col.object_id
and idc.column_id = col.column_id
left outer join sys.computed_columns cmc on cmc.object_id = col.object_id
and cmc.column_id = col.column_id
left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id
and ftc.column_id = col.column_id
left outer join sys.xml_schema_collections xmlcoll
on xmlcoll.xml_Collection_id = col.xml_Collection_id
where col.object_id =
object_id(N'dbo.tbl_test')
order by col.column_id

Read Full Post »

SQL SERVER 2012 – T-SQL Scripts to Find Maximum between

Posted in Solutions, SQL SERVER, tagged , , , on October 20, 2012| Leave a Comment »

I need to find maximum between two numbers, So, I was searching for a solution to find maximum number between two numbers and I found few interesting articles by Pinal Dave And Madhivana .

I also would like to add one more solution with the help of new logical function “IIF” in SQL SERVER 2012.

Example 1 :

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
9.22

Example 2 : (With NULL) 

DECLARE @Value1 DECIMAL(5,2) = 9.22
DECLARE @Value2 DECIMAL(5,2) = NULL
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
9.22

Example 3 : (With Negative Value) 

DECLARE @Value1 DECIMAL(5,2) = -9.22
DECLARE @Value2 DECIMAL(5,2) = 8.34
SELECT IIF(ISNULL(@Value1,0) > ISNULL(@Value2,0) ,@Value1, @Value2) AS MaxColumn
--RESULT
8.34

Read Full Post »

SQL SERVER – Change Database Compatibility Level

Posted in Solutions, SQL SERVER, tagged , , , , , , , , , , on October 20, 2012| Leave a Comment »

SQL SERVER 2012 :

Given below are the methods you can use to change the compatibility level in SQL Server 2012.

Method 1 :

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 110
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 100
ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 90

But you cannot change it to  COMPATIBILITY LEVEL 80 in SQL SERVER 2012.

Lets try to change the compatibility level  to 80 in SQL SERVER 2012.

ALTER DATABASE [AdventureWorks2012] SET COMPATIBILITY_LEVEL = 80
--RESULT

Msg 15048, Level 16, State 1, Line 1
Valid values of the database compatibility level are 90, 100, or 110.

Version of SQL Server with respect to compatibility level.

  • SQL Server 2012 = COMPATIBILITY_LEVEL 110
  • SQL Server 2008 = COMPATIBILITY_LEVEL 100
  • SQL Server 2005 = COMPATIBILITY_LEVEL 90
  • SQL Server 2000 = COMPATIBILITY_LEVEL 80

Method 2 :

EXEC sp_dbcmptlevel [AdventureWorks2012] , 110;

This method will not be supported from the future version of SQL SERVER. So, don’t use this in the future development.



SQL SERVER 2005 / 2008 :

Given below is the method to set the compatibility level in SQL SERVER 2005 and above.


EXEC sp_dbcmptlevel [AdventureWorks] , 90;


					
Read Full Post »

				

							


		
			

				

					
SQL SERVER – DO’s & Don’ts of sp_rename

					

					Posted in SQL SERVER, Tips, tagged , , , , ,  on October 17, 2012|
													Leave a Comment »											

				


				

					
Today, we will discuss about the Do’s and Don’ts of SQL SERVER- System Stored Procedure -“sp_rename”. This procedure is used to change the name of any object created by user.


Syntax :


sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]



Do’s for Sp_Name :

Given below are the transactions that you can do via sp_rename.


    

  • Rename a table
    • 

  • Rename a column
    • 

  • Rename an index
    • 

  • Rename constraints
    • 



Don’ts for Sp_Name :

Given below are the transactions that you must not do via sp_rename.


    

  • Donot Rename a stored procedure
    • 

  • Donot Rename a function
    • 

  • Donot Rename a view
    • 

  • Donot Rename a trigger
    • 



Syntax and example of Do’s of Sp_rename are available here.


Lets discuss why we must not rename stored procedure,function,view & trigger via sp_rename.


Lets create a table and view to demonstrate.


Create table tbl_Sample
([Sno] int,
[Dept_Name] varchar(50)
)
Go
Create View vw_Sample
As
Select * from tbl_Sample



Lets run the given below query to check that each and every references are updated.


Select A.[object_id],B.[object_id],A.[name],[Definition]
from sys.Objects  A
Inner Join sys.sql_modules B on A.[object_id]=B.[object_id]
And A.[Type]='V'





You can see that name and definition column matched with respect to name.

Now, lets rename the view.


USE Test
GO
--Syntax sp_rename 'Old View name', 'New view Name'
EXEC sp_rename 'dbo.vw_Sample', 'vw_Sample2';



Lets check again if the references are updated.


Select A.[object_id],B.[object_id],A.[name],[Definition]
from sys.Objects  A
Inner Join sys.sql_modules B on A.[object_id]=B.[object_id]
And A.[Type]='V'





You can see that if you rename view the reference(Definition column of sys.sql_modules) is not updated because of this SQL Server recommends not to rename these objects via sp_rename. Always use drop and create to rename these objects.

					
Read Full Post »

				

							


		
			

				

					
SQL Server – Best way to get Table Row Count

					

					Posted in Solutions, SQL SERVER, tagged , , ,  on October 16, 2012|
													11 Comments »											

				


				

					
Today, we will discuss the efficient way to count records of any table. Lets suppose you have millions of records in a table. How will you calculate the record count quickly ?


Let me explain this with simple examples :


Example 1 :

First, lets use the traditional way to count records from the table.


SET STATISTICS TIME ON

Select Count(*) as [Total Records]
from [tbl_Sample]

--Result
SQL Server parse and compile time:
CPU time = 2 ms, elapsed time = 2 ms.

SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.

Total Records
-------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 5046 ms,  elapsed time = 26518 ms.



As per the results, the above query took almost 26 seconds.


Example 2 :

Lets go to SSMS to view how SQL Server calculates the record count.



Right click on the table and go to properties. It will give you a lot of information including record count in a fraction of seconds.




Now, we need to find out what query is running behind these properties. So lets open the SQL Server profiler. A lot of queries were running to calculate different information but I grabbed the query that calculates record count.


Given below is the query that SQL Server uses to calculate the record count of any table.


SET STATISTICS TIME ON

select SUM([rows]) as [Total records] from sys.partitions
where object_id=object_ID('tbl_Sample') And [index_id] =1

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 3 ms.

SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.
Total records
--------------------
44040192

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.



Conclusion :

You can view a remarkable difference between both queries.

In Example 1 , the SQL needs to calculate the record count from the table but in Example 2, the Information is already calculated and you just need to query it.

					
Read Full Post »

				

							


		
		
Older Posts »