Is there a way to make a TSQL variable constant?
Sql ServerTsqlSql Server Problem Overview
Is there a way to make a TSQL variable constant?
Sql Server Solutions
Solution 1 - Sql Server
No, but you can create a function and hardcode it in there and use that.
Here is an example:
CREATE FUNCTION fnConstant()
RETURNS INT
AS
BEGIN
RETURN 2
END
GO
SELECT dbo.fnConstant()
Solution 2 - Sql Server
One solution, offered by Jared Ko is to use pseudo-constants.
As explained in SQL Server: Variables, Parameters or Literals? Or… Constants?:
> Pseudo-Constants are not variables or parameters. Instead, they're simply views with one row, and enough columns to support your constants. With these simple rules, the SQL Engine completely ignores the value of the view but still builds an execution plan based on its value. The execution plan doesn't even show a join to the view!
>
> Create like this:
>
> sql > CREATE SCHEMA ShipMethod > GO > -- Each view can only have one row. > -- Create one column for each desired constant. > -- Each column is restricted to a single value. > CREATE VIEW ShipMethod.ShipMethodID 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] >
>
> Then use like this:
> sql > SELECT h.* > FROM Sales.SalesOrderHeader h > JOIN ShipMethod.ShipMethodID const > ON h.ShipMethodID = const.[OVERNIGHT J-FAST] >
>
> Or like this:
>
> sql > SELECT h.* > FROM Sales.SalesOrderHeader h > WHERE h.ShipMethodID = (SELECT TOP 1 [OVERNIGHT J-FAST] FROM ShipMethod.ShipMethodID) >
Solution 3 - Sql Server
My workaround to missing constans is to give hints about the value 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 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.
Solution 4 - Sql Server
No, but good old naming conventions should be used.
declare @MY_VALUE as int
Solution 5 - Sql Server
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, as suggested over here. Perhaps write a trigger that rolls back any changes to the ConstantValue
column?
Solution 6 - Sql Server
Prior to using a SQL function run the following script to see the differences in performance:
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
Solution 7 - Sql Server
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.
DECLARE @var varchar(100) = 'some text'
DECLARE @sql varchar(MAX)
SET @sql = 'SELECT * FROM table WHERE col = '''+@var+''''
EXEC (@sql)
Solution 8 - Sql Server
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 https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/
create the view
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]
use the view
SELECT h.*
FROM Sales.SalesOrderHeader
WHERE ShipMethodID = ( select [OVERNIGHT J-FAST] from ShipMethods )
Solution 9 - Sql Server
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
declare @myvalue as int
set @myvalue = 5
set @myvalue = 10--oops we just changed it
the value just changed
Solution 10 - Sql Server
Since there is no build in support for constants, my solution is very simple.
Since this is not supported:
Declare Constant @supplement int = 240
SELECT price + @supplement
FROM what_does_it_cost
I would simply convert it to
SELECT price + 240/*CONSTANT:supplement*/
FROM what_does_it_cost
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.
Solution 11 - Sql Server
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:
DECLARE @two INT
SET @two = 2
Here @two is a variable and 2 is a value/constant.
Solution 12 - Sql Server
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:
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