Solutions | SQL Server Portal

Archive for the ‘Solutions’ Category

SQL SERVER 2012- How to format phone numbers

Posted in Solutions, SQL SERVER, tagged Format Function, How to format phone numbers, raresql, SQL, SQL Server, SQL Server 2012 on August 30, 2013| 1 Comment »

Formatting phone number has never been an issue, the only thing that you need to do is, use few string functions and you can convert it into any format. But what encourages me to write this article is that we can format the phone numbers in any format using only one function, i.e. Format (introduced in SQL Server 2012).

Note : I usually recommend that phone number must be properly maintained inside the database. But sometimes we come across this issue in legacy systems.

Sample :
Let me create a sample to explain it.

USE tempdb
GO
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
[ID] INT,
[Phone Numbers] INT
)
GO
INSERT INTO tbl_sample VALUES (1,3333333333)
GO
SELECT * FROM tbl_sample
GO
--OUTPUT

ID Phone Numbers
———– ————-
1 3333333333

(1 row(s) affected)

SOLUTION :
In the solution, we will use FORMAT function and provide a custom format of telephone number such that, the Format function will automatically convert it into custom format.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'###-###-####') AS [Formatted Phone]
FROM tbl_sample
UNION ALL
SELECT
[Phone Numbers]
,FORMAT([Phone Numbers],'(###) ###-####') AS [Formatted Phone]
FROM tbl_sample
GO
--OUTPUT

Read Full Post »

SQL SERVER 2012 – How to register code snippet

Posted in Solutions, SQL SERVER, tagged How to register code snippet, raresql, SQL, SQL Server, SQL Server 2012 on August 26, 2013| 1 Comment »

In my earlier articles, I wrote about how to insert buit-in code snippet in SQL Server script. But sometimes due to the requirements, we need to create our own custom snippet. Once you create the custom snippet(s) , you need to register these snippets, because without registration you cannot use in the SQL Server Query editor.

There are two ways two register the code snippet. Given below are the details.

Method 1 :
In this method, you can add folder having code snippet file(s). The advantage of this method is that you do not need to do one by one code snippet files.
Let me explain it step by step.

Step 1 :
In this step, browse the Tools menu and select the Code Snippet Manager as shown in the picture below.

It will open the code snippet manager as shown in the picture below.

Step 2 :
As you can see above, multiple built-in folders and snippet files for standard objects code snippets are available in the Code Snippet Manager. Now, you need to add custom folder and snippet files in the Code snippet Manager. Click on the Add.. button as shown in the picture below.

Step 3 :
Once you click on the Add.. button, it will open the files explorer, now you need to select the folders having snippet files.You cannot select an individual file using Add.. button. Just to explain this registration, I created one folder namely Custom and placed few code snippet file in it. Once you select the folder click on Select Folder button as shown in the picture below.

Step 4 :
Now, you can see that custom folder and snippet files are registered and added in the Code Snippet Manager as shown in the picture below.

Method 2 :
In this method you can add multiple files into any custom folders.
Let me explain this method step by step.

Step 1 :
This step will be same as method 1.

Step 2 :
Once you are in the Code Snippet Manager, click on Import button as shown in the picture below.

Step 3 :
Once you click on Import button, it will open an explorer and allow you to select code snippet file(s).
Once you selected the file(s), click on Open button as shown in the picture below.

Step 3 :
Once you select the code snippet file(s), it will open an another import window and ask in which custom folder you want to add this file(s) as shown in the picture below.

Step 4 :
Once you select any custom folder for the code snippet file(s), click Finish button. The snippet file(s) will be available in that custom folder as shown in the image below.

Reference : MSDN

Read Full Post »

SQL Server – How to find Who Modified / Updated What records at What Time

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL Server – How to find Who Modified / Updated What records at What Time on August 25, 2013| 11 Comments »

After publishing a solution how to recover the modified / updated records without backup in one of my previous articles, I received lot of queries inquiring how to find who modified / updated and at what time ? So, I started my research and recently developed the solution.

Before proceeding with the solution, let me create a sample to demonstrate the solution.
Given below is the script.

--Create Table
CREATE TABLE [dbo].[Student](
[Sno] [int] NOT NULL,
[Student ID] nvarchar(6) Not NULL ,
[Student name] [varchar](50) NOT NULL,
[Date of Birth]  datetime not null,
[Weight] [int] NULL)
GO
--Inserting data into table

Insert into dbo.[Student] values (1,'STD001','Bob','2003-12-31',40)
Insert into dbo.[Student] values (2,'STD002','Alexander','2004-11-15',35)
GO

--Check the existence of the data
Select * from dbo.[Student]
GO
--OUTPUT

--Modified the date by mistake without where clause
Update [Student] Set [Student Name]='Bob jerry'
GO

--Verify the data has been modified
Select * from dbo.[Student]

GO
--OUTPUT

Solution :
Given below is the stored procedure that you need to execute in the database having modified records.

--DROP PROCEDURE Recover_Modified_Data_Proc
--GO
Create PROCEDURE Recover_Modified_Data_Proc
@Database_Name NVARCHAR(MAX),
@SchemaName_n_TableName NVARCHAR(MAX),
@Date_From DATETIME='1900/01/01',
@Date_To DATETIME ='9999/12/31'
AS
DECLARE @parms nvarchar(1024)
DECLARE @Fileid INT
DECLARE @Pageid INT
DECLARE @Slotid INT
DECLARE @RowLogContents0 VARBINARY(8000)
DECLARE @RowLogContents1 VARBINARY(8000)
DECLARE @RowLogContents3 VARBINARY(8000)
DECLARE @RowLogContents3_Var VARCHAR(MAX)

DECLARE @RowLogContents4 VARBINARY(8000)
DECLARE @LogRecord VARBINARY(8000)
DECLARE @LogRecord_Var VARCHAR(MAX)

