InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 16:14:40 GMT20http://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxPlease post table definitions, index definitions and execution plan, as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Pictures of the plan are pretty useless.Mon, 25 Feb 2013 01:56:18 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxIf 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 column. Please check the attached two execution plans. One is Row_Number with PK & second one is Row_Number with any other column.one shows SCAN & another one shows SEEK. Difference is only in Row_Number order by column.Sun, 24 Feb 2013 21:19:28 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxthanks for this valuable advice.Sun, 24 Feb 2013 10:15:51 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxYou're not filtering by AdID. If you were, that would be SARGable. You're filtering by a function on AdID, and that, like all other functions, is not SARGable, hence no index seek. As to why the cluster, probably because the query is not covered by any other indexes and hence the clustered index is the more efficient one to use.Sun, 24 Feb 2013 10:07:00 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxthanks 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 by clause then why this is a SCAN rather than SEEK & even if I have used non clustered index too.I have googled several topics but I haven't found why It is a SCAN ?Sun, 24 Feb 2013 09:25:55 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxWell the row number can't be a seek predicate, because there's no index on the row_number.As for the other columns, incorrect index order?[url]http://sqlinthewild.co.za/index.php/2009/02/06/index-columns-selectivity-and-inequality-predicates/[/url]Also there's a chapter in Pro SQL 2012 Practices about predicates and seek predicatesSun, 24 Feb 2013 07:33:06 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxBoth 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.Sun, 24 Feb 2013 04:09:20 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspx[quote][b]Anuj Rathi (2/23/2013)[/b][hr]Which one is efficient?[/quote]Test them both and see?Sun, 24 Feb 2013 03:38:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxanother 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.AdUIDFROM( 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, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID FROM Catalogue.vwAvailableActiveAds vaa WHERE vaa.CategoryID = 1 AND vaa.CountryCode = 'GB' AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE()) AND vaa.StateID = 1737 AND vaa.CityID = 86) AWHERE A.RowNum > (@intPageSize * (@intPageNumber - 1));Which one is efficient?Sat, 23 Feb 2013 23:25:34 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxI found a bit better situation.DECLARE @intPageNumber INT = 1, @intPageSize INT = 20;WITH SQLPagingAS( 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, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID FROM Catalogue.vwAvailableActiveAds vaa WHERE vaa.CategoryID = 1 AND vaa.CountryCode = 'GB' AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE()) AND vaa.StateID = 1737 AND vaa.CityID = 86 )SELECT * FROM SQLPagingWHERE RowNum > (@intPageSize * (@intPageNumber - 1))If I use CreatedDate in place of AdID in Row_Number order by clause, then it uses the index seek till Created date. After that it looks for the predicate StateID & CityID.I can not move the createddate before stateid & cityid becaue stateid & cityid are optional parameters in dynamic query & categoryid, country & created date are fixed conditions.Sat, 23 Feb 2013 23:09:40 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxGail,I have attached the required details.& please ignore the data rows count because I have generated in local server not in production server.Sat, 23 Feb 2013 21:14:25 GMTAnuj Rathihttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxPlease post table definitions, index definitions and execution plan, as per [url]http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Sat, 23 Feb 2013 10:28:23 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1423352-392-1.aspxHi,I have used Row_Number() to implement the paging in my stored procedure. Paging is working fine. But problem is, after implementing the Row_Number(), indexes does not work & a Clustered index SCAN happens even if I use the primary key column in order by section.below is the sample query:SELECT TOP (@insPageSize) A.RowNum, A.AdID, A.AdTitle, A.AdFor, A.AdCondition, A.AdExpPrice, A.CreatedDate, A.ModifiedDate, A.AdUID FROM ( SELECT ROW_NUMBER() OVER (ORDER BY vaa.AdID DESC) AS RowNum, vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition, vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID FROM Catalogue.vwAvailableActiveAds vaa WHERE vaa.CategoryID = @intCategoryID AND vaa.CountryCode = @chrCountryCode AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE()) AND vaa.StateID = @inbStateID AND vaa.CityID = @inbCityID ) A WHERE A.RowNum > (@insPageSize * (@insPageNo - 1))if I try to execute only inner query: SELECT ROW_NUMBER() OVER (ORDER BY vaa.AdID DESC) AS RowNum, vaa.AdID, vaa.AdTitle, vaa.CityID, vaa.AdFor, vaa.AdCondition, vaa.AdExpPrice, vaa.CreatedDate, vaa.ModifiedDate, vaa.AdUID FROM Catalogue.vwAvailableActiveAds vaa WHERE vaa.CategoryID = @intCategoryID AND vaa.CountryCode = @chrCountryCode AND vaa.CreatedDate > DATEADD(dd, -90, GETUTCDATE()) AND vaa.StateID = @inbStateID AND vaa.CityID = @inbCityIDIt does not use any index. AdID is primary key & there is another non clustered index which covers all where clause. But index scan occurs. If I remove the Row_Number() from inner query & check its execution plan, now index works but again StateID & CityID display as "predicate" while they are in non clustered index.I have read that ">" comes under SARGable, then why StateID & City is not coming in seek predicate list.Please give me some guidance to solve my both problems.Sat, 23 Feb 2013 09:07:56 GMTAnuj Rathi