SQL Server T-SQL中的整型最大值常量?

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?

在T-SQL中是否存在一些其他语言的常量，它们提供了诸如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所能容纳的最大值(有点像hackish infinity)。我不喜欢硬编码，而是使用SET upper - range = int.Max

3 个解决方案

#1

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...

在Oracle中，您可以在包中执行此操作——最近的SQL服务器是程序集……

#2

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.

设置一个包含三列的表:TypeName、Max和Min，这样只需要填充一次。

Scalar Valued Function

标量值函数

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

您也可以使用标量值函数GetMaxInt()(具体示例请参见*答案)。

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

您可以在这里找到所有的max/min值:http://msdn.microsoft.com/en-us/library/ms187752.aspx

#3

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:

避免类似瘟疫的标量函数:标量UDF性能问题，尽管如此，我不会使用另一个人建议的三列表。这将导致隐式转换几乎在您使用它的任何地方。如果需要对多个类型使用该表，还必须多次加入该表。相反，每个数据类型的最小值和最大值都有一个列(使用它自己的数据类型定义)，并直接调用它们进行比较。例子:

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

#1