DECLARE @ConsolidatedPageID VARCHAR(MAX)
DECLARE @AllocUnitID BIGINT
DECLARE @TransactionID VARCHAR(MAX)
DECLARE @TrID SYSNAME
DECLARE @Operation VARCHAR(MAX)
DECLARE @DatabaseCollation VARCHAR(MAX)

/*  Pick The actual data
*/
DECLARE @temppagedata TABLE
(
[ParentObject] SYSNAME,
[Object] SYSNAME,
[Field] SYSNAME,
[Value] SYSNAME)

DECLARE @pagedata TABLE
(
[Page ID] SYSNAME,
[AllocUnitId] BIGINT,
[Tr ID] SYSNAME,
[ParentObject] SYSNAME,
[Object] SYSNAME,
[Field] SYSNAME,
[Value] SYSNAME)

DECLARE Page_Data_Cursor CURSOR FOR
/*We need to filter LOP_MODIFY_ROW,LOP_MODIFY_COLUMNS from log for modified records & Get its Slot No, Page ID & AllocUnit ID*/
SELECT [PAGE ID],[Slot ID],[AllocUnitId],[Transaction 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 Operation IN ('LOP_MODIFY_ROW','LOP_MODIFY_COLUMNS')  AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*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]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

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

GROUP BY [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID]
ORDER BY [Slot ID]

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TrID

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)

DELETE @temppagedata
-- Now we need to get the actual data (After modification) from the page
INSERT INTO @temppagedata EXEC( 'DBCC PAGE(' + @DataBase_Name + ', ' + @fileid + ', ' + @pageid + ', 3) with tableresults,no_infomsgs;');
-- Add Page Number and allocUnit ID in data to identity which one page it belongs to.
INSERT INTO @pagedata SELECT @ConsolidatedPageID,@AllocUnitID,@TrID,[ParentObject],[Object],[Field] ,[Value] FROM @temppagedata

FETCH NEXT FROM Page_Data_Cursor INTO  @ConsolidatedPageID, @Slotid,@AllocUnitID,@TrID
END

CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor

DECLARE @Newhexstring VARCHAR(MAX);

DECLARE @ModifiedRawData TABLE
(
[ID] INT IDENTITY(1,1),
[PAGE ID] VARCHAR(MAX),
[Slot ID] INT,
[AllocUnitId] BIGINT,
[Tr ID] sysname,
[RowLog Contents 0_var] VARCHAR(MAX),
[RowLog Contents 0] VARBINARY(8000)
)
--The modified 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],[Slot ID],[AllocUnitId],[Tr ID]
,[RowLog Contents 0_var])
SELECT B.[PAGE ID],A.[Slot ID],A.[AllocUnitId],A.[Transaction ID]
,(
SELECT REPLACE(STUFF((SELECT REPLACE(SUBSTRING([VALUE],CHARINDEX(':',[Value])+1,48),'†','')
FROM @pagedata C  WHERE B.[Page ID]= C.[Page ID] And A.[Slot ID] =LTRIM(RTRIM(SUBSTRING(C.[ParentObject],5,3))) And [Object] Like '%Memory Dump%'
Group By [Value] FOR XML PATH('') ),1,1,'') ,' ','')
) AS [Value]

FROM sys.fn_dblog(NULL, NULL) A
INNER JOIN @pagedata B On A.[PAGE ID]=B.[PAGE ID]
AND A.[AllocUnitId]=B.[AllocUnitId]
AND A.[Slot ID] =LTRIM(RTRIM(Substring(B.[ParentObject],5,3)))
AND B.[Object] Like '%Memory Dump%'
WHERE A.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','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*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]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/
GROUP BY B.[PAGE ID],A.[Slot ID],A.[AllocUnitId],A.[Transaction ID]
ORDER BY [Slot ID]

-- Convert the hex value data in string, convert it into Hex value as well.
UPDATE @ModifiedRawData  SET [RowLog Contents 0] = cast('' AS XML).value('xs:hexBinary(substring(sql:column("[RowLog Contents 0_var]"), 0) )', 'varbinary(max)')
FROM @ModifiedRawData

---Now we have modifed data plus its slot ID , page ID and allocunit as well.
--After that we need to get the old values before modfication, these datas are in chunks.
DECLARE Page_Data_Cursor CURSOR FOR

SELECT [PAGE ID],[Slot ID],[AllocUnitId],[Transaction ID],[RowLog Contents 0], [RowLog Contents 1],[RowLog Contents 3],[RowLog Contents 4]
,SUBSTRING ([Log Record],[Log Record Fixed Length],([Log Record Length]+1)-([Log Record Fixed Length])) AS [Log Record]
,Operation
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','LOP_MODIFY_COLUMNS') AND [Context] IN ('LCX_HEAP','LCX_CLUSTERED')
/*Use this sub query 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]='UPDATE'
AND  CONVERT(NVARCHAR(11),[Begin Time]) BETWEEN @Date_From AND @Date_To)

/****************************************/
ORDER BY [Slot ID],[Transaction ID] DESC

OPEN Page_Data_Cursor

