blocks|key|1778750|text|在数据库文献中没有“创建常量”这样的东西。常量是以它们的形式存在的，通常称为值。可以声明一个变量并为其赋值(常量)。从学术角度看：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778751|DECLARE+@two+INT
SET+@two+=+2|code-block|syntax|javascript|1778752|这里@two是一个变量，2是一个值/常量。|1778753|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

There are no such thing as "creating a constant" in database literature. Constants exist as they are and often called values. One can declare a variable and assign a value (constant) to it. From a scholastic view:

<pre><code>DECLARE @two INT
SET @two = 2
</code></pre>

Here @two is a variable and 2 is a value/constant.

blocks|key|1778955|text|不能，但您可以创建一个函数并将其硬编码在其中并使用它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778956|下面是一个示例：|1778957|CREATE+FUNCTION+fnConstant()
RETURNS+INT
AS
BEGIN
++++RETURN+2
END
GO

SELECT+dbo.fnConstant()|code-block|syntax|javascript|1778958|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

No, but you can create a function and hardcode it in there and use that.

Here is an example:

<pre><code>CREATE FUNCTION fnConstant()
RETURNS INT
AS
BEGIN
 RETURN 2
END
GO

SELECT dbo.fnConstant()
</code></pre>

blocks|key|1778987|text|不，但是应该使用好的旧的命名约定。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1778988|declare+@MY_VALUE+as+int|code-block|syntax|javascript|1778989|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

No, but good old naming conventions should be used.

<pre><code>declare @MY_VALUE as int
</code></pre>

blocks|key|1779023|text|好的，让我们看看|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779024|常量是在编译时已知的不可变值，并且在程序的生命周期内不会更改|1779025|这意味着在SQL+Server中永远不能有常量|1779026|declare+@myvalue+as+int
set+@myvalue+=+5
set+@myvalue+=+10--oops+we+just+changed+it|code-block|syntax|javascript|1779027|该值刚刚更改|1779028|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Okay, lets see

Constants are immutable values which are known at compile time and do not change for the life of the program

that means you can never have a constant in SQL Server

<pre><code>declare @myvalue as int
set @myvalue = 5
set @myvalue = 10--oops we just changed it
</code></pre>

the value just changed

blocks|key|1779081|text|在T-SQL中没有对常量的内置支持。您可以使用SQLMenace的方法来模拟它(尽管您永远不能确定是否有人覆盖了该函数以返回其他…)。，或者可能写一个包含常量的表，as+suggested+over+here。或者编写一个触发器来回滚对ConstantValue列的任何更改？|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1779082|entityMap|0|LINK|mutability|MUTABLE|url|http://www.sqlteam.com/article/simulating-constants-using-user-defined-functions^0|3A|D|2A|M|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

There is no built-in support for constants in T-SQL. You could use SQLMenace's approach to simulate it (though you can never be sure whether someone else has overwritten the function to return something else…), or possibly write a table containing constants, <a href="http://www.sqlteam.com/article/simulating-constants-using-user-defined-functions" rel="noreferrer">as suggested over here</a>. Perhaps write a trigger that rolls back any changes to the <code>ConstantValue</code> column?

blocks|key|1779125|text|对于缺少constans，我的解决办法是向优化器提供有关值的提示。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779126|DECLARE+@Constant+INT+=+123;

SELECT+*+
FROM+[some_relation]+
WHERE+[some_attribute]+=+@Constant
OPTION(+OPTIMIZE+FOR+(@Constant+=+123))|code-block|syntax|javascript|1779127|这将告诉查询编译器在创建执行计划时将变量视为常量。缺点是您必须定义该值两次。|1779128|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

My workaround to missing constans is to give hints about the value to the optimizer.

<pre><code>DECLARE @Constant INT = 123;

SELECT * 
FROM [some_relation] 
WHERE [some_attribute] = @Constant
OPTION( OPTIMIZE FOR (@Constant = 123))
</code></pre>

