Custom collation in SQL Server
I have a table where the results are sorted using the "ORDER" column, for example:
Doc_Id Doc_Value Doc_Order
1 aaa 1
12 xxx 5
2 bbb 12
3 ccc 24
My problem is to tune this column as efficiently as possible first.
My initial approach was to set up a scalar function that can be used as the default when a new record is added to the table:
ALTER FUNCTION [dbo].[Documents_Initial_Order]
( )
RETURNS int
AS
BEGIN
RETURN (SELECT ISNULL(MAX(DOC_ORDER),0) + 1 FROM dbo.Documents)
When a user wants to rearrange 2 documents, I can easily switch 2 orders.
This works well, but now I have a second table that I need to configure in the same way, and I'm sure there is a better way to do it. Any idea?
a source to share
It sounds like you want an id column that can then be overridden as soon as it has an initial value. One solution would be to have two columns, after calling "InitialOrder", which is an auto-increment identity column, and then a second column named doc_order, which is initially set to the same value as the InitialOrder field (perhaps even as part of insert trigger or stored procedure if you do inserts this way), but give the user the ability to edit that column.
It requires a few bytes per write, but solves your problem and if it matters at all, you will have both the original document order and the user reset order.
Also, I'm not sure if your doc_order needs to be unique or not, but if not, you can sort the return values using doc_order and InitialOrder to ensure a consistent return sequence.
a source to share
Based on your comment, I think you have a very effective solution. You can make this a little more user-friendly by specifying it as the default:
alter table documents
add constraint constraint_name
default (dbo.documents_initial_order()) for doc_order
Alternatively, you can create an update trigger that copies the id field to the doc_order field after pasting:
create trigger Doc_Trigger
on Documents
for insert
as
update d
set d.doc_order = d.doc_id
from Documents d
inner join inserted i on i.doc_id = d.doc_id
An example of defining doc_id as an identity column:
create table Documents (
doc_id int identity primary key,
doc_order int,
doc_value ntext
)
a source to share