FYI, have you ever used the "display estimated execution plan" in query
analyzer?

You can determine if something is equivilent <usually> by running this
on two similar queries.
(I mean optimization equivilent ofcourse, by the use of what indices,
etc.)




-----Original Message-----
From: Ken Ross
[mailto:mssqldba-ezmlmshield-x14857683.[Email address protected]
Sent: Thursday, March 30, 2006 4:55 PM
To: LazyDBA Discussion
Subject: RE: Optimizing date searches
Importance: Low

Great tips - thanks!!

So, using a snip from your example then, we're comfortable saying that:

SELECT *
FROM myTable
WHERE tableDate BETWEEN @SearchStart AND @SearchEnd

Should be equivalent to:

SELECT *
FROM myTable
WHERE tableDate >= @SearchStart AND tableDate <= @SearchEnd

Yes?


-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x49491208.[Email address protected]
Sent: Thursday, March 30, 2006 4:43 PM
To: LazyDBA Discussion
Subject: RE: Optimizing date searches

Yes. But, the trick is to restate the condition so that the DateAdd is
operating on the local variable rather than the column. Does the search
date have a time component? Or, is it set to midnight? Table column have
any time?

The more complex situation might be if the search date and the date
column both have a time component. And, you want to find all rows that
fall on the same day as the search date.

--Initial value is whatever...
SET @SearchDate = '2006-03-30 14:17:03.997'
--Clean it (i.e., strip the time)...
SELECT @SearchStart= Convert( varchar, @SearchDate, 112 )
-- Now the value will be '2006-03-30 00:00:00.000'
--
-- Set the end timepoint to the last time in the day SELECT @SearchEnd =
DateAdd( ms, -3, DateAdd( dd, 1, @SearchStart ) )
-- That will be '2006-03-30 23:59:59.997' because the next datetime
value will be midnight of day + 1

SELECT *
FROM myTable
WHERE tableDate BETWEEN @SearchStart AND @SearchEnd

Jay



From: Ken Ross
Sent: Thu 30-Mar-06 16:33
To: LazyDBA Discussion
Subject: RE: Optimizing date searches


Hmm... Ok, but in a case where the underlying column type is a datetime
but I'm really just looking for all records for a particular date (or
range) I'd still need to use the DateAdd trick wouldn't I?

-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x73815591.[Email address protected]
Sent: Thursday, March 30, 2006 4:24 PM
To: LazyDBA Discussion
Subject: RE: Optimizing date searches

Internally, SQL Server probably processes those two constructs
identically (with the exception of excluding the begin point in your
">=").

But, neither of those will use an index on your table column if one
exists. You need to state the condition such that you reference the
column rather than some operation (like DateAdd) on the column.


-----Original Message-----
From: Ken Ross [mailto:mssqldba-ezmlmshield-x99131614.[Email address
protected]
Sent: Thu 30-Mar-06 15:10
To: LazyDBA Discussion
Subject: Optimizing date searches

O guru's of GUID, O sultans of SQL, O great wizards of WHERE...

When doing a search filtering by dates, is there any
advantage/disadvantage to doing:

... WHERE @mySearchDate >= Table.Date_Field AND @mySearcDAte <
DateAdd(dd, 1, Table.Date_Field)

VS.

... WHERE @mySearchDate BETWEEN Table.Date_Field AND DateAdd(dd, 1,
Table.Date_Field)


Thanks!!


Ken



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page