I am facing an issue related to organizing the sequence values in SQL Server and would appreciate some guidance on finding the best approach to address it. Here's the problem:
We are using float data type in SQL Server to determine the values of a sequence. When we change the order of folders or documents within this sequence, we use a formula to calculate the new sequence value for the moved folder or document in between two others. The formula we are using is as follows:
New Sequence Value = (Value of Previous Sequence + Value of Next Sequence) / 2
So, if you are moving a folder between two folders with sequence values 1 and 2, your new sequence value would be:
(1 + 2) / 2 = 1.5
(1.5 +2) / 2 = 1.75
(1.75 + 2) / 2 = 1.875 ...However, we have encountered an issue with this formula. After moving items around approximately 14 times, the fractional part of the float number starts getting filled up, causing inaccuracies in the sequence values.
I'm looking for suggestions on how to address this problem and find a more robust way to organize the sequence values without running into the precision issues associated with the float data type. Any insights or alternative methods would be greatly appreciated.
1条答案
按热度按时间blmhpbnm1#
You'll need a process to spread out the values to create room. It's fine if they start to accumulate lots of digits of precision, so long as there's enough room.
So periodically rewrite the sequence using a query like setting all the values to whole numbers with ROW_NUMBER().
You can also trigger this when you discover that a new sequence value is equal to one of the ones you're trying to find a value between.