Folks, don't even think of using a user defined function for this. Although it's convenient and you don't have to teach newbies why it works and they don't have to memorize the code to do it, it's just not worth the performance hit. Using a user defined function for this is a whole lot slower than just learning how to do it with inline code.
Let's prove it... here's my normal million row table. Don't let it scare you. It doesn't take any time at all to build...
--===== Create and populate a 1,000,000 row test table.
-- Column "RowNum" has a range of 1 to 100,000 unique numbers
-- Column "SomeInt" has a range of 1 to 50,000 non-unique numbers
-- Column "SomeLetters2" has a range of "AA" to "ZZ" non-unique 2 character strings
-- Column "SomeMoney has a range of 0.0000 to 99.9999 non-unique numbers
-- Column "SomeDate" has a range of >=01/01/2000 and <01/01/2010 non-unique date/times
-- Column "SomeCSV" contains 'Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10'
-- for all rows.
-- Column "SomeHex12" contains 12 random hex characters (ie, 0-9,A-F)
-- Jeff Moden
SELECT TOP 1000000
SomeID = IDENTITY(INT,1,1),
SomeInt = ABS(CHECKSUM(NEWID()))%50000+1,
SomeLetters2 = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
+ CHAR(ABS(CHECKSUM(NEWID()))%26+65),
SomeCSV = CAST('Part01,Part02,Part03,Part04,Part05,Part06,Part07,Part08,Part09,Part10' AS VARCHAR(80)),
SomeMoney = CAST(ABS(CHECKSUM(NEWID()))%10000 /100.0 AS MONEY),
SomeDate = CAST(RAND(CHECKSUM(NEWID()))*3653.0+36524.0 AS DATETIME),
SomeHex12 = RIGHT(NEWID(),12)
INTO #JBMTest
FROM Master.dbo.SysColumns t1
CROSS JOIN Master.dbo.SysColumns t2
--===== Add a primary key just because
ALTER TABLE #JBMTest
ADD PRIMARY KEY CLUSTERED (SomeID)
... and here's a function made from the efficient method Michael Valentine Jones made for finding the last day of the month...
CREATE FUNCTION dbo.LastDayOfMonth (@AnyDate DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN DATEADD(mm,DATEDIFF(mm,-1,@AnyDate),-1)
END
... and here's a test where we dump the calculation to a dummy variable to take the display speed out of the picture and some stats code to measure the difference...
SET NOCOUNT ON
DECLARE @Bitbucket DATETIME
PRINT '========== Inline method =========='
SET STATISTICS TIME ON
SELECT @Bitbucket = DATEADD(mm,DATEDIFF(mm,-1,SomeDate),-1)
FROM #JBMTest
SET STATISTICS TIME OFF
PRINT '========== UDF method =========='
SET STATISTICS TIME ON
SELECT @Bitbucket = dbo.LastDayOfMonth(SomeDate)
FROM #JBMTest
SET STATISTICS TIME OFF
Last, but not least, here's the results from my humble desktop...
========== Inline method ==========
SQL Server Execution Times:
CPU time = 1172 ms, elapsed time = 4311 ms.
========== UDF method ==========
SQL Server Execution Times:
CPU time = 80047 ms, elapsed time = 147857 ms.
Heh... try it with TaskMgr running and see what it really does to the CPU and the Kernel. A UDF for this very simple thing to memorize just isn't worth it. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.