FETCH NEXT FROM Page_Data_Cursor INTO @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Operation ='LOP_MODIFY_ROW'
BEGIN
/* If it is @Operation Type is 'LOP_MODIFY_ROW' then it is very simple to recover the modified data. The old data is in [RowLog Contents 0] Field and modified data is in [RowLog Contents 1] Field. Simply replace it with the modified data and get the old data.
*/
INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[Tr ID],[RowLog Contents 0_var])
SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],@TransactionID AS [Tr ID]
,REPLACE (UPPER([RowLog Contents 0_var]),UPPER(CAST('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents1") )', 'varchar(max)')),UPPER(cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents0") )', 'varchar(max)'))) AS [RowLog Contents 0_var]
FROM  @ModifiedRawData WHERE [PAGE ID]=@ConsolidatedPageID And [Slot ID]=@Slotid And [AllocUnitId]=@AllocUnitID ORDER BY [ID] DESC

--- Convert the old data which is in string format to hex format.
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 [Slot ID]=@SlotID

END
IF @Operation ='LOP_MODIFY_COLUMNS'
BEGIN

/* If it is @Operation Type is 'LOP_MODIFY_ROW' then we need to follow a different procedure to recover modified
.Because this time the data is also in chunks but merge with the data log.
*/
--First, we need to get the [RowLog Contents 3] Because in [Log Record] field the modified data is available after the [RowLog Contents 3] data.
SET @RowLogContents3_Var=cast('' AS XML).value('xs:hexBinary(sql:variable("@RowLogContents3") )', 'varchar(max)')
SET @LogRecord_Var =cast('' AS XML).value('xs:hexBinary(sql:variable("@LogRecord"))', 'varchar(max)')

DECLARE @RowLogData_Var VARCHAR(Max)
DECLARE @RowLogData_Hex VARBINARY(Max)
---First get the modifed data chunks in string format
SET @RowLogData_Var = SUBSTRING(@LogRecord_Var, CHARINDEX(@RowLogContents3_Var,@LogRecord_Var) +LEN(@RowLogContents3_Var) ,LEN(@LogRecord_Var))
--Then convert it into the hex values.
SELECT @RowLogData_Hex=CAST('' AS XML).value('xs:hexBinary( substring(sql:variable("@RowLogData_Var"),0) )', 'varbinary(max)')
FROM (SELECT CASE SUBSTRING(@RowLogData_Var, 1, 2) WHEN '0x' THEN 3 ELSE 0 END) AS t(pos)
DECLARE @TotalFixedLengthData INT
DECLARE @FixedLength_Offset INT
DECLARE @VariableLength_Offset INT
DECLARE @VariableLength_Offset_Start INT
DECLARE @VariableLengthIncrease INT
DECLARE @FixedLengthIncrease INT
DECLARE @OldFixedLengthStartPosition INT
DECLARE @FixedLength_Loc INT
DECLARE @VariableLength_Loc INT
DECLARE @FixedOldValues VARBINARY(MAX)
DECLARE @FixedNewValues VARBINARY(MAX)
DECLARE @VariableOldValues VARBINARY(MAX)
DECLARE @VariableNewValues VARBINARY(MAX)

-- Before recovering the modfied data we need to get the total fixed length data size and start position of the varaible data

SELECT TOP 1 @TotalFixedLengthData=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))
,@VariableLength_Offset_Start=CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0] , 2 + 1, 2))))+5+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))
FROM @ModifiedRawData
ORDER BY [ID] DESC

SET @FixedLength_Offset= CONVERT(BINARY(2),REVERSE(CONVERT(BINARY(4),(@RowLogContents0))))--)
SET @VariableLength_Offset=CONVERT(int,CONVERT(BINARY(2),REVERSE(@RowLogContents0)))

/* We already have modified data chunks in @RowLogData_Hex but this data is in merge format (modified plus actual data)
So , here we need [Row Log Contents 1] field , because in this field we have the data length both the modified and actual data
so this length will help us to break it into original and modified data chunks.
*/
SET @FixedLength_Loc= CONVERT(INT,SUBSTRING(@RowLogContents1,1,1))
SET @VariableLength_Loc =CONVERT(INT,SUBSTRING(@RowLogContents1,3,1))

/*First , we need to break Fix length data actual with the help of data length  */
SET @OldFixedLengthStartPosition= CHARINDEX(@RowLogContents4,@RowLogData_Hex)
SET @FixedOldValues = SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition,@FixedLength_Loc)
SET @FixedLengthIncrease = (CASE WHEN (Len(@FixedOldValues)%4)=0 THEN 1 ELSE (4-(LEN(@FixedOldValues)%4))  END)
/*After that , we need to break Fix length data modified data with the help of data length  */
SET @FixedNewValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease,@FixedLength_Loc)

/*Same we need to break the variable data with the help of data length*/
SET @VariableOldValues =SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease),@VariableLength_Loc)
SET @VariableLengthIncrease =  (CASE WHEN (LEN(@VariableOldValues)%4)=0 THEN 1 ELSE (4-(Len(@VariableOldValues)%4))+1  END)
SET @VariableOldValues =(CASE WHEN @VariableLength_Loc =1 THEN  @VariableOldValues+0x00 ELSE @VariableOldValues END)

SET @VariableNewValues =SUBSTRING(SUBSTRING(@RowLogData_Hex,@OldFixedLengthStartPosition+@FixedLength_Loc+@FixedLengthIncrease+@FixedLength_Loc+(@FixedLengthIncrease-1)+@VariableLength_Loc+@VariableLengthIncrease,Len(@RowLogData_Hex)+1),1,Len(@RowLogData_Hex)+1) --LEN(@VariableOldValues)

/*here we need to replace the fixed length &  variable length actaul data with modifed data
*/

SELECT TOP 1 @VariableNewValues=CASE
WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)+1),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)+1)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)),[RowLog Contents 0])<>0 THEN  SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues))
WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-1),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-1)--3 --Substring(@VariableNewValues,0,Len(@VariableNewValues)-1)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-2),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-2)
WHEN CHARINDEX(SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-3),[RowLog Contents 0])<>0 THEN SUBSTRING(@VariableNewValues,0,LEN(@VariableNewValues)-3) --5--Substring(@VariableNewValues,0,Len(@VariableNewValues)-3)
END
FROM @ModifiedRawData  WHERE [Slot ID]=@SlotID  ORDER BY [ID] DESC

