Set a sort order field based on the alphabetical order of another field

Question

I recently added several fields to some tables in my database (SQL Server 2005) so that users can customize the sort order of rows. I followed this pattern for all 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

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

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

Update:
, , , . ( , ), .

/ (, , , ..). , , , ( - ), , , , ( , ).

, ( ), , Disabled SortOrder . Disabled "" ( - , ), SortOrder , . , ( ) .

Answer 1

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

Answer 2

ROW_NUMBER(), .

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

, . as is .

Answer 3

, , , -, .

, -.

, - , , , ORDER BY (columnname)

edit: , , . , .