[sql-server] TSQL 변수를 상수로 만드는 방법이 있습니까?

Program / 글쓴이

TSQL 변수를 상수로 만드는 방법이 있습니까?

답변

아니요,하지만 함수를 생성하고 거기에 하드 코딩하여 사용할 수 있습니다.

다음은 그 예입니다.

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

SELECT dbo.fnConstant()

답변

Jared Ko가 제공하는 한 가지 해결책은 pseudo-constants 를 사용하는 것 입니다.

SQL Server에 설명 된대로 : 변수, 매개 변수 또는 리터럴? 아니면 … 상수? :

의사 상수는 변수 나 매개 변수가 아닙니다. 대신 단순히 하나의 행과 상수를 지원하기에 충분한 열이있는보기입니다. 이러한 간단한 규칙을 사용하면 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]

그런 다음 다음과 같이 사용하십시오.

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

또는 다음과 같이 :

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

답변

상수 누락에 대한 내 해결 방법은 옵티 마이저에 값에 대한 힌트를 제공하는 것입니다.

DECLARE @Constant INT = 123;

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

이것은 실행 계획을 생성 할 때 변수를 상수 인 것처럼 처리하도록 쿼리 컴파일러에 지시합니다. 단점은 값을 두 번 정의해야한다는 것입니다.

답변

아니요,하지만 좋은 오래된 명명 규칙을 사용해야합니다.

declare @MY_VALUE as int

답변

T-SQL에는 상수에 대한 기본 제공 지원이 없습니다. SQLMenace의 접근 방식을 사용하여 시뮬레이션하거나 (다른 사람이 다른 것을 반환하기 위해 함수를 덮어 썼는지 여부를 확신 할 수는 없지만…) 여기에 제안 된대로 상수를 포함하는 테이블을 작성할 수 있습니다 . ConstantValue열에 대한 변경 사항을 롤백하는 트리거를 작성할 수 있습니까?

답변

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 코드를 사용할 수 있습니다. 변수를 일정하게 만듭니다.

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