TSQL 변수를 일정하게 하는 방법이 있습니까?

programing

TSQL 변수를 일정하게 하는 방법이 있습니까?

lastmoon 2023. 4. 17. 22:16

TSQL 변수를 일정하게 하는 방법이 있습니까?

아니요, 하지만 함수를 만들고 거기서 하드코드를 해서 사용할 수 있습니다.

다음은 예를 제시하겠습니다.

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

SELECT dbo.fnConstant()

Jared Ko가 제안한 해결책 중 하나는 의사 상수(pseud-constants)를 사용하는 것입니다.

SQL Server에서 설명한 바와 같이 변수, 파라미터 또는 리터럴? 또는 상수:

유사 상수는 변수나 모수가 아닙니다.대신 하나의 행과 상수를 지원하기에 충분한 열이 있는 보기입니다.이러한 간단한 규칙을 사용하면 SQL Engine은 뷰의 값을 완전히 무시하면서도 뷰의 값을 기반으로 실행 계획을 수립합니다.실행 계획에는 뷰에 대한 결합도 표시되지 않습니다.

다음과 같이 작성합니다.
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)

Constan 누락에 대한 해결 방법은 최적기에 값에 대한 힌트를 제공하는 것입니다.

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)

enum 또는 단순 상수의 경우 행이 1개인 뷰는 뛰어난 퍼포먼스와 컴파일 시간 체크/의존성 트래킹(컬럼 이름 발생)이 있습니다.

Jared Ko의 블로그 투고를 참조하십시오.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  )

좋아, 어디 보자

상수는 컴파일 시 알려진 불변의 값이며 프로그램 수명 동안 변경되지 않습니다.

즉, SQL Server에서는 상수를 설정할 수 없습니다.

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

값이 방금 바뀌었다

상수에 대한 지원이 구축되어 있지 않기 때문에 솔루션은 매우 간단합니다.

이 기능은 지원되지 않으므로:

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

간단하게 변환해서

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

분명히 이것은 전체(후행 공백이 없는 값 및 주석)가 고유해야 합니다.글로벌 검색 및 치환을 통해 변경할 수 있습니다.

데이터베이스 문헌에는 "상수 생성"과 같은 것은 없습니다.상수는 그대로 존재하며 종종 값이라고 합니다.변수를 선언하고 값(상수)을 할당할 수 있습니다.스콜라적인 관점에서 보면:

DECLARE @two INT
SET @two = 2

여기서 @2는 변수, 2는 값/상수입니다.

SQLServer 2022(현재는 Preview만 사용 가능)는 SQLMenace에서 제안하는 함수를 인라인화할 수 있게 되었습니다.이것에 의해, 코멘트에 의해서 설명되고 있는 퍼포먼스에의 타격을 막을 수 있습니다.

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

SELECT is_inlineable FROM sys.sql_modules WHERE [object_id]=OBJECT_ID('dbo.fnConstant');

실행 가능한
1

SELECT dbo.fnConstant()

실행 계획

함수에서 나오는 값도 사용하는지 테스트하기 위해 두 번째 함수 반환 값 "1"을 추가했습니다.

CREATE FUNCTION fnConstant1()
RETURNS INT
AS
BEGIN
    RETURN 1
END
GO

값이 1인 행과 값이 2인 행이 약 500k인 임시 테이블을 만듭니다.

DROP TABLE IF EXISTS #temp ;  
create table #temp (value_int INT)  
DECLARE @counter INT;  
SET @counter = 0  
WHILE @counter <= 500000  
BEGIN  
    INSERT INTO #temp VALUES (1);  
    SET @counter = @counter +1  
END  
SET @counter = 0
  
WHILE @counter <= 3  
BEGIN  
    INSERT INTO #temp VALUES (2);
    SET @counter = @counter +1
END
create index i_temp on #temp (value_int);

는 Optimizer에 대해 Optimizer에 50만 을 알 수 .
select * from #temp where value_int = dbo.fnConstant1(); --Returns 500001 rows 1 수 11