INSERT INTO @ModifiedRawData ([PAGE ID],[Slot ID],[AllocUnitId],[Tr ID],[RowLog Contents 0_var],[RowLog Contents 0])
SELECT TOP 1  @ConsolidatedPageID AS [PAGE ID],@Slotid AS [Slot ID],@AllocUnitID AS [AllocUnitId],@TransactionID,NULL
,CAST(REPLACE(SUBSTRING([RowLog Contents 0],0,@TotalFixedLengthData+1),@FixedNewValues, @FixedOldValues) AS VARBINARY(max))
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2)
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3, CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)))
+ SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 3 + CONVERT(INT, CEILING(CONVERT(INT, CONVERT(BINARY(2), REVERSE(SUBSTRING([RowLog Contents 0], @TotalFixedLengthData + 1, 2))))/8.0)), 2)
+ SUBSTRING([RowLog Contents 0],@VariableLength_Offset_Start,(@VariableLength_Offset-(@VariableLength_Offset_Start-1)))
+ CAST(REPLACE(SUBSTRING([RowLog Contents 0],@VariableLength_Offset+1,LEN(@VariableNewValues))
, @VariableNewValues
, @VariableOldValues) AS VARBINARY)
+ Substring([RowLog Contents 0],@VariableLength_Offset+LEN(@VariableNewValues)+1,LEN([RowLog Contents 0]))
FROM @ModifiedRawData  WHERE [Slot ID]=@SlotID  ORDER BY [ID] DESC

END

FETCH NEXT FROM Page_Data_Cursor INTO   @ConsolidatedPageID, @Slotid,@AllocUnitID,@TransactionID,@RowLogContents0,@RowLogContents1,@RowLogContents3,@RowLogContents4,@LogRecord,@Operation
END

CLOSE Page_Data_Cursor
DEALLOCATE Page_Data_Cursor

DECLARE @RowLogContents VARBINARY(8000)
Declare @AllocUnitName NVARCHAR(Max)
Declare @SQL NVARCHAR(Max)

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
(
[ID] INT IDENTITY(1,1),
[RowLogContents]    VARBINARY(8000),
[AllocUnitID]       BIGINT,
[Tr ID] sysname			  ,
[Transaction ID]    NVARCHAR(Max),
[Slot ID]           INT,
[FixedLengthData]   SMALLINT,
[TotalNoOfCols]     SMALLINT,
[NullBitMapLength]  SMALLINT,
[NullBytes]         VARBINARY(8000),
[TotalNoofVarCols]  SMALLINT,
[ColumnOffsetArray] VARBINARY(8000),
[VarColumnStart]    SMALLINT,
[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]

,[Tr ID]
,[ID] AS [Transaction ID]

,[Slot ID] as [Slot 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 (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 (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 (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 (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 (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]
From @ModifiedRawData

),

---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]
,[Tr ID]
,[Transaction ID]
,[Slot ID]
,[FixedLengthData]
,[TotalNoOfCols]
,[NullBitMapLength]
,[NullBytes]
,[TotalNoofVarCols]
,[ColumnOffsetArray]
,[VarColumnStart]
--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

CREATE TABLE [#temp_Data]
(

[FieldName]  VARCHAR(MAX) COLLATE database_default NOT NULL,
[FieldValue] VARCHAR(MAX) COLLATE database_default NULL,
[Rowlogcontents] VARBINARY(8000),
[Transaction ID] VARCHAR(MAX) COLLATE database_default NOT NULL,
[Tr ID] SYSNAME,
[Slot ID] INT,
[NonID] INT
--[System_type_id] int
)
---Create common table expression and join it with the rowdata table
--to get each column details
;With CTE AS (
/*This part is for variable data columns*/
SELECT
A.[ID],
Rowlogcontents,
[Transaction ID],
[Tr ID],
[Slot ID],
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,
--Calculate the variable column size from the variable column offset array
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN
CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END) AS [Column value Size],

---Calculate the column length
(CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END) AS [Column Length]

--Get the Hexa decimal value from the RowlogContent
--HexValue of the variable column=Substring([Column value Size] - [Column Length] + 1,[Column Length])
--This is the data of your column but in the Hexvalue
,CASE WHEN SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=1 THEN NULL ELSE
SUBSTRING(Rowlogcontents,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * leaf_offset*-1) - 1, 2)))) ELSE 0 END)
- ((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) + 1,((CASE WHEN leaf_offset<1 and SUBSTRING([nullBitMap], cols.leaf_null_bit, 1)=0 THEN  CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * (leaf_offset*-1)) - 1, 2))))
- ISNULL(NULLIF(CONVERT(SMALLINT, CONVERT(BINARY(2), REVERSE (SUBSTRING ([ColumnOffsetArray], (2 * ((leaf_offset*-1) - 1)) - 1, 2)))), 0), [varColumnStart])
ELSE 0 END))) END AS hex_Value

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
A.[ID],
Rowlogcontents,
[Transaction ID],
[Tr ID],
[Slot ID],
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 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
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 )

