SQL Server Database Engine
Assuming that your Primary Key is CLUSTERED then personally I would create a NONCLUSTERED index on the InvoiceID column then use the following query:
SELECT
TOP 1 IDFROM
<Table>WHERE
InvoiceID = @InvoiceIDORDER
BY ID DESC
If your Primary Key is NONCLUSTERED, and assuming you have used the CLUSTERED index elsewhere, then create a NONCLUSTERED composite index on InvoiceID (ASC), ID (DESC) before running the same query.
Hope this helps,
Chris
You might try creating a unique, non-clustered index with InvoiceID as the first column, and ID as the second column (sorted DESC). This will create a "covering" index that completely covers the query below.
SELECT TOP (1) ID
FROM <Table>
WHERE InvoiceID = @InvoiceID
ORDER BY ID DESC
If you have SQL 2005 Enterprise, you can use the online index creation functionality to create the index without causing blocking/locking and without taking an outage. Then you can run your query in SSMS and see whether it is using your index.