BigInt and datetime performance comparisons in SQL Server

--Create TABLE TEST_TBL (ID varchar (primary) key nonclustered, Intcol int,datecol datetime)--=============== ===================================================================--"100w Data Test"--============================== ====================================================--Create 100w test data declare @j intdeclare @data floatdeclare @style Bigintset @j = 1while @j<1000000 begin set @style = CAST (replace (replace (varchar (), GETDATE () , (+), '-', '), ': ', '), ', ') as bigint) insert into Test_tbl (ID, Intcol, Datecol) VALUES (NEWID (), @style, GETDATE ( )) Set @j = @j + 1end Declare @d datetimeset @d = GETDATE () Declare @i intprint ' "100w Data Query 100 Tests" '--Test performance 1,datetime   -------------------------------------------------------------------------------------SET NOCOUNT ON--does not show the number of rows affected set @i=0while @i <20begin Select top 1 * from TEST_TBL where Datecol>getdate () set @i = @i+1end----------- --------------------------------------------------------------------------SELECT [Statement Execution time (ms)]=datediff (MS, @d, GETDATE ()) Set NOCOUNT off print ' 100w Data date statement execution time (MS): ' + CONVERT (varchar (+), DateDiff (MS, @d, GETDATE ()))--Test performance 2, int-------------------------------------------------------------------------------------SET NOCOUNT ON--   The number of rows affected is not displayed set @i=0while @i <20begin select top 1 * from TEST_TBL where intcol>20151212030303 set @i = @i+1end-------------------------------------------------------------------------------------SELECT [Statement execution time (milliseconds)] =datediff (MS, @d, GETDATE ()) Set NOCOUNT off print ' 100w data int statement Execution time (ms): ' + CONVERT (varchar), DateDiff (MS, @d, GETDATE ()))--==================================================================================--"1000w Data Test"--======== ==========================================================================--Create 900w test data, cumulative 1000wset @j = 1while @j  <9000000 begin Set @style = CAST (replace (replace (varchar (+), GETDATE (), 120), '-', '), ': ', '), ' ', ') asbigint) insert into Test_tbl (ID, Intcol, Datecol) VALUES (NEWID (), @style, GETDATE ()) Set @j = @j + 1end print ' "100 0w data Query 100 Tests "'--test performance 1, DateTime-------------------------------------------------------------------------------------SET NOCOUNT ON-- The number of rows affected is not displayed set @i=0while @i <100begin select top 1 * from TEST_TBL where Datecol>getdate () set @i = @i+ 1end-------------------------------------------------------------------------------------SELECT [Statement Execution time (MS)]= DateDiff (MS, @d, GETDATE ()) Set NOCOUNT off print ' 1000w data date statement execution time (ms): ' + CONVERT (varchar), DateDiff (MS, @d, Getdat E ()))--Test performance 2,int-------------------------------------------------------------------------------------set    Nocount ON--not showing the number of rows affected set @i=0while @i <100begin select top 1 * from TEST_TBL where intcol>20151212030303 Set @i = @i+1end-------------------------------------------------------------------------------------SELECT [ Statement execution time (ms)]=datediff (MS, @d, GETDATE ()) Set NocoUnt off print ' 1000w data int statement Execution time (ms): ' + CONVERT (varchar (+), DateDiff (MS, @d, GETDATE ())) 

BigInt and datetime performance comparisons in SQL Server