Program to measure the time consumed to execute the query

The below program will do so

DECLARE @t1 DATETIME;
DECLARE @t2 DATETIME;
SET @t1 = GETDATE();

--***********   THE QUERY BEGINS        ************************ 

DECLARE @maxLimit INT = 1000000

;WITH
        a AS (SELECT 1 AS i UNION ALL SELECT 1),
        b AS (SELECT 1 AS i FROM a AS x, a AS y),
        c AS (SELECT 1 AS i FROM b AS x, b AS y),
        d AS (SELECT 1 AS i FROM c AS x, c AS y),
        e AS (SELECT 1 AS i FROM d AS x, d AS y),
        f AS (SELECT 1 AS i FROM e AS x, e AS y),
NumCTE AS (SELECT TOP(@maxLimit) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
FROM f)
SELECT *
FROM NumCTE

--***********   THE QUERY ENDS          ************************ 

SET @t2 = GETDATE();
SELECT 
                DATEDIFF(millisecond,@t1,@t2) AS ElapsedTimeInMilliSeconds,
                (DATEDIFF(millisecond,@t1,@t2) - (((DATEDIFF(millisecond,@t1,@t2))/60000)*60000)) /1000 AS ElapsedTimeInSeconds;

/* Result */
ElapsedTimeInMilliSeconds       ElapsedTimeInSeconds
5246                            5.246

We declared two variables viz @t1 and @t2 as DATETIME. @t1 is set to current date and time. Then we wrote the SQL Query. Then we set the Date and Time component for @t2. And finally we performed a difference between the two dates and their times for obtaining the Difference in time both the seconds and milliseconds.