Thanx for the pre-feedback, John won't have to correct out small error now .

I don't think you'll get much performance out of this.  Either way, the view will have to do a scan.  But that scan will always be faster if it doesn't...

Just to complete the delete part.

I would just create an instead of delete trigger. 

In the trigger do a simple :

Insert into table_deleted (KeyCol, col1, col2, coln) Select KeyCol,...

Update dbo.TableName SET IsDelete = IdentColumn WHERE IdentColumn in ([Deleted ids go here])

Is it just me or do I see a 4 minutes delay in there ???

Because the date format most likely is MM/DD/YYYY

RUN THIS (scope is for the connection only)

SET DATEFORMAT DMY

and the convert should work.

"-- YOU'RE right about that one. ".

I like the sound of that

If the column names are known at design time, then all that dynamic sql is not needed.  But then again maybe I forgot the requirements... again

I'm not sure I'm following you here Joe.  Can you explain more on what you think is the best indexing approach?  I have no experience live or theorical in that...

It gets materialised when indexed.  So you add another 8 bytes / row + PK size.

But then again this may be very acceptable if the inserts can be a tiny bit...

What about adding an indexed calculated field based on those 4 columns.  Than would make the range search much more simpler and it should be much more overhead on the...

 Now that idea warrants some more attention from my part .

I haven't reread the whole thread but here's what my guts is telling me :

You don't need to get out of the server to execute something in the same...

Much simpler than you think :

SELECT DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))

--'2006-12-20 00:00:00.000'

That sounds more reasonable.  Much like all cursors are evil... except those 2-3 cases .