의 경우
select * from #temp where value_int = dbo.fnConstant(); --Returns 4rows
2수 2 2

로버트의 수행 테스트는 흥미롭다.그리고 2022년 후반에도 스칼라 함수는 변수나 리터럴보다 훨씬 느립니다(규모순으로).뷰(권장된 mbobka)는 이 같은 테스트에 사용할 때 중간 위치에 있습니다.

단, SQL Server에서 이와 같은 루프를 사용하는 것은 보통 전체 세트에서 작동하기 때문에 수행할 수 있는 작업이 아닙니다.

SQL 2019에서 스키마 바인딩 함수를 집합 작업에서 사용하면 차이가 훨씬 덜 눈에 띕니다.

테스트 테이블을 만들고 입력했습니다.

create table #testTable (id int identity(1, 1) primary key, value tinyint);

또한 변수를 루프하거나 변경하는 대신 테스트 테이블을 쿼리하고 테스트 테이블의 값에 따라 true 또는 false를 반환하도록 테스트를 변경했습니다.예를 들어 다음과 같습니다.

insert @testTable(value)
select case when value > 127
    then @FALSE
    else @TRUE
end
from #testTable with(nolock)

5가지 시나리오를 테스트했습니다.

하드 코드화된 가치관
지역 변수
스칼라 함수
경치
테이블 값 함수

테스트를 10회 실행한 결과 다음과 같은 결과가 나왔습니다.

시나리오	분	맥스.	평균
스칼라 함수	233	259	240
하드 코드화된 가치관	236	265	243
지역 변수	235	278	245
테이블 값 함수	243	272	253
보다	244	267	254

2019년 이후 세트 베이스 작업은 별로 없습니다.

set nocount on;
go

-- create test data table
drop table if exists #testTable;
create table #testTable (id int identity(1, 1) primary key, value tinyint);

-- populate test data
insert #testTable (value)
select top (1000000) convert(binary (1), newid()) 
from sys.all_objects a
    , sys.all_objects b
go

-- scalar function for True
drop function if exists fnTrue;
go
create function dbo.fnTrue() returns bit with schemabinding as
begin
    return 1
end
go

-- scalar function for False
drop function if exists fnFalse;
go
create function dbo.fnFalse () returns bit with schemabinding as
begin
    return 0
end
go

-- table-valued function for booleans
drop function if exists dbo.tvfBoolean;
go
create function tvfBoolean() returns table with schemabinding as
return
    select convert(bit, 1) as true, convert(bit, 0) as false
go

-- view for booleans
drop view if exists dbo.viewBoolean;
go
create view dbo.viewBoolean with schemabinding as
    select convert(bit, 1) as true, convert(bit, 0) as false
go

-- create table for results
drop table if exists #testResults
create table #testResults (id int identity(1,1), test int, elapsed bigint, message varchar(1000));

