Search code examples
sqlsql-server-2008functiontimeexecution

Why the execution time is different for following SQL Server code?


Note: Neglect the logic inside GO...........GO, focus on only Line 1 and Line N

SELECT DATENAME(NANOSECOND, GETDATE()) --Line 1

GO
BEGIN
    DECLARE @t1 NVARCHAR(100)
    DECLARE @datetime1 DATETIME = '12-23-35';
    DECLARE @time1 TIME(4) = '11:10:05.1234';
    DECLARE @time2 TIME(5) = '11:10:05.12345';
    DECLARE @datetime2 DATETIME;

    SET @datetime2 = @datetime1
    SET @datetime2 = @time1

    DECLARE @datetime3 DATETIME = @time2
    DECLARE @datetime4 SMALLDATETIME = @time1

    IF @datetime1 = @datetime2
        PRINT 'True'
    ELSE
        PRINT 'False'

    IF @datetime2 = @datetime3
        PRINT 'True'
    ELSE
        PRINT 'False'

    IF @datetime3 = @datetime4
        PRINT 'True'
    ELSE
        PRINT 'False'

    SELECT @datetime1 AS 'datetime1'
        ,@datetime2 AS 'datetimevar2'
        ,@datetime3 AS 'datetimevar3'
        ,@datetime4 AS 'datetimevar4'
END
GO

SELECT DATENAME(NANOSECOND, GETDATE()) --Line N

Question: For the above code, at each new execution Difference between Line N Result - Line1 Result is not same. Why is this So?


Solution

  • There are lots of reasons, bu the most simple one is that the computer is multitasking, i.e. doing many things at the same time. So, it depends on what else it's doing.

    Believe me, you should not worry about this kind of things. Why is it important? You have to worry about important differences in execution times when you find a performance problem. Worrying about this is useless.