SQL Server

How do I use GetDate() in a Function?

pauliomLeave a comment
I’ve been writing a User Defined Table (UDT) function based upon user defined conditions. One of the valid conditions utilises GetDate() to discover today’s date. However, if you simply use GETDATE() in a function you’ll get;
 Invalid use of ‘getdate’ within a function.
 
This is because you cannot use non-deterministic functions within a UDT. The "correct" solution is to pass the GETDATE() value as an argument to the function, however, this requires that I change all the calling code and I don’t want to do that, besides why should I!
 
So my workaround is to create a fully fledged SQL table whose role in life is simply to return GETDATE()…
 
CREATE TABLE [dbo].[DummyDate](
            [Today]  AS (getdate()),
            [Dummy] [nchar](1)
)
Then within the function you can access the value by…
declare @TodaysDate datetime
select @TodaysDate = Today from DummyDate

Better performance by using a local var vs param in SQL Server?

pauliomLeave a comment
I’d forgotten about this “issue” until I read on the ASP.NET forum someone else having the same problem. I can’t vouch for what happened to the that user but I can explain what I saw. Basically I had a very simple stored procedure that took an Id value in as a param and then happily used it with a query, something like (not the actual query for customer IP reasons):
Create Proc MyProc(@Id int)
As
Select Col1, Col2 from MyTable Where Col3=@Id
 
The proc worked fine until the database data was scaled-up. Suddenly it performed very poorly and no amount of statistics updates would fix it. So I went through the usual steps of breaking the inner query out of the procedure and hard-coding the values:
 
Select Col1, Col2 from MyTable Where Col3=3
 
It worked great, so I started to add bits back until it went wrong again:
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
 
Worked great…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
 
Worked great…starting to get concerned…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = 3
Select Col1, Col2 from MyTable Where Col3=@Id
 
Poor again…hmm…
Create Proc MyProc(@Id int)
As
Declare @MyLocalId int
Set @MyLocalId = @Id
Select Col1, Col2 from MyTable Where Col3=@MyLocalId
 
Worked great!!!
What just happened? All I’ve done is de-reference the param into a local variable and used that. It worked and since I was under a lot of pressure at the time I forgot all about it. However, since reading that at least one other person is seeing the same issue I’ve decided to dig a little deeper. My first clue came from TechNet that stated that by using a local variable the query wouldn’t use statistics, now I’m not sure about that but maybe that’s something do with it. The reason that is plausible is because at low data volumes the procedure worked fine but with different statistics it started to perform badly. Although uncommon it’s not impossible for the Query Optimizer to get confused and produce a poor plan. If you remove its ability to use statistics it falls back to a sort of worst-case algorithm. In this situation it maybe that the default worst-case plan is better that its optimized attempt, in SQL 2005 you could then force the “correct” plan. But it’s still a theory at the moment, but I’ve dug out a Kimberly Tripp webcast on how params work so hopefully that may shed some light on the matter. More posts soon, in the meantime if anyone else has any ideas then please post.
 

Inconsistent behaviour of FOR XML AUTO?

pauliomLeave a comment
I’ve recently had to fix what seemed like a very strange problem with SQL Server, where very occasionally the system would stop working but would eventually fix itself. The system in question runs under a single identity, all access to the database goes via this "gateway" identity. Under particular circumstances the users of the system were getting incorrect results. The underlying stored procedure producing the incorrect results uses FOR XML AUTO to send back an XML result set. However, when I profiled the SQL and re-ran the same query in Management Studio it produced the correct results. I believe the problem was based upon the following facts;
  1. When the system runs it runs under identity A, therefore any stored procedure executed will have a specific plan created for identity A – "plan A". When I run the query as myself I get – "plan Me"
  2. The query uses FOR XML AUTO but does not have an ORDER BY
  3. FOR XML AUTO relies on the order to create the parent/child relationships
  4. Plans change depending upon the exact circumstances of the specific call, i.e. statistics, calling arguments, user id, etc

So what happended? Well I believe the problem was the "plan A" was constructing its query in a way that results in a different ordering of the results, I’ve certainly seen this happen in other queries (especially when attempting to CAST data). "Plan Me" was then using the natural (and more typical) order of how the data was added. This would explain why the XML results were different. This would also go a long way to explaining why after a reset of SQL or a DBCC FreeProcCache the system would start working again. This is because the plans would have to be re-created and I’d see the correct results since normally "plan A" would be exactly the same as "plan me" and therefore the system would work.

 

So the lessons learnt – 1) Always supply an ORDER BY for XML AUTO 2) When you’re testing for inconsistent results, use the same user id.