Example SQL Server snippet demonstrating how to capture and compare the timings of various SQL command operations. Commonly useful in performance testing and benchmark scenarios.

/**
How to record T-SQL execution times using a SQL Prompt snippet
Based on code by Phil Factor (https://www.red-gate.com/hub/product-learning/sql-prompt/record-t-sql-execution-times-using-sql-prompt-snippet).
**/
declare @log table (
    TheOrder int identity(1, 1),
    WhatHappened varchar(200) not null,
    WhenItDid datetime2 default getdate() not null
);

----------------------- START OF TIMING -----------------------------------
insert into @log (WhatHappened) select 'Select record(s) performance';
---------------------------------------------------------------------------

-- 1.
select * from dbo.EmailAddresses;
insert into @log (WhatHappened) select 'Select all records using wildcard field selector';

-- 2.
select EmailAddress from dbo.EmailAddresses;
insert into @log (WhatHappened) select 'Select all records and one field';

-- 3.
select top(1000) EmailAddress from dbo.EmailAddresses;
insert into @log (WhatHappened) select 'Select top 1000 records and one field without an order by clause';

-- 4.
select top(1000) EmailAddress from dbo.EmailAddresses order by EmailAddress;
insert into @log (WhatHappened) select 'Select top 1000 records and one field with an order by clause';

----------------------- LIST OUT ALL THE TIMINGS --------------------------
select ending.WhatHappened, datediff(ms, starting.WhenItDid, ending.WhenItDid) as TimeTakenMs
from @log starting inner join @log ending on ending.TheOrder = starting.TheOrder + 1;
---------------------------------------------------------------------------

-- ========================================================================
-- OUTPUT
-- ========================================================================

-- |                           WhatHappened                           | TimeTakenMs |
-- |------------------------------------------------------------------|-------------|
-- | Select all records using wildcard field selector                 |         240 |
-- | Select all records and one field                                 |         163 |
-- | Select top 1000 records and one field without an order by clause |         147 |
-- | Select top 1000 records and one field with an order by clause    |          83 |