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

How to split string value into Integer

How to split string value into Integer

--==================================================================

-- how to split string value in to int using delimmiter (you can do these by creating TVF-function.

--==================================================================

CREATE FUNCTION [dbo].[SplitStringReturnInt]

(      @string varchar(max)

       ,@delimiter varchar(10)

)

RETURNS

@return TABLE (id int)

AS

BEGIN

       SET @string = RTRIM(LTRIM(@delimiter)) + RTRIM(LTRIM(@string)) + RTRIM(LTRIM(@delimiter))

       DECLARE @x bit = 1

      

       ;WITH E1(N)

       AS

       (      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

              UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

       ), E2(N)

       AS

       (      SELECT 1

              FROM E1 a, E1 b

       ), E4(N)

       AS

       (      SELECT ROW_NUMBER() OVER(ORDER BY @x)

              FROM E2 a, E2 b, E2 c

       )

       INSERT INTO @return

    SELECT    CASE ISNUMERIC(SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1))

                           WHEN 1 THEN SUBSTRING(@string, N + 1, CHARINDEX(@delimiter, @string, N + 1) - N -1)

                     END

    FROM E4

    WHERE N <= LEN(@string) - 1

              AND SUBSTRING(@string, N, 1) = @delimiter

      

       RETURN

END

-- Now Lets Test these function.

Declare @stre varchar(max) = ('1234567891,234234,23424,2342,324234,2,4234,23423,243634,345435,53453')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,',')

Declare @stre varchar(max) = ('5345.53453.3453534.345345.3453453.435345.3')

SELECT * FROM [dbo].[SplitStringReturnInt](@stre,'.')

SQL SERVER CURSORS

CURSORS : - In Simple words, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis,

--======================================================

-- SINGLE  VARIABLE AS INPUT TO EXEC PROCEDURE which takes only one variable as input

--======================================================

DECLARE @CLIENTKEY INT           

DECLARE CLIENTKEYCURSOR CURSOR

              FOR SELECT ID FROM customer 

OPEN CLIENTKEYCURSOR                                    -- open cursor

                                                       -- DO SOMETHING USEFUL

       FETCH NEXT FROM CLIENTKEYCURSOR                 -- fetch record

       INTO @CLIENTKEY

       WHILE @@FETCH_STATUS = 0

              BEGIN 

                     FETCH NEXT FROM CLIENTKEYCURSOR   -- loop through every record

                           INTO @CLIENTKEY

                           EXEC dbo.uspUpdateClientDate @CLIENTKEY

                           --SELECT   @CLIENTKEY

               END         

CLOSE CLIENTKEYCURSOR                                  -- close cursor

DEALLOCATE CLIENTKEYCURSOR                             -- deallocate cursor

--======================================================

---- MORE THAN ONE VARIABLE AS INPUT TO EXEC PROCEDURE which takes three variable as input

--======================================================

DECLARE @ID INT,@NAME VARCHAR(100), @AD VARCHAR(100)

DECLARE  NAMECURSOR CURSOR

               FOR SELECT ID, NAME, [ADD] FROM CUSTOMER

OPEN NAMECURSOR

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

       WHILE @@FETCH_STATUS = 0

       BEGIN

              FETCH NEXT FROM NAMECURSOR

                     INTO @ID, @NAME, @AD

           EXEC [dbo].[UspInsertCustomerDetails] @ID, @NAME, @AD

       END

CLOSE NAMECURSOR

DEALLOCATE NAMECURSOR