Can You Sort on Non-Key Data in a Table? 1

[HenryE]

Hey All,

Is it possible to sort on a non-key field in a table? For example, say table Employees has primary field EmployeeNumber, and another field called Name. Is it possible to make the table be always sorted by Name, instead of by Employee Number?

Thanks.

Henry

 

[swampBoogie]

You can use any column in the order by clause. A query with an order by clause on a unindexed column will be rather slow as SQL server must build a temp table for the sorting.

You can't say that a table is sorted in a specific way. To be certain that the data is returned in a specific order you must supply an order by clause. If you need to order by on a column frequenetly it is beneficial to create an index for that column.

 

[SQLBill]

Specifically, try this:

SELECT LastName, FirstName
FROM NorthWind
ORDER BY LastName ASC
GO
SELECT LastName, FirstName
FROM NorthWind
ORDER BY FirstName ASC

Both are basically the same query, just a different sort order.

-SQLBill

 

[SQLSister]

If you want a table to be sorted permanently on a field you can create a clustered index. However, if you created your primary key in Enterprise Manager, it is likely that you have a clustered index on that. So first you have to drop your Perimarykey and recreate it as a nonclustered index. Then you have to create aclustered index on the field you want the database to always be sorted on. Be aware that clustered indexes can add more overhead to the insert, update process and might slow it down.

 

[HenryE]

Thanks everyone.

I used SQLSister's suggestion and created a computed field Name (LastName+FirstName) that I made a clustered index, while keeping the primary key as EmployeeNumber

As far as I know, this keeps the primary key unique, while sorting on the clusted index Name.

Am I making any major goofs this way?

The alternative, as I see it, would have been to create a view, and sort that on the computed field Name.

Thanks.