--Converting data from Hexvalue to its orgional datatype.
--Implemented datatype conversion mechanism for each datatype
--Select * from sys.columns Where [object_id]=object_id('' + @SchemaName_n_TableName + '')
--Select * from CTE

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),REPLACE(hex_Value, 0x00, 0x20))))  --VARCHAR,CHAR
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 IN( 40) Then CONVERT(VARCHAR(MAX),CONVERT(DATE,CONVERT(VARBINARY(8000),(hex_Value))),100) --DATE This datatype only works for SQL Server 2008
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), CAST(CONVERT(NUMERIC(38,30), CONVERT(VARBINARY,CONVERT(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- NUMERIC
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 =106 THEN CONVERT(VARCHAR(MAX), CAST(CONVERT(Decimal(38,34), CONVERT(VARBINARY,Convert(VARBINARY,xprec)+CONVERT(VARBINARY,xscale))+CONVERT(VARBINARY(1),0) + hex_Value) as FLOAT)) --- DECIMAL
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 =36 THEN CONVERT(VARCHAR(MAX),CONVERT(UNIQUEIDENTIFIER,hex_Value)) --UNIQUEIDENTIFIER
END AS FieldValue
,[Rowlogcontents]
,[Transaction ID]
,[Tr ID]
,[Slot ID]
,[ID]
FROM CTE ORDER BY nullbit

/*Create Update statement*/
/*Now we have the modified and actual data as well*/
/*We need to create the update statement in case of recovery*/

;With CTE AS (SELECT
(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + [A].[FieldValue]+ '''','NULL')+ ' ,'+' '
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='  + ISNULL(+ 'N''' +[A].[FieldValue]+ '''','NULL')+ ' ,'+''
WHEN system_type_id In (58,40,61,36) THEN QUOTENAME([Name]) + '='  + ISNULL(+  ''''+[A].[FieldValue]+ '''','NULL') + '  ,'+' '
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL([A].[FieldValue],'NULL')+ ' ,'+' '
END) as [Field]
,A.[Slot ID]
,A.[Transaction ID] as [Transaction ID]
,A.[tr ID]
,'D' AS [Type]
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID]  FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] On  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND A.[Fieldname] = D.[name]
WHERE ISNULL([A].[FieldValue],'')<>ISNULL([B].[FieldValue],'')
UNION ALL

SELECT(CASE
WHEN system_type_id In (167,175,189) THEN QUOTENAME([Name]) + '=' + ISNULL(+ '''' + [A].[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (231,239) THEN  QUOTENAME([Name]) + '='+ ISNULL(+ 'N''' +[A].[FieldValue]+ '''','NULL')+ ' AND '+''
WHEN system_type_id In (58,40,61,36) THEN QUOTENAME([Name]) + '=' + ISNULL(+  ''''+[A].[FieldValue]+ '''','NULL') + ' AND '+''
WHEN system_type_id In (48,52,56,59,60,62,104,106,108,122,127) THEN QUOTENAME([Name]) + '='  + ISNULL([A].[FieldValue],'NULL') + ' AND '+''
END) AS [Field]
,A.[Slot ID]
,A.[Transaction ID] AS [Transaction ID]
,A.[tr ID]
,'S' AS [Type]
,[A].Rowlogcontents
,[A].[NonID]
FROM #temp_Data AS [A]
INNER JOIN #temp_Data AS [B] ON [A].[FieldName]=[B].[FieldName]
AND [A].[Slot ID]=[B].[Slot ID]
--And [A].[Transaction ID]=[B].[Transaction ID]+1
AND [B].[Transaction ID]=  (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
INNER JOIN sys.columns [D] ON  [object_id]=object_id('' + @SchemaName_n_TableName + '')
AND [A].[Fieldname]=D.[name]
WHERE ISNULL([A].[FieldValue],'')=ISNULL([B].[FieldValue],'')
AND A.[Transaction ID] NOT IN (SELECT Min(Cast([Transaction ID] as int)) as [Transaction ID] FROM #temp_Data AS [C]
WHERE [A].[Slot ID]=[C].[Slot ID]
GROUP BY [Slot ID])
)

,CTEUpdateQuery AS (SELECT 'UPDATE ' +  @SchemaName_n_TableName +  ' SET ' + LEFT(
STUFF((SELECT ' ' + ISNULL([Field],'')+ ' ' FROM CTE B
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,''),

LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE B
WHERE A.[Slot ID]=B.[Slot ID] AND A.[Transaction ID]=B.[Transaction ID] And B.[Type]='D' FOR XML PATH('') ),1,1,'') )-2)

+ '  WHERE  ' +

LEFT(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,'') ,

LEN(STUFF((SELECT ' ' +ISNULL([Field],'')+ ' ' FROM CTE C
WHERE A.[Slot ID]=C.[Slot ID] AND A.[Transaction ID]=C.[Transaction ID] And C.[Type]='S' FOR XML PATH('') ),1,1,''))-4)
AS [Update Statement],
[Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID]
FROM CTE A
GROUP BY [Slot ID]
,[Transaction ID]
,A.[tr ID]
,Rowlogcontents
,[A].[NonID] )

INSERT INTO #temp_Data
SELECT 'Update Statement',ISNULL(A.[Update Statement],''),A.[Rowlogcontents],A.[Transaction ID],A.[tr ID],A.[Slot ID],A.[NonID] FROM CTEUpdateQuery A

INSERT INTO #temp_Data
Select 'Modified User Name',C.[name],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID] from #temp_Data A
Inner Join fn_dblog(NULL,NULL) B On A.[tr ID]= B.[Transaction ID]
And B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='UPDATE'
Inner Join sys.sysusers  C On B.[Transaction SID]=C.[Sid]
Where [FieldName]='Update Statement'
Group By C.[name],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID]
Union ALL
Select 'Modified Date Time',B.[Begin Time],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID] from #temp_Data A
Inner Join fn_dblog(NULL,NULL) B On A.[tr ID]= B.[Transaction ID]
And B.[Operation]='LOP_BEGIN_XACT' And B.[Context]='LCX_NULL' And B.[Transaction Name]='UPDATE'
Where [FieldName]='Update Statement'
Group By B.[Begin Time],A.[RowLogcontents],A.[Transaction ID],A.[Tr ID],A.[Slot ID], A.[NonID]

/**************************/
--Create the column name in the same order to do pivot table.
DECLARE @FieldName 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, '')

