Set sort-order field based on alphabetically ordering of another field

I've recently added a couple of fields to some tables in my database (SQL Server 2005) to allow users to customize the sort order of the rows. I've followed this pattern for all of the tables:

-- Alter the InvoiceStatus table
ALTER TABLE [dbo].[InvoiceStatus] ADD [Disabled] bit NOT NULL DEFAULT 0
GO
ALTER TABLE [dbo].[InvoiceStatus] ADD [SortOrder] int NOT NULL DEFAULT 0
GO
-- Use the primary key as the default sort order
UPDATE [dbo].[InvoiceStatus]
   SET [SortOrder] = [InvoiceStatusId]
GO

Normally, as you can see, I've used the primary key as the default sort order. Now I am however in the situation that I would like to use the alphabetical ordering of a text field in the table as the default sort order.

Using the above table as an example (which has a text field [InvoiceStatusName]), is there a similar nice and short query I could write to use the alphabetical ordering of [InvoiceStatusName] as the default sort order?

Update:

The question is already answered, but some has pointed out that this solution might not be ideal so I just want to add some context for future references. This is an old system (not legacy-old, but it has been around for quite some years) in use a handful of different places.

There are several lists/drop-downs in the application with your typical "status" type (such as invoice status, order status, customer type etc.). Back when the system was first written these were standard values in use every place (not meant to be changed in any way), but some users have started to request the ability to add new statuses, remove those no longer in use and specify a custom sort order (one status might be more frequently used, and it is thus nice to have it at the top of the list).

The easiest way I found to do this (without having to mess around with too much of the old code) was to add two new fields, Disabled and SortOrder, to all the relevant tables. The Disabled field is used to "hide" un-used types (cannot delete them because of referential integrity, and the value they hold does also ne开发者_开发知识库ed to be kept), and the SortOrder field is there so the users can specify their own custom sort order. Since all the relevant tables also share these same two columns, it was very easy to make a simple interface to handle the sorting (and disabling) in a generic way.

---

;WITH so AS
(
SELECT 
   SortOrder,
   ROW_NUMBER() OVER (ORDER BY InvoiceStatusName) AS rn
FROM   dbo.InvoiceStatus
)
UPDATE so SET SortOrder = rn

---

You could use the ROW_NUMBER() function to map the sorted name to an integer.

UPDATE  dbo.InvoiceStatus
SET     SortOrder = ivsn.Number
FROM    dbo.InvoiceStatus ivs
        INNER JOIN (
          SELECT dbo.InvoiceStatusID
                 , [number] = ROW_NUMBER() OVER (ORDER BY InvoiceStatusName)
          FROM   dbo.InvoiceStatus
        ) ivsn ON ivsn.InvoiceStatusID = ivs.InvoiceStatusID

You should ask yourself though if this scheme is the best solution for your problem. The implementation as is doesn't scale well.

---

The way you have chosen to implement table sorting is unusual and apparently restricted to sorting by integers only.

Really I'd recommend you redesign your sorting sub-system in another way.

e.g Have a meta-data table somewhere that holds the name of the column that users want to sort by, and then use ORDER BY (columnname) on the relevant queries

edit: Oh wait, we're talking about lookup tables and letting users change the order. Now I've got my head around that your implementation makes a lot more sense to me.