integer Max value constants in SQL Server T-SQL?

Question

Are there any constants in T-SQL like there are in some other languages that provide the max and min values ranges of data types such as int?

I have a code table where each row has an upper and lower range column, and I need an entry that represents a range where the upper range is the maximum value an int can hold(sort of like a hackish infinity). I would prefer not to hard code it and instead use something like SET UpperRange = int.Max

**int:** -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) - http://msdn.microsoft.com/en-us/library/ms187745.aspx — Krisztián Balla, Apr 23 '14 at 09:17

score 13 · Accepted Answer · edited Mar 15 '17 at 10:43

13

There are two options:

user-defined scalar function
properties table

In Oracle, you can do it within Packages - the closest SQL Server has is Assemblies...

edited Mar 15 '17 at 10:43

vidriduch

4,753
8
41
63

answered Apr 29 '10 at 20:52

OMG Ponies

325,700
82
523
502

What is this properties table? I googled for a while but didn't get anything so that I can define some constant value in the properties of a table (as it sounds from its name) which can be used to represent int.MaxValue. Can you please help me with some link or details about it. – RBT Aug 19 '16 at 23:45
@RBT All he's saying is you could make a table and have entries for the different min/max values, then query that table. – AaronLS Jun 01 '17 at 12:46

score 9 · Answer 2 · edited May 23 '17 at 11:53

I don't think there are any defined constants but you could define them yourself by storing the values in a table or by using a scalar valued function.

Table

Setup a table that has three columns: TypeName, Max and Min. That way you only have to populate them once.

Scalar Valued Function

Alternatively you could use scalar valued functions GetMaxInt() for example (see this StackOverflow answer for a real example.

You can find all the max/min values here: http://msdn.microsoft.com/en-us/library/ms187752.aspx

score 3 · Answer 3 · answered Feb 14 '14 at 00:46

Avoid Scalar-Functions like the plague:
Scalar UDF Performance Problem

That being said, I wouldn't use the 3-Column table another person suggested.
This would cause implicit conversions just about everywhere you'd use it.
You'd also have to join to the table multiple times if you needed to use it for more than one type.

Instead have a column for each Min and Max of each Data Type (defined using it's own data type) and call those directly to compare to.

Example:

SELECT *
  FROM SomeTable as ST
  CROSS JOIN TypeRange as TR
  WHERE ST.MyNumber BETWEEN TR.IntMin  AND TR.IntMax

You could call the scalar function only once, assign the result to a variable and use that as a "constant" in the query. But you are right. By all means: avoid calling scalar functions for every data row. If you have a lot of data, this is the real performance killer. — Krisztián Balla, Apr 23 '14 at 09:20

integer Max value constants in SQL Server T-SQL?

3 Answers3

Linked