This tells the query compiler to treat the variable as if it was a constant when creating the execution plan. The down side is that you have to define the value twice.

blocks|key|1779180|text|最好的答案是SQLMenace根据要求创建一个临时常量在脚本中使用，即跨多个GO语句/批处理。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779181|只需在tempdb中创建过程，就不会对目标数据库产生任何影响。|1779182|这方面的一个实际示例是数据库创建脚本，该脚本在包含逻辑模式版本的脚本末尾写入一个控制值。在文件的顶部是一些带有更改历史等的注释。但在实践中，大多数开发人员会忘记向下滚动并更新文件底部的模式版本。|1779183|使用上面的代码允许在数据库脚本(从SSMS的生成脚本特性复制)创建数据库之前，在顶部定义一个可见的模式版本常量，但在最后使用它。这就是面对开发人员旁边的变更历史和其他评论，所以他们很可能会更新它。|1779184|例如：|1779185|use+tempdb
go
create+function+dbo.MySchemaVersion()
returns+int
as
begin
++++return+123
end
go

use+master
go

--+Big+long+database+create+script+with+multiple+batches...
print+'Creating+database+schema+version+'+%2B+CAST(tempdb.dbo.MySchemaVersion()+as+NVARCHAR)+%2B+'...'
go
--+...
go
--+...
go
use+MyDatabase
go

--+Update+schema+version+with+constant+at+end+(not+normally+possible+as+GO+puts
--+local+@variables+out+of+scope)
insert+MyConfigTable+values+('SchemaVersion',+tempdb.dbo.MySchemaVersion())
go

--+Clean-up
use+tempdb
drop+function+MySchemaVersion
go|code-block|syntax|javascript|1779186|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

The best answer is from SQLMenace according to the requirement if that is to create a temporary constant for use within scripts, i.e. across multiple GO statements/batches.

Just create the procedure in the tempdb then you have no impact on the target database.

One practical example of this is a database create script which writes a control value at the end of the script containing the logical schema version. At the top of the file are some comments with change history etc... But in practice most developers will forget to scroll down and update the schema version at the bottom of the file.

Using the above code allows a visible schema version constant to be defined at the top before the database script (copied from the generate scripts feature of SSMS) creates the database but used at the end. This is right in the face of the developer next to the change history and other comments, so they are very likely to update it.

For example:

<pre><code>use tempdb
go
create function dbo.MySchemaVersion()
returns int
as
begin
 return 123
end
go

use master
go

-- Big long database create script with multiple batches...
print 'Creating database schema version ' + CAST(tempdb.dbo.MySchemaVersion() as NVARCHAR) + '...'
go
-- ...
go
-- ...
go
use MyDatabase
go

-- Update schema version with constant at end (not normally possible as GO puts
-- local @variables out of scope)
insert MyConfigTable values ('SchemaVersion', tempdb.dbo.MySchemaVersion())
go

-- Clean-up
use tempdb
drop function MySchemaVersion
go
</code></pre>

blocks|key|1779239|text|在使用SQL函数之前，运行以下脚本以查看性能差异：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779240|IF+OBJECT_ID('fnFalse')+IS+NOT+NULL
DROP+FUNCTION+fnFalse
GO

IF+OBJECT_ID('fnTrue')+IS+NOT+NULL
DROP+FUNCTION+fnTrue
GO

CREATE+FUNCTION+fnTrue()+RETURNS+INT+WITH+SCHEMABINDING
AS
BEGIN
RETURN+1
END
GO

CREATE+FUNCTION+fnFalse()+RETURNS+INT+WITH+SCHEMABINDING
AS
BEGIN
RETURN+~+dbo.fnTrue()
END
GO

DECLARE+@TimeStart+DATETIME+=+GETDATE()
DECLARE+@Count+INT+=+100000
WHILE+@Count+>+0+BEGIN
SET+@Count+-=+1

