ROW_NUMBER OVER (ORDER by date_column)

Question

I am curious. I have a complex query that runs in a release of SQL Server 2005 Express in about 3 seconds.

The main table has about 300 thousand rows.

When i add

ROW_NUMBER() OVER (ORDER BY date_column) 

it takes 123 seconds and date_column is a datetime column.

If i do

 ROW_NUMBER() OVER (ORDER BY string_title) 

It starts after 3 seconds.

I added an index to the datetime column. Without changes. Another 123 seconds.

Then I tried:

 ROW_NUMBER() OVER (ORDER BY CAST(date_column AS int)) 

and the request will start in 3 seconds.

Since casting takes time, why does SQL Server behave like this:

UPDATE: It seems that ROW_NUMBER generally ignores my WHERE statements and builds a list of row columns for all available records? Can anyone confirm this?

Here I copied a better readable way (still a tone of logic :)) in SQL Management Studio:

 SELECT ROW_NUMBER() OVER (ORDER BY xinfobase.lid) AS row_num, * FROM xinfobase LEFT OUTER JOIN [xinfobasetree] ON [xinfobasetree].[lid] = [xinfobase].[xlngfolder] LEFT OUTER JOIN [xapptqadr] ON [xapptqadr].[lid] = [xinfobase].[xlngcontact] LEFT OUTER JOIN [xinfobasepvaluesdyn] ON [xinfobasepvaluesdyn].[lparentid] = [xinfobase].[lid] WHERE (xinfobase.xlngisdeleted=2 AND xinfobase.xlinvalid=2) AND (xinfobase.xlngcurrent=1) AND ( (xinfobase.lownerid = 1 OR (SELECT COUNT(lid) FROM xinfobaseacl WHERE xinfobaseacl.lparentid = xinfobase.lid AND xlactor IN(1,-3,-4,-230,-243,-254,-255,-256,-257,-268,-589,-5,-6,-7,-8,-675,-676,-677,-9,-10,-864,-661,-671,-913))>0 OR xinfobasetree.xlresponsible = 1) AND (xinfobase.lid IN (SELECT lparentid FROM xinfobasealt a, xinfobasetree t WHERE a.xlfolder IN(1369) AND a.xlfolder = t.lid AND dbo.sf_MatchRights(1, t.xtxtrights,'|')=1 )) ) AND ((SELECT COUNT(*) FROM dbo.fn_Split(cf_17,',') WHERE [value] = 39)>0) 

This query requires 2-3 seconds for 300 thousand records. Now I changed ORDER BY to xinfobase.xstrtitle , then it started up again for about 2-3 seconds. If I switch to xinfobase.dtedit (the datetime column with an additional index that I just added), it needs hte time, which I mentioned above.

I also tried to “trick” it and made my expression as SUB SELECT to make it pick up records first, and make ROW_NUMBER() in another SQL expression, the same performance result.

Answer 1

UPDATE

After I was still disappointed in making a workaround, I explored more. I deleted all existing indexes and ran several SQL statements for the tables. It turns out that to create new indexes with a new column sort order and include different columns, I fixed my problem, and the query was executed quickly with the dtedit (datetime) column.

So, lessons learned: Take care of your indexes and execution plans and double-check them with every update (new version) of the software you create ...

But still wondering why CAST (datetime_column AS int) does it faster than ...