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 -- <images>
         pcs.image_type AS [@Type]
        ,pcs.image_type_id AS [@ImageTypeId]
            SELECT -- <imageCollection>
                 pcc.image_id AS [@Guid]
                ,pcc.image_angle AS [@Angle]
                ,pcc.image_weight AS [@Weight]
                    SELECT -- <image>
                         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 -- <attribute>
                                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)
                        @ProductContent pci

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

                        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

            FOR XML PATH('ImageCollection'), TYPE)

    FROM @ProductContent pcs

    WHERE pcs.media_type_id = 15
    AND pcs.product_id = pc.product_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.

  • 8
    Without an ORDER BY clause, anything can happen to your output.
    – 孔夫子
    Apr 23 '13 at 9:16
  • @孔夫子 Even on a table variable that was just created and has no cached query plans?? Apr 23 '13 at 9:17
  • 8
    Yes. ORDER BY exists for a reason. Use it.
    – Philᵀᴹ
    Apr 23 '13 at 9:38
  • @phil If really there's no way around it I was hoping for some insight as to how the order may vary in the scenario of some small set in a table variable Apr 23 '13 at 12:46
  • 2
    There are many reasons the order may vary. Just keep this simple fact in mind: if you don't use an ORDER BY clause, you are telling SQL Server you don't care about the order, so it will return the data using whatever ordering is most efficient. If you care about order, use an order by clause. May 20 '13 at 3:27

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.

  • I know this, however I'm talking about a small size table variable with no indexes and no persistence and no cached query plans. How does that apply here? What could possibly make the order change? That's my question Apr 23 '13 at 12:41
  • The way the rows are stored on data pages influences how the query plan is built, which in turn will influence the order that the data is retreived and the order in which it is dsplayed. Apr 23 '13 at 12:55
  • If you dealing with image and XML data at a guess without seeing the execution plans to confirm, the server is using a number of processors. Then it pieces the results back together. But as Michael Said, it will complete as quickly as possible rather than in any order. May 20 '13 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.