Of the two queries below, which one is faster? The table contains more than 100M records. So I just want to know whether using ISNULL in the where clause is the same as first assigning a value to the variable and only using the variable in the where clause?
declare @dt datetime
select COUNT(*) from pandl
where PaymentDate >= ISNULL(@dt, convert(nvarchar(10),getdate(), 121))
select @dt = ISNULL(@dt, convert(nvarchar(10),getdate(), 121))
select COUNT(*) from pandl
where PaymentDate >= @dt
Second one will be better. Calling functions from your where clause will make the query optimizer use scans instead of seeks. In other words you'll get a less than ideal execution plan
where clause definitely causes a scan. But i was under the impression that SQL Server knows enough to call the function once if the return value won't change over the life of the call.
I would suggest a slightly different approach, avoiding the slightly more expensive conversion to a string, and using a slightly smaller data type to store the date (since you don't need sub-minute granularity if you only care about day boundaries):
DECLARE @dt SMALLDATETIME;
SET @dt = DATEADD(DAY, DATEDIFF(DAY, '19000101', CURRENT_TIMESTAMP), '19000101'); --*
SELECT COUNT(*) FROM dbo.pandl
WHERE PaymentDate >= @dt;
If you're going to continue using the conversion to a string, use CHAR(10) - I don't think style 121 is going to produce any date formats that require Unicode support.
* yes you can do this without the outer DATEADD but not with new types like DATE
FWIW, if you don't have an index, it isn't going to affect the plan:
Run in text mode, check show execution plan.
I figured one of these date columns in StackExchange would have an index, but I didn't find one.
Other than that, I am with the other answers on this - don't use nvarchar unnecessarily, don't convert dates to strings for no good reason, don't use functions which can be evaluated more readably outside the query inside a query (and don't use functions in a query at all if you don't need to).
getdate()is evaluated multiple times over the course of the first query (I would have to run some tests to verify), since the convert rounds it to the day, it would only really matter if the query started before midnight and was still processing (and assigning new values togetdate()) after midnight. I totally agree with you that assigning once is better, just wanted to clarify when it can be really bad compared to just "not best."