# A Simple Formula to Calculate the ISO Week Number

• ChrisM@Work (4/8/2013)

How often we see "so how does that work, exactly?" when someone posts an up-to-date solution to their tricky problem. Try explaining this algorithm in the remaining 3 minutes of your lunch break!

Thanks Jeff for taking the time to write - for a brilliant algorithm - an equally brilliant explanation. Bookmarked.

I sure do appreciate that kind of feedback, Chris. I guess "how it works" is what made things like the Tally Table article so popular in it's day. I didn't invent it... I just 'splained it. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• ALZDBA (4/8/2013)

It took me some time to crawl back into working position after reading this finding.

Completely astonished by the simplicity of the solution.

Huge thank you for notifying and publishing another great help for the community.

Thanks, Johan. I agree. The author of that formula did an amazing job at reducing the formula to such a nice, tight, short, high performance bit o' code. I sat there with my mouth open for an unknown but long period of time when I realized what he'd done.

BTW, when you were crawling around, did you happen to see any of my dust bunnies? After the Tally Oh article, some of them said they were going overseas for an extended vacation. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• *removed*

N 56°04'39.16"
E 12°55'05.25"

• After further investigation, it seems you don't need the CASE constructor either.

Another simple math trick will do.

`SELECT (DATEPART(DAYOFYEAR, DATEDIFF(DAY, -53690, Date) / 7 * 7 - 53687) + 6) / 7`

N 56°04'39.16"
E 12°55'05.25"

• SwePeso (4/8/2013)

Jeff Moden (4/8/2013)

Thanks for stopping by, Peter. Haven't seen you around much and it's a real pleasure to see that fabulous mind at work here, again.

I've been trying to reach you at the email adress I have for you. All emails last year are returned with "Email address unknown".

It hasn't changed in 16 years although some ISP's block it because the old provider that I used to have was famous for spamming folks. I'll send it to you again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.

Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.

I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.

Not sure what use anyone might have with that information, but that's my report! 😉

• Steven Willis (4/8/2013)

Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.

Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.

I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.

Not sure what use anyone might have with that information, but that's my report! 😉

Could you post your code, including the itvf you created?

• Lynn Pettis (4/8/2013)

Steven Willis (4/8/2013)

Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.

Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.

I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.

Not sure what use anyone might have with that information, but that's my report! 😉

Could you post your code, including the itvf you created?

Jeff's test script:

`--===== 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`

`SELECT`

` tvf.*`

`FROM`

`#TestTable tt`

`CROSS APPLY`

`dbo.itvfGetWeekNumberFromDate_ISO(tt.Date) tvf`

`CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]`

` (`

` @dDate DATETIME`

` )`

`RETURNS @WeekNumber TABLE`

` (`

` [Date] DATE NULL`

` ,[DayOfWeek] VARCHAR(20) NULL`

` ,[ISOWeek] INT NULL`

` )`

`WITH SCHEMABINDING`

`AS`

`BEGIN`

`/*`

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

`*/`

`;WITH cte AS`

`(`

`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]`

`)`

`INSERT INTO @WeekNumber`

`SELECT`

` Date`

` ,DayOfWeek`

` ,ISOWeek`

`FROM`

`cte`

`RETURN`

`END`

`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`

• Steven Willis (4/8/2013)

Lynn Pettis (4/8/2013)

Steven Willis (4/8/2013)

Jeff, just FYI...I created the million row table on my slow laptop and tested the code from your article. With a normal SELECT statement it took appx 19-22 secs per run to get a million rows.

Now out of curiosity, I whipped up an iTVF and ran that against the data with a cross apply and it did NOT perform well. Something on the order of 2 minutes or more. That just goes to show that iTVFs are not always the answer. I also re-tested after adding a non-clustered index to the temp table. That dropped about 3 seconds off the ordinary SELECT and about 10 seconds when using the iTVF.

I tried the iTVF both with a WITH SELECT/INSERT INTO and an garden-variety INSERT INTO SELECT and the WITH statement version was the slowest.