Print @FieldName
--Finally did pivot table and got the data back in the same format.
--The [Update Statement] column will give you the query that you can execute in case of recovery.
SET @sql = 'SELECT ' + @FieldName  + ',[Update Statement],[Modified User Name],[Modified Date Time] FROM #temp_Data
PIVOT (Min([FieldValue]) FOR FieldName IN (' + @FieldName  + ',[Update Statement],[Modified User Name],[Modified Date Time])) AS pvt
Where [Transaction ID] NOT In (Select Min(Cast([Transaction ID] as int)) as [Transaction ID] from #temp_Data
Group By [Slot ID]) ORDER BY Convert(int,[Slot ID]),Convert(int,[Transaction ID])'
Print @sql
EXEC sp_executesql @sql

GO
--Execute the procedure like
--Recover_Modified_Data_Proc 'Database name''Schema.table name','Date from' ,'Date to'

----EXAMPLE #1 : FOR ALL MODIFIED RECORDS
EXEC Recover_Modified_Data_Proc 'test','dbo.Student'
GO
--EXAMPLE #2 : FOR ANY SPECIFIC DATE RANGE
EXEC Recover_Modified_Data_Proc  'test','dbo.Student','2000/01/01','9999/12/31'
--It will give you the result of all modified records.
--OUTPUT

Read Full Post »

SQL SERVER 2012 – How to convert a dd/mm/yyyy string to datetime

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL Server, SQL SERVER - How to convert a dd/mm/yyyy string to datetime, SQL Server 2012 on August 21, 2013| 4 Comments »

Whenever you work on legacy data, you usually expect some sort of data in incorrect data types, but of course with the compatibility. Recently, I migrated one of my client’s data from legacy system to SQL Server 2012 and I came across an issue where the client had a date in varchar field and the data had been placed in dd/mm/yyyy format in that field. The data type had to be changed from varchar to datetime. Lets try to convert it from dd/mm/yyyy (varchar) to datetime.

Given below is the script.

DECLARE @Varchar_Date varchar(11)
SET @Varchar_Date='31/12/2012'
SELECT CONVERT(datetime,@Varchar_Date)
--OUTPUT

Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Ooopsss, you cannot convert it directly. I continued with my research. Finally resolved it. Given below is the solution.

Solution :
In this solution, we will use TRY_PARSE function introduced in SQL Server 2012. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @Varchar_Date as varchar(11)
SET @Varchar_Date='31/12/2013'
SELECT TRY_PARSE(@Varchar_Date AS DATETIME USING 'en-GB')
As [Result]
--OUTPUT

Result
———————–
2013-12-31 00:00:00.000

(1 row(s) affected)

Let me know if you came across this issue and resolved it in a better way.
Note : For the earlier versions of SQL Servers, you can convert it like this.

Read Full Post »

SQL SERVER 2012 – How to prevent sequence values from being changed

Posted in Solutions, SQL SERVER, tagged raresql, Sequence object, SQL, SQL Server, SQL Server 2012, SQL SERVER 2012 - How to prevent sequence values from being changed on August 18, 2013| Leave a Comment »

A couple of month ago, I migrated one of my client’s data from SQL Server earlier versions to SQL Server 2012. The most important part was, I changed all the identity columns of the tables from identity to sequence in the entire database, here is the solution. But another important aspect that I did not discuss is that, whenever you migrate or create a new sequence column make sure that it is not be changeable like identity columns. In the identity column it is implemented by default but in sequence column, you must implement it. But how ?

First of all let me update an identity column of a table to show how identity columns are prevented from being changed (by default). Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
UPDATE HumanResources.Department
SET [DepartmentID]=17
WHERE [DepartmentID]=16
--OUTPUT

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column ‘DepartmentID’.

Solution :

By default, sequence columns are not prevented from being changed, you need to manually control this aspect. Let me show you the solution step by step.

Step 1 :
Let me create a table to demonstrate the solution.

USE tempdb
GO
--DROP TABLE tbl_sample_Seq
--GO
CREATE TABLE tbl_sample_Seq
(
[ID] int,
[Name] varchar(50)
)
GO

Step 2 :
Once you created the table, create a sequence object to insert [ID]s in the table.

USE tempdb
GO
--DROP SEQUENCE [dbo].[Seq_Student]
--GO
CREATE SEQUENCE [dbo].[Seq_Student]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 1000
GO

Step 3 :
Once you created the sequence object, insert few records using sequence object in the table. Given below is the script.

USE tempdb
GO
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Imran')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Bob')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Alexander')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'Hosanna')
INSERT INTO tbl_sample_Seq VALUES (NEXT VALUE FOR [Seq_Student],'William')
GO

Step 4 :
Let me browse the table and show the records that have been inserted successfully.

USE tempdb
GO
SELECT * FROM tbl_sample_Seq
GO
--OUTPUT

Step 5 :
Lets update the sequence column. Do not DO this step in your production database. This step is just to demonstrate the issue.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

(1 row(s) affected)

Step 6 :
Ooops….in the above step the [ID] column of the table got modified. Let me create an update trigger on the [ID] column of the table, so that we can prevent sequence values from being changed. This is the most important step while implementing sequence object in a table. Do not skip it.

USE tempdb
GO
CREATE TRIGGER trg_update
ON tbl_sample_Seq
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;

DECLARE @OldID int
DECLARE @NewID int

SELECT @OldID =[ID] FROM deleted
SELECT @NewID =[ID] FROM inserted

--Print @OldID
--Print @NewID

IF @OldID <> @NewID
BEGIN
RAISERROR('Failed', 16, 1);
ROLLBACK TRANSACTION
END
END
GO

Step 7 :
Once the update trigger has been created, lets update the sequence column ([ID]) again.

USE tempdb
GO
UPDATE tbl_sample_Seq SET [ID]=33 WHERE [ID]=3
GO
--OUTPUT

Msg 50000, Level 16, State 1, Procedure trg_update, Line 19
Failed
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

Conclusion :
Whenever you implement sequence object in any table, make sure you create the trigger to prevent sequence values from being changed.

Let me know if you came across this issue and its solutions.

Read Full Post »

