This site will work and look better in a browser that supports web standards, but it is accessible to any browser or Internet device.

CleanCode logo
NAVIGATION: first page in sectionprevious pagenext pagefinal page in section


DescriptionReturns upper and lower boundaries adjusted to round lots.
EnvironmentSQL Server 2000/2005
AuthorMichael Sorens
SinceCleanCode 0.9.22

This function implements a boundary smoothing algorithm as a table-valued function. It was designed for use with my Histogram stored procedure but is documented here separately to allow for other applications as well. It returns its inputs (upper and lower bounds); several intermediate, diagnostic values; and new upper and lower bounds that are more... palatable. In practice you only need the final two values; the others are present to allow for learning or tinkering, in case you wonder why certain values are smoothed to certain boundaries and wish to experiment.

Usage and Parameters

BoundSmoothing @LowerBoundInput, @UpperBoundInput

   @LowerBoundInput ::= starting lower bound
   @UpperBoundInput ::= starting upper bound
@LowerBoundInput [ Type: int Default: -none- ]
Starting lower bound of the range to be smoothed.
@UpperBoundInput [ Type: int Default: -none- ]
Starting upper bound of the range to be smoothed.
Returns: table[Power int, Magnitude float, Closeness int, ClosenessToHigh float, ClosenessToZero float, LowerBoundInput int, UpperBoundInput int, LowerBoundOutput int, UpperBoundOutput int]
The single-row table returned contains the input values (LowerBoundInput, UpperBoundInput) smoothed to (LowerBoundOutput, UpperBoundInput) along with several intermediate, diagnostic values.


The algorithm involves adjusting each boundary value so that it is divisible by an appropriate power of ten and giving the lower boundary a tendency towards zero when it is nearby. This is a rather qualitative statement; here are just a couple applications of this general principle:

This query invokes the function multiple times, putting all the results into a single result set for easy comparision:

SELECT * FROM dbo.BoundSmoothing(3, 21)
UNION SELECT * FROM dbo.BoundSmoothing(71, 76)
UNION SELECT * FROM dbo.BoundSmoothing(19, 76)
UNION SELECT * FROM dbo.BoundSmoothing(119, 176)
UNION SELECT * FROM dbo.BoundSmoothing(119, 276)
UNION SELECT * FROM dbo.BoundSmoothing(119, 976)
UNION SELECT * FROM dbo.BoundSmoothing(119, 676)
UNION SELECT * FROM dbo.BoundSmoothing(49, 976)
UNION SELECT * FROM dbo.BoundSmoothing(199, 899)
UNION SELECT * FROM dbo.BoundSmoothing(199, 901)
UNION SELECT * FROM dbo.BoundSmoothing(199, 976)
UNION SELECT * FROM dbo.BoundSmoothing(201, 976)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1176)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1499)
UNION SELECT * FROM dbo.BoundSmoothing(119, 1501)
UNION SELECT * FROM dbo.BoundSmoothing(319, 1576)
UNION SELECT * FROM dbo.BoundSmoothing(2319, 11576)
UNION SELECT * FROM dbo.BoundSmoothing(12383, 12389)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 12389)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 84111)
UNION SELECT * FROM dbo.BoundSmoothing(12319, 90111)
ORDER BY LowerBoundInput, UpperBoundInput
My article on the online technical journal, A Generic Histogram Generator for SQL Server, describes the BoundSmoothing function, reviewing the results of the query shown here.

Get CleanCode at Fast, secure and Free Open Source software downloads
Copyright © 2001-2013 Michael Sorens • Contact usPrivacy Policy
Usage governed by Mozilla Public License 1.1 and CleanCode Courtesy License
CleanCode -- The Website for Clean DesignRevised 2013.06.30