Create an empty database and run the following:

--===== Conditionally drop the test table to make reruns in SSMS easier.

IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL

DROP TABLE #TestTable

;

--===== Create and populate a million row test table with random dates/times

-- where (01 Jan 2000) <= Date < (01 Jan 2020)

SELECT TOP 1000000

Date = CAST(RAND(CHECKSUM(NEWID()))*DATEDIFF(dd,'2000','2020')+DATEDIFF(dd,0,'2000') AS DATETIME)

INTO #TestTable

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

GO

CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]

(

@dDate DATETIME

)

RETURNS @WeekNumber TABLE

(

[Date] DATE NULL

,[DayOfWeek] VARCHAR(20) NULL

,[ISOWeek] INT NULL

)

WITH SCHEMABINDING

AS

BEGIN

/*

SELECT * FROM dbo.itvfGetWeekNumberFromDate_ISO_v2('2009-12-24')

*/

INSERT INTO @WeekNumber

SELECT

CONVERT(VARCHAR(20),@dDate,120) AS [Date]

,LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek]

,(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek]

RETURN

END

GO

CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v3]

(

@dDate DATETIME

)

RETURNS TABLE

WITH SCHEMABINDING

AS

RETURNSELECT

CONVERT(VARCHAR(20),@dDate,120) AS [Date],

LEFT(DATENAME(dw,@dDate),9) AS [DayOfWeek],

(DATEPART(dy,DATEDIFF(dd,0,@dDate)/7*7+3)+6)/7 AS [ISOWeek];

GO

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @BitBucket1 sql_variant,

@BitBucket2 sql_variant,

@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

SELECT

@BitBucket1 = tvf.Date,

@BitBucket2 = tvf.DayOfWeek,

@BitBucket3 = tvf.ISOWeek

FROM

#TestTable tt

CROSS APPLY

itvfGetWeekNumberFromDate_ISO_v2(tt.Date) tvf

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @BitBucket1 sql_variant,

@BitBucket2 sql_variant,

@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

SELECT

@BitBucket1 = tvf.Date,

@BitBucket2 = tvf.DayOfWeek,

@BitBucket3 = tvf.ISOWeek

FROM

#TestTable tt

CROSS APPLY

itvfGetWeekNumberFromDate_ISO_v3(tt.Date) tvf

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @BitBucket1 sql_variant,

@BitBucket2 sql_variant,

@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

SELECT

BitBucket1 = tvf.Date,

BitBucket2 = tvf.DayOfWeek,

BitBucket3 = tvf.ISOWeek

FROM

#TestTable tt

CROSS APPLY

itvfGetWeekNumberFromDate_ISO_v2(tt.Date) tvf

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO

set nocount on;

--===== Create the timer and bit-bucket variables and start the timer.

DECLARE @BitBucket1 sql_variant,

@BitBucket2 sql_variant,

@BitBucket3 sql_variant; --< change or add variables needed here to eliminate display distortion

Declare @cpu_ int,

@lreads_ bigint,

@eMsec_ bigint,

@Reads_ bigint,

@Writes_ bigint;

declare @CpuMs int,

@LogRds bigint,

@Elapsed bigint,

@Reads bigint,

@Writes bigint;

dbcc freeproccache with no_infomsgs;

dbcc freesystemcache('ALL') with no_infomsgs;

dbcc dropcleanbuffers with no_infomsgs;

Select

@cpu_ = cpu_time

, @lreads_ = logical_reads

, @eMsec_ = total_elapsed_time

, @Reads_ = reads

, @Writes_ = writes

From

sys.dm_exec_requests

Where

session_id = @@spid;

---

SELECT

BitBucket1 = tvf.Date,

BitBucket2 = tvf.DayOfWeek,

BitBucket3 = tvf.ISOWeek

FROM

#TestTable tt

CROSS APPLY

itvfGetWeekNumberFromDate_ISO_v3(tt.Date) tvf

---

Select

@CpuMs = cpu_time - @cpu_

, @LogRds = logical_reads - @lreads_

, @Elapsed = total_elapsed_time - @eMsec_

, @Reads = reads - @Reads_

, @Writes = writes - @Writes_

From

sys.dm_exec_requests

Where

session_id = @@spid;

--===== Display the duration

PRINT 'CPU(ms): ' + right(' ' + cast(@CpuMs as varchar(10)),10) +

' Logical Reads: ' + right(' ' + cast(@LogRds as varchar(10)),10) +

' Elapsed(ms): ' + right(' ' + cast(@Elapsed as varchar(10)),10) +

' Reads: ' + right(' ' + cast(@Reads as varchar(10)),10) +

' Writes: ' + right(' ' + cast(@Writes as varchar(10)),10);

set nocount off;

GO