CODEWITHSUNDEEP
sql-server
inon
inon

Reputation: 1772

What is the fastest way to insert the current date in sql server

I want to create a procedure. the running speed is important.

What the faster way to insert the current date?

1.

create table T1(D datetime, A int)
declare @D datetime
set @D = getdate()
insert T1(D,A)
select @D, B
from T2

2.

create table T1(D datetime default getdate(), A int)
insert T1(A)
select B
from T2

3.

create table T1(D datetime, A int)
insert T1(D,A)
select getdate(), B
from T2

Thank You!

Upvotes: 1

Views: 405

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44326

There is a difference, but only measurable in extreme cases. The first query is slightly faster because it doesn't use extra CPU power on looking up the value for getdate for every row.

Here are some test scripts to compare, you can turn on INCLUDE CLIENT STATISTICS before executing the scripts. Each script takes around 7 or 8 second on my computer:

This will be around 1 second faster than the other scripts:

Script 1

--DROP TABLE #t1
CREATE TABLE #T1(D datetime, A int)
DECLARE @D datetime
SET @D = getdate()
;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #T1(D,A)
SELECT top 10000000 @D, N FROM tally

This will be roughly the same performance as the third script

Script 2

--DROP TABLE #t1
CREATE TABLE #T1(D datetime default getdate(), A int)
;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #T1(A)
SELECT top 10000000 N FROM tally

This will be roughly the same performance as the second script

Script 3

--DROP TABLE #t1
CREATE TABLE #T1(D datetime, A int)
;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a,N b,N c,N d,N e,N f)
INSERT #T1(D,A)
SELECT top 10000000 getdate(), N
FROM tally

Upvotes: 1

Related Questions