SQL SERVER – How to add st, nd, rd & th to dates

Posted in Solutions, SQL SERVER, tagged Format Function, IIF function, SQL, SQL Server, SQL SERVER - How to add st rd th to dates, SQL Server 2012 on August 17, 2013| 4 Comments »

Sometimes, date formatting becomes much critical when you know that the format is not supported by the application but to gain the client’s satisfaction, you need to format as per the requirement. I recently came across a date format where I had to add st, nd, rd & th to the dates. Fortunately my client is using SQL Server 2012. So I thought of developing the solution using new functions introduced in SQL Server 2012 to reduce the code and improve the performance.
Let me create a sample to demonstrate the solution.

USE [tempdb]
GO
--DROP TABLE [dbo].[tbl_sample]
--GO
--Create a sample table
CREATE TABLE [dbo].[tbl_sample](
[ID] [int] NULL,
[Date] [date] NULL
)
GO
--Insert records in the table
INSERT INTO dbo.[tbl_sample]
SELECT 1 AS [ID] ,N'2013-08-01' AS [Date] UNION ALL
SELECT 2 AS [ID] ,N'2013-08-02' AS [Date] UNION ALL
SELECT 3 AS [ID] ,N'2013-08-03' AS [Date] UNION ALL
SELECT 4 AS [ID] ,N'2013-08-04' AS [Date] UNION ALL
SELECT 5 AS [ID] ,N'2013-08-05' AS [Date] UNION ALL
SELECT 6 AS [ID] ,N'2013-08-06' AS [Date] UNION ALL
SELECT 7 AS [ID] ,N'2013-08-07' AS [Date] UNION ALL
SELECT 8 AS [ID] ,N'2013-08-08' AS [Date] UNION ALL
SELECT 9 AS [ID] ,N'2013-08-09' AS [Date] UNION ALL
SELECT 10 AS [ID] ,N'2013-08-10' AS [Date] UNION ALL
SELECT 11 AS [ID] ,N'2013-08-11' AS [Date] UNION ALL
SELECT 12 AS [ID] ,N'2013-08-12' AS [Date] UNION ALL
SELECT 13 AS [ID] ,N'2013-08-13' AS [Date] UNION ALL
SELECT 14 AS [ID] ,N'2013-08-14' AS [Date] UNION ALL
SELECT 15 AS [ID] ,N'2013-08-15' AS [Date] UNION ALL
SELECT 16 AS [ID] ,N'2013-08-16' AS [Date] UNION ALL
SELECT 17 AS [ID] ,N'2013-08-17' AS [Date] UNION ALL
SELECT 18 AS [ID] ,N'2013-08-18' AS [Date] UNION ALL
SELECT 19 AS [ID] ,N'2013-08-19' AS [Date] UNION ALL
SELECT 20 AS [ID] ,N'2013-08-20' AS [Date] UNION ALL
SELECT 21 AS [ID] ,N'2013-08-21' AS [Date] UNION ALL
SELECT 22 AS [ID] ,N'2013-08-22' AS [Date] UNION ALL
SELECT 23 AS [ID] ,N'2013-08-23' AS [Date] UNION ALL
SELECT 24 AS [ID] ,N'2013-08-24' AS [Date] UNION ALL
SELECT 25 AS [ID] ,N'2013-08-25' AS [Date] UNION ALL
SELECT 26 AS [ID] ,N'2013-08-26' AS [Date] UNION ALL
SELECT 27 AS [ID] ,N'2013-08-27' AS [Date] UNION ALL
SELECT 28 AS [ID] ,N'2013-08-28' AS [Date] UNION ALL
SELECT 29 AS [ID] ,N'2013-08-29' AS [Date] UNION ALL
SELECT 30 AS [ID] ,N'2013-08-30' AS [Date] UNION ALL
SELECT 31 AS [ID] ,N'2013-08-31' AS [Date]

Solution :
In this solution, I used IIF & Format function introduced in SQL Server 2012.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample
--OUTPUT

Let me know if you come across such issues and the proposed solution.

Read Full Post »

SQL SERVER HEKATON – How to update a Primary key in the memory optimized table

Posted in Solutions, SQL SERVER, tagged Msg 10770 Level 16 State 10, raresql, SQL SERVER HEKATON, SQL SERVER HEKATON - How to update a Primary key in the memory optimized table on August 6, 2013| Leave a Comment »

‘Update statement’ is one of the frequently used Data Manipulation Language (DML) in SQL Server scripting. We usually use this statement to rectify the data. Recently, I was working on memory optimized table and I tried to update a primary key column and I came across an error.
Given below is the script that we usually use to update any disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
--DROP TABLE tbl_Product_Master
--GO
--Create table
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO
--Insert record into the table
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (1, 'SQL Server 2012',getdate())
GO
--Update table
Update tbl_Product_Master SET [Product ID]=11 WHERE [Product ID]=1
GO

Msg 10770, Level 16, State 10, Line 40
The operation ‘primary key update’ is not supported with memory optimized tables.

Oooops.. I am unable to update a primary key in memory optimized table. So how to update the primary key of a memory optimized table?

Let me explain it step by step.

Step 1 :
In this step, you need to delete the record which you need to update .

USE hkNorthwind
GO
DELETE FROM tbl_Product_Master WHERE [Product ID]=1
GO

Step 2 :
In this step, you need to insert the record with the updated data.

USE hkNorthwind
GO
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (11, 'SQL Server 2012',getdate())
GO

Step 3 :
Browse the table and check whether the data has been updated or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

Read Full Post »

SQL SERVER – How to format values as a percentage

Posted in Solutions, SQL SERVER, tagged Format() Function in SQL Server 2012, raresql, SQL, SQL Server, SQL Server 2012 on August 4, 2013| 2 Comments »

