thanks praveen !!

thanks praveen,

Do you archive data by using this type of flag column?

Lutz, Steve,

thanks for your valuable suggestions.

I think, union all will be a solution for me.

thanks again.

thanks for your reply.

Every social networking site face this situation. E.g. : when we delete any photo or or when we delete our profile etc.

They doesn't delete data physically. I...

If I use any other column which is in NCI (& not PK), then this is a seek.

NCI is same in both situations. Difference is only is Row_Number order by...

thanks for this valuable advice.

thanks Gail !

This link is really very helpful.

But I am not able to understand that if I use AdID (which is primary key & Clustered Index also) in Row_Number order...

Both are using same execution plan.

but I am still concerned about "CreatedDate" filter.

because all the filters which comes after date, are not coming under seek predicate.

another version is:

DECLARE @intPageNumber INT = 1, @intPageSize INT = 20;

SELECT A.AdID, A.AdTitle, A.AdFor, A.AdCondition, A.AdExpPrice, A.CreatedDate, A.ModifiedDate, A.AdUID

FROM

(

SELECT TOP(@intPageNumber * @intPageSize) ROW_NUMBER() OVER (ORDER BY vaa.CreatedDate) AS RowNum,

vaa.AdID, vaa.AdTitle,...

I found a bit better situation.

DECLARE @intPageNumber INT = 1, @intPageSize INT = 20;

WITH SQLPaging

AS

(

SELECT TOP(@intPageNumber * @intPageSize) ROW_NUMBER() OVER (ORDER BY vaa.CreatedDate) AS RowNum,

vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition,

vaa.AdExpPrice,...

Gail,

I have attached the required details.

& please ignore the data rows count because I have generated in local server not in production server.

For the date comparison, I am using

CreatedDate > DATEADD(dd, -90, GETUTCDATE())

Should I change the ">" to "between"?

Currently, I am using below query:

WHERE CategoryID = 1 AND CountryCode = 'GB'...

Mate,

Thanks for your reply.

What will you suggest for:

Question 1: If I am searching for a city(or state) then Should I use

Country = @country AND StateID = @stateID AND CityID...

one more thing which forgot to include the Time Difference between IST & GMT.

It depends on your DB server/you that in which time zone you are saving your date time...

I hope this can help you.

SELECT CONVERT(DATETIME, LTRIM(RTRIM(A.FormattedDate)))

FROM

(

SELECT CASE

WHEN PATINDEX('%CEST%', dr.date) > 0 THEN SUBSTRING(dr.date, PATINDEX('%CEST%', dr.date)+5, 4) + ' ' + SUBSTRING(dr.date, 5, (PATINDEX('%CEST%', dr.date)-5))

WHEN PATINDEX('%IST%', dr.date)...