DECLARE+@Value+BIT
SELECT+@Value+=+dbo.fnTrue()
IF+@Value+=+1
++++SELECT+@Value+=+dbo.fnFalse()
END
DECLARE+@TimeEnd+DATETIME+=+GETDATE()
PRINT+CAST(DATEDIFF(ms,+@TimeStart,+@TimeEnd)+AS+VARCHAR)+%2B+'+elapsed,+using+function'
GO

DECLARE+@TimeStart+DATETIME+=+GETDATE()
DECLARE+@Count+INT+=+100000
DECLARE+@FALSE+AS+BIT+=+0
DECLARE+@TRUE+AS+BIT+=+~+@FALSE

WHILE+@Count+>+0+BEGIN
SET+@Count+-=+1

DECLARE+@Value+BIT
SELECT+@Value+=+@TRUE
IF+@Value+=+1
++++SELECT+@Value+=+@FALSE
END
DECLARE+@TimeEnd+DATETIME+=+GETDATE()
PRINT+CAST(DATEDIFF(ms,+@TimeStart,+@TimeEnd)+AS+VARCHAR)+%2B+'+elapsed,+using+local+variable'
GO

DECLARE+@TimeStart+DATETIME+=+GETDATE()
DECLARE+@Count+INT+=+100000

WHILE+@Count+>+0+BEGIN
SET+@Count+-=+1

DECLARE+@Value+BIT
SELECT+@Value+=+1
IF+@Value+=+1
++++SELECT+@Value+=+0
END
DECLARE+@TimeEnd+DATETIME+=+GETDATE()
PRINT+CAST(DATEDIFF(ms,+@TimeStart,+@TimeEnd)+AS+VARCHAR)+%2B+'+elapsed,+using+hard+coded+values'
GO|code-block|syntax|javascript|1779241|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Prior to using a SQL function run the following script to see the differences in performance:

<pre><code>IF OBJECT_ID('fnFalse') IS NOT NULL
DROP FUNCTION fnFalse
GO

IF OBJECT_ID('fnTrue') IS NOT NULL
DROP FUNCTION fnTrue
GO

CREATE FUNCTION fnTrue() RETURNS INT WITH SCHEMABINDING
AS
BEGIN
RETURN 1
END
GO

CREATE FUNCTION fnFalse() RETURNS INT WITH SCHEMABINDING
AS
BEGIN
RETURN ~ dbo.fnTrue()
END
GO

DECLARE @TimeStart DATETIME = GETDATE()
DECLARE @Count INT = 100000
WHILE @Count &gt; 0 BEGIN
SET @Count -= 1

DECLARE @Value BIT
SELECT @Value = dbo.fnTrue()
IF @Value = 1
 SELECT @Value = dbo.fnFalse()
END
DECLARE @TimeEnd DATETIME = GETDATE()
PRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using function'
GO

DECLARE @TimeStart DATETIME = GETDATE()
DECLARE @Count INT = 100000
DECLARE @FALSE AS BIT = 0
DECLARE @TRUE AS BIT = ~ @FALSE

WHILE @Count &gt; 0 BEGIN
SET @Count -= 1

DECLARE @Value BIT
SELECT @Value = @TRUE
IF @Value = 1
 SELECT @Value = @FALSE
END
DECLARE @TimeEnd DATETIME = GETDATE()
PRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using local variable'
GO

DECLARE @TimeStart DATETIME = GETDATE()
DECLARE @Count INT = 100000

WHILE @Count &gt; 0 BEGIN
SET @Count -= 1

DECLARE @Value BIT
SELECT @Value = 1
IF @Value = 1
 SELECT @Value = 0
END
DECLARE @TimeEnd DATETIME = GETDATE()
PRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using hard coded values'
GO
</code></pre>

