Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Custom Sequence Numbering Expand / Collapse
Author
Message
Posted Wednesday, February 9, 2011 2:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:00 AM
Points: 76, Visits: 354
Thank you. It was a great learning experience for me in terms of writing style and content. All the feedback was beneficial and my future articles will surely improve as a result!

Cheers, James


James
MCM [@TheSQLPimp]
Post #1060910
Posted Wednesday, February 9, 2011 11:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:46 PM
Points: 106, Visits: 369
I agree that the UI layer should be passing the correct combination, and there is a problem if it is not. From a defensive programming standpoint, you have created a procedure that will not operate correctly if bad parameters are passed, which leaves the system open to problems. I suggest you have two choices.

Option 1 is to exclude the PersonID from the parameters. The procedure doesn't need the UI to pass it because the value can be looked up from the ToDoID. Without the parameter, there is no possibility that the UI will pass the incorrect value. You have eliminated a possible source of error.

Option 2 is to validate the PersonID against the value in the ToDoList table. If the UI passes the incorrect value for some reason, you want the database to pass that error back to the UI so the developer knows that incorrect values were passed and can fix the UI. A junior programmer may have made a mistake, but will never see it because he never gets feedback that the mistake was made. On top of that, the procedure will either not perform the intended action or will perform an incorrect action based on bad parameters.

I would choose Option 1, which eliminates a source of error and eliminiates the need to validate a parameter.
Post #1061423
Posted Wednesday, February 9, 2011 12:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:00 AM
Points: 76, Visits: 354
Hi Jonathan,

Unfortunately the two systems that this is implemented in are seriously high volume OLTP systems and so the extra cost of defensive programming has been vetoed - plus we have no junior developers! I do take your point though and I would probably go for a option 3 (a new option similar to your option 1 but one that doesn't require an additional lookup): this would be to check the @@ROWCOUNT after the update and raise an error if it was zero - this would tell the caller that there had been some kind of issue without incurring too much extra overhead. Tables with sequences like this are generally very active, so the least time taken up with sequencing the better - hence the complicated "all in one hit" update statement!

This has given me food for thought though, as in future articles I will now include some defensive programming, with a comment that if performance is paramount then this can be removed.

Thanks again for taking the time to comment on this.

Cheers, James



James
MCM [@TheSQLPimp]
Post #1061494
Posted Thursday, February 10, 2011 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:27 AM
Points: 337, Visits: 2,282
The main idea of keeping everything in sequence is ofcourse always a performance issue as changing one record implicitly means an update to all following records, which is implicitly expensive and more so for larger tables.

To minimize the cost, I see two options:

* Keep the sequence numbers in a separate table with a 1 to 1 relationship to the main table. So there is a second table with the same PK as for the original table, and it has one extra attribute, namely a sequence counter with a unique index on it.

* Keep a seprarate numbers with table the same number of records as the main table and let it have a foreign key to the main table. The primary key is the sequence number and it also has a unique index on the foreign key in this instance. You never have gaps this way and never delete records other then the last record(s) in the clustered index. Order changes affect only updates to the foreign key on existing pages.

The static nature of the last method should work particulary well combined with with view- or table-partitions.

I can think of more complex schemes that reduce things ever further by eliminating a lot of updates, and by localizing them. This is best pictured as working with offsets relative to a reference value. By updating the reference value(s), all following records are renumbered automatically without update. To reference the effective sequence number or perform sorts, you reference both the reference and the offeset to get the final sequence number/ordering. In such a scheme, moving a record to the top of the list only requires updating one or more reference records, and sometimes some of the offsets as well. But never all the offset records at once that exist for every records in the main table.
Post #1062054
Posted Thursday, February 10, 2011 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 4:00 AM
Points: 76, Visits: 354
Interesting Peter but you still then have the overhead of maintaining the base table upon insert, update and delete. I think you'd have to test with both scenarios to ascertain the impact.

Nice to know the article is making people think!

Cheers, James


James
MCM [@TheSQLPimp]
Post #1062087
Posted Monday, June 17, 2013 7:12 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 1:47 PM
Points: 478, Visits: 1,427
Interesting article. However, the code in the article is basically re-numbering manually instead of taking advantage of the built in Row_Number() functionality.

This seems to be a lot simpler:
(Note: I changed the table to a temp table for easier testing)

-- create the sequence test table

create table #ToDoList
(
todoID int identity(1,1) not null primary key,
PersonID int not null,
todoText nvarchar(200) not null,
todoSequence smallint not null default(0)
)
go

-- populate the sequence test table
insert into #ToDoList(PersonID, todoText, todoSequence)
select 1, 'Task 0', 0 union all
select 1, 'Task 1', 1 union all
select 1, 'Task 2', 2 union all
select 1, 'Task 3', 3 union all
select 1, 'Task 4', 4 union all
select 1, 'Task 5', 5 union all
select 1, 'Task 6', 6 union all
select 1, 'Task 7', 7 union all
select 1, 'Task 8', 8 union all
select 1, 'Task 9', 9
go

SELECT *
FROM #ToDoList

-- resequence tasks
DECLARE @PersonID BIGINT = 1
, @ToDoID INT = 1
, @NewSeq INT = 9

-- figure out the direction we are moving in
-- down = when the new sequence variable is greater than the old sequence variable
-- up = when the new sequence variable is less than then the old sequence variable
DECLARE @Down BIT -- 1 means down, 0 means up

SELECT @Down = CASE WHEN todoSequence < @NewSeq -- move down
THEN 1
ELSE 0 -- move up
END
FROM #ToDoList
WHERE todoID = @todoID


;WITH NewSequences AS
(
SELECT *
, ROW_NUMBER() OVER(ORDER BY
CASE WHEN TDL.toDoID = @ToDoID THEN @NewSeq ELSE toDoSequence END
-- order by new sequence without ordering, two numbers will have the same seq
, CASE WHEN TDL.toDoID = @ToDoID THEN @Down ELSE ~@Down END)
-- pick with of the two will be first, based on wether we are moving up/down
- 1 -- for 0 based list
AS RowNum
FROM #ToDoList TDL
WHERE PersonID = @PersonID
)
UPDATE TDL
SET TDL.todoSequence = NS.RowNum
FROM #ToDoList TDL
JOIN NewSequences NS ON TDL.ToDoID = NS.ToDoID


SELECT *
FROM #ToDoList
ORDER BY todoSequence


-- clean up
DROP TABLE #ToDoList

Post #1464130
Posted Monday, June 17, 2013 9:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 17, 2013 9:04 AM
Points: 2, Visits: 8
good article; just a quick note on the insert statement; SQL 2008 allows multiple values in one INSERT statement and is quite faster than an "INSERT... SELECT... UNION ALL":

INSERT INTO Table ( Column1, Column2 ) VALUES
( Value1, Value2 ), ( Value1, Value2 )...
Post #1464204
Posted Tuesday, July 2, 2013 1:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 5, 2014 7:05 AM
Points: 6, Visits: 48
Well, I was not as satisfied with rownumber over order-by ..
I needed to enumerate 22mio records and over 3h didn't know wheather I'm on half way or no-way.
So I used an alternative:
1. original table: T(n int, data char..) with 22mio records
2. copy top 0 into T' from T
3. alter table T': make n as autonumber
4. insert into T' from T
if 4th step fails because of transaction lenght, use some criteria on T to only get
part of data, i.e. year(date), etc.

Worked fine.

Grega
Post #1469324
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse