Query not returning items in the original order Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it:

 

Notice how items are sorted by media_type_id, image_weight, image_id and finally image_width DESC.
If I run the following query:

SELECT -- &lt;images&gt;
         pcs.image_type AS [@Type]
        ,pcs.image_type_id AS [@ImageTypeId]
        ,(
            SELECT -- &lt;imageCollection&gt;
                 pcc.image_id AS [@Guid]
                ,pcc.image_angle AS [@Angle]
                ,pcc.image_weight AS [@Weight]
                ,(
                    SELECT -- &lt;image&gt;
                         pci.mime_type AS [@MimeType]
                        ,pci.content_guid AS [@Guid]
                        ,pci.url AS [@Url]
                        ,pci.media_type_id AS '@MediaTypeId'
                        ,t.media_type_description '@Description'
                        ,(
                            SELECT DISTINCT -- &lt;attribute&gt;
                                a.meta_attribute_id '@Id'
                                ,a.meta_attribute_name  '@Name'
                                ,v.meta_value_name AS '@Value'
                            FROM [ADS2].dbo.ads_digital_content_meta m
                            JOIN [ADS2].dbo.ads_digital_content_meta_atr_voc a ON a.meta_attribute_id = m.meta_attribute_id
                            JOIN [ADS2].dbo.ads_digital_content_meta_value_voc v ON v.meta_value_id = m.meta_value_id
                            WHERE m.content_guid = pci.content_guid
                            FOR XML PATH('Attribute'), TYPE)
                    FROM
                        @ProductContent pci

                    JOIN [ADS2].dbo.ads_digital_content_media_type t ON t.media_type_id = pci.media_type_id

                    WHERE
                        pci.image_id = pcc.image_id

                    GROUP BY pci.content_guid, pci.mime_type, pci.url, pci.media_type_id, pci.image_width, t.media_type_description

                    FOR XML PATH('Image'), TYPE)        

            FROM @ProductContent pcc

            WHERE pcc.image_type_id = pcs.image_type_id
            AND pcc.product_id = pc.product_id

            GROUP BY
                    pcc.product_id
                ,pcc.image_weight
                ,pcc.image_id
                ,pcc.image_angle

            FOR XML PATH('ImageCollection'), TYPE)

    FROM @ProductContent pcs

    WHERE pcs.media_type_id = 15
    AND pcs.product_id = pc.product_id

    GROUP BY
        pcs.image_type,
        pcs.image_type_id

    FOR XML PATH('Images'), TYPE


For some reason the the images don't come out in the right order. For instance, the image with width 400 comes first, instead of the one with width 640. My question is, what part of this query is changing the order from the way it is in @ProductContent? I'd rather just sort once before inserting into @ProductContent as the order by clauses in the XML query are a big performance hit.
Without an ORDER BY clause, the order the values are returned in will be unreliable, as they can be influenced by indexes and the data pages the individual rows are stored on.

If you require the data to be in a particular order, use the ORDER BY clause.