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