I have two stored proc's that are a literally copy of each other. One runs again one table, the other another table. They started off as the same query I just changed the table alias. The columns they are running again are the same type, eg Datetime and nvarchange. The issue I have is one of the queries is returning the datetime order by month, the other isn't. I need it ordered by month. When looking at the excution plan one is doing a sort the other is doing a hash compare.
I've checked the table structure, the only different is the nvarchar column in one is 255 in size, the other (non sorting) is 20. Neither of these columns are used to sort. The index's are the same, and not on the open time column. Collation is also the same. It is very frustrating
Top one doesn't sort, bottom one does
DATENAME(Month, SDi.OpenTime)as openmonth ,
CAST(YEAR(sdi.OpenTime) AS nvarchar) AS openyear
from ServiceDeskInteractions sdi
GROUP BY dateadd(month,datediff(month, 0, SDi.OpenTime),0),SDi.OpenTime,sdi.sdnumber
DATENAME(Month, TC.[Open Time])as openmonth ,
CAST(YEAR(TC.[Open Time]) AS nvarchar) AS openyear
from TargetCalls tc
GROUP BY dateadd(month,datediff(month, 0, TC.[Open Time]),0),TC.[Open Time],TC.[Incident ID]
Any hints or suggestions would be great.