Retrieve data after execute TRUNCATE command by the help of Transaction

 

We do use truncate table, but we don't get data after execute this command, 

But how we can  retrieve data after execute TRUNCATE command, so follow the below steps.

-- Create Test Table with "TruncateDemoTable" name

CREATE TABLE TruncateDemoTable (ID INT)

INSERT INTO TruncateDemoTable (ID)

SELECT 1

UNION ALL

SELECT 2

UNION ALL

SELECT 3

GO

-- Check the data before truncate

SELECT * FROM TruncateDemoTable

GO

-- Begin Transaction

BEGIN TRAN

-- Truncate Table

TRUNCATE TABLE TruncateDemoTable

GO

-- Check the data after truncate

SELECT * FROM TruncateDemoTable

GO

-- Rollback Transaction

ROLLBACK TRAN

GO

-- Check the data after Rollback

SELECT * FROM TruncateDemoTable

GO

-- Clean up

DROP TABLE TruncateDemoTable

GO

 

Fastest way to get ROWCOUNT of any table..

During performance health check, we generally use below query to get total number of rows in any table.

SET STATISTICS IO ON 

SELECT COUNT(*) FROM CustomerTbl WITH(NOLOCK)

But this can be take lot of time to scan whole table if table is having TB's of data.

So, below is the alternative query to get row count of all the tables of your database in one go, and if you want, you can search for a particular table as well.

SELECT  SCHEMA_NAME(t.schema_id) SchemaName,

        t.[name] TableName, 

        SUM(p.row_count) TotalRows

FROM sys.tables t

INNER JOIN sys.dm_db_partition_stats p

ON t.object_id = p.object_id AND t.type_desc = 'USER_TABLE'  AND p.index_id IN (0,1)

-- WHERE t.[name] = 'CustomerTbl'

GROUP BY t.schema_id, t.[name]

ORDER BY TotalRows DESC

Use of recursive CTE to get user tree hierarchy (Top to Bottom)

Below script can be use with recursive CTE to get user hierarchy  in SQL Server..

DECLARE @userType INT = 3

DECLARE @emp AS TABLE (Id INT, FName VARCHAR(100),LName VARCHAR(100), ReportTo INT)

INSERT INTO @emp (Id,  FName, LName, ReportTo) VALUES

(1, 'Tom','Joe', 5)

,(2, 'Peter','Pep', 4)

,(3, 'Sem','Leo', 4)

,(4, 'Neeraj','Gusain', 0)

,(5, 'Mark','Pettit', 3)

,(6, 'Sara','Thomas', 2)

,(7, 'Jobb','Mular', 2)

,(8, 'Mark','Rekhei', 5)

,(9, 'Sara','Leon', 3)

,(10, 'Peter','James', 8)

;WITH CTE 

AS

(

SELECT id, FName, LName, ReportTo, 0 as [level]

FROM @emp

WHERE (ReportTo = 0 AND  @userType = 0) OR id = @userType

    UNION ALL

SELECT e.id, e.FName, e.LName, e.ReportTo, (ct.[level] + 1) AS [level]

FROM @emp e

JOIN CTE ct ON ct.id = e.ReportTo

WHERE e.ReportTo <> 0 

)

SELECT * FROM CTE 

ORDER BY id