Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rihardh
Constraint Violating Yak Guru
307 Posts |
 Posted - 2002-12-02 : 08:05:39
|
| I have a user defined function which returns a table. It's a simple select statement selecting three columns of a table. This table has a clustered index defined on two columns.The problem:If I select only the two columns which are included in the clustered index the order appearance is OK, but if I add the third column to the select statement, the order appearance is wrong. The resultset is ordered by the THIRD column!!??Did I miss something...? |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-02 : 08:08:11
|
| If you need to guarantee a particular order, you must use ORDER BY. You can't rely on SQL Server's internal processing to guarantee a sort order (obviously) |
 |
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-12-02 : 08:21:56
|
| I have to be honest, it's not a select statement with a GROUP BY clause.I tried it with and without the GROUP BY clause and it returns resultsets with different sort order.I can't ROB, ORDER BY is not allowed in UDF's. |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-12-02 : 08:25:04
|
| Sorry it should say:"I have to be honest, it's a select statement with a GROUP BY clause"It's kinda late... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-12-02 : 08:26:11
|
| You'll have to post your code, it might be modifiable so that you can order it. Also post the table structure and the syntax of the function call (i.e. - SELECT * FROM dbo.myTableUDF() ) |
|
|
|
rihardh
Constraint Violating Yak Guru
307 Posts |
Posted - 2002-12-02 : 08:58:43
|
| I think that won't be necessary. I found out that if I include both columns in the select statement, which make up the clustered index, the resultset is OK. But if one of the columns is left out of the select, the resulset fails in terms of ordering.You can try it by yourself on a test table.Is that common or did I miss something in BOL? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-12-02 : 09:03:46
|
| It was processing the first query in the order of the clustered index.Leaving out a column or adding one means that it either isn't using the clustered index or is creating a work table and using that.In either case the order of the data in the returned table (a meaningless concept) is not guaranteed. The order in which you access the data in the table is not guaranteed either even if you can order the physical storage.You need to put an order by on the access of the table to get anywhere.If you can't do that then you need to re-design so that you are working on something that you can order.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Clustered index for nothing?