blocks|key|1564212|text|如果您对获得变量中某个值的最佳执行计划感兴趣，那么可以使用动态sql代码。它使变量保持不变。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1564213|DECLARE+@var+varchar(100)+=+'some+text'
DECLARE+@sql+varchar(MAX)
SET+@sql+=+'SELECT+*+FROM+table+WHERE+col+=+'''%2B@var%2B''''
EXEC+(@sql)|code-block|syntax|javascript|1564214|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

If you are interested in getting optimal execution plan for a value in the variable you can use a dynamic sql code. It makes the variable constant.

<pre><code>DECLARE @var varchar(100) = 'some text'
DECLARE @sql varchar(MAX)
SET @sql = 'SELECT * FROM table WHERE col = '''+@var+''''
EXEC (@sql)
</code></pre>

blocks|key|1779353|text|对于枚举或简单常量，只有一行的视图具有很好的性能和编译时检查/依赖跟踪(原因是它是一个列名)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1779354|请参阅Jared+Ko的博客文章https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/|offset|length|1779355|创建视图|1779356|+CREATE+VIEW+ShipMethods+AS
+SELECT+CAST(1+AS+INT)+AS+[XRQ+-+TRUCK+GROUND]
+++,CAST(2+AS+INT)+AS+[ZY+-+EXPRESS]
+++,CAST(3+AS+INT)+AS+[OVERSEAS+-+DELUXE]
++,+CAST(4+AS+INT)+AS+[OVERNIGHT+J-FAST]
+++,CAST(5+AS+INT)+AS+[CARGO+TRANSPORT+5]|code-block|syntax|javascript|1779357|使用视图|1779358|SELECT+h.*
FROM+Sales.SalesOrderHeader+
WHERE+ShipMethodID+=+(+select+[OVERNIGHT+J-FAST]+from+ShipMethods++)|1779359|entityMap|0|LINK|mutability|MUTABLE|url|https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/^0|0|G|3F|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@$D|10|E|11|1|12]]|A|$]]|$1|F|3|G|5|6|7|13|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|14|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|15|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|16|8|@]|9|@]|A|$K|L]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

For enums or simple constants, a view with a single row has great performance and compile time checking / dependency tracking ( cause its a column name )

See Jared Ko's blog post <a href="https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/" rel="nofollow noreferrer">https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/</a>

create the view

<pre><code> CREATE VIEW ShipMethods AS
 SELECT CAST(1 AS INT) AS [XRQ - TRUCK GROUND]
 ,CAST(2 AS INT) AS [ZY - EXPRESS]
 ,CAST(3 AS INT) AS [OVERSEAS - DELUXE]
 , CAST(4 AS INT) AS [OVERNIGHT J-FAST]
 ,CAST(5 AS INT) AS [CARGO TRANSPORT 5]
</code></pre>

use the view

<pre><code>SELECT h.*
FROM Sales.SalesOrderHeader 
WHERE ShipMethodID = ( select [OVERNIGHT J-FAST] from ShipMethods )
</code></pre>

blocks|key|1564349|text|因为没有内置的常量支持，所以我的解决方案非常简单。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1564350|由于不支持此功能，因此：|1564351|Declare+Constant+@supplement+int+=+240
SELECT+price+%2B+@supplement
FROM+++what_does_it_cost|code-block|syntax|javascript|1564352|我会简单地将它转换为|1564353|SELECT+price+%2B+240/*CONSTANT:supplement*/
FROM+++what_does_it_cost|1564354|显然，这依赖于整个事物(没有尾随空格和注释的值)的唯一性。可以通过全局搜索和替换来更改它。|1564355|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Since there is no build in support for constants, my solution is very simple.

Since this is not supported:

<pre><code>Declare Constant @supplement int = 240
SELECT price + @supplement
FROM what_does_it_cost
</code></pre>

I would simply convert it to

<pre><code>SELECT price + 240/*CONSTANT:supplement*/
FROM what_does_it_cost
</code></pre>

Obviously, this relies on the whole thing (the value without trailing space and the comment) to be unique. Changing it is possible with a global search and replace.

问有没有办法让TSQL变量成为常量？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有没有办法让TSQL变量成为常量？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问有没有办法让TSQL变量成为常量？
EN