June 25, 2010 at 8:00 am
skcadavre (6/25/2010)
Paul, that's great! I was hoping that when the other thread about this came up one of you gurus would come in and shatter my little code. There's always more to learn with SQL, and posts like yours are why I signed up to SQLServerCentral in the first place.
Well I don't think there was any shattering done 🙂
The code you posted is just a slightly different approach - there's absolutely nothing wrong there.
If I had seen that thread before I would probably just have linked to it as Gianluca did, and saved myself ten minutes' typing 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 25, 2010 at 8:50 am
Paul, have you got any guidelines as to when you should and should not compile a UDF WITH SCHEMABINDING?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 25, 2010 at 9:06 am
WayneS (6/25/2010)
Paul, have you got any guidelines as to when you should and should not compile a UDF WITH SCHEMABINDING?
As a general rule, always do 🙂
If you change an existing UDF, test it just to be sure.
edit: Scalar UDFs should pretty much *never* do data access. Multi-statement table-valued UDFs that do data access are rarely a good idea (but it can happen). In-line table-valued UDFs are fine, of course.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 28, 2010 at 2:27 am
Hi Paul
Many thanks that works brilliantly sorry to be a pain but I need to do the same for time also
the time is stored in the same way ie the list below
151402240
152311808
152443904
153092608
153361664
154469376
154477312
154543616
154667776
169619200
170008832
170132992
170265344
185276160
185672448
185870592
What would i need to change in the Func to create one to convert the time please?
Kind regards
Simon Hardstaff
June 28, 2010 at 5:51 pm
Paul White NZ (6/25/2010)
Gianluca Sartori (6/25/2010)
Great code, Paul, as usual!!Thanks. Sorry I didn't notice your reply before.
Just one question: what does schemabinding mean here? The return value depends only on the input parameter, so I miss the point on enabling shemabinding.
SCHEMABINDING ensures that SQL Server checks the function for determinism.
A function that is not schema-bound is marked by SQL Server as non-deterministic and as doing data access.
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596424.aspx
Heh... "It Depends" 😉 I don't see the spooling mentioned in the article for the following functions. Must have been an old sp1 bug or something. {edit} Seems like the only time the table spool occurs is during an update. It also seems that the use of an iTVF provides much more value than the schema binding although I'm one of those that will try to squeeze every last millisecond out of a query on a million rows. Nice tip, Paul.
USE TempDB;
GO
DROP FUNCTION dbo.IntToDateSB, dbo.IntToDateNSB, dbo.IntToDateSCSB, dbo.IntToDateSCNSB;
DROP TABLE #Sample;
GO
SET NOCOUNT ON;
GO
CREATE FUNCTION dbo.IntToDateSB
(
@Input INTEGER
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT value = DATEADD(DAY, (@Input & 0x000000FF) - 1, DATEADD(MONTH, (@Input & 0x0000FF00) / 256 - 1, DATEADD(YEAR, (@Input & 0xFFFF0000) / 65536 - 1900, 0)));
GO
CREATE FUNCTION dbo.IntToDateNSB
(
@Input INTEGER
)
RETURNS TABLE
AS RETURN
SELECT value = DATEADD(DAY, (@Input & 0x000000FF) - 1, DATEADD(MONTH, (@Input & 0x0000FF00) / 256 - 1, DATEADD(YEAR, (@Input & 0xFFFF0000) / 65536 - 1900, 0)));
GO
CREATE FUNCTION dbo.IntToDateScSB(@Input int)
RETURNS DATETIME
WITH SCHEMABINDING
BEGIN
RETURN (SELECT DATEADD(DAY, (@Input & 0x000000FF) - 1, DATEADD(MONTH, (@Input & 0x0000FF00) / 256 - 1, DATEADD(YEAR, (@Input & 0xFFFF0000) / 65536 - 1900, 0))) AS value)
END
GO
CREATE FUNCTION dbo.IntToDateScNSB(@Input int)
RETURNS DATETIME
BEGIN
RETURN (SELECT DATEADD(DAY, (@Input & 0x000000FF) - 1, DATEADD(MONTH, (@Input & 0x0000FF00) / 256 - 1, DATEADD(YEAR, (@Input & 0xFFFF0000) / 65536 - 1900, 0))) AS value);
END
GO
SELECT is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateSB', N'IF'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateSB', N'IF'), 'IsPrecise'),
is_system_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateSB', N'IF'), 'IsSystemVerified');
GO
SELECT is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateNSB', N'IF'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateNSB', N'IF'), 'IsPrecise'),
is_system_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateNSB', N'IF'), 'IsSystemVerified');
GO
SELECT is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScSB', N'FN'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScSB', N'FN'), 'IsPrecise'),
is_system_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScSB', N'FN'), 'IsSystemVerified');
GO
SELECT is_deterministic = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScNSB', N'FN'), 'IsDeterministic'),
is_precise = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScNSB', N'FN'), 'IsPrecise'),
is_system_verified = OBJECTPROPERTYEX(OBJECT_ID(N'dbo.IntToDateScNSB', N'FN'), 'IsSystemVerified');
GO
CREATE TABLE #Sample (data INTEGER NOT NULL);
GO
INSERT #Sample (data) VALUES (131402524);
INSERT #Sample (data) VALUES (131336971);
GO
INSERT INTO #Sample (data)
SELECT DATA FROM #Sample
GO 11
CREATE INDEX IX_#Sample_Data ON #Sample (data);
GO
SELECT 'THERE ARE ' + CAST((SELECT COUNT(*) FROM #Sample) AS VARCHAR(10)) + ' ROWS IN #SAMPLE.'
GO
------------------------------------------------
DECLARE @Bitbucket DATETIME
SET STATISTICS TIME ON;
SELECT @Bitbucket = iTVF.value
FROM #Sample S
CROSS
APPLY dbo.IntToDateSB(S.data) iTVF
WHERE S.data > 0;
SELECT @Bitbucket = iTVF.value
FROM #Sample S
CROSS
APPLY dbo.IntToDateNSB(S.data) iTVF
WHERE S.data > 0;
SELECT @Bitbucket = dbo.IntToDateSCSB(S.data)
FROM #Sample S
WHERE S.data > 0;
SELECT @Bitbucket = dbo.IntToDateSCNSB(S.data)
FROM #Sample S
WHERE S.data > 0;
SET STATISTICS TIME OFF;
RAISERROR('-------------------------------------------------------------------',10,1) WITH NOWAIT;
GO 4
--Jeff Moden
June 29, 2010 at 7:13 am
Jeff, yes you're right - the table spool (or some other blocking operator) only appears in update plans because it is there for Halloween protection. The optimiser is smart enough to know when Halloween protection is not required - but it can't safely assume anything when it comes across a non-deterministic function.
The wider point is that non-deterministic functions defeat all sorts of potential optimisations, transformations, and re-orderings. In keeping with my general approach of helping the optimiser to do its job well, I recommend schema binding pretty much all functions. You need a very good reason not to, in any case.
Finally, and just for clarity, I hope I have made it clear that schema binding a function just allows SQL Server to evaluate it for determinism - it is still possible for a schema bound function to be non-deterministic: it depends what it does.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 29, 2010 at 7:31 am
Simon Hardstaff (6/28/2010)
What would i need to change in the Func to create one to convert the time please?
Hey Simon,
For my part, I think it's important to understand how the solution works, rather than just treating it as a black box. If you spend some time working out how the date conversion thing works, it should be obvious how to apply the same technique to times 🙂
Have a go. If you get totally stuck, post back your thoughts (and perhaps your best effort so far at a time converter) and one of us will gladly point you in the right direction.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 30, 2010 at 1:28 am
Hi Paul
Many thanks I will do and thanks again for the code
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply