October 1, 2014 at 8:19 am
Toreador (10/1/2014)
Interesting. Though the issue appears to be with applying functions to the Order By, which I don't think I've tried. I've certainly used this method successfully for many years - but it sounds like I should maybe have a rethink!
There are three different issues:
(i) putting functions in the Order By clause instead of in the select list can create chaos.
(ii) You can't rely on the order, because even if there is an order by clause that says nothing about order of computingthe rows, it is perfectly OK to compute the rows in whatever order is most efficient and once they are all computed then apply the order by clause (and since that's what the ISO spec says, any decent optimiser should try to work out whether it's cheaper to compute the rows in the desired order or order after computing them all and do the appropriate thing). So relying on the order is a mistake.
(iii) MS says the results of accumulations like this are undefined. So you shouldn't rely on them doing what you expect.
In practise, provided you avoid (i) and don't make the mistake in (ii) you will often (not always) be OK; it's probably a good idea to limit parallelism for any such queries, of course, but even doing that provides no guarantee that you will be OK. It's a great flaw in the language that there's no reliable set-based way of doing this sort of accumulation so that you have to resort to an explicit loop, but I suspect that it will not get fixed (because fixing it might disturb the optimiser) although it's possible (but unlikely) that we might see accumulation functions for non-arithmetic operations in the future.
Tom
October 1, 2014 at 4:55 pm
TomThomson (10/1/2014)
Toreador (10/1/2014)
Interesting. Though the issue appears to be with applying functions to the Order By, which I don't think I've tried. I've certainly used this method successfully for many years - but it sounds like I should maybe have a rethink!There are three different issues:
(i) putting functions in the Order By clause instead of in the select list can create chaos.
(ii) You can't rely on the order, because even if there is an order by clause that says nothing about order of computingthe rows, it is perfectly OK to compute the rows in whatever order is most efficient and once they are all computed then apply the order by clause (and since that's what the ISO spec says, any decent optimiser should try to work out whether it's cheaper to compute the rows in the desired order or order after computing them all and do the appropriate thing). So relying on the order is a mistake.
(iii) MS says the results of accumulations like this are undefined. So you shouldn't rely on them doing what you expect.
In practise, provided you avoid (i) and don't make the mistake in (ii) you will often (not always) be OK; it's probably a good idea to limit parallelism for any such queries, of course, but even doing that provides no guarantee that you will be OK. It's a great flaw in the language that there's no reliable set-based way of doing this sort of accumulation so that you have to resort to an explicit loop, but I suspect that it will not get fixed (because fixing it might disturb the optimiser) although it's possible (but unlikely) that we might see accumulation functions for non-arithmetic operations in the future.
There actually is a reliable set-based way to do this, even though it does involve a bit of trickery, and some voodoo black magic (aka XML):
SELECT STUFF((SELECT ', ' + ColName
FROM dbo.TabName
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'), 1, 2, '');
October 2, 2014 at 9:55 am
Hugo Kornelis (10/1/2014)
There actually is a reliable set-based way to do this, even though it does involve a bit of trickery, and some voodoo black magic (aka XML):
SELECT STUFF((SELECT ', ' + ColNameFROM dbo.TabName
FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)'), 1, 2, '');
I already use the XML method (or something very similar) for new work. It sounds like I should think about revisiting older code and converting it!
Viewing 3 posts - 31 through 32 (of 32 total)
You must be logged in to reply to this topic. Login to reply