Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Consider the following set, which is the content of the table variable @ProductContent that was just created for the query that runs against it:

@ProductContent

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)
                    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.

share|improve this question
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?? –  Luis Ferrao Apr 23 '13 at 9:17
7  
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 –  Luis Ferrao Apr 23 '13 at 12:46
1  
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. –  Aaron Bertrand May 20 '13 at 3:27
add comment

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
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 –  Luis Ferrao 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. –  Michael Gardner 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. –  DamagedGoods May 20 '13 at 15:50
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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