How To Check Time Difference in SQL Server

-- How To Check Time Difference in SQL Server

 DECLARE @StartTime DateTime = DateAdd(Mi, -15, GetDate())

 Select @StartTime  StartTime;

 Declare @EndTime DateTime = GetDate()

 Select @EndTime  EndTime

 -- Now See the Difference betwen StartTime and EndTime

Select Convert(Varchar(15), DateAdd(Ms, DateDiff(Ms, @StartTime, @EndTime),0),114) as TimeDifference

--WAITFOR DELAY '00:00:10'

Merge Statement E.G

MERGE: Performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. For example:

-- MERGE STATEMENT E.G

DECLARE @sCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

DECLARE @dCUSTOMER TABLE (ID INT, NAME VARCHAR(50), ADDD VARCHAR(50))

INSERT INTO @sCUSTOMER VALUES

                              (1, 'KRIS','HYDERABAD'),

                              (2,'KUMAR','HAYATHNAGAR'),

                              (3,'SAM','LONDON'),

                              (4,'TOM','NEWYORK')

 INSERT INTO @dCUSTOMER VALUES

                              (100,'MOON','PLANET')

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 1 RECORDS

 MERGE @dCUSTOMER DW

 USING @sCUSTOMER DC

      ON DW.ID = DC.ID

      WHEN MATCHED THEN UPDATE

            SET ID = DC.ID,

                   NAME = DC.NAME,

                   ADDD = DC.ADDD

      WHEN NOT MATCHED THEN

                  INSERT  (ID, NAME, ADDD)

                  VALUES (DC.ID, DC.NAME, DC.ADDD)

      WHEN NOT MATCHED BY SOURCE

            THEN DELETE ;

  

 SELECT * FROM @sCUSTOMER -- 4 RECORDS AVAILABLE

 SELECT * FROM @dCUSTOMER -- 4  RECORDS AVAILABLE  BUT PLANET RECORD WILL BE  DELETED BCZ ID NOT MATCHING WITH SOURCE

How to check the last restoration done on database.

If you want to know the last restore done on your database run below query.

WITH LastRestores AS

(

SELECT

    DatabaseName = [d].[name] ,

    [d].[create_date] ,

    [d].[compatibility_level] ,

    [d].[collation_name] ,

    r.*,

    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)

FROM master.sys.databases d

LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name

)

SELECT *

FROM [LastRestores]

WHERE [RowNum] = 1

How to check backup or restore time completion

We may have different way to check completion time Backup/Restore databases using DMV's

I have simple query here.

SELECT

              percent_complete  AS [Completed (%)]

              ,command  AS [Activity]

              ,start_time  AS [ActivityStartTime]

              ,d.name  AS [DatabaseName]

              ,DATEADD(MS,estimated_completion_time ,GETDATE()) AS [TotalTimeRemaining]

              ,(estimated_completion_time /1000/60) AS [RemainingTimeInMins]

              ,(estimated_completion_time /1000) AS [RemainingTimeInSecs]

FROM sys.dm_exec_requests r --ER

INNER JOIN sys.databases d  --sd

ON r.database_id  = d.database_id

WHERE command  LIKE '%RESTORE%'

OR command  LIKE '%BACKUP%'

AND estimated_completion_time  > 0

GO

How to check disabled Index in database

--How to check disabled index in Database

SELECT

    sys.objects.name,

    sys.indexes.name

FROM sys.indexes

    inner join sys.objects ON sys.objects.object_id = sys.indexes.object_id

WHERE sys.indexes.is_disabled = 1

ORDER BY sys.objects.name, sys.indexes.name