SQL Server – Creating Range for Special Fields

check-constraintsperformancequery-performancesql server

I have two columns that look like this:

Start_Post  End_Post
----------  --------
102+20.45   153+19.22 
120+21.25   220+25.30
...         ...

And I want to introduce a constraint to each column:

  • Start_Pos
    Min Range: 100+50.30
    Max Range: 150+20.65
  • End_Pos
    Min Range: 150+60.30
    Max Range: 500+20.75

All values are in feet and follow a special format. It's a construction convention and I am not an expert but to my knowledge, 1+00 would be 100 feet. Essentially, taking the above range of 100+50.30 to 150+20.65 would mean a distance of 4970 ft. What I did was just remove the plus sign and subtract the two numbers. Hope that made sense.

Is there a way to define this constraint whilst maintaining the format?

Best Answer

Assuming the digits to the left of the + are measured in units of 100, you could perhaps create constraints like this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * 100 + StartPostPlus) > 100*100+50.3)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * 100 + StartPostPlus) < 150*100+20.65)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * 100 + EndPostPlus) > 150*100+60.3)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * 100 + EndPostPlus) < 500*100+20.75)
);

However, this doesn't allow dynamic constraints, all rows in the table will be constrained to those measurements, which may in fact be exactly what you need.

If you need dynamically adjusting constraints for each independent row, you could do this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * PostUnits + StartPostPlus) > MinStartPost * PostUnits + MinStartPostPlus)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * PostUnits + StartPostPlus) < MaxStartPost * PostUnits + MaxStartPostPlus)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * PostUnits + EndPostPlus) > MinEndPost * PostUnits + MinEndPostPlus)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * PostUnits + EndPostPlus) < MaxEndPost * PostUnits + MaxEndPostPlus)
    , MinStartPost int NOT NULL
    , MinStartPostPlus decimal(38,3) NOT NULL
    , MaxStartPost int NOT NULL
    , MaxStartPostPlus decimal(38,3) NOT NULL
    , MinEndPost int NOT NULL
    , MinEndPostPlus decimal(38,3) NOT NULL
    , MaxEndPost int NOT NULL
    , MaxEndPostPlus decimal(38,3) NOT NULL
    , PostUnits int NOT NULL
);


INSERT INTO dbo.SurveyData (StartPostStation, StartPostPlus, EndPostStation, EndPostPlus
    , MinStartPost, MinStartPostPlus, MaxStartPost, MaxStartPostPlus
    , MinEndPost, MinEndPostPlus, MaxEndPost, MaxEndPostPlus
    , PostUnits)
VALUES (120, 49.2, 175, 80.5  --measurements
    , 100, 50.3, 150, 20.65   --valid start post range
    , 150, 60.3, 500, 20.75   --valid end post range
    , 100); --units per post

Selecting data would look like this:

SELECT StartPost = CONVERT(varchar(10), sd.StartPostStation) + '+' + CONVERT(varchar(50), sd.StartPostPlus)
    , EndPost = CONVERT(varchar(10), sd.EndPostStation) + '+' + CONVERT(varchar(50), sd.EndPostPlus)
FROM dbo.SurveyData sd;

Results, like this:

+------------+------------+
| StartPost  |  EndPost   |
+------------+------------+
| 120+49.200 | 175+80.500 |
+------------+------------+

The post columns have been split into two to enable us to use the correct data types to store numeric data. If we try to store 175+80.50 in a single field, we end up using a varchar(x) column, which allows all kinds of possibilities for bad data, such as tee+27.-1, which are very difficult to comprehensively prevent. So, we store the station in one column, and the offset from that station in the next column. When presenting this data on screen or on reports, to humans, we'd use the concatenated version shown above.

Related Question