You cannot use the timestamp column as a revision date.

Timestamps are unique.

From Books online:

timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be...

I'm pretty sure Noel ment to add the % signs in the like statements.

Ex: ...when AddrInstr Like '%;P;%' Then 'P'...

I don't think there is anything wrong with Select * except that as columns may be added or removed from the table over time your result set may...

The test results I stated above reflect the use of a NON-clustered index on the date_field.  I have retested with a Clustered index on the date_field  and when selecting all...

I ran a test using these two statements in a batch:

Select my_date

from my_table

where my_date >= dateadd(d,0, datediff(d, 0, getdate()))

and   my_date < dateadd(d,1, datediff(d, 0, getdate()))

SELECT...

I think Remi is correct.  On a large table where the date you are looking for is  a small percentage of the total rows then Remi's method will use an...

Remi,

Your solution Select * from dbo.MyTable where date_field between dateadd(d,0, datediff(d, 0, getdate())) and dateadd(d,1, datediff(d, 0, getdate()))

returns date_field = to Getdate() and Getdate() + 1...

What in the Journal table determines the "lastest" entry and what is its column definition.

For what its worth, my recommendation is to use the BIT datatype.  It provides the built in limitation of valid values and when looking at the column definition it is...

From BOL:

 ...Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement. To define a statement block, use the...

Rick,

For more info on your original question check out this theread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=174762&edit=1

Remi,

Will this work without a table/index scan?

where datediff(d, Getdate(), date_column) = 0

For a complete discussion see this thread:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=171688#bm171793

Also check out this article for lots of info on dates and date comparisons:

http://www.karaszi.com/SQLServer/info_datetime.asp

DECLARE @key ....

SELECT TOP 1 @key = tKey FROM #Temp

Maybe I missing something, but using TOP without an Order By clause still will...

On a side note.  You might want to double check this statement:

         ....

          or u.c<>ADT.c

          or u.l<>ADT.l

          or u.st<>ADT.st

          or u.postalCode<>ADT.postalCode

          or u.mAPIRecipient<>ADT.mAPIRecipient

          and ADT.objectclass in ('user', 'contact')

Should that...