Not sure what use anyone might have with that information, but that's my report! 😉

Could you post your code, including the itvf you created?

Jeff's test script:

`--===== 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`

`SELECT`

` tvf.*`

`FROM`

`#TestTable tt`

`CROSS APPLY`

`dbo.itvfGetWeekNumberFromDate_ISO(tt.Date) tvf`

`CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]`

` (`

` @dDate DATETIME`

` )`

`RETURNS @WeekNumber TABLE`

` (`

` [Date] DATE NULL`

` ,[DayOfWeek] VARCHAR(20) NULL`

` ,[ISOWeek] INT NULL`

` )`

`WITH SCHEMABINDING`

`AS`

`BEGIN`

`/*`

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

`*/`

`;WITH cte AS`

`(`

`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]`

`)`

`INSERT INTO @WeekNumber`

`SELECT`

` Date`

` ,DayOfWeek`

` ,ISOWeek`

`FROM`

`cte`

`RETURN`

`END`

`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`

I hate to be the bearer of bad news but your function is not an inline table valued function, it is a multi-statement table valued function.

The following is your function rewritten as an inline table valued function:

`CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]`

` (`

` @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`

• Spot on, Lynn. Thanks for the cover. Busy 20 hour day at work yesterday.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Lynn Pettis (4/8/2013)

I hate to be the bearer of bad news but your function is not an inline table valued function, it is a multi-statement table valued function.

The following is your function rewritten as an inline table valued function:

`CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]`

` (`

` @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`

Lynn, of course you're correct. Too many late hours and not enough sleep. :blush:

But I tested the iTVF against the mlTVF and really found no difference. Again, surprising results.

`SELECT`

` CONVERT(VARCHAR(20),tt.Date,120) AS [Date]`

` ,DATENAME(weekday,tt.Date) AS [DayOfWeek]`

` ,(DATEPART(dy,DATEDIFF(dd,0,tt.Date) / 7 * 7 + 3) + 6) / 7 AS [ISOWeek]`

`FROM`

` #TestTable tt` `CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO]`

` (`

` @dDate DATETIME`

` )`

`RETURNS @WeekNumber TABLE`

` (`

` [Date] DATE NULL`

` ,[DayOfWeek] VARCHAR(20) NULL`

` ,[ISOWeek] INT NULL`

` )`

`WITH SCHEMABINDING`

`AS`

`BEGIN`

` 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`

`SELECT`

` tvf.*`

`FROM`

` #TestTable tt`

`CROSS APPLY`

` dbo.itvfGetWeekNumberFromDate_ISO(tt.date) tvf` `CREATE FUNCTION [dbo].[itvfGetWeekNumberFromDate_ISO_v2]`

`(@dDate DATETIME)`

`RETURNS TABLE`

`WITH SCHEMABINDING`

`AS`

`RETURN`

` 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] ;`

`GO`

`SELECT`

` tvf.*`

`FROM`

` #TestTable tt`

`CROSS APPLY`

` dbo.itvfGetWeekNumberFromDate_ISO_v2(tt.date) tvf` • 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`

• Wow.

Amazing work Jeff. Great article too. 5 stars.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

• Alan.B (4/9/2013)

Wow.

Amazing work Jeff. Great article too. 5 stars.

Thanks, Alan. I really appreciate that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

• Lynn Pettis (4/9/2013)

Create an empty database and run the following:

...

Tested your script at my slow box:

`CPU(ms): 102009 Logical Reads: 10002259 Elapsed(ms): 104478 Reads: 2044 Writes: 0`

`CPU(ms): 2745 Logical Reads: 2175 Elapsed(ms): 2867 Reads: 2016 Writes: 0`

`CPU(ms): 100028 Logical Reads: 10002257 Elapsed(ms): 101861 Reads: 2043 Writes: 0`

`CPU(ms): 2090 Logical Reads: 2175 Elapsed(ms): 11723 Reads: 2016 Writes: 0`

[

Clear case with regards to the conclusion for the test 😎

Lovely script on how easy one can just measure such consumption.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems