Есть ли способ сделать переменную TSQL постоянной?

В литературе по базам данных нет такого понятия, как «создание константы». Константы существуют как есть и часто называются значениями. Можно объявить переменную и присвоить ей значение (константу). Со схоластической точки зрения:

DECLARE @two INT
SET @two = 2

Здесь @two - переменная, а 2 - значение / константа.

Нет, но вы можете создать функцию, жестко закодировать ее и использовать.

Вот пример:

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

SELECT dbo.fnConstant()

Нет, но следует использовать старые добрые соглашения об именах.

declare @MY_VALUE as int

Хорошо, давай посмотрим

Константы - это неизменяемые значения, которые известны во время компиляции и не меняются в течение жизни программы.

это означает, что у вас никогда не может быть константы в SQL Server

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

значение только что изменилось

В T-SQL нет встроенной поддержки констант. Вы можете использовать подход SQLMenace для моделирования этого (хотя вы никогда не можете быть уверены, что кто-то перезаписал функцию, чтобы вернуть что-то еще…), или, возможно, написать таблицу, содержащую константы, как предлагается здесь. Может быть, написать триггер, который откатывает любые изменения в столбце ConstantValue?

Мой обходной путь к отсутствию констант - дать подсказку о значении оптимизатору.

DECLARE @Constant INT = 123;

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

Это указывает компилятору запроса обрабатывать переменную, как если бы она была константой при создании плана выполнения. Обратной стороной является то, что вам нужно определить значение дважды.

Лучший ответ - от SQLMenace в соответствии с требованием, если это необходимо для создания временной константы для использования в сценариях, то есть в нескольких операторах / пакетах GO.

Просто создайте процедуру в базе данных tempdb, тогда вы не окажете никакого влияния на целевую базу данных.

Одним из практических примеров этого является сценарий создания базы данных, который записывает контрольное значение в конце сценария, содержащего версию логической схемы. Вверху файла есть комментарии с историей изменений и т. д. Но на практике большинство разработчиков забывают прокрутить вниз и обновить версию схемы внизу файла.

Использование приведенного выше кода позволяет определить видимую константу версии схемы вверху до того, как сценарий базы данных (скопированный из функции создания сценариев SSMS) создаст базу данных, но будет использоваться в конце. Это прямо на глазах разработчика рядом с историей изменений и другими комментариями, поэтому они, скорее всего, обновят ее.

Например:

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

Перед использованием функции SQL запустите следующий сценарий, чтобы увидеть разницу в производительности:

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

Одним из решений, предложенных Джаредом Ко, является использование псевдоконстанты.

Как поясняется в SQL Server: переменные, параметры или литералы? Или… константы?:

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:

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:

SELECT h.*
FROM Sales.SalesOrderHeader h
JOIN ShipMethod.ShipMethodID const
    ON h.ShipMethodID = const.[OVERNIGHT J-FAST]

Or like this:

SELECT h.*
FROM Sales.SalesOrderHeader h
WHERE h.ShipMethodID = (SELECT TOP 1 [OVERNIGHT J-FAST] FROM ShipMethod.ShipMethodID)

Если вы заинтересованы в получении оптимального плана выполнения для значения в переменной, вы можете использовать динамический код sql. Это делает переменную постоянной.

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

Для перечислений или простых констант представление с одной строкой имеет отличную производительность и проверку времени компиляции / отслеживание зависимостей (потому что это имя столбца)

См. Сообщение в блоге Джареда Ко https://blogs.msdn.microsoft.com/sql_server_appendix_z/2013/09/16/sql-server-variables-parameters-or-literals-or-constants/

создать представление

 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]

использовать вид

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

Поскольку нет встроенной поддержки констант, мое решение очень простое.

Поскольку это не поддерживается:

Declare Constant @supplement int = 240
SELECT price + @supplement
FROM   what_does_it_cost

Я бы просто преобразовал его в

SELECT price + 240/*CONSTANT:supplement*/
FROM   what_does_it_cost

Очевидно, это зависит от уникальности всего объекта (значения без конечного пробела и комментария). Изменить его можно с помощью глобального поиска и замены.