total time in executing a query

You probably want to use set statistics time

Be careful though, there are different 'times'

• query cpu time - time spent in the cpu
• query duration - actual time taken between starting the query and it completing, including returning any results to your client

As @Kev wrote, you can use the set statistics time.

If you are executing the query from SSMS, you can also tick the Include Client Statistics, which contains also execution times and other useful information.

You can also get "grand" total execution time for the query from sys.dm_exec_query_stats. The query below works with SQL 2005 and later and will give you total/max/min/avg execution time, number of execution, total physical and logical reads:

       SELECT 
       QS.sql_handle,         
       ROW_NUMBER() OVER(PARTITION BY qs.sql_handle ORDER BY statement_start_offset) AS statement_no,    
       qs.execution_count,
       qs.total_physical_reads,
       qs.total_logical_reads,
       qs.total_logical_writes,
       qs.last_execution_time,
       sql_total_elapsed_time_s=convert(money,qs.total_elapsed_time)/1000000,
       sql_max_elapsed_time_s=convert(money,qs.max_elapsed_time)/1000000,
       sql_last_elapsed_time_s=convert(money,qs.last_elapsed_time)/1000000,
       sql_min_elapsed_time_s=convert(money,qs.min_elapsed_time)/1000000,
       sql_avg_time_s=(convert(money,qs.total_elapsed_time)/qs.execution_count)/1000000,            
       SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
         ((CASE statement_end_offset 
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE QS.statement_end_offset END 
          - QS.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats AS QS
    CROSS APPLY .sys.dm_exec_sql_text(QS.sql_handle) as ST
    WHERE DBID=DB_ID(@DBName ) OR @DBname IS NULL  

In it's simplest form my preference is

USE [adventureworks]
go

DECLARE @StartTime DATETIME 
DECLARE @EndTime DATETIME 
DECLARE @results TABLE ( [SalesOrderID] INT )

SET @StartTime = GETDATE()


INSERT  INTO @results
        ( salesorderid
        )
        SELECT TOP 100
            [sod].[SalesOrderID]
        FROM
            [Sales].[SalesOrderDetail] AS sod
        ORDER BY
            [sod].[UnitPrice]


SET @EndTime = GETDATE()



SELECT
    DATEDIFF(ss, @StartTime, @endtime) AS [seconds] ,
    DATEDIFF(ms, @StartTime, @endtime) AS [milli seconds] ,
    DATEDIFF(mcs, @StartTime, @endtime) AS [micro seconds]

go

Getting deep details from the dmvs as @Hakan Winther shows is the better option if you want to do proper analysis