-- define tests
declare @tests table(testNumber int, description nvarchar(100), sql nvarchar(max))
insert @tests values 
  (1, N'hard-coded values', N'
declare @testTable table (id int, value bit);
insert @testTable(id, value)
select id, case when t.value > 127 
    then 0 
    else 1
end
from #testTable t')
, (2, N'local variables', N'
declare @FALSE as bit = 0
declare @TRUE as bit = 1
declare @testTable table (id int, value bit);
insert @testTable(id, value)
select id, case when t.value > 127 
    then @FALSE
    else @TRUE
end
from #testTable t'), 
(3, N'scalar functions', N'
declare @testTable table (id int, value bit);
insert @testTable(id, value)
select id, case when t.value > 127 
    then dbo.fnFalse()
    else dbo.fnTrue()
end
from #testTable t'), 
(4, N'view', N'
declare @testTable table (id int, value bit);
insert @testTable(id, value)
select id, case when value > 127
    then b.false
    else b.true
end
from #testTable t with(nolock), viewBoolean b'),
(5, N'table-valued function', N'
declare @testTable table (id int, value bit);
insert @testTable(id, value)
select id, case when value > 127
    then b.false
    else b.true
end
from #testTable with(nolock), dbo.tvfBoolean() b')
;

declare @testNumber int, @description varchar(100), @sql nvarchar(max)

declare @testRuns int = 10;

-- execute tests
while @testRuns > 0 begin
    set @testRuns -= 1

    declare testCursor cursor for select testNumber, description, sql from @tests;
    open testCursor

    fetch next from testCursor into @testNumber, @description, @sql
    while @@FETCH_STATUS = 0 begin
        
        declare @TimeStart datetime2(7) = sysdatetime();

        execute sp_executesql @sql;

        declare @TimeEnd datetime2(7) = sysdatetime()

        insert #testResults(test, elapsed, message) 
        select @testNumber, datediff_big(ms, @TimeStart, @TimeEnd), @description

        fetch next from testCursor into @testNumber, @description, @sql
    end 
    close testCursor
    deallocate testCursor
end


-- display results
select test, message, count(*) runs, min(elapsed) as min, max(elapsed) as max, avg(elapsed) as avg
from #testResults
group by test, message
order by avg(elapsed);

스크립트 내에서 사용하기 위한 임시 상수(즉, 여러 GO 문/배치)를 작성하는 경우 요건에 따라 SQLMenace를 선택하는 것이 가장 좋습니다.

tempdb에 프로시저를 작성하기만 하면 타깃 데이터베이스에 영향을 주지 않습니다.

예를 들어 데이터베이스 작성 스크립트는 논리 스키마버전을 포함하는 제어값을 스크립트의 끝에 씁니다.파일 맨 위에는 변경 내역 등이 있는 코멘트가 몇 개 있습니다.그러나 실제로는 대부분의 개발자가 파일 하단의 스키마 버전을 아래로 스크롤하여 업데이트하는 것을 잊게 됩니다.

위의 코드를 사용하면 (SSMS의 generate scripts 기능에서 복사된) 데이터베이스 스크립트가 데이터베이스를 작성하기 전에 맨 위에 표시되는 스키마 버전 상수를 정의할 수 있습니다.이것은 변경 이력이나 다른 코멘트 옆에 있는 개발자의 면전에 있기 때문에 갱신할 가능성이 매우 높습니다.

예를 들어 다음과 같습니다.

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

언급URL : https://stackoverflow.com/questions/26652/is-there-a-way-to-make-a-tsql-variable-constant

'programing' 카테고리의 다른 글

XmlSerializer에서 오류가 발생한 이유는 무엇입니까? (0)	2023.04.17
Xcode 9의 안전 영역 (0)	2023.04.17
한 목록의 모든 값을 다른 목록에서 제거하시겠습니까? (0)	2023.04.17
WPF 응용 프로그램을 Administrator 모드로 강제 실행하는 방법 (0)	2023.04.17
PyWin32를 시작하려면 어떻게 해야 하나요? (0)	2023.04.17

현재글TSQL 변수를 일정하게 하는 방법이 있습니까?

각종 프로그래밍 정보를 다루는 블로그입니다.

jquery, ReactJS, json, sql-server, mongodb, spring-boot, Oracle, WordPress, Excel, Android, ajax, PowerShell, Python, MariaDB, asp.net, CSS, angularjs, spring, C, git,

Today :
Yesterday :

lastmoon

TSQL 변수를 일정하게 하는 방법이 있습니까?

TSQL 변수를 일정하게 하는 방법이 있습니까?

'programing' 카테고리의 다른 글

'programing'의 다른글

티스토리툴바

« 2024/12 »
일	월	화	수	목	금	토
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28
29	30	31

TSQL 변수를 일정하게 하는 방법이 있습니까?

TSQL 변수를 일정하게 하는 방법이 있습니까?

'programing' 카테고리의 다른 글

'programing'의 다른글

관련글

티스토리툴바