In SQL Server, we usually come across a situation where we need to format values as a percentage. Usually, developers format the values as a percentage manually. But in this article, I will share a shortcut to format values as a percentage.
Before proceeding with the solution, I would like to create a sample table and data to demonstrate the formatting.

USE tempdb
GO
--DROP TABLE [dbo].[Student]
--GO
CREATE TABLE [dbo].[Student]
(
[Student ID] int,
[Student Name] [varchar](50) NOT NULL,
[Percentage] numeric(18,2)
)
GO
Insert into dbo.[Student] values (1,'Bob', 0.10)
Insert into dbo.[Student] values (2,'Alexander',0.50)
Insert into dbo.[Student] values (3,'Hosanna',0.85)
Insert into dbo.[Student] values (4,'William',0.11)
Insert into dbo.[Student] values (5,'Hulda',1.20)
Insert into dbo.[Student] values (6,'Jacoba',1.15)
GO

Solution 1 :
In this solution, we need to manually calculate the percentage and then format it. Given below is the script.

--This script is compatible with SQL Server 2000 and above.
USE tempdb
GO
SELECT [Student ID]
,[Student Name]
,[Percentage]
,CONVERT(VARCHAR(50),[Percentage]*100)+' %' AS [%] FROM dbo.Student
GO

Solution 2 :
In this solution, we will use a new function shipped with SQL Server 2012 namely Format. You just need to pass the values in this function and it will format values as a percentage (without any hurdles of calculations).
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
SELECT [Student ID]
,[Student Name]
,[Percentage]
,FORMAT([Percentage],'p') AS [%] FROM dbo.Student
GO

Read Full Post »

SQL SERVER HEKATON – How to implement default constraints in the memory optimized table

Posted in Solutions, SQL SERVER, tagged raresql, SQL, SQL SERVER HEKATON, SQL SERVER HEKATON - How to implement default constraints in the memory optimized table on August 2, 2013| 1 Comment »

‘How to implement default constraints in the memory optimized table’ seems very simple to me and I tried to implement in the same way, as we usually do it in the disk based table. But the output was not as per the expectation. Let me show you the output.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime default Getdate()
) WITH (MEMORY_OPTIMIZED = ON)
GO

Msg 10770, Level 16, State 13, Line 3
The feature ‘default constraints’ is not supported with memory optimized tables.

Oooops.. I am unable to create a default constraint in memory optimized table.

So, can we create a default constraint column in memory optimized table ? The answer is No & Yes.

Why No: Because you cannot create a default constraint column in memory optimized table.
Why Yes:Because you can implement the default constraint functionality in memory optimized table using stored procedure.

Let me create the table & stored procedure to implement default constraints in the memory optimized table.

Step 1 :
First of all, you need to create a memory optimized table without any default constraint.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_Product_Master
(
[Product ID] INT not null primary key nonclustered hash
with (bucket_count = 1024),
[Product Name] NVARCHAR(100),
[Creation Datetime] datetime
) WITH (MEMORY_OPTIMIZED = ON)
GO

Step 2 :
Once you have created the memory optimized table, create a stored procedure to insert records in it.

USE hkNorthwind
GO
CREATE PROCEDURE usp_Insert_Product_Master
@ProductID int,
@ProductName nvarchar(100)
AS
INSERT INTO tbl_Product_Master
([Product ID],[Product Name],[Creation Datetime])
VALUES (@ProductID,@ProductName,getdate())

Step 3 :
Insert the records using stored procedure in the memory optimized table.
Given below is the script.

USE hkNorthwind
GO
EXEC usp_Insert_Product_Master 1, 'SQL Server 2012'
GO
EXEC usp_Insert_Product_Master 2, 'SQL Server 2014'

Step 4 :
Just browse the table and check whether the default constraint is implemented or not.

USE hkNorthwind
GO
SELECT * FROM tbl_Product_Master
GO
--OUTPUT

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »

SQL SERVER – How to concatenate int and varchar

Posted in Solutions, SQL SERVER, tagged SQL, SQL Server, SQL SERVER - How to concatenate int and varchar, SQL Server 2012, SQL Server 2012 - CONCAT on July 31, 2013| 2 Comments »

Concatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. But what encourages me to write this article is, we can resolve this issue using a built in function in SQL Server 2012.
Let me show you the issue that we generally face when we concatenate int and varchar. Given below is the script.

Use AdventureWorks2012
GO
SELECT [DepartmentID] + ' ' + [Name] AS [Department ID & Name]
FROM [HumanResources].[Department]
GO
--OUTPUT

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘Document Control’ to data type smallint.

Solution 1:
In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONVERT(VARCHAR(5),[DepartmentID]) + ' ' + [Name]
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

Solution 2:
In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it.
Given below is the script.

Use AdventureWorks2012
GO
SELECT CONCAT([DepartmentID] , ' ' , [Name] )
AS [Department ID & Name]
FROM [HumanResources].[Department]
--OUTPUT

Conclusion :
I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here.

Read Full Post »

« Newer Posts - Older Posts »

SQL Server Portal

Real world SQL scenarios & its unique & optimized solutions

Archive for the ‘Solutions’ Category

SQL SERVER 2012- How to format phone numbers

SQL SERVER 2012 – How to register code snippet

SQL Server – How to find Who Modified / Updated What records at What Time

SQL SERVER 2012 – How to convert a dd/mm/yyyy string to datetime

SQL SERVER 2012 – How to prevent sequence values from being changed

SQL SERVER – How to add st, nd, rd & th to dates

SQL SERVER HEKATON – How to update a Primary key in the memory optimized table

SQL SERVER – How to format values as a percentage

SQL SERVER HEKATON – How to implement default constraints in the memory optimized table

SQL SERVER – How to concatenate int and varchar

Recent Posts

Archives

Categories

Blog Stats

Follow Blog via Email

Find us on Facebook

Disclaimer