Is there a way to make the TSQL variable constant?

Question

Is there a way to make the TSQL variable constant?

Answer 1

No, but you can create a function and make it hard, and use it.

Here is an example:

CREATE FUNCTION fnConstant() RETURNS INT AS BEGIN RETURN 2 END GO SELECT dbo.fnConstant() 

Answer 2

Use pseudo-constants: http://blogs.msdn.com/b/sql_server_appendix_z/archive/2013/09/16/sql-server-variables-parameters-or-literals-or-constants.aspx

Pseudo-constants are not variables or parameters. Instead, they are just single-row views and enough columns to support your constants. With these simple rules, SQL Engine completely ignores the value of the view, but still builds an execution plan based on its value. The execution plan does not even show the connection to the view!

Answer 3

My workaround for missing constants is to give value hints to the optimizer.

 DECLARE @Constant INT = 123; SELECT * FROM [some_relation] WHERE [some_attribute] = @Constant OPTION( OPTIMIZE FOR (@Constant = 123)) 

This tells the query compiler to process the variable as if it were a constant when creating the execution plan. The downside is that you must define the value twice.

Answer 4

No, but you should use the good old naming conventions.

 declare @MY_VALUE as int 

Answer 5

There is no built-in constant support in T-SQL. You can use the SQLMenace approach to simulate it (although you can never be sure that someone else has overwritten the function to return something else ...) or, perhaps, write a table containing constants, as suggested here . Perhaps write a trigger that rolls back any changes to the ConstantValue column?

Answer 6

Before using the SQL function, run the following script to see the performance differences:

 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) + ' 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) + ' 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) + ' elapsed, using hard coded values' GO 

Answer 7

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

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

Answer 8

Ok let's see

Constants are immutable values ​​that 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

 declare @myvalue as int set @myvalue = 5 set @myvalue = 10--oops we just changed it 

value just changed

Answer 9

There is no such thing as creating a constant in the database. Constants exist as they are, and are often called values. You can declare a variable and assign it a value (constant). From an educational point of view:

 DECLARE @two INT SET @two = 2 

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

Answer 10

The best answer is SQLMenace as required if necessary to create a time constant for use in scripts, i.e. for multiple GO statements / packages.

Just create a procedure in tempdb, then you will not affect the target database.

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

Using the above code allows you to determine the constant of the visible version of the diagram at the top before the script database (copied from the SSMS script generation function) creates the database, but is used at the end. This is right in front of the developer along with the change history and other comments, so they are likely to update it.

For example:

 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 

Answer 11

Unfortunately, this is the wrong answer, which was chosen as the correct one here ...

The correct answer is given by mbobka / Pixelated above: Use (pseudo) CONSTANT VIEW

Even using functions with schemabinding does not make the compiler clear that it is a constant!