question

Tittu avatar image
Tittu asked

Can we order the records retrived from a SQL table by a Select statement based on the composite primary key?

I just wanted to know if its possible to order the records retrieved from SQL select statement based on the composite primary key for the table. I have a table with a composite primary key and I wanted to select the records in this table ordered by the composite key. Any help is really appreciated, Thanks!
primary-keyorder-bycomposite
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
If your composite key contains columns A, B and C, you can ORDER BY A, B, C in your SELECT-statement. But if you don't know beforehand which columns make up the composite key, you'd have to create a dynamic SQL statement, using the tables sys.key_constraints, sys.index_columns, sys.columns, sys.tables and sys.schemas to find out which columns make up the composite primary key for a table.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image
Håkan Winther answered
Magnus is absolutely right, and if you order your data by the same columns as you are filtering on and the index is used, you'll get rid of an expensive sort operator in the execution plan, as the data is already sorted in the index.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.