Get duration of executing script in SQl Server 2008 R2

Question

I have an script to execute and need executing time duration, how can I calculate it?

I think to use this way:

DECLARE @startTime DATETIME
SET @startTime = GETDATE();

--Script


DECLARE @endTime DATETIME
SET @endTime = GETDATE();

PRINT 'Transfer Duration:';
GO
PRINT CONVERT(varchar,@endTime-@startTime,14);
GO

But because I use GO (and I have to) so there is an error, So what is your suggestion to calculate and print duration of executing script (with Go) ?

Answer 1

Use a table to store the start and the end times, throw in your process ID also. Then use DATEDIFF() to calculate the time elapsed.

    CREATE TABLE Profiling(
            spid            smallint        NOT NULL,
            StartTime       DATETIME2       NOT NULL,
            EndTime         DATETIME2
    )

    INSERT INTO Profiling(spid, StartTime)
    SELECT @@SPID, CURRENT_TIMESTAMP
    GO

    -- your queries come here
    -- .....

    UPDATE Profiling
    SET EndTime = CURRENT_TIMESTAMP
    WHERE spid = @@SPID
    GO

    SELECT DATEDIFF(MS, startTime, EndTime) as MilliSeconds
    FROM Profiling
    WHERE spid = @@SPID

Truncate the table from time to time, to avoid collisions of the spid's.

Answer 2

Look at SET STATISTICS TIME statement.