<p>Is there a way to make a TSQL variable constant?</p>

[{'answer_id': 26655, 'author': 'Greg Hurlman', 'author_id': 35, 'author_profile': 'https://Stackoverflow.com/users/35', 'pm_score': 0, 'selected': False, 'text': '<p>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:</p>\n\n<pre><code>DECLARE @two INT\nSET @two = 2\n</code></pre>\n\n<p>Here @two is a variable and 2 is a value/constant.</p>\n'}, {'answer_id': 26656, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 7, 'selected': True, 'text': '<p>No, but you can create a function and hardcode it in there and use that.</p>\n\n<p>Here is an example:</p>\n\n<pre><code>CREATE FUNCTION fnConstant()\nRETURNS INT\nAS\nBEGIN\n    RETURN 2\nEND\nGO\n\nSELECT dbo.fnConstant()\n</code></pre>\n'}, {'answer_id': 26673, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 3, 'selected': False, 'text': '<p>No, but good old naming conventions should be used.</p>\n\n<pre><code>declare @MY_VALUE as int\n</code></pre>\n'}, {'answer_id': 26675, 'author': 'SQLMenace', 'author_id': 740, 'author_profile': 'https://Stackoverflow.com/users/740', 'pm_score': 2, 'selected': False, 'text': '<p>Okay, lets see</p>\n\n<p>Constants are immutable values which are known at compile time and do not change for the life of the program</p>\n\n<p>that means you can never have a constant in SQL Server</p>\n\n<pre><code>declare @myvalue as int\nset @myvalue = 5\nset @myvalue = 10--oops we just changed it\n</code></pre>\n\n<p>the value just changed</p>\n'}, {'answer_id': 26676, 'author': 'Sören Kuklau', 'author_id': 1600, 'author_profile': 'https://Stackoverflow.com/users/1600', 'pm_score': 3, 'selected': False, 'text': '<p>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?</p>\n'}, {'answer_id': 12723310, 'author': 'John Nilsson', 'author_id': 24243, 'author_profile': 'https://Stackoverflow.com/users/24243', 'pm_score': 5, 'selected': False, 'text': '<p>My workaround to missing constans is to give hints about the value to the optimizer.</p>\n\n<pre><code>DECLARE @Constant INT = 123;\n\nSELECT * \nFROM [some_relation] \nWHERE [some_attribute] = @Constant\nOPTION( OPTIMIZE FOR (@Constant = 123))\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 13685800, 'author': 'Tony Wall', 'author_id': 1080914, 'author_profile': 'https://Stackoverflow.com/users/1080914', 'pm_score': -1, 'selected': False, 'text': "<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.</p>\n\n<p>Just create the procedure in the tempdb then you have no impact on the target database.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>For example:</p>\n\n<pre><code>use tempdb\ngo\ncreate function dbo.MySchemaVersion()\nreturns int\nas\nbegin\n    return 123\nend\ngo\n\nuse master\ngo\n\n-- Big long database create script with multiple batches...\nprint 'Creating database schema version ' + CAST(tempdb.dbo.MySchemaVersion() as NVARCHAR) + '...'\ngo\n-- ...\ngo\n-- ...\ngo\nuse MyDatabase\ngo\n\n-- Update schema version with constant at end (not normally possible as GO puts\n-- local @variables out of scope)\ninsert MyConfigTable values ('SchemaVersion', tempdb.dbo.MySchemaVersion())\ngo\n\n-- Clean-up\nuse tempdb\ndrop function MySchemaVersion\ngo\n</code></pre>\n"}, {'answer_id': 14096239, 'author': 'Robert', 'author_id': 1938754, 'author_profile': 'https://Stackoverflow.com/users/1938754', 'pm_score': 3, 'selected': False, 'text': "<p>Prior to using a SQL function run the following script to see the differences in performance:</p>\n\n<pre><code>IF OBJECT_ID('fnFalse') IS NOT NULL\nDROP FUNCTION fnFalse\nGO\n\nIF OBJECT_ID('fnTrue') IS NOT NULL\nDROP FUNCTION fnTrue\nGO\n\nCREATE FUNCTION fnTrue() RETURNS INT WITH SCHEMABINDING\nAS\nBEGIN\nRETURN 1\nEND\nGO\n\nCREATE FUNCTION fnFalse() RETURNS INT WITH SCHEMABINDING\nAS\nBEGIN\nRETURN ~ dbo.fnTrue()\nEND\nGO\n\nDECLARE @TimeStart DATETIME = GETDATE()\nDECLARE @Count INT = 100000\nWHILE @Count &gt; 0 BEGIN\nSET @Count -= 1\n\nDECLARE @Value BIT\nSELECT @Value = dbo.fnTrue()\nIF @Value = 1\n    SELECT @Value = dbo.fnFalse()\nEND\nDECLARE @TimeEnd DATETIME = GETDATE()\nPRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using function'\nGO\n\nDECLARE @TimeStart DATETIME = GETDATE()\nDECLARE @Count INT = 100000\nDECLARE @FALSE AS BIT = 0\nDECLARE @TRUE AS BIT = ~ @FALSE\n\nWHILE @Count &gt; 0 BEGIN\nSET @Count -= 1\n\nDECLARE @Value BIT\nSELECT @Value = @TRUE\nIF @Value = 1\n    SELECT @Value = @FALSE\nEND\nDECLARE @TimeEnd DATETIME = GETDATE()\nPRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using local variable'\nGO\n\nDECLARE @TimeStart DATETIME = GETDATE()\nDECLARE @Count INT = 100000\n\nWHILE @Count &gt; 0 BEGIN\nSET @Count -= 1\n\nDECLARE @Value BIT\nSELECT @Value = 1\nIF @Value = 1\n    SELECT @Value = 0\nEND\nDECLARE @TimeEnd DATETIME = GETDATE()\nPRINT CAST(DATEDIFF(ms, @TimeStart, @TimeEnd) AS VARCHAR) + ' elapsed, using hard coded values'\nGO\n</code></pre>\n"}, {'answer_id': 32373858, 'author': 'mbobka', 'author_id': 2201119, 'author_profile': 'https://Stackoverflow.com/users/2201119', 'pm_score': 5, 'selected': False, 'text': '<p>One solution, offered by Jared Ko is to use <strong>pseudo-constants</strong>.</p>\n<p>As explained in <a href="https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/" rel="noreferrer">SQL Server: Variables, Parameters or Literals? Or… Constants?</a>:</p>\n<blockquote>\n<p>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!</p>\n<p>Create like this:</p>\n<pre class="lang-sql prettyprint-override"><code>CREATE SCHEMA ShipMethod\nGO\n-- Each view can only have one row.\n-- Create one column for each desired constant.\n-- Each column is restricted to a single value.\nCREATE VIEW ShipMethod.ShipMethodID AS\nSELECT CAST(1 AS INT) AS [XRQ - TRUCK GROUND]\n      ,CAST(2 AS INT) AS [ZY - EXPRESS]\n      ,CAST(3 AS INT) AS [OVERSEAS - DELUXE]\n      ,CAST(4 AS INT) AS [OVERNIGHT J-FAST]\n      ,CAST(5 AS INT) AS [CARGO TRANSPORT 5]\n</code></pre>\n<p>Then use like this:</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT h.*\nFROM Sales.SalesOrderHeader h\nJOIN ShipMethod.ShipMethodID const\n    ON h.ShipMethodID = const.[OVERNIGHT J-FAST]\n</code></pre>\n<p>Or like this:</p>\n<pre class="lang-sql prettyprint-override"><code>SELECT h.*\nFROM Sales.SalesOrderHeader h\nWHERE h.ShipMethodID = (SELECT TOP 1 [OVERNIGHT J-FAST] FROM ShipMethod.ShipMethodID)\n</code></pre>\n</blockquote>\n'}, {'answer_id': 39659808, 'author': 'Michal D.', 'author_id': 2150054, 'author_profile': 'https://Stackoverflow.com/users/2150054', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<pre><code>DECLARE @var varchar(100) = 'some text'\nDECLARE @sql varchar(MAX)\nSET @sql = 'SELECT * FROM table WHERE col = '''+@var+''''\nEXEC (@sql)\n</code></pre>\n"}, {'answer_id': 48013270, 'author': 'monkeyhouse', 'author_id': 1778606, 'author_profile': 'https://Stackoverflow.com/users/1778606', 'pm_score': 3, 'selected': False, 'text': '<p>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 )</p>\n\n<p>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></p>\n\n<p>create the view</p>\n\n<pre><code> CREATE VIEW ShipMethods AS\n SELECT CAST(1 AS INT) AS [XRQ - TRUCK GROUND]\n   ,CAST(2 AS INT) AS [ZY - EXPRESS]\n   ,CAST(3 AS INT) AS [OVERSEAS - DELUXE]\n  , CAST(4 AS INT) AS [OVERNIGHT J-FAST]\n   ,CAST(5 AS INT) AS [CARGO TRANSPORT 5]\n</code></pre>\n\n<p>use the view</p>\n\n<pre><code>SELECT h.*\nFROM Sales.SalesOrderHeader \nWHERE ShipMethodID = ( select [OVERNIGHT J-FAST] from ShipMethods  )\n</code></pre>\n'}, {'answer_id': 48025646, 'author': 'Gert-Jan', 'author_id': 8992916, 'author_profile': 'https://Stackoverflow.com/users/8992916', 'pm_score': 1, 'selected': False, 'text': '<p>Since there is no build in support for constants, my solution is very simple.</p>\n\n<p>Since this is not supported:</p>\n\n<pre><code>Declare Constant @supplement int = 240\nSELECT price + @supplement\nFROM   what_does_it_cost\n</code></pre>\n\n<p>I would simply convert it to</p>\n\n<pre><code>SELECT price + 240/*CONSTANT:supplement*/\nFROM   what_does_it_cost\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 74219496, 'author': 'JensG', 'author_id': 19150755, 'author_profile': 'https://Stackoverflow.com/users/19150755', 'pm_score': 0, 'selected': False, 'text': '<p>SQLServer 2022 (currently only as Preview available) is now able to Inline the function proposed by SQLMenace, this should prevent the performance hit described by some comments.</p>\n<p><code>CREATE FUNCTION fnConstant() RETURNS INT AS BEGIN RETURN 2 END GO</code></p>\n<p><code>SELECT is_inlineable FROM sys.sql_modules WHERE [object_id]=OBJECT_ID(\'dbo.fnConstant\');</code></p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th>is_inlineable</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td>1</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p><code>SELECT dbo.fnConstant()</code></p>\n<p><a href="https://i.stack.imgur.com/3SMqo.png" rel="nofollow noreferrer">ExecutionPlan</a></p>\n<hr />\n<p>To test if it also uses the value coming from the Function, I added a second function returning value &quot;1&quot;</p>\n<pre><code>CREATE FUNCTION fnConstant1()\nRETURNS INT\nAS\nBEGIN\n    RETURN 1\nEND\nGO\n</code></pre>\n<p>Create Temp Table with about 500k rows with Value 1 and 4 rows with Value 2:</p>\n<pre><code>DROP TABLE IF EXISTS #temp ;  \ncreate table #temp (value_int INT)  \nDECLARE @counter INT;  \nSET @counter = 0  \nWHILE @counter &lt;= 500000  \nBEGIN  \n    INSERT INTO #temp VALUES (1);  \n    SET @counter = @counter +1  \nEND  \nSET @counter = 0\n  \nWHILE @counter &lt;= 3  \nBEGIN  \n    INSERT INTO #temp VALUES (2);\n    SET @counter = @counter +1\nEND\ncreate index i_temp on #temp (value_int);\n</code></pre>\n<p>Using the describe plan we can see that the Optimizer expects 500k values for<br />\n<code>select * from #temp where value_int = dbo.fnConstant1(); --Returns 500001 rows</code>\n<a href="https://i.stack.imgur.com/MDW4T.png" rel="nofollow noreferrer">Constant 1</a></p>\n<p>and 4 rows for<br />\n<code>select * from #temp where value_int = dbo.fnConstant(); --Returns 4rows</code><br />\n<a href="https://i.stack.imgur.com/Vu8T3.png" rel="nofollow noreferrer">Constant 2</a></p>\n'}, {'answer_id': 74360479, 'author': 'pricerc', 'author_id': 2258866, 'author_profile': 'https://Stackoverflow.com/users/2258866', 'pm_score': 0, 'selected': False, 'text': '<p>Robert\'s performance test is interesting. And even in late 2022, the scalar functions are much slower (by an order of magnitude) than variables or literals. A view (as suggested mbobka) is somewhere in-between when used for this same test.</p>\n<p>That said, using a loop like that in SQL Server is not something I\'d ever do, because I\'d normally be operating on a whole set.</p>\n<p>In SQL 2019, if you use schema-bound functions in a set operation, the difference is much less noticeable.</p>\n<p>I created and populated a test table:</p>\n<pre class="lang-sql prettyprint-override"><code>create table #testTable (id int identity(1, 1) primary key, value tinyint);\n</code></pre>\n<p>And changed the test so that instead of looping and changing a variable, it queries the test table and returns true or false depending on the value in the test table, e.g.:</p>\n<pre class="lang-sql prettyprint-override"><code>insert @testTable(value)\nselect case when value &gt; 127\n    then @FALSE\n    else @TRUE\nend\nfrom #testTable with(nolock)\n</code></pre>\n<p>I tested 5 scenarios:</p>\n<ol>\n<li>hard-coded values</li>\n<li>local variables</li>\n<li>scalar functions</li>\n<li>a view</li>\n<li>a table-valued function</li>\n</ol>\n<p>running the test 10 times, yielded the following results:</p>\n<div class="s-table-container">\n<table class="s-table">\n<thead>\n<tr>\n<th style="text-align: left;">scenario</th>\n<th style="text-align: center;">min</th>\n<th style="text-align: center;">max</th>\n<th style="text-align: center;">avg</th>\n</tr>\n</thead>\n<tbody>\n<tr>\n<td style="text-align: left;">scalar functions</td>\n<td style="text-align: center;">233</td>\n<td style="text-align: center;">259</td>\n<td style="text-align: center;">240</td>\n</tr>\n<tr>\n<td style="text-align: left;">hard-coded values</td>\n<td style="text-align: center;">236</td>\n<td style="text-align: center;">265</td>\n<td style="text-align: center;">243</td>\n</tr>\n<tr>\n<td style="text-align: left;">local variables</td>\n<td style="text-align: center;">235</td>\n<td style="text-align: center;">278</td>\n<td style="text-align: center;">245</td>\n</tr>\n<tr>\n<td style="text-align: left;">table-valued function</td>\n<td style="text-align: center;">243</td>\n<td style="text-align: center;">272</td>\n<td style="text-align: center;">253</td>\n</tr>\n<tr>\n<td style="text-align: left;">view</td>\n<td style="text-align: center;">244</td>\n<td style="text-align: center;">267</td>\n<td style="text-align: center;">254</td>\n</tr>\n</tbody>\n</table>\n</div>\n<p>Suggesting to me, that for set-based work in (at least) 2019 and better, there\'s not much in it.</p>\n<pre class="lang-sql prettyprint-override"><code>set nocount on;\ngo\n\n-- create test data table\ndrop table if exists #testTable;\ncreate table #testTable (id int identity(1, 1) primary key, value tinyint);\n\n-- populate test data\ninsert #testTable (value)\nselect top (1000000) convert(binary (1), newid()) \nfrom sys.all_objects a\n    , sys.all_objects b\ngo\n\n-- scalar function for True\ndrop function if exists fnTrue;\ngo\ncreate function dbo.fnTrue() returns bit with schemabinding as\nbegin\n    return 1\nend\ngo\n\n-- scalar function for False\ndrop function if exists fnFalse;\ngo\ncreate function dbo.fnFalse () returns bit with schemabinding as\nbegin\n    return 0\nend\ngo\n\n-- table-valued function for booleans\ndrop function if exists dbo.tvfBoolean;\ngo\ncreate function tvfBoolean() returns table with schemabinding as\nreturn\n    select convert(bit, 1) as true, convert(bit, 0) as false\ngo\n\n-- view for booleans\ndrop view if exists dbo.viewBoolean;\ngo\ncreate view dbo.viewBoolean with schemabinding as\n    select convert(bit, 1) as true, convert(bit, 0) as false\ngo\n\n-- create table for results\ndrop table if exists #testResults\ncreate table #testResults (id int identity(1,1), test int, elapsed bigint, message varchar(1000));\n\n-- define tests\ndeclare @tests table(testNumber int, description nvarchar(100), sql nvarchar(max))\ninsert @tests values \n  (1, N\'hard-coded values\', N\'\ndeclare @testTable table (id int, value bit);\ninsert @testTable(id, value)\nselect id, case when t.value &gt; 127 \n    then 0 \n    else 1\nend\nfrom #testTable t\')\n, (2, N\'local variables\', N\'\ndeclare @FALSE as bit = 0\ndeclare @TRUE as bit = 1\ndeclare @testTable table (id int, value bit);\ninsert @testTable(id, value)\nselect id, case when t.value &gt; 127 \n    then @FALSE\n    else @TRUE\nend\nfrom #testTable t\'), \n(3, N\'scalar functions\', N\'\ndeclare @testTable table (id int, value bit);\ninsert @testTable(id, value)\nselect id, case when t.value &gt; 127 \n    then dbo.fnFalse()\n    else dbo.fnTrue()\nend\nfrom #testTable t\'), \n(4, N\'view\', N\'\ndeclare @testTable table (id int, value bit);\ninsert @testTable(id, value)\nselect id, case when value &gt; 127\n    then b.false\n    else b.true\nend\nfrom #testTable t with(nolock), viewBoolean b\'),\n(5, N\'table-valued function\', N\'\ndeclare @testTable table (id int, value bit);\ninsert @testTable(id, value)\nselect id, case when value &gt; 127\n    then b.false\n    else b.true\nend\nfrom #testTable with(nolock), dbo.tvfBoolean() b\')\n;\n\ndeclare @testNumber int, @description varchar(100), @sql nvarchar(max)\n\ndeclare @testRuns int = 10;\n\n-- execute tests\nwhile @testRuns &gt; 0 begin\n    set @testRuns -= 1\n\n    declare testCursor cursor for select testNumber, description, sql from @tests;\n    open testCursor\n\n    fetch next from testCursor into @testNumber, @description, @sql\n    while @@FETCH_STATUS = 0 begin\n        \n        declare @TimeStart datetime2(7) = sysdatetime();\n\n        execute sp_executesql @sql;\n\n        declare @TimeEnd datetime2(7) = sysdatetime()\n\n        insert #testResults(test, elapsed, message) \n        select @testNumber, datediff_big(ms, @TimeStart, @TimeEnd), @description\n\n        fetch next from testCursor into @testNumber, @description, @sql\n    end \n    close testCursor\n    deallocate testCursor\nend\n\n\n-- display results\nselect test, message, count(*) runs, min(elapsed) as min, max(elapsed) as max, avg(elapsed) as avg\nfrom #testResults\ngroup by test, message\norder by avg(elapsed);